Migrating MySQL to new server

Question

We have a MySQL 5.1 instance running on Windows Server 2008 R2 x64 (Server A) replicated to another MySQL 5.1 instance on Windows Server 2003 R2 (Server B).

We have a new MySQL 5.5 instance on another Windows Server 2008 R2 x64 machine (Server C).

I want to replace Server A with Server C.

My plan is to set up Server C as another slave to Server A by 'cloning' Server B like this:

http://www.redips.net/mysql/add-new-slave/.

After that I plan on doing this:

http://blesseddlo.wordpress.com/2011/06/03/convert-mysql-slave-to-master/.

After that I want to change Server B's master to Server C.

Is this a viable approach? Are there caveats? May there be any compatibility issues when setting a MySQL 5.5 instance as slave to a MySQL 5.1 instance?

I can't just stop everything and use mysqldump since there can be no downtime and we have ~25 GB of data that must be transferred over a Gigabit LAN.

Answer 1

8 months later, but here's the answer:

No problem in having a MySQL 5.5 slave to a 5.1 master. There is a problem with having it the other way around -- I've seen it fail.

Your approach is very viable and is being done frequently. You have both B and C replication from A. Then, at some stage you:

1. freeze A (via FLUSH TABLES WITH READ LOCK)
2. Verify both slaves are caught up with A's last statement.
3. Take A down
4. Change C to replicate from B, using B's SHOW MASTER STATUS
5. Tell your application B is the new master
6. Celebrate with beer

This does require a minimal downtime of a few seconds (and up to a minute) -- it mostly depends on your skills and confidence.

Answer 2

The first compatibility issue I worry about is the layout of the mysql schema tables.

Here is the layout of mysql.user in MySQL 5.1

+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     | NULL    |       |
| x509_issuer           | blob                              | NO   |     | NULL    |       |
| x509_subject          | blob                              | NO   |     | NULL    |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)

Here is the layout of mysql.user in MySQL 5.5

+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(60)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)

If you mysqldump from MySQL 5.1, please exclude the mysql schema.

How do you port over the MySQL Grants? Like this:

C:\> set MYSQL_CONN=-uroot -prootpassword
C:\> set GETUSER=GETUSER.sql
C:\> set MAKEGRANTS=MAKEGRANTS.sql
C:\> mysql %MYSQL_CONN% -ANe"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" > %GETUSER%
C:\> mysql %MYSQL_CONN% -AN < %GETUSER% > %MAKEGRANTS%

The output file will contain the SQL to restore grants. The file has one glitch: Each line needs a terminating semicolon (;). Open the output file MAKEGRANTS in Notepad and append a semicolon to every line.

Move the MAKEGRANTS file to the MySQL 5.5 server and execute. Then, load the mysqldump.

Some may object to this convoluted approach and say run mysql_upgrade.exe on MySQL 5.5, especially if you load a 5.1 mysqldump that includes the mysql schema. Hey it's also works. What I mentioned before is just my personal preference (my way of doing things) since I like leaving the MySQL 5.5 schema tables intact.

Here is something interesting: What if all you data is InnoDB? You can mysqldump without downtime (Like This):

echo "STOP SLAVE;" > MySQLData.sql
mysqldump -uroot -p --single-transaction --master-data=1 --routines --triggers --databases db1 db2 >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql

Move this file over to the MySQL 5.5. machine. Before you load MySQL 5.5, setup replication but do not run START SLAVE;. Load the script and it will stop the slave, load the data, and start the slave.