Mysql – How to upgrade MySQL (5.5) production DB to 5.6 with minimal downtime

MySQLmysql-5.5mysql-5.6replication

Our existing Database size around 200GB. We want to use consistent, parallel backups methods. For testing, I migrated my Development DB from 5.5 to 5.6 by running mysqldump with option --single-transaction. For production, I am looking for way where I can upgrade mysql prod DB to 5.6 with minimal downtime.

We are using a different server, but the problem is how I can manage ongoing transaction on production DB, because while taking mysqldump and restoring to new server their will be many transactions performed on my existing production server. How can I sync this prod-DB updated transactions with new 5.6 mysql-server ?

I am thinking if we can run mysqldump with --single-transaction --flush-logs and after restored this dump, restore remaining transaction by applying new binary logs that created after mysqldump.

I am also planning to enable GTID replication with our new 5.6 mysql production.

Best Answer

We used replication (no GTID) to replicate from our production 5.5 servers to our new 5.6 servers so to minimize downtime. Basically, set up your new 5.6 server to replicate from the 5.5 (cannot use GTID at this time). Dump 5.5 and restore to 5.6, start the replication.

When you move production to the 5.6 server, stop the replication from the 5.5 server . Reconfigure 5.6 server to use GTID (need to issue RESET MASTER and RESET SLAVE on the 5.6). Then configure your downstream 5.6 slaves.

Related Solutions

Mysql – Replicating from MySQL 5.1 master to 5.6 slave failing because ‘INSERT … VALUES (NOW())’ results in ‘Error_code: 1062’

Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is triggering the error here?

Yes that's exactly what the error telling

Error 'Duplicate entry '8072' for key 'PRIMARY'' on query. 
Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())', 
Error_code: 1062

Can I get replication to work without skipping rows with SET GLOBAL sql_slave_skip_counter = 1;?

Yes, You can go and delete the row with the ID 8072 in the table.. you can add the option of slave-skip-errors = 1062 in my.cnf and restart mysql

But you have to be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

The only clean way is:

In the master

You FLUSH TABLES WITH READ LOCK; in the database you want to replicate
SHOW MASTER STATUS and copy the file name and the position
Excute a full backup to the database you want to replicate
Then release the tables with UNLOCK TABLES;

In slave

Restore the databse
Excute this CHANGE MASTER TO MASTER_HOST = 'masterhost', MASTER_USER = 'masteruser', MASTER_PASSWORD = 'masterpass', MASTER_LOG_FILE = 'filename', MASTER_LOG_POS = 'position';
Then start slave;

MySQL 5.5 – How to Fix Replication Backup and Restoration Failures

The error itself is happening because there is a create view (or procedure) statement and the slave doesn't have the user who created it. In mysqldump you will have this format:

CREATE ALGORITHM=UNDEFINED DEFINER=`lipl_ga_app`@`%` SQL SECURITY DEFINER...

That's not specific to replication. It happens even if you would want to restore it to an empty db.

On the replication side

1) One way to do it is mysqldump but you need to LOCK the tables or make sure you don't get any writes during the process of dump (read_only=1 for example). Otherwise you get your slave out of sync from the start. Also there is certainly an impact on the application which is connected to the master. More on this you can read on the mysql doc: http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

To same or higher major version (these cannot be done if master is 5.6 and slave is 5.5):

2) You can also retrieve the data without impacting the master with hot backup solutions like percona Xtrabackup (https://www.percona.com/doc/percona-xtrabackup/2.3/index.html). This is probably going to be faster as well. Once you applied the logs it behaves as normal mysql binaries and you can do the change master command. You will have an xtrabackup_binlog_info file which contains the master position you need to point the slave to.

3) You can stop the master after acquired the master position, copy the binary to the slave, start mysql and change master to ... with the position you saved. This could be also done by snapshotting the filesystem (LVM, ZFS, BTRFS, etc.) if you want to quickly restart mysql. Usually this is the fastest way.

If your version mismatch you might need to run mysql_upgrade since with the latter two you're copying binary data not logical.

A sidenote:

Why are you replicating from 5.6 to 5.5? Replicating from higher version to lower is not guaranteed to work.

Best Answer

Related Solutions

Mysql – Replicating from MySQL 5.1 master to 5.6 slave failing because ‘INSERT … VALUES (NOW())’ results in ‘Error_code: 1062’

MySQL 5.5 – How to Fix Replication Backup and Restoration Failures

On the replication side

Related Question