moving live innodb db from one server to another

Question

I need to move an innodb db from one server to another - it is also going from mysql > mariadb. I want to ensure there are no problems at all and with that I will be using mysqldump to export and then import on the new server. The my.cnf settings are different on the new server so copying files over is not something I wish to try. I am not entirely concerned with downtime either as long as it ensures a good dump/import without problems.

Because this is a live db I am concerned with errors in the dump due to transaction or anything else which when imported on the new server would cause constraint issues/corruption.

What is the best procedure here? Here is what I was thinking...

1. restart mysql without user access so nobody can read/write to the db.
2. mysqldump with mysqldump -u [username] -p[password] -h [host] [databaseName] > [backup-name].sql
3. import with mysql -u [username] -p[password] -h [host] [databaseName] < [filename].sql

Is it possible to restart mysql and lock all users out? Would this solve any lingering transactions and ensure the data is legit when importing? Basically I want to 'shutoff' access to the db forever once I start the backup process as once it is imported on the new server it would never need to be accessed again, and more importantly, I do not want it accessed during the dump/restore to the other server. Of course, I would like to leave the db on the original server in case there are importing issues that come up.

Answer 1

Note that you must use --hex-blob with mysqldump if you have any encrypted fields in the database. If --hex-blob is not used, the data will be rendered useless when imported.

Before you backup, do a FLUSH TABLES WITH READ LOCK to prevent any changes. You can pipe the backup to the new server in one step, saving the step of copying the SQL file.

mysqldump --single-transaction --routines --triggers --events --hex-blob db-name | mysql -h server db-name

Answer 2

the first thing you should do is to make a backup of your database and then import it into the new server and test the new server first if all ok , also check for the timing you take to create a backup and restore into the new server so you know how much time it will take for you to put the new server in production.

when you are ready to pass everything to the new server is better you deactivate all connections to your database unless localhost as you will use mysqldump to access the database and make the backup,if all accesses to the database comes from your webserver you can put down the webserver the other way would be to configure the iptables to block all traffic on your mysql port except localhost. then you backup your database , restore to the new server and set your new server live and make sure all your connections would go to the new server.

Answer 3

this all depends if your database big or small enough, also depends what service you are running, but i guess there must be some maintenance window, during weekends late night, shutdown your apache/nginx, also you can restart your database with disabled networking, firewalled or different local port. export:

mysqldump -p --single-transaction --routines --triggers --events DATABASE | gzip > backup.sql.gz

import:

zcat backup.sql.gz | mysql -f -p DATABASE

re-check:

mysql_upgrade -p -f

gzip and zcat will help you to do it faster.