MySQL Database backup restore

MYSQL Database Backup Restore

One, database backup

Backing up data is the most common operation for database management. To ensure the security of data in the database, data administrators need to perform regular data backups. Once the database has been compromised, it can be restored by a backed-up file. Therefore, data backup is a very important task.

1. Backup with mysqldump command

The mysqldump command can back up data from a database into a text file. The structure of the table and the data in the table are stored in the generated text file. The mysqldump command works very simply: it detects the structure of the table that needs to be backed up, generates a CREATE statement in a text file, and then converts all the records in the table to an INSERT statement. Number of restores

It is possible to use the CREATE statement in the creation of the table to restore the data using the INSERT statement.

The basic syntax for backing up a database with the mysqldump command is as follows:

Mysqldump–u Username–ppassword dbname table1t able2...>backupname.sql

2. Backup via replication

MySQL has one of the simplest methods of backup, which is to copy the database files directly . This method is the simplest and fastest. When using this method, it is best to stop the server first, which ensures that the data in the database does not change during replication. If there is data written during the copying of the database, the data will be inconsistent. This approach is simple and fast, but not the best backup method. Because, the actual situation may not allow to stop the MySQL server. Furthermore, this method does not apply to tables of the InnoDB storage engine. For MyISAM storage engine tables, this is convenient for backup and restore. However, it is best to restore the same version of the MySQL database, or you may have different storage file types.

3. Quick Backup using Msqlhotcopy tool

If you cannot stop the MySQL server while backing up, you can use the Msqlhotcopy tool. The Msqlhotcopy tool is backed up faster than the Msqlhotcopy command. The Msqlhotcopy tool is a Perl script that is primarily used under the Linux operating system. The Msqlhotcopy tool uses Locktables, Flustables, and CP for quick backups. It works by first imet-the data that needs to be made into a lock. Then, use Fushtales to write the data in memory back to the data book on the hard bit. Finally, copy the database files that need to be backed up to the target directory.

Second, database restore

Both the administrator's illegal operation and the computer's failure will corrupt the database files. When the database encounters these surprises, it is possible to restore the database to the state of the backup by backing up the file, which minimizes the loss.

1. Using MySQL Restore

Typically, you use the Myqdump command to back up data from a database into a text file, usually the suffix of this file is . sql. When you need to restore, you can use the MySQL command to restore the backed up data. The backup file typically contains the CREATE statement and the INSERT statement. The MySQL command executes the CREATE clause and insert language from the backup file, creating the database and table through the CREATE statement, and inserting the backed-up data through the INSERT statement. The basic syntax of the MySQL command is as follows,

Mysql–u Root–ppassword [dbname] <backup.sql

After execution, all databases in the All.sql file have been restored in the MySQL database

2. Restore by replication

When restoring in this way, you must ensure that the major version number of the two MySQL databases is the same , and that this method is valid for tables of type MyISAM and is not available for tables of type InnoDB, because table spaces for InnoDB tables cannot be copied directly.

MySQL Database backup restore