Re: backup and migrate live database

[Rick James]

Complain to WordPress -- their schema design leads to tedious work like this.

If you are familiar with Perl or PHP or C++ or some other programming language, it is fairly simple to walk through SHOW DATABASES or information_schema to find all the databases and generate a file with mysqldump statements. Then treat that as a .bat file (if Windows) or a shell script (if unix).

That's 2 clicks -- one to generate the dump script, the other to execute it. A further wrapping can have a single script/.bat that performs those two steps.

(I am assuming that new "users", hence new "databases", are generated often enough to necessitate rebuilding the dump script every time.)

Another approach is to set up replication, then plan to dump the entire Slave periodically. With replication, you have the additional advantage of having an up-to-the-minute backup that is virtually ready to come online if the Master dies.