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...
- restart mysql without user access so nobody can read/write to the db.
- mysqldump with
mysqldump -u [username] -p[password] -h [host] [databaseName] > [backup-name].sql
- 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.