MySQL provides a very nifty tool called mysqldump for backing up or dumping MySQL databases locally. This tool is a built-in tool and works great especially in situations where you want to quickly backup huge databases.
This brief tutorial shows students and new users how to use mysqldump to quickly backup MySQL databases on Ubuntu 16.04 | 18.04 servers.
Other tools such as phpMyAdmin and MySQL workbench can be used to manage and backup SQL databases. However, when dealing with large databases, the quickest and easiest tool to use is mysqldump.
The file created by mysqldump command contains all the database structure and data, set of SQL statements to create database objects, and the dumped file can be a CSV, delimited or XML file.When you’re ready to back up or dump MySQL databases using mysqldump command, follow the steps below:
Backing Up MySQL Databases
To backup a MySQL database, the database first has to exist in the database server. You also need to use the root account or an account with full access to the database you wish to back up.
You can use SSH or similar tool to remote into the server and backup the database locally on the server. The command to back up a MySQL database as follows:
mysqldump -u username –p database_name > database_dump.sql
The parameter of the command above as follows:
- [username]: valid MySQL username.
- [p]: the p parameter will prompt the the user password
- [database_name]: database name you want to backup
- [database_dump.sql]: the name of the file that will be created with the database content as back up
When you commands above, all database structure and data will be exported into a single database_dump.sql file.
For example, in order to backup a WordPress database called wpdb, we’ll use the commands below:
mysqldump -u username –p wpdb > wpdb_backup.sql
You can also backup multiple databases or all databases using mysqldump commands.
For example, if you want to backup multiple databases called database1, database2 and database3, run the commands below:
mysqldump -u username –p database1,database2,database2 > databases_backup.sql
To backup all databases on the server, you run the commands below:
mysqldump -u username –p --all-database > all_databases_backup.sql
That should do it!
Congratulations! You have learned how to use the mysqldump tool to backup MySQL databases with various options.
You may also like the post below:
