How to Dump All Databases in MySQL Database Server

Posted on

This is an article written to dump all of available databases or all of existing databases in MySQL Database Server. The reason for doing this is just because of there are two many databases exists in the MySQL Database Server. So, instead of one-by-one manually dumping all of the databases exists in the MySQL Database Server, just do it once with a single command. There is actually a single command which can be executed in order to acccomplish this task. But the consequence of executing this command which is intended to dump all databases exists at once, it is going to produce one file which has a large size depends on how many and how big those databases are. First of all, make sure that the MySQL Database Server can be connected where for further reference, the article titled ‘How to Access MySQL Database Server via Command Line’ in this link can be used as a useful information.

The command which is taken for accomplishing the task is shown as follows :

mysqldump -uroot -p --all-databases > /home/user/all-database.sql

Description : 
mysqldump : it is the tool command which is specified only for dumping from MySQL Database Server. 
-uroot : it is an additional parameter specifying on the user used to connect to MySQL Database Server which in the context of the above command, it is 'root'
-p : it is an additional parameter for specifying password input interactively where the password itself is associated with the user specified previously
--all-databases : it is an additional parameter for specifying the dump process is done to all databases exists in MySQL Database Server
> : it is a redirect output sign which is passing any output generated from the command specified before the sign '>' 
/home/user/all-database. sql : it is specifying the output file which is generated to store the output of the previous command before the sign '>'

The command execution example in a real situation is shown as follows :

user@hostname:~$ mysqldump -uroot -p --all-databases > /home/user/all-database.sql
Enter password: 

In the above output command, the generated file’s name is ‘all-database.sql’ furthermore it is stored in a location named ‘/home/user’. Depends on the number of the database and also the size of each database, it can took quite some time and also the size of the file generated will also larger than the normal one which only containing one single dump database.

One thought on “How to Dump All Databases in MySQL Database Server

Leave a Reply