How to Backup a Single MySQL Database from Command Line

MySQL is a popular choice when it comes to relational database management systems. MySQL and its clones power a large percentage of the worldwide web today; the reason for it being its robustness and plethora of options available.

There are scenarios when a MySQL database needs to be backed up and saved in a file: either to secure a copy of the data as a backup or to copy the database 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 back up a single MySQL database using the MySQLDump utility in Linux.

Backing Up a Single MySQL Database

MySQLDump‘ is a tool automatically installed along with MySQL Client in Linux. This tool is basically used to create a file out of a MySQL database, i.e. dump the contents of a database to the output file.

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 back up a 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

Let’s check the contents of the ‘sportsdb_bkp.sql‘ file.

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

The backup file is now ready. Notice that the backup file is essentially a list of SQL commands. It can be copied to another system now, where MySQL is already installed and you can input this file to the command ‘mysql‘ to run these commands and deploy them to the backed-up database here.

$ mysql -u root -p sports < sportsdb_bkp.sql
Conclusion

We saw a simple way to create a backup of a MySQL database. The tool ‘MySQLDump’ has many more options at its disposal: for instance to only backup the structure of the database without the data, or inversely to only backup the data and not the structure (either database already exists in the target machine, you just need to copy the data there).

Make sure you check all such options in the command line manual using:

$ man mysqldump

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

Got something to say? Join the discussion.