Unix Runes For Those Playing With Servers: Part 1 How to mysqldump all databases on a server

How to mysqldump all databases on a server

Yes, this is a little digression from the usual gaming/educational programming posts but I wanted to jot a few “runes” down for those playing with servers – assuming you’re using a *nix server – this one looks is for anyone that wants to mysqldump all databases.

Sometimes you need to move your MySQL database to another machine, this little rune ‘mysqldump all databases’ will dump everything out into a single file which you can then import into the new machine. Watch out, this could be quite large so you can also compress (gzip) the output (just enter ‘gzip dumpfilename.sql’ and it will turn dumpfilename.sql into dumpfilename.sql.gz)

The dumpfile that is created by MySQL should include all the necessary create database instructions and maintain the encoding that was specified with the database and corresponding data.

mysqldump -u USERNAME -p –all-databases > all_my_dbases.sql

This will ask you for USERNAME’s password and then create the file in your current working directory.

How to mysqldump a single database

You can use a similar rune to dump out a single database e.g.

mysqldump -u USERNAME -p DATABASE_NAME > DATABASE_NAME.sql

Restoring all the database

The following will take your dump file and “insert” it into the MySQL database server. Warning, if you already have databases with the same name, they will be overwritten by this action.

mysql -u USERNAME -p < all_my_dbases.sql

Restoring a single database

The following will take your dump file and “insert” it into the MySQL database server. Warning, if you already have databases with the same name, they will be overwritten by this action.

mysql -u USERNAME -p DATABASE_NAME < DATABASE_NAME.sql

Comments are closed.