Backup and Restore a MySQL Database (Command Line)
Building up replica servers are necessary for safety before updating a MySQL installation. You select the backup procedures that best meet the needs of your installation from the range of options MySQL provides. This blog familiarizes you with the MYSQL database’s needs and types of backup and recovery.
Need of Backup & Restore in MySQL Database
When main data failure occurs, the backup objective is to make a retrievable copy of the data. Primary data failures are caused by hardware, software, data corruption, human mistake, a cyber attack (virus or malware), an accidentally deleted file, or another human induced incident. Therefore, backup copies helps to restore lost data, which further helps a business recover from an unanticipated calamity.
- It is crucial to preserve a copy of the data on a separate medium, such as an external drive or USB stick. You could also pick something more substantial, such a cloud storage, cloud storage container, or tape drive, to protect against original data loss or corruption. The alternative media could be close to or far from the main data.
- Keep the amount of lost data to a minimum for the best outcomes. Generate backup copies consistently and frequently. By storing multiple copies of the data, it is possible to go back in time to when there was no data damage or destructive attacks.
- If there is a power loss, MySQL might be unable to close files or write data as usual. Data corruption sometimes prevents MySQL from working properly after a crash recovery process and prevents it from restarting.
- Backups and recovery options guarantee client satisfaction and business value.
Types of Backups in MySQL Database
All in all, a database backup is a replica of server based storage, and one can retrieve data loss by utilising backup. Accessing lost data becomes much easier with a backup database. Here are the types of Backup & Recovery in MySQL.
MySQL Physical Backup
The bare copies of files and directories in a MySQL physical backup are ideal for massive databases and are easily recovered. It takes less time to finish a MySQL Raw Backup than a logical backup.
Key Features
- It is helpful whenever the user needs to restore the entire database quickly.
- They offer information on transactions and database modifications.
MySQL Logical Backup
The MySQL logical backup, usually accomplished with mysqldump, stores data displayed as logical data content and framework. Since the server must visit the database and transform the physical features into a logical representation, a logical backup takes more time than a physical backup. Holding back the results on the client side effects the total performance, since the server also needs to submit the backup program’s logic formatting.
Key Features
- It simply offers structural information, and logical backups must be carried out once a week. Physical backups and logical backups are used together.
- This is helpful when a user must restore the entire database to its previous state.
- It has granular recovery capabilities and is more complicated.
Incremental Backups
An incremental backup includes all changes made since the last backup. A binary log backup is a specific type of incremental backup.
Key Features
- This is a great option if you have a large dataset, since you can take a complete backup at the beginning of the week and incremental backups each day.
- The backup size is much smaller than the primary data.
Snapshot Backups
Some file system implementations allow for the taking of “snapshots.” Without needing a physical duplicate of the complete file system, these offer logical copies of the file system at a certain moment.
Key Features
- Although MySQL can’t create file system snapshots on its own, this is done with the help of third party programmes like LVM or ZFS.
Binary Log Backups
These specifically address RPO. Binary log Files record every SQL query which was conducted and caused changes.
Key Features
- Mysqlbinlog streams binary logs from a remote server, starting with MySQL 5.6.
- You fully restore the last binary log backup by fusing binary backups with backups from Percona, XtraBackup or mydumper.
We have reached the main part of the article Backup and Restore a MySQL Database (Command Line).
Backup and Restore a MySQL Database (Command Line)
Basic Syntax of mysqldump
mysqldump is a command line tool and is a part of the MySQL package used to backup a database into a text file. The basic syntax to backup a MySQL database is shown below:
mysqldump -u [username] –p[password] [database-name] > [backup-file.sql]
A brief explanation of each option is shown below:
- username – A valid MySQL username.
- password – Password for the MySQL user.
- database-name – A database name that you want to backup.
- backup-file.sql – The name of the backup file you want to generate.
How to Backup MySQL Database
If you are a database administrator then you need to backup a single, multiple and all database backup in your day to day task. To backup a single MySQL database, run the following command.
mysqldump -u user -pyour-password db1 > db1_backup.sql
This command takes a backup of a database named db1 and generates a file named db1_backup.sql.
You can also take a backup of multiple MySQL databases using the following command.
mysqldump -u user -pyour-password db1 db2 > multidb_backup.sql
This command creates a backup of both databases named db1 and db2 and then generates a single backup file multidb_backup.sql.
The mysqldump tool also allows you to backup all MySQL databases with a single command. For example, to take a backup of all MySQL databases and generates a backup file named alldb_backup.sql, run the following command.
mysqldump -u user -pyour-password -all-databases > alldb_backup.sql
If you want to backup only the MySQL database structure instead of the whole data then you use the –no-data option with mysqldump command.
mysqldump -u user -pyour-password -–no-data db1 > db1_structure.sql
The above command backups only the database structure of db1 and generate a file named db1_structure.sql.
You can use the –no-create-info option with mysqldump command to backup only MySQL database data without structure.
mysqldump -u user -pyour-password --no-create-db --no-create-info db1 > db1_data.sql
This command backup only MySQL data from the database db1 and generates a backup file named db1_data.sql.
Backup a Table of MySQL Database
MySQL also allows you to take a backup of any tables from the specified database using the mysqldump command. The following command will take a backup of the students tables from the db1 database and generates a backup file named db1_students_backup.sql.
mysqldump -u user -pyour-password db1 students > db1_students_backup.sql
You can also take a backup of multiple MySQL tables by specifying them with space.
mysqldump -u user -pyour-password db1 students teachers > db1_students_teachers_backup.sql
This will take a backup of students and teachers tables from the db1 database and creates a backup file named db1_students_teachers_backup.sql.
Backup a Remote MySQL Database
In some cases, you may need to take a backup MySQL database hosted on the remote server. The basic syntax to backup a remote MySQL database is shown below.
mysqldump -h [remote-server-ip] -u [username] -p[password] [db-name] > [backup-name.sql]
The following command will take a backup of a remote database named remotedb and generates a backup file named remotedb_backup.sql.
mysqldump -h remote-server-ip -u user -pyour-password remotedb > remotedb_backup.sql
How to Restore MySQL Database
MySQL provides a restore features that allows you to restore a database from the backup file in the event of database corruption. In this section, we show you how to restore a MySQL database using the mysql command line utility.
The basic syntax to restore a database from the backup file is shown below.
mysql -u [username] –p[password] [database-name] < [database-backup.sql]
Before restoring a MySQL database, you need to create an empty database on the machine where you want to restore the database.
For example, to restore a single MySQL database named db1 from the backup file named db1_backup.sql, run the following command.
mysql -u user -pyour-password db1 < db1_backup.sql
You can use the mysqlimport command to restore a MySQL database, if the database already exists on the system.
mysqlimport -u user -pyour-password db1 < db1_backup.sql
To restore all MySQL databases, run the following command.
mysql -u user -pyour-password < all-db-backup.sql
If you want to restore a table from the backup file, run the following command.
mysql -u user -pyour-password db1 students < db1_students_backup.sql
Thank you for reading Backup and Restore a MySQL Database (Command Line). We shall conclude the article now.
Backup and Restore a MySQL Database (Command Line) Conclusion
In this post, you have learned how to backup and restore a MySQL database. I hope this helps you to perform your day to day tasks. Checking the backups at least once a month is a great safety protocol. This procedure verifies that your backups are not damaged and gives crucial recovery time information. You obtain the whole backup for your MYSQL after restoring it.
Please explore more of MySQL content in our blog by navigating here.
