MySQL (5.5)

The way things are going, I'm not sure the next DB guide I write for Debian is going to have MySQL in the title. Regardless, this hasn't changed too much over the past few years.

Preparation

1. Unless your database server is completely incidental to everything else you are doing, set up hugepages. The performance gain is extremely noticeable, even for sub-gigabyte databases. The exact way to do this may differ with your operating system, so adjust accordingly.
   1. Some distributions use a group to designate who can access Hugepages, e.g.
   2. usermod -a -G hugepager mysql
2. Debian has its mysqld_safe section in a separate file ( /etc/mysql/conf.d/mysqld_safe_syslog.cnf ) I'm assuming it's gone, here.
3. Make sure MySQL is shut down before doing the following tasks:
4. If you will be changing the sizes of ib_logfiles, you will need to delete them:
   1. rm /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1
   2. Adjust directory location as needed.
5. If, and only if, this is a fresh, virgin installation:
   1. Delete your ibdata1 file.
6. If not, you will want to move it if you are using a multi-disk setup. ibdata1 is where the double-buffer log is written (along with a number of other things that get split in future versions of MySQL), and will represent half of your database writes. Because these are all sequential writes, it's fine to stick this on a platter drive - ideal, even.
7. If you are running a master-slave setup, and you are mounting /tmp on tmpfs, you will want to make your own temp directory for MySQL specifically, lest a power failure result in data corruption:

mkdir /mytmp
chown mysql:mysql /mytmp
chmod 0750 /mytmp