Category: Articles
-
MySQL DBA for non-DBAs – Why do I need replication?
Unless you are working on a small, internal application for an organisation, eventually you’re going to have to consider how to scale your database service to handle more traffic. If you consult the Internet for advice, the two most frequent answers you’ll get will be “Use {other system} because it is WEB SCALE” and “Set…
-
MySQL DBA for non-DBAs – What is replication?
Databases are inherent parts of most applications these days, and are usually one of the most critical parts – if the database fails, the rest of the system usually fails as well. Common advice is to use database replication to mitigate failures in the database layer, but it’s then left as an exercise for the…
-
Fast track to failure: GRANT ALL ON schema.* TO…
It’s depressingly common to see (mainly web) applications that, as part of their installation procedure, prompt the user to enter a set of credentials in order to build or upgrade the database schema. On the face of it, this seems to be an essential requirement, but the problems start when these credentials are used for…
-
Introduction to stored routines (part 3)
Last time we created a simple, but not particularly useful, stored function. This time, we’ll create something a little more complicated and useful.
-
Introduction to stored routines
Stored routines are a relatively new addition to the MySQL server, and aren’t generally used by a lot of people. Although they can greatly simplify application code and ensure that all parts of a system behave in a consistent manner, creating and using stored routines can still be a daunting prospect to those who have…
-
Introduction to stored routines (part 2)
Previously, I gave a brief introduction into what stored routines actually are and gave some advantages of using them. Now, I’ll cover some of the basics for creating stored routines.
-
Why turning on binary logging is good
Binary logging is one of those options that is available to all, but in most cases is never turned on. Here is why I think it’s a good idea to turn it on.
-
Re-ordering columns without deleting data
Sometimes you may feel the need to re-order the fields in one of your tables – you shouldn’t really have to do this, but some people prefer a ‘neat’ schema. The most obvious way of doing this is to recreate the entire table, or drop the field and recreate it, but there is an easier…
-
“Too many connections” errors
At some point you will encounter “Too many connections” errors – given that the MySQL database server has three different connection limits, it’s important to know which limit you’ve hit, and what you can do about it.
-
Let sleeping dogs die – automatically close idle connections
The MySQL database server is used for both desktop and web-based applications, and so ships with default values that try to meet both of their needs. In some cases, what’s appropriate for one is not appropriate for another, and idle/sleeping connections is one of them.