Shulou(Shulou.com)06/01 Report--

This article mainly introduces Mysql database backup and recovery analysis, hoping to supplement and update some knowledge, if there are other problems you need to know, you can continue to follow my updated article in the industry information.

The importance of database backup in the production environment, the security of data is very important, any loss of data may have serious consequences. The importance of data backup is mainly reflected in (1) improve the high availability and disaster recoverability of the system. When the database system crashes, there is no way to recover data without database backup. (2) use database backup to restore the database. It is the best scheme to provide the minimum cost of data recovery when the database crashes. If users are allowed to add data again, it will be too expensive. (3) if there is no data, there will be nothing. Database backup is a powerful means to prevent disasters and causes data loss: 1) program error 2) human misoperation 3) computer failure (system crash) 4) disk failure 5) disasters (such as fire, earthquake) and theft classification of database backups: (1) physical backup Refers to the backup of physical files (such as data files, log files, etc.) of the database operating system. Physical backup can be divided into offline backup (cold backup) and online backup (hot backup) cold backup: backup operation when the database is closed, can better ensure the integrity of the database hot backup: backup in the database running state This backup method depends on the log files of the database (2) logical backup: refers to the backup of database logical components (such as tables and other database objects) from the perspective of database backup strategy, backup can be divided into full backup, differential backup and incremental backup 1) full backup: complete backup of the data each time. You can back up the entire database, including all database objects such as user tables, system tables, indexes, views, and stored procedures. But it takes more time and space. Therefore, the cycle of doing a full backup is longer. 2) differential backup: back up those files that have been modified since the last full backup, and only part of the database is backed up, which is smaller than the initial full backup. Because it contains only the database that has changed since the last full backup. The advantage is that the storage and recovery speed is fast. 3) incremental backup: only those files that were modified after the last full backup or incremental backup will be backed up. Example create database school Create table info (id int, nsme char (10)); insert into info values (1 Magi Magi QW');-full backup-insert into info values (2 Magi Magi as') -incremental backup-- insert into info values (3); incremental backup and differential backup-insert into info values (4) This incremental backup only backs up 4, while the differential backup only backs up the content incremental backup after the last full backup, then the backup Mysql full backup is based on the part added after the last backup. The database files of mysql backed up with tar packaged folders are saved in the data folder of the installation directory by default, and the data folder can be saved directly, but it takes up a lot of space. So use tar package compression to save (1) the database file is very large, to use the xz format with high compression ratio, yum-y install xz # install the compression tool xztar Jcvf / opt/mysql-$ (date +% F). Tar.xz / usr/local/mysql/data # option is uppercase J

2. Using the mysqldump tool to back up the Mysql entire database directory described earlier is to back up everything in the database. Use the mysqldump tool to control the contents of the backup more flexibly, for example, several tables or libraries can be backed up separately. The file after backup is a sql script that ends with a fixed format (1) backup table data mysqldump-u user name-p password option database name table name > / backup path / backup file name mysqldump-u root-p school info > / opt/info.sql

(2) single library backup mysqldump-u user name-p password option database name > / backup path / backup file name mysqldump-u root-p school > / opt/school.sql

(3) multiple library backup mysqldump-u username-p password option-- databases library name 1 library name 2 > / backup path / backup file name mysqldump-u root-p-- databases kgc school > / opt/school_kgc.sql

(4) full backup of all libraries mysqldump-u username-p password option-- all-databases > / backup path / backup file name mysqldump-u root-p-- all-databases > / opt/all.sql

(5) backup table structure mysqldump-u user name-p password-d database name table name > / backup path / backup file name mysqldump-u root-p-d school info > / opt/info-d.sql

Mysqul data full recovery when you need to restore the database, you can use the source command and the mysql command 1 focus source command to restore the entire database and restore mysqldump-u root-p school > / opt/school.sql if the library school has been backed up.

2Permanent MySQL command to restore the whole library is also under the premise that the library school has been backed up.

(1) mysqldump-u root-p school > / opt/school.sql # only the contents of the table in the backup library school, but the school of the library itself is not backed up. When restoring data, you need to create a library school before you can restore it.

(2) mysqldump-u root-p-- databases school > / opt/school01.sql # together with the library school itself and the tables in it will be backed up and can be restored directly during recovery.

Restore table operation 1, use the source command to restore the table mysqldump-u root-p school info > / opt/info.sql if the data table has been backed up

2. Using mysql to restore a data table is also a recovery operation mysqldump-u root-p school info > / opt/info.sqlmysql-u user name-p password library name if the data table has been backed up.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.