MySQL dump all databases via mysqldump -- the proper way

Here's a command that properly dumps all databases, in case you wanted to restore a full backup.

For whatever reason, many sites are using improper characters ( ' –– ' , ' – ' ) to display this mysqldump command line--copy pasting will throw up an error stating something like:

mysqldump: Got error: 1049: Unknown database '??extended-insert' when selecting the database

Here is a sample of wrong characters, which appear just fine, but won't work:

mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers > all-databases.sql

or

mysqldump –extended-insert –all-databases –add-drop-database –disable-keys –flush-privileges –quick –routines –triggers > /root/all-databases.sql

So here's the proper character mysqldump command line:

First, make a copy of your mysql database--this contains your database user data/permission. More on this below.

cp -a /var/lib/mysql/mysql /var/lib/mysql/mysql-01

Now dump all the databases via mysqldump:

mysqldump -uroot -p --all-databases --extended-insert --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql

Be warned about including the '--add-drop-database', as this will also delete the 'mysql' database, which has all your database user data/permissions. If the restore process was to get interrupted after it deleted (dropped) the 'mysql' database, you won't be able to start mysqld (this happened to me, as the partition ran out of space during the restore process).

Here is how you restore your backup made via mysqldump

mysql -uroot -p < all-databases.sql

If the database restore process gets interrupted and you lose your mysql database, simply restore the backup mysql database via ssh:

cp -a /var/lib/mysql/mysql-01 /var/lib/mysql/mysql
Tags: mysql linux server admin