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
MySQL:
MariaDB or Percona.

MariaDB can be had here.

For Percona server and client tools, it’s best to have direct access to Percona’s repository:
[sourcecode language=”text”]
yum install gpg
rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
[/sourcecode]

Do
[sourcecode language=”text”]
yum search Percona
[/sourcecode]
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

[sourcecode language=”text”]
-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
[/sourcecode]

Restart the firewall:
[sourcecode language=”text”]
service iptables restart
[/sourcecode]

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:

[sourcecode language=”text”]
innobackupex-1.5.1 –databases=myDB –user=samIam –password=IamSam –stream=tar ./ | gzip -> /home/sam/myDb.tar.gz
[/sourcecode]

For InnoDB tables, I did:

[sourcecode language=”text”]
mysqldump -usamIam -pIamSam myDb innoDbTable > innoDbTable.sql
[/sourcecode]

For stored routines, I did:
[sourcecode language=”text”}
mysqldump –routines –no-create-info –no-data –no-create-db –skip-opt myDb > routine.sql
[/sourcecode]

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

[sourcecode language=”text”]
chgrp -R mysql myDB/
chown -R mysql myDB/
[/sourcecode]

If you took a dump earlier, pun not intended, run this on the shell

[sourcecode language=”text”]
mysql myDB < innoDbTable.sql [/sourcecode] For stored routines, move over the dumped file, and run this: [sourcecode language="text"] mysql myDb < routine.sql [/sourcecode] 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 Web server: If a brand new web server, these are needed, at a minimum [sourcecode language="text"] yum install php yum install php-mysql yum install httpd [/sourcecode] It might be necessary to edit /etc/php.ini, so there is a line extension=myql.so under Dynamic Extensions