Page Table of Contents
Backing up MySQL database is vital to ensure the safety and integrity of your valuable data. While there are various methods available, using the command line interface provides a powerful and practical way to perform backups. Therefore, EaseUS will offer you a comprehensive guide on how to run a backup MySQL database command line. To simplify the process of backing up MySQL database, tables, data, and schema, we recommend novices try the EaseUS Todo PCTrans Server.
EaseUS Todo PCTrans Server is a professional data migration software designed specifically for server users. It offers a straightforward and efficient method to migrate and back up MySQL database. This software supports various types of server migration: Windows Server 2003/2008/2012/2016/2019. With its PC-to-PC tool, you will experience a smooth and efficient transition.
Download this tool and create a backup of your MySQL database effortlessly:
Step 1. Launch EaseUS Todo PCTrans on source and target Server PCs, on the source PC, select "This is Old PC" and "Next".
Step 2. Connect the target new Server computer via the password or identification code.
Step 3. EaseUS Todo PCTrans will analyze the content on the source Server PC, once done, at the Applications section, select the desired MySQL apps and click "Transfer".
Step 4. Wait for the MySQL transfer proceder to complete and click "OK" to finish.
For more related information, you can check out the posts below:
The command-line tool mysqldump is used to create the logical backup of a MySQL database. Additionally, the command can be used to produce the output in CSV, delimited text, or XML format.
But you should pay attention to SQL commands required to rebuild or restore the database, which are created in the backup file when we create a backup of the MySQL database. Thus, if your database is big, it could be a time-consuming process. Continue to read and learn how to generate the backup of MySQL database.
Create the Backup of a Single Database
To back up a single MySQL database, you can type the commands below(replace username with your right name):
- mysqldump -u database_username -p database_name > database_backup_file.sql
Then, it will prompt for the password. If you forget it, you can enter the following command to bypass the password:
- mysqldump -u database_username --password="database_password" database_name > database_backup_file.sql
Create the Backup of Multiple Databases
Regarding multiple databases, you can try the following command(replace name 1 and 2 with the right database):
- mysqldump -u database_username -p --databases database_name1 database_name2 > database_backup_file.sql
Then, enter the following line to avoid password prompt:
- mysqldump -u database_username --password="database_password" --databases database_name1 database_name2 > database_backup_file.sql
Create the Backup of All Databases
Similarly, you can type this command to back up all MySQL databases:
- mysqldump -u root -p --all-databases > C:\MySQLBackup\all_databases_20200424.sql
Create the Backup of Database Structure
- mysqldump -u root -p --no-data username > C:\MySQLBackup\username_objects_definition_20200424.sql
Create the Backup of a Specific Table
- mysqldump -u root -p username tablename payment > C:\MySQLBackup\tablename_payment_table_20200424.sql
Create the Backup of Database Data
- mysqldump -u root -p username --no-create-info > C:\MySQLBackup\username_data_only_20200424.sq
Restore the MySQL Database
When you restore the MySQL, you need to use mysql tool instead of mysqldump:
Step 1. Delete the original database and create a new one by addressing the commands below:
- mysql> drop database username;
- Query OK, 24 rows affected (0.35 sec)
- mysql> create database username1;
- Query OK, 1 row affected (0.01 sec)
Step 2. Execute the command to restore the original MySQL database:
- mysql -u root -p username < C:\MySQLBackup\username_20200424.sql
In this article, we have learned two professional tools to back up the MySQL database. By running EaseUS Todo PCTrans Server or MySQL database command line, you can easily create backups of your database and restore it when you need to. However, tasks like migrating or backing up MySQL databases are frustrating, especially for computer beginners. Thus, for a seamless experience, we highly recommend you use the most efficient tool, EaseUS Todo PCTrans Server. Begin your journey with this efficient software now!
FAQ About Backup MySQL Database Command Line
Now, you have learned two methods to back up MySQL database. Keep following, and you will discover more valuable information in this FAQ section:
1. What is the MySQL database backup script?
To create a MySQL database backup, you need to create the following two scripts: Pre-db-backup-mode. sh and post-db-backup-mode. sh. The first script locks all tables for all databases with a global read lock and closes all open tables. The second script releases all locks.
2. How many types of MySQL backup?
Logical, physical, and incremental backups are the three main kinds of MySQL backups. It is helpful to export data in a readable format using logical backups. Physical backups are appropriate for complete system recovery since they capture the binary data files.
3. How do you export the MySQL database using the command line?
Below are the simplified processes for exporting the MySQL database:
- To export one database: mysqldump -u username -p dbname > newspot.sql.
- To export more than one database: mysqldump -u username -p –databases database_1 database _2 > newdatabasespot.sql.
- To export all your databases: mysqldump -u root -p –all-databases > newdatabasesot.sql.
About the Author
Reviews and Awards
EaseUS Todo PCTrans Free is a useful and and reliable software solution created to serve in computer migration operations, be it from one machine to another or from an earlier version of your OS to a newer one. Read More
EaseUS Todo PCTrans is by the folks at EaseUS who make incredible tools that let you manage your backups and recover your dead hard disks. While it is not an official tool, EaseUS has a reputation for being very good with their software and code.Read More
It offers the ability to move your entire user account from your old computer, meaning everything is quickly migrated. You still get to keep your Windows installation (and the relevant drivers for your new PC), but you also get all your apps, settings, preferences, images, documents and other important data. Read More