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

The importance of database backup improves the high availability and disaster recoverability of the system. When the database system crashes, it is impossible to find data without data backup. Using database backup to restore database is the best way to provide the minimum cost of data recovery when the database crashes. There is no database, there is nothing, database backup is a powerful means to prevent disasters. Classified physical backup of database backup: refers to the backup of physical files (such as data files, log files, etc.) of the database operating system. Physical backup visitors are divided into offline backup (cold backup) and online backup (hot backup).

Cold backup: the backup operation carried out when the database is closed can better ensure the integrity of the database.

Hot backup: operates in the running state of the database, which relies on the log files of the database. Logical backup: a backup of database logical components, such as tables and other database objects. From the point of view of database backup strategy, backup can be divided into full backup, differential backup and incremental backup.

Full backup: make a full backup of the database each time. You can back up the entire database, including all database objects such as user tables, system tables, indexes, views, and stored procedures.

Differential backup: back up files that have been modified since the last full backup, backing up only part of the database.

Incremental backup: only those files that were modified after the last full backup or incremental backup will be backed up. Full backup (backup operation in linux mode) full physical backup uses tar packaged folders to back up database compression using xz format compression with a high compression ratio. Rpm-Q xz / / check whether the xz package installs yum-y install xz / / install the xz package to package the database folder (/ usr/local/mysql/data/) tar Jcvf / opt/mysql-$ (date +% F) .tar.xz / usr/local/mysql/data/ back up the database folder and name ls / opt/mysql-2018-07-02.tar.xz in the form of year, month and day

Restore database files

You only need to decompress the database file and unzip it. Even if the entire / data/ directory is lost, you can still find mkdir / abctar Jxf mysql-2018-07-03.tar.xz-C / abc/.

Full logical backup use the mysqldump tool to back up a database mysqldump-u root-p stady > / opt/stady.sql// back up the stady library to the / opt directory.-p can be followed by a password or not. If you don't write, enter your password to verify it after you enter. * * backup files are all suffixed with .sql. The preceding name can be found in the name * *

View the contents of the backup file

Back up multiple databases mysqldump-u root-p-- databases mysql stady > / opt/mysql-stady.sql

Backup all databases mysqldump-u root-p-- opt-- all-databases > / opt//all.sql backup tables in the database mysqldump-u root-p stady info > / opt/stady-info.sql / / nearly database name, followed by table name backup database table structure mysqldump-u root-p-d stady info > / opt/decribe-info.sql//-d database name + table name data recovery login MySQL Use the source command to restore the info table [root@centos7-6 /] # mysql-u root-p Enter password: / / omit part of the contents mysql > use stady in the backup database step / / switch to the library Database changedmysql > drop table info; / / delete the info table Query OK, 0 rows affected (0.02 sec) mysql > show tables; / / View the empty mysql > source / opt/stady-info.sql / / restore info table in the library table Empty set (0.00 sec) / / restore the info table in the format source+ backup file path and file name Query OK, 0 rows affected (0.02 sec) / / omit some of the contents mysql > show tables / / View the table in the library +-+ | Tables_in_stady | +-+ | info | / / the info table has been restored successfully +-+ 1 row in set (0.00 sec) use the mysql command to restore the table [root@centos7-6 /] # mysql-u root- P Enter password: / / omit part of the content mysql > use stady / / switch to the library Database changedmysql > drop table info; / / delete the info table Query OK, 0 rows affected (0.02 sec) mysql > show tables; / / View the empty mysql (0.00 sec) / / in the database, and switch to linuxBye [root @ centos7-6 /] # mysql-u root-p stady

< /opt/stady-info.sql //在linux环境中恢复Enter password: [root@centos7-6 /]# mysql -u root -p //进入数据库查看Enter password: ··· //省略部分内容mysql>

Use stady;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables;+-+ | Tables_in_stady | +-+ | info | / / info table has been restored +-+ 1 row in set (0.00 sec)

Restore the library and restore the table in the same way, but when restoring the data table, be aware that if only the table file is backed up in the backup file, and there is no library in mysql, the direct recovery will fail. You must first create the library and then restore it.

Incremental backup

Create stady libraries in MySQL databases, create info tables, and insert data

Mysql > use stady;mysql > select * from info +-+-+ | name | score | +-+-+ | zhangsan | 80.00 | | lisi | 90.00 | +-+-+ 2 rows in set (0.00 sec) start binary log vim / etc/my.cnflog-bin=mysql-bin / / insert the statement systemctl restart mysqld.service / / restart the mysql service in the [mysqld] segment A new log file (empty file) is generated in the / data/ directory

Use mysqldump to back up the stady library mkdir / backupmysqldump-u root-p stady > / backup/stady.sqlmysqladmin-u root-p flush-logs / / refresh to generate a new log file, which exists in 01, and the new 02 is an empty file

Insert new data to generate a new incremental log file mysql > insert into info (name,score) values ('chen',90); / / enter the database, insert data mysqladmin-u root-p flush-logs / / refresh to generate a new incremental file, and the incremental backup is saved in 02 mysql > insert into info (name,score) values (' chen01',96); / / enter the database again and insert data mysql > select * from info into the table +-+-+ | name | score | +-+-+ | zhangsan | 80.00 | | lisi | 90.00 | chen | 90.00 | chen01 | 96.00 | +-+ 4 rows in set (0.00 sec) mysqladmin-u root-p flush-logs / / refresh again, incremental backup saved in 03

Incremental recovery simulation data loss, delete two newly added records [root@centos7-6 data] # mysql-u root-p Enter password: mysql > use stady;Database changedmysql > delete from info where name='chen';Query OK, 1 row affected (0.00 sec) mysql > delete from info where name='chen01';Query OK, 1 row affected (0.00 sec) mysql > select * from info +-+-+ | name | score | +-+-+ | zhangsan | 80.00 | | lisi | 90.00 | +-+-+ 2 rows in set (0.00 sec) use the mysqlbinlog command Restore data [root@centos7-6 data] # mysqlbinlog-- no-defaults mysql-bin.000002 | mysql- u root-pEnter password: / / enter password to confirm recovery data [root@centos7-6 data] # mysql- u root-pEnter password: / / enter password to log in to the system mysql > use stady Database changedmysql > select * from info +-+-+ | name | score | +-+-+ | zhangsan | 80.00 | | lisi | 90.00 | | chen | 90.00 | / / 02 data of incremental backup successfully recovered +-+-+ 3 rows in set (0.00 sec) use mysqlbinlog to view incremental backup text The contents of mysqlbinlog-no-defaults-base64-output-decode-rows-v / usr/local/mysql/data/mysql-bin.000002//--base64-output=decode-rows are decoded using 64-bit encoding mechanism Read the-v display by line / *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/ / / omit part of the content # at 345 / 180703 21:12:11 server id 1 end_log_pos 389 CRC32 0xb0bc2cb1 Write_rows: table id 118 flags: STMT_END_F### INSERT INTO `stady`.`info` # SET### @ 1='chen'### @ 2 = 90.00 / / omit part of the content

After opening the binary log, an empty mysql-bin.000001 file is generated in the / data/ directory. After using the command mysqladmin-u root-p flush-logs, the incremental backup is written to mysql-bin.000001, and a new empty mysql-bin.000002 file is generated, waiting for the next refresh to write, and then create mysql-bin.000003.

Incremental breakpoint recovery simulation environment

The misoperation of the simulated insert data occurred, and while inserting two pieces of data, a correct piece of data was deleted.

[root@centos7-6 data] # mysql-u root-p Enter password: mysql > use stady;Database changedmysql > insert into info (name,score) values ('test01',88); Query OK, 1 row affected (0.00 sec) mysql > delete from info where name='chen';Query OK, 1 row affected (0.00 sec) mysql > insert into info (name,score) values (' test02',88); Query OK, 1 row affected (0.01 sec) mysql > select * from info +-+-+ | name | score | +-+-+ | zhangsan | 80.00 | | lisi | 90.00 | | test01 | 88.00 | test02 | 88.00 | +-+ 4 rows in set (0.00 sec) / / two test01 and test02 are inserted But chen is operated to delete mysql > quit / exit database Bye [root@centos7-6 data] # mysqladmin-u root-p flush-logs / / refresh to generate new incremental files, save in 04 Enter password: incremental time node to restore decoding exported incremental backup files, easy to view and do recovery mysqlbinlog-- no-defaults-- base64-output=decode-rows-v mysql-bin.000004 > / backup/info.txtcd / backup

Record the start time of the error operation and the start time of the correct operation, and skip the operation during the recovery period.

Recover the misoperated data mysql > drop table info; / / delete the misoperated table mysql-u root-p stady according to the point in time

< /backup/stady.sql //恢复完全备份的stady库mysqlbinlog --no-defaults --stop-datetime='2018-07-03 21:57:08' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p //恢复误操作前的内容mysqlbinlog --no-defaults --start-datetime='2018-07-03 21:57:14' /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p //恢复误操作之后的内容根据位置标记号恢复数据 mysql>

Drop table info; / / Delete the misoperated table mysql- u root-p stady < / backup/stady.sql / / restore the full backup stady library mysqlbinlog-- no-defaults-- stop-position='1408' / usr/local/mysql/data/mysql-bin.000005 | mysql- u root-pmysqlbinlog-- no-defaults-- start-position='1674' / usr/local/mysql/data/mysql-bin.000005 | mysql- u root-p data recovery is complete

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.