MySQL dump, recovery, restore…

In a recovery procedure for a productive environment I faced a MySQL subtask. My need was to restore a productive DB from a backup virtual machine.
So, I had 2 MySQL db server:

  • DB Live (ip: 192.168.0.1, schema: webapp)
  • DB Recovery (ip: 192.168.0.2, schema: webapp)

The first step was to delete all the tables from the productive schema. MySQL does not provide any command like “DROP ALL TABLES” but I found a good Unix script:

mysql --host='192.168.0.1' --user=root --password=pass -BNe "show tables" webapp | awk '{print "drop table " $1 ";"}' | mysql --host='192.168.0.1' --user=root --password=pass webapp

The second step was to bring all recovery data into live production server. For different reasons, I had to do this operation in one single step, without having any dump .sql file to export / restore. Again, a good unix command help me:

mysqldump --host='192.168.0.2' --user=root --password=pass --single-transaction --flush-logs --hex-blob --max_allowed_packet=512M  webapp | mysql --host='192.168.0.1' --user=root --password=pass webapp

And the game is done.

Latest articles

Written by:

Be First to Comment

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *


sei − 2 =