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

Overview of database backup

Overview: the existing data in the database is stored as a copy of the data, which can solve the problem of data disaster recovery.

Improve the high availability and disaster recovery of the system, and restore the data at a minimum cost when the data crashes

Causes of data loss: program errors, human errors, disk errors, natural and man-made disasters

Classification of database backup

Physical backup

Refers to the backup of physical files (data files, log files) of the database operating system

Cold backup

Backup must be made when the database is closed, which can better ensure the integrity of the database.

Hot backup

Can back up the database when the database is running normally, which can guarantee the availability of the service.

Logical backup

Refers to the backup of the logical components of a database (databases, tables, data objects)

Full backup

It takes longer to make a full backup of the data, including complete libraries, tables, indexes, views, etc.

Differential backup

Backup the contents of the database that have changed since the last full backup, the backup files are smaller than the full backup files, and the backup speed is faster

Incremental backup

Backup to the contents of the database that have been modified since the last full or incremental backup

Data backup and recovery

Physical backup (cold backup)

[root@host50 backup] # cp-rp / var/lib/mysql/dumptest1/ / opt/backup/dumptest1 [root@host50 backup] # lsdumptest1 [root@host50 backup] # lsdumptest1 / db.opt test.frm test.ibd [root@host50 backup] # ls / var/lib/mysql/dumptest1/db.opt test.frm test.ibd [root@host50 backup] # tar-zcvf mysql-backup-$ (date +% F). Tar.gz dumptest1/dumptest1/dumptest1/db.optdumptest1/test.frmdumptest1/test .ibd [root@host50 backup] # lsdumptest1 mysql-backup-2019-07-07.tar.gz / / dumptest1 library backup and compression completed [root@host50 backup] # rm-dfr / var/lib/mysql/dumptest1/ delete mysql database dumptest1 library [root@host50 backup] # systemctl restart mysqld [root@host50 backup] # mysql- uroot- P123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 3Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases / / check that the deleted library is no longer +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | transtb | | +-+ 5 rows in set (0.00 sec) [root@host50 backup] # tar-zxvf mysql-backup-2019-07-07.tar.gz-C / var/lib/mysql/ restore database dumptest1/dumptest1/db.optdumptest1/test.frmdumptest1/test.ibd [root@host50 backup] # systemctl restart mysqld [root@host50 backup] # mysql- uroot-p1234566mysql: [ Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) [root@host50 backup] # mysql-uroot-p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.7.17 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases +-+ | Database | +-+ | information_schema | | dumptest1 | | mysql | | performance_schema | | sys | | transtb | +-+ 6 rows in set (0.00 sec) mysql > use dumptest1 Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from test; / / Library restored successfully +-+ | job | +-+ | aa | | bb | | c | dd | +-+ 4 rows in set (0.00 sec)

Logical backup (hot backup)

Library name representation

-all-databases or-An all libraries

-Database name single library

-Database name table name sheet table

-- B database 1, database 2, more than one library

Matters needing attention

-validate user permissions regardless of backup or restore

[root@host50 backup] # mysqldump-uroot-p-- all-databases > / opt/backup/alldb.sql / / back up all libraries Enter password: [root@host50 backup] # lsalldb.sql [root@host50 backup] # grep-vE'^ / | ^ $'alldb.sql | head-15 / / View some contents of the library CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `dumptest1` / *! 40100 DEFAULT CHARACTER SET latin1 * /; USE `dumptest1`; DROP TABLE IF EXISTS `test` CREATE TABLE `test` (`job` varchar (30) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES ('aa'), (' bb'), ('c'), ('dd'); UNLOCK TABLES;CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `mysql` / *! 40100 DEFAULT CHARACTER SET latin1 * /; USE `mysql`; DROP TABLE IF EXISTS `test` CREATE TABLE `Host` char (60) COLLATE utf8_bin NOT NULL DEFAULT'', `Db` char (64) COLLATE utf8_bin NOT NULL DEFAULT'' [root@host50 backup] # mysqldump-uroot-p dumptest1 > / opt/backup/dumptest1.sql / / back up only one library Enter password: [root@host50 backup] # lsalldb.sql dumptest1.sql [root@host50 backup] # mysqldump-uroot-p-B dumptest1 transtb > / opt/backup/dumptest1+transdb.sql / / backup specified multiple libraries Enter password: [root@host50 backup] # lsalldb.sql dumptest1.sql dumptest1+transdb.sql

Restore from a backup using the MYSQL command

It is usually not recommended to directly overwrite the old library, but to create a new library and import a logical backup to perform the restore, and when the new library is normal, you can discard or delete the old library.

[root@host50 backup] # mysql-uroot-p dumptest2

< /opt/backup/dumptest1.sql Enter password: [root@host50 backup]# mysql -uroot -p123456mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 14Server version: 5.7.17 MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Select * from dumptest2.test;+-+ | job | +-+ | aa | | bb | | c | | dd | +-+ 4 rows in set (0.00 sec)

Overview of binlog Log

The use and configuration of binary logs

Operation of type usage configuration binary log recording all change data

Log_bin= [dir / name]

Server_id= digit

Max_binlog_size= digit m

Benefits of adopting binlog logs

-record all SQL commands except queries

-can be used for data recovery

-configure the necessary conditions for MySQL master-slave synchronization

Binlog related files

-Hostname-bin.index record has a log file name

-Hostname-bin.000001 's first binary log

-Hostname-bin.000002 second binary log

Example:

Configure / etc/my.cnf and restart the service

[root@host50 backup] # vim / etc/ my.cnf[mysqld].. .. log-bin-index=mysql-bin / / enables binary logging and specifies the prefix server_id=1binlog_format=STATEMENT / / in Mysql5.7, the binlog log format defaults to ROW, but it does not record context-sensitive information about sql statements. The binlog log format needs to be modified to STATEMENT [root@host50 backup] # systemctl restart mysqld [root@host50 backup] # ls / var/lib/mysql/mysql-bin.* / / after binlog is newly enabled, each time you start the MySQl service, a new log file / var/lib/mysql/mysql-bin.000001 / var/lib/mysql/mysql-bin.index [root@host50 backup] # ls / var/lib/mysql/mysql-bin.* / / restarts the MySQL service program Or perform the SQL operation "FLUSH LOGS" A new log is generated: / var/lib/mysql/mysql-bin.000001 / var/lib/mysql/mysql-bin.index/var/lib/mysql/mysql-bin.000002 [root@host50 backup] # cat / var/lib/mysql/mysql-bin.index/ / mysql-bin.index file records the list of currently held binaries. / mysql-bin.000001./mysql-bin.000002

Use binlog logs to restore table records

Mysql > create database binlogdb; / / create the library table, and restore Query OK after deletion, 1 row affected (0.01 sec) mysql > use binlogdb;Database changedmysql > create table tb1 (- > id int (4),-> name varchar (20)); Query OK, 0 rows affected (0.00 sec) mysql > insert into tb1 values-> (1), (2)),-> (3) Query OK, 3 rows affected (0.21 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from tb1;+-+-+ | id | name | +-+-+ | 1 | aa | | 2 | bb | 3 | cc | +-+-+ 3 rows in set (0.00 sec) mysql > delete from tb1;Query OK, 3 rows affected (0.00 sec) mysql > select * from tb1 Empty set (0.00 sec) [root@host50 backup] # mysqlbinlog / var/lib/mysql/host50-bin.000003 / / check the binlog log to confirm the recovery time / *! 50530 SET @ @ SESSION.PSEUDOThe SLAVEMODEP 1 "SET" 50003 / OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / / # at 4 "190707 1:49:26 server id 1 end_log_pos 123 CRC32 0x92d92fbb Start: binlog v 4, server v 5.7.17-log created 190707 1GV 49 CRC32 0x92d92fbb Start: this binlog is either in use or was not closed properly.BINLOG 'Jt8gXQ8BAAAdwAAHsAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 123 "190707 1:49:26 server id 1 end_log_pos 1554 CRC32 0x12dd5e8b Previous-GTIDs# [empty] # at 154" 190707 1:53:03 server id 1 end_log_pos 219 CRC32 0xba04792a Anonymous_GTID last_committed=0 sequence_number=1SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSITE at 219 "190707 1:53:03 server id 1 end_log_pos 325 CRC32 0x651624b7 Query thread_id=6 exec_time=0 error_code=0SET Timestamps 1562435583 ex ex session. SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlchocolate modeboxes 1436549152, SET @ @ session.session .autoincrementalization offsetbacks @ @ session.auto_increment_increment=1, @ @ session .autoincrementalization offsetbacks @ @ session. Session * / / *! * /; SET @ @ session. Session. SET @ @ session.lcroomtimestamps namespace namespaces @ @ session.collationalization databaseholders defaultDFAULTDULTGER server id create database binlogdbpact where at 325: 190707 1:53:43 server id 1 session 390 CRC32 0x13116bdf Anonymous_GTID last_committed=1 sequence_number=2SET @ SESSION.GTID_NEXT= 'ANONYMOUSSTAMPSTAMPOS 1562435623 server id `binlogdb` / *! /; SET TimESTAMP Create table tb1 (id int (4), name varchar (20)) / *! * /; # at 518 19707 1:54:23 server id 1 end_log_pos 583 CRC32 0x8dc7fcdb Anonymous_GTID last_committed=2 sequence_number=3SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to server id # at 583 "190707 1:54:23 server id 1 end_log_pos 670 CRC32 0x58cc7317 Query thread_id=6 exec_time=0 error_code=0SET Timestamp 1562435663 # at 670 end_log_pos 190707 1:54:23 server id 1 end_log_pos 803 CRC32 0xf0848df1 Query thread_id=6 exec_time=0 error_code=0SET timestamp 1562435663Compact lead lead inserts into tb1 values (1recollection AA'), (2memorialbb'), (3recollection cc') / * /; # at 80314190707 1:54:23 server id 1 end_log_pos 834 CRC32 0x01790e76 Xid = 17COMIT # at 834 "190707 1:54:55 server id 1 end_log_pos 899 CRC32 0xba024a34 Anonymous_GTID last_committed=3 sequence_number=4SET @ @ SESSION.GTID_NEXT= 'ANONYMOUSAccording to the server id # at 899 190707 1:54:55 server id 1 end_log_pos 986 CRC32 0xc99b2859 Query thread_id=6 exec_time=0 error_code=0SET timestamp 1562435695 BEGINMAX / CRC32 0xda0eb644 Query thread_id=6 exec_time=0 error_code=0SET TIMESTAMP1562435695 / Delete from tb1According to set @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *!; DELIMITER; # End of log fileholder / at 1083' 190707 1:54:55 server id 1 end_log_pos 1114 CRC32 0xd72d7ba3 Xid = 19COMMIT lead to set @ @ SESSION.GTID_NEXT= 'SET / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filewise to show that 50003 SET components are available to users. [root@host50 backup] # mysqlbinlog-- start-datetime= "2019-07-07 1:54:23"-- stop-datetime= "2019-07-07 1:54:55" / var/lib/mysql/host50-bin.000003 | mysql-uroot-p123456mysql > select * from binlogdb.tb1 +-+-+ | id | name | +-+-+ | 1 | aa | | 2 | bb | | 3 | cc | +-+-+ 3 rows in set (0.00 sec)

Backup Strategy of Mysql Database in production Environment

Strategy design ideas:

1. If the data is updated frequently, more frequent backups should be carried out.

two。 If the data is more important, make a backup when there is an appropriate update

3. Make a full backup during the period of low database pressure

Backup scenario:

1. Use mysqldump for full backups of important databases at some point every weekend (preferably a single database backup)

Instead of all)

two。 Terminate the binary log file during the usual evening time (provided that it is reasonable according to the number of data records generated

Order the size of the binary file), so that a binary log file is generated every 24 hours

3. The total weekly backup files and the daily binary log files add up to the overall content of the database during the week.

4. When a failure occurs, restore the full backup first. You can execute the mysqlbinlog command according to the position value according to the actual situation.

Or restore at a point in time to make sure it's foolproof.

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.