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.
Let’s check the contents of the ‘sportsdb_bkp.sql‘ file.
$ cat sportsdb_bkp.sql
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!
