This brief tutorial shows students and new users how to use mysqldump to quickly back up 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 the mysqldump command contains all the database structure and data, a 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 the mysqldump command, follow the steps below: Backing Up MySQL Databases To back up 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 a similar tool to remote into the server and back up the database locally on the server. The command to back up a MySQL database is as follows: The parameter of the command above is 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 a backup
When your commands are above, all database structure and data will be exported into a single database_dump.sql file. For example, to backup a WordPress database called wpdb, we’ll use the commands below: 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: To backup all databases on the server, you run the commands below: That should do it! Congratulations! You have learned how to use the mysqldump tool to back up MySQL databases with various options. You may also like the post below:
