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.
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
View Contents of MySQL Database
Let’s see what the file ‘backup_all.sql’ contains.
$ cat backup_all.sql
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!
