Database administrators and system administrators must take backups of their MySQL database, and the mysqldump utility is the most common tool used to convert an entire database or single object to a text file. After the mysqldump utility creates a backup file, it can be used to restore data to a server or migrate data to another database.
What Is mysqldump?
The mysqldump tool is a command-line utility included with MySQLto use for exporting SQL statements that can rebuild a database or a subset of objects in a database. It can be used to create a backup of database objects and data, or it can be used to transfer a database from one server to another. Administrators can use mysqldump to export data into a specific format such as XML and CSV.
How to use mysqldump to backup a single table
The mysqldump utility can be used to export only specific tables within a database. For example, suppose that you only want to export a single customer table from an e-commerce database. The customer table data can then be used to send marketing emails. The mysqldump utility will export the customer table and its data to a text file.
Note: To successfully run any command in mysqldump, the username used to access the database must have the appropriate permissions to access objects. Without access, the command will not export any schema, object, or data.
You must specify the database name and the table name to successfully export objects to a text file. If you want to verify that you have the right table name, first make sure you’re authenticated into the database that you want to work with. Then, run the following SQL command in the MySQL command line or the Workbench application:
SHOW TABLES;
After you find the right table name, you can export the table to a text file. The following command will export the Customer table from the database myDB to the file customers.sql:
mysqldump -u username -p password myDB Customer > customers.sql
The “username” and “password” terms should be swapped with your own username and password. You can export multiple tables by separating each table with a space character. The following will export the Customer and Order tables to customers_orders.sql:
mysqldump -u username -p password myDB Customer Order > customers_orders.sql
After you export the SQL statements to a file, you can open the SQL file with a standard text editor or open it with MySQL Workbench. MySQL Workbench is a separate installation from the MySQL database, but you can download it for free from the MySQL website. It’s best to open it with Workbench because then you can run the statements and import data to the target MySQL database server.
mysqldump Examples
The mysqldump utility is mainly used for backups or transferring data to another database server. Whether you take a backup of your database or want to transfer data to another server, the mysqldump utility works the same. One common use for a mysqldump backup is to create a test environment from the production database.
To use a production database to then create a testing environment, you can take a full backup of your database using the mysqldump utility. The following command takes a backup of your entire database named myDB and exports it to a file named myDB.sql:
mysqldump -u username -p password -databases myDB > myDB.sql
Now, log in to your testing server. It must also have MySQL installed so that you can import the exported data. Notice that the export command uses the ‘>’ character to move data from a database to a SQL file. The import process uses the ‘<’ character. The following command imports all data in the myDB.sql file to a test environment with the same database named myDB:
mysqldump -u username -p password -databases myDB < myDB.sql
With the above two commands, you replicate data from the original myDB database in production to your test environment. The test environment gives developers and quality assurance people the ability to work with real-world data and a database structure that mimics any customer or employee issues for troubleshooting.
Basic Use of mysqldump
The .sql file created during a mysqldump is a simple text file. You can open it in any text editor, but it’s best to open it with MySQL Workbench. Workbench color codes commands and data types, which makes the SQL commands easier to read.
You can open the .sql file in Workbench using the File menu. Click “Open SQL Script” in the dialog box and choose the .sql file that you want to read. With the .sql file opened, you can read it and run it with the “Run SQL Script” File menu item, if you choose to execute the commands. Workbench runs on Windows and Linux, so you can run a script this way on both operating systems.
You can also run a mysqldump file in Windows and Linux using the mysqldump utility. The following command runs the SQL statements on a MySQL database in both Windows and Linux from the mysqldump utility:
mysqldump -u username -p password -databases myDB < myDB.sql
The above command imports data from the myDB.sql mysqldump file and executes the SQL statements on the local database server. The command imports data to the myDB database.
If no data is present in the database or any of the database’s tables, the database structures are still created, and the database is added to the schema. The table structures are available for you to add data to them in the future.
Advanced Use of mysqldump
A large database could contain tables with terabytes of data. Too many exports could exhaust storage resources, but you can compress each file to reduce storage space needed for MySQL data exports. Compressing a file can save multiple gigabytes of storage space necessary for very large files. The following mysqldump command exports the myDB database and compresses it using the gzip utility to a file named myDB.sql.gz:
mysqldump -u username -p password -databases myDB | gzip myDB.sql.gz
Backups often contain sensitive data, so encrypting it is necessary for compliance and data protection. You need a third-party tool to encrypt data. We’ll use ccrypt in this example to encrypt the myDB.sql file. You need a key (similar to a passphrase) to encrypt a file, but you must remember this key to decrypt it. The ccrypt utility will prompt you to enter a key when you encrypt the file, or you can store a key in a secure file where it can be reused to encrypt and decrypt a .sql file.
In the following example, the exported .sql file is encrypted using a stored key in the file named mykey.key:
mysqldump -u username -p password -databases myDB | ccrypt -k mykey.key > myDB.sql.cpt
Notice that the data is exported to a .cpt file. This .cpt file is where all the encrypted data is stored. You need the same key to decrypt the file. You can decrypt it using the following command:
cdecrypt -k mykey.key myDB.sql.cpt
Most databases contain more than tables and data. Triggers and procedures must also be backed up. By default, the mysqldump utility exports triggers with a standard export, but administrators can explicitly tell the command to export them. Procedures are not exported by default, so administrators must explicitly export them. The following command exports triggers and procedures along with other database objects and data:
mysqldump -u username -p password -databases myDB –triggers=true –routines=true > myDB.sql
Best Practices for mysqldump
Anytime you need to use the mysqldump utility, you can freely use it without interrupting productivity. It’s especially useful when you need to make frequent backups or exports of your database and its data. Backups should be done often so that data can be retrieved in case of database failure, malware, or data corruption.
The frequency of backups depends on the amount of data you add to a database every day and the frequency of changes made to the database. Generally, the frequency at which you use the mysqldump command depends on the amount of data that can be lost without causing a negative impact on revenue and business continuity. You don’t always need a full backup, but some businesses use the mysqldump utility once a week and others use it once a day. Determine the frequency of full backups necessary for your business and use automation tools to execute mysqldump at a set interval.
Conclusion
Managing a database is a full-time job, but the mysqldump utility makes management much more efficient. You can use it for full backups of your data or use it to transfer data between servers. It’s compatible with Windows or Linux, so you can use it on any environment hosting your MySQL server.
