Migrating MySQL Data

Its time to migrate a copy of the MySQL data so that I can begin testing the websites running on Pericles in a closed environment before I let this out into the wild.

sudo /etc/init.d/mysql stop

The data lives in /var/lib/mysql

I'm cool with that, but I'm going to replace it with my existing data, so first I'll rename it to preserve the original:

cd /var/lib
sudo mv mysql mysql-original
sudo mkdir mysql
sudo chown mysql.mysql mysql

Next, I unpack my MySQL backup, which was archived from my replication slave a couple days ago. Since I'm going to make this into the new master, I'll first use this data as a testbed and afterwards I'll replace it with a fresh copy when I'm really ready to switch masters. My data is currently about 1 gigabyte, which shrinks to 111 megabytes when compressed, so it takes about five minutes to copy and longer to unpack. I should point out that innobase and myisam data on Linux and Windows are "binary compatible" so they can be copied directly across the board, but you need to make sure your mysql configuration files have the same innodb settings in them, or else. Oh yeah, I should also mention that the MySQL version numbers are both very close, and up to date, so the fields in the user tables and so forth are the same format. I wouldn't recommend migrating a MySQL 4.0 database to a MySQL 5.0 environment using this technique because the user fields changed a bit.

I deleted the master.info file so that this server won't try to connect to the replication master like the replication slave from which it was copied was programmed to do. I'll configure it to be a replication master once I do the real thing.

I had to change my bind-address=0.0.0.0 in /etc/mysql/my.cnf in order to get the MySQL to bind to all network interfaces (I need to be able to connect from several networks.) This is ok, because the mysql users table has restrictions on which users are allowed to connect from which hosts. I wanted to change it in /var/lib/mysql/my.cnf which seemed to be recommended by the heading in /etc/mysql/my.cnf, but copying the cnf file and changing it there did not make any difference, so I resorted to changing the /etc/mysql/my.cnf version of the file.

Starting up MySQL I see this:

error:  'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

Now I need to add back in the debian-sys-maint user that I lost by overwriting the user tables. This is easy, because they leave the random password sitting unencrypted in /etc/mysql/debian.cnf

I did this by logging into MySQL from the command line interface as root, and executing the following commands:

USE mysql;
CREATE USER 'debian-sys-maint'@'localhost';
UPDATE user SET password = PASSWORD('PasswordCopiedEarlier') WHERE user = 'debian-sys-maint';
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost';
FLUSH PRIVILEGES;

I now stopped and started the MySQL server:

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

This worked like a charm; I no longer see the error message, which means debian-sys-maint can do its magic.