How to Backup All MySQL Databases from Command Line

MySQL is a relational database management system and is used widely all over the IT industry for efficient data storage. In Linux, MySQL is available in standard installation packages of almost all major Linux distributions. They install not only the MySQL Client and Server but also some other database utilities.

One such utility is MySQLDump. There are cases when MySQL databases need to be backed up and saved to a file: for example to secure a copy of the data as a backup, or to copy the databases to another system.

Prerequisites

MySQL Client should be already installed in your system, and it should be configured with either a remote or local MySQL Server. Apart from this, the user who wants to backup the database must be an administrator (root user) or the user must have privileges to backup databases.

Today, in this article, we will learn how to backup all MySQL databases using the MySQLDump utility in Linux.

Backing Up All MySQL Databases

The tool MySQLDump is basically used to output the dump of one or more databases in MySQL. The output can be then redirected to a file, and the file can be moved or copied over to another system and contents deployed in the database on that system.

The syntax for using MySQLDump is:

$ mysqldump -u username -p database_name > backup_filename.sql

For example, to backup a single database called ‘Sports‘ to a backup file called ‘sportsdb_bkp.sql‘, we can run:

$ mysqldump -u root -p sports > sportsdb_bkp.sql
OR
$ mysqldump -u abhi -p sports > sportsdb_bkp.sql

Enter your password when prompted.

Backup MySQL Database in Linux
Backup MySQL Database in Linux

Now, to backup all MySQL Databases, instead of mentioning the name of a single database or tables, we mention the flag '--all-databases'.

$ mysqldump -u root -p --all-databases > backup_filename.sql
OR
$ mysqldump -u abhi -p --all-databases > backup_filename.sql
Backup All MySQL Database in Linux
Backup All MySQL Database in Linux

View Contents of MySQL Database

Let’s see what the file ‘backup_all.sql’ contains.

$ cat backup_all.sql
View Contents of MySQL Database
View Contents of MySQL Database

The file is huge in size as expected and contains a dump of the entire MySQL system on the machine. The backup file is essentially a list of SQL commands which perform tasks like creating databases, entering data, setting configuration variables, etc.

Import All MySQL Databases

You can copy it to another system now, where MySQL is already installed and you can input this file to the command ‘mysql’ to run these commands automatically and deploy all the databases there.

$ mysql -u root -p < backup_all.sql
Conclusion

We learned how to dump the entire MySQL database system on a Linux machine, to a file; which can then be kept in a secure store or moved to another machine to be restored.

Thanks for reading and let us know your thoughts in the comments below!

Got something to say? Join the discussion.