I’ve done this in the past, but thought this time I’ve got to take notes. It can be used as a crude check list in the future. Don’t underestimate the power of a practical, down-to-earth check list! Perhaps documents like this should be kept in a wiki page, for easy updating to avoid being stale, a proeblem with blog entries, it seems.
P in LAMP here stands for php, not Python or Perl. L is CentOS (I used CentOS 5.5) or Red Hat Linux. I am not covering moving all databases in a MySQL instance, just a select few or just one.
I’d appreciate your comments or suggestions.
Software install and configuration
MariaDB or Percona.
For Percona server and client tools, it’s best to have direct access to Percona’s repository:
yum install gpg rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
yum search Percona
to find Percona packages you are interested in.
Percona MySQL Server package does not create /etc/my.cnf for you. In today’s server, I think you should use /usr/share/doc/Percona-Server-server-51-5.1.54/my-huge.cnf as default /etc/my.cnf if you are not sure. Remember to turn on the slow-query-log. Also, remember to set interactive-timeout=600. You may also need to increase the max_connections setting.
Open up port 3306:
Edit /etc/sysconfig/iptables: add this line somewhere above COMMIT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
Restart the firewall:
service iptables restart
Note: Here is how I came up with this configuration. I found the line that has 22 in it, which is the default port for ssh, yanked and pasted it, replacing 22 with 3306. This works, but I am not sure if it is done correctly. Comments welcome.
On source MySQL server:
1. FLUSH TABLES WITH READ LOCK
2. The backup can be done many ways. This time I used xtrabackup, but I am leaning toward using mysqldump or mysqlhotcopy exclusively next time I do it, because I was not satisfied with innobackupex-1.5.1′s –stream and –apply-log functions. Perhaps my bad experience was due to working with a particularly messed up MySQL instance and/or a under-powered server. Percona did mention that they are not satisfied with innobackupex so hopefully a better version will come out.
In any case, here is what I did with innobackupex-1.5.1 for database backup. It did get all MyISAM tables correctly, though, just not the InnoDB tables:
innobackupex-1.5.1 --databases=myDB --user=samIam --password=IamSam --stream=tar ./ | gzip -> /home/sam/myDb.tar.gz
For InnoDB tables, I did:
mysqldump -usamIam -pIamSam myDb innoDbTable > innoDbTable.sql
For stored routines, I did:
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt myDb > routine.sql
3. Move the backup file to the new server. Percona mentioned a few creative ways of moving files here.
4. Use the excellent mk-show-grants, part of Maatkit, to get the login/user/permission generation script. Modify that as necessary.
On destination MySQL server:
1. Unpack myDB.tar.gz and move all myDb directory and its contents to MySQL’s data-dir, perhaps /var/lib/mysql or /usr/local/mysql/data. While inside MySQL data-dir on the shell, do
chgrp -R mysql myDB/ chown -R mysql myDB/
If you took a dump earlier, pun not intended, run this on the shell
mysql myDB < innoDbTable.sql
For stored routines, move over the dumped file, and run this:
mysql myDb < routine.sql
2. Do a select count(*) on all myDB tables. I don’t have a clever way of doing this, I did “show tables”, grabbed the results, and hacked with Vim’s macro to generate a bunch of select count(*) statements and ran it. This should let you know if there are any issues with your newly restored myDB. mysqldump -d myDB is also a good thing to run to find out problematic tables, I think.
3. Run the login creation script generated earlier with mk-show-grants
If a brand new web server, these are needed, at a minimum
yum install php yum install php-mysql yum install httpd
It might be necessary to edit /etc/php.ini, so there is a line
under Dynamic Extensions