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
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.
- 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.
- 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.
- 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.
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.
- 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
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 -u [username] –p[password] [database-name] > [backup-file.sql]
A brief explanation of each option is shown below:
How to Backup MySQL Database
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.
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.
mysqldump -u user -pyour-password --no-create-db --no-create-info db1 > 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
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
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
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.