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

The importance of data backup

In the production environment, the security of data is very important, and any loss of data may have serious consequences and cause the loss of data.

1. Program error

two。 Human error

3. Computer failure

4. Disk failed

5.zai is difficult and stealing

Classified physical backup of database backup: 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: when the database is closed

-Hot backup: the database is running, and this backup method depends on the logical backup of log files of the database: backup of database components (such as tables and other database objects)

Table: table structure file (FRM), data file (MYD), index file (MYI)

From the point of view of database backup strategy, backup can be divided into

1. Full backup A complete backup of data at a time.

Full backup: all data in the server will be backed up, and this will be done every time

Pros: security

Disadvantages: redundant data backup, taking up disk space

two。 Differential backup backing up files that have been modified since the last full backup requires a full backup, and then only the files that have been modified based on the full backup are backed up at a time

3. Incremental backup only those files that were modified after the last full backup or incremental backup will be backed up

Comparison between differential backup and incremental backup:

The same point: the foundation is full backup.

Difference: differential backups only refer to basic full backups

Incremental backup refers to the last data backup compared with the current state, backup of modified files

Incremental backup is more efficient and space utilization is high, but the security performance is not high.

Mysql full backup is the backup of the whole database, database structure and file structure. Full backup saves the full backup of the database at the time of backup completion is the basis of incremental backup.

Advantages and disadvantages of full backup

The disadvantage of simple and convenient backup and recovery operation

There is a lot of duplication of data.

Take up a lot of backup space

Long backup and recovery space

Mysqldump backup library

Overview of mysqldump backup Library

The backup of mysql database can be directly packaged with database folders in a variety of ways, such as / usr/local/mysql/data----, which is a physical backup, using a professional backup tool, mysqldump----, which is a logical backup.

The mysqldump command backs up mysql's own backup tool for libraries. It is quite convenient to back up mysql. Through this command tool, you can export specified libraries, tables or all libraries to sql scripts and restore data when you need to restore.

Real fuck

Create databases, tables, insert data

The mysqldump command makes a full backup of a single library

Add multiple databases

[root@localhost mysql] # mysqldump-u root-p school > / opt/school.sqlEnter password: [root@localhost mysql] # ls / opt/dir_SC_UTF8 mysql-5.7.20 php-7.1.10 school.sqlmysql-2020-01-07.tar.xz nginx-1.12.2 rh description .htm [root@localhost mysql] # cd / opt [root@localhost opt] # ls school.sql school.sql [root@localhost opt] # vim school.sql [root@localhost opt] #

The mysqldump command makes a full backup of multiple libraries

Mysqdump-u username-p [password] [option]-- databases library name 1 [library name 2]... > / backup path / backup file name

Example of multi-database backup mysqldump-u root-p-- databases auth mysql > / backup/databases-auth-mysql.sql

Full backup of all libraries mysqdump-u username-p [password] [option]-- all-databases > / backup path / backup file name

Examples of all library backups: mysqldump-u root-p-- opt-- all-databases > / backup/all-data.sql

Operation using mysqldump backup table mysqdump-u username-p [password] [option] database name table name > / backup path / backup file name

Example of backup table mysqldump-uroot-p mysql user > / backup/mysql-user.sql [root@localhost opt] # mysqldump-uroot-p12341234 school info > / opt/school_info.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt] # ls / opt/school_info.sql / opt/school_ info.sql [root @ localhost opt] # vim / opt/school_info.sql

Backup based on table structure

[root@localhost opt] # mysqldump-uroot-p12341234-d school info > / opt/school_infod.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt] # vim / opt/school_infod.sql

Restore the database

SQL backup scripts exported using the musqldump command can be imported during data recovery using the following methods

Source command mysql command

Note: the source command is used in mysql mode; the mysql command is used in linux mode.

To restore a database using source

Log in to the mysql database

The path to execute source backup sql script: mysql [(none)] > source / backup/all-data.sql

Example:

Note: the path of the backup sql script is written to the absolute path

To restore a database is to restore the tables in the database. If the database is also deleted at this time, you need to create a database with the same name-take a closer look at the school.sql script, you can find that there is no school database operation.

[root@localhost opt] # mysql-uroot-pEnter password: Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases +-+ | Database | +-+ | information_schema | | bbs | | mysql | | performance_schema | | school | | sys | +-+ 6 rows in set (0.00 sec) mysql > use school;Database changedmysql > drop table info Query OK, 1 row affected (0.01 sec) mysql > show tables;Empty set (0.00 sec) mysql > source / opt/school.sqlQuery OK, 0 rows affected (0.00 sec) .Query OK, 0 rows affected (0.00 sec) mysql > show tables +-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec)

Use the mysql command to restore data

Mysql-u-p [password]

< 库备份脚本的路径 mysql>

Drop table info;Query OK, 0 rows affected (0.00 sec) mysql > show tables;Empty set (0.00 sec) mysql > quitBye [root@localhost opt] # [root@localhost opt] # mysql-uroot-p12341234 school

< /opt/school.sql mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost opt]# mysql -uroot -pEnter password: mysql>

Use school;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_school | +-+ | info | +-+ 1 row in set (0.00 sec) mysql > select * from info +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.00 sec)

Example of mysql command recovery

Mysql-u-p

< /backup/all-data.sql 恢复表的操作 恢复表时同样可以使用source或者mysql命令进行source恢复表的操作与恢复库的操作相同当备份文件中只包含表的备份，而不包括创建库的语句是，必须指定库名，切目标库必须存在mysql -u 用户名 -p [密码] < 表备份脚本的路径mysql -u -p mysql < /backup/mysql-user.sql mysql备份思路: 定期实施备份，制定备份计划或者策略，并严格遵守 除了进行完全备份，开启mysql服务器的日志功能是很重要的 完全备份加上日志，可以对mysql进行最大化还原 使用统一的和易理解的备份文件名称 不要使用backup1/2这样没有意义的名字 推荐使用库名或者表名加上时间的命名规则 备份文件名使用时间+业务名+库名 要开启服务器的日志功能 mysql增量备份 诞生增量备份的原因 解决使用mysqldump进行完全备份时的存在的问题 备份数据中有重复数据，会造成数据冗余、占用磁盘空间备份时间与回复时间长 增量备份就是备份上一次备份之后增加或变化的文件或内容 增量备份的优缺点: 优点：没有重复数据，备份量不大，时间短 缺点：恢复麻烦；需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复 通过mysql的二进制日志文件分割间接实现增量备份mysql没有提供直接的增量备份方法可以通过mysql提供的二进制文件（binary logs）间接实现增量备份mysql二进制日志对备份的意义 二进制日志保存了所有更新或者可能更新数据库的操作 二进制日志在启动mysql服务器后开始记录，并在文件达到maxbinlogsize所设置的大小或者接收到flush logs命令后重新创建新的日志文件 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份二进制文件针对位置点、时间点进行有效的恢复查看日志文件需要进行解码 mysql数据库增量恢复mysqlbinlog [-no-defaults] 增量备份文件 | mysql -u 用户名 -p 基于位置恢复 就是将某个起始时间的二进制日志导入到数据库中，从而跳过某个发生错误的时间点实现数据的恢复 命令： #恢复数据到指定位置mysqlbinlog --stop-position='操作 id' 二进制日志 | mysql -u 用户名 -p 密码#从指定的位置开始恢复数据mysqlbinlog --start-position='操作 id' 二进制日志 | mysql -u 用户名 -p 密码 基于时间点恢复 使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式 针对过程中的误操作备份，如何跳过误操作的方式----可以进行断点恢复 语法： #从日志开头截止到某个时间点的恢复mysqlbinlog [-no-defaults] --stop-datetime='年-月-日 小时：分钟：秒' 二进制日志 | mysql -u 用户名 -p 密码#从某个时间点到日志结尾的恢复mysqlbinlog [-no-defaults] --start-datetime='年-月-日 小时：分钟：秒' 二进制日志 | mysql -u 用户名 -p 密码#从某个时间点到某个时间点的恢复mysqlbinlog [-no-defaults] --start-datetime='年-月-日 小时：分钟：秒' --stop-datetime='年-月-日 小时：分钟：秒' 二进制日志 | mysql -u 用户名 -p 密码 实操 [root@localhost opt]# lsall.sql mysql-5.7.20 school_infod.sqldb_school_mysql.sql nginx-1.12.2 school_info.sqldir_SC_UTF8 php-7.1.10 school.sqlmysql-2020-01-07.tar.xz rh 说明.htm[root@localhost opt]# rm -rf *.sql[root@localhost opt]# lsdir_SC_UTF8 mysql-5.7.20 php-7.1.10 说明.htmmysql-2020-01-07.tar.xz nginx-1.12.2 rh 开启二进制日志功能，修改/etc/my.cnf文件，然后重启服务 [client]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysql]port = 3306default-character-set=utf8socket = /usr/local/mysql/mysql.sock[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sock'log-bin=mysql-binserver-id = 1default-storage-engine=Myisamsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES[root@localhost opt]# systemctl restart mysqld 查看二进制日志文件 [root@localhost opt]# cd /usr/local/mysql/[root@localhost mysql]# cd data/[root@localhost data]# lsauto.cnf ibdata1 ibtmp1 mysql-bin.index sysbbs ib_logfile0 mysql performance_schemaib_buffer_pool ib_logfile1 'mysql-bin.000001' school[root@localhost data]# 做增量备份前，要先进行一次完全备份 [root@localhost data]# mysqldump -uroot -p12341234 school >

/ opt/school.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure. [root@localhost data] # cd / opt [root@localhost opt] # lsdir_SC_UTF8 mysql-5.7.20 php-7.1.10 school.sqlmysql-2020-01-07.tar.xz nginx-1.12.2 rh description .htm [root@localhost opt] # vim school.sql

Next, do an incremental backup. At this point, the previous operation is stored in 001, and the next operation is stored in 002.

[root@localhost opt] # mysqladmin-uroot-p12341234 flush-logsmysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt] # ls / usr/local/mysql/data/auto.cnf ibdata1 ibtmp1 mysql-bin.000002 schoolbbs ib_logfile0 mysql mysql-bin.index sysib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schemamysql > use school 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 info +-+ | id | name | score | +-+ | 1 | stu01 | 88.0 | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.00 sec) mysql > insert into info (name,score) values ('test01',66) / / this is the normal operation Query OK, 1 row affected (0.00 sec) mysql > select * from info +-1 | stu01 | 88.0 | 2 | stu02 | 77.0 | | 3 | test01 | 66.0 | +-+ 3 rows in set (0.00 sec) mysql > delete from info where name='stu01' / / misoperation Query OK, 1 row affected (0.01 sec) mysql > insert into info (name,score) values ('test02',99); / / normal operation Query OK, 1 row affected (0.01 sec) mysql > select * from info +-4 | test02 | 2 | stu02 | 77.0 | | 3 | test01 | 66.0 | +-+ 3 rows in set (0.01sec) mysql > quitBye

At this time, an incremental backup is carried out without knowing it, and the misoperation is written in 002.

[root@localhost opt] # mysqladmin-uroot-p12341234 flush-logsmysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@localhost opt] # cd / usr/local/mysql/data/ [root@localhost data] # lsauto.cnf ib_logfile0 mysql-bin.000001 performance_schemabbs ib_logfile1 mysql-bin.000002 schoolib_buffer_pool ibtmp1 mysql-bin.000003 sysibdata1 mysql mysql-bin.index

View log file:-v display content in the interface,-base64 decoder output output decode-rows read by line read

[root@localhost data] # mysqlbinlog-- no-defaults mysql-bin.000002 / / look at the binary log file, but you can find that you don't understand / *! 50530 SET @ @ SESSION.PSEUDOSLAVEMODEX1 "50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / / # at 4root@localhost data / this is the location # 200107 16:54:11 server id 1 end_log_pos 123 CRC32 0x76a9dc26 Start: binlog v 4, server v 5.7.20-log created 200107 16 CRC32 0x76a9dc26 Start 54 CRC32 0x76a9dc26 Start / this is a point in time BINLOG 'M0cUXg8BAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA// like this is the encrypted command [root@localhost data] # mysqlbinlog-- base64-output=decode-rows-v mysql-bin.000002 > / opt/back.txt//-v display content in the interface Base64 decoder output output decode-rows read read by line [root@localhost data] # cd / opt [root@localhost opt] # vim back.txt / / the screenshot below is the data information in bak.txt

Screenshot of error operation

200107 16:57:56-stop-datetime / means starting from this log file and stopping execution at this point in time

200107 16:58:46-start-datetime / refers to the log file that executes later from this point in time

First full backup and restore, source / opt/school.sql

[root@localhost opt] # mysql-uroot-p12341234mysql > use school;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > drop table info;Query OK, 0 rows affected (0.01 sec) mysql > select * from info;ERROR 1146 (42S02): Table 'school.info' doesn't existmysql > show tables;Empty set (0.00 sec) mysql > source / opt/school.sql;Query OK, 0 rows affected (0.00 sec). Mysql > show tables;+-+ | Tables_in_school | +-+ | info | +-+ 1 row in set (0.00 sec) mysql > select * from info +-score | 1 | stu01 | 88.0 | 2 | stu02 | 77.0 | +-+ 2 rows in set (0.00 sec) mysql >

Then incremental recovery, that is, breakpoint recovery in time.

[root@localhost opt] # mysqlbinlog-- no-defaults-- stop-datetime='2020-01-07 16V 57 stop-datetime='2020 56'/ usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-pEnter password: [root@localhost opt] # mysql- uroot-p12341234mysql: [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 9Server version: 5.7.20-log Source distributionCopyright (c) 2000, 2017, 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 > use school;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 info +-mysql > quitBye [root@localhost opt] # mysqlbinlog-- no | 3 | test01 | 66.0 | +-- + 3 rows in set (0.00 sec) mysql > quitBye [root@localhost opt] # mysqlbinlog-- no -defaults-- start-datetime='2020-01-07 16 usr/local/mysql/data/mysql-bin.000002 58 usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-pEnter password: [root@localhost opt] # mysql- uroot-p12341234mysql: [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 11Server version: 5.7.20-log Source distributionCopyright (c) 2000, 2017, 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 > use school;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 info +-the deleted stu01 has not been deleted mysql >.

Location-based recovery

Log file for error operation

[root@localhost opt] # mysqlbinlog-- no-defaults-- stop-postion='612' / usr/local/mysql/data/mysql-bin.000002 | mysql- u root-pmysqlbinlog: [ERROR] unknown variable 'stop-postion=612'Enter password: ERROR 1045 (28000): Access denied for user' root'@'localhost' (using password: YES) [root@localhost opt] # mysqlbinlog-no-defaults-stop-position='612' / usr/local/mysql/data/mysql-bin.000002 | mysql -uroot-pEnter password: [root@localhost opt] # mysql-uroot-p12341234mysql: [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.20-log Source distributionCopyright (c) 2000, 2017, 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 > use school;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 info +-mysql > quitBye [root@localhost opt] # mysqlbinlog-- no | 3 | test01 | 66.0 | +-- + 3 rows in set (0.00 sec) mysql > quitBye [root@localhost opt] # mysqlbinlog-- no -defaults-- start-position='716' / usr/local/mysql/data/mysql-bin.000002 | mysql- uroot-pEnter password: [root@localhost opt] # mysql- uroot-p12341234mysql: [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 16Server version: 5.7.20-log Source distributionCopyright (c) 2000, 2017, 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 > use school;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 info +-mysql > desc info. 4 | test02 | 99.0 | 3 | test01 | 66.0 | +-+ 4 rows in set (0.00 sec) mysql > desc info +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+-+ | id | int (4) | NO | PRI | NULL | auto_increment | | name | varchar (10) | NO | | NULL | | score | decimal (4) | NO | | NULL | | +-+- -+ 3 rows in set (0.00 sec) mysql > quitBye [root@localhost opt] # mysqlbinlog-- no-defaults / usr/local/mysql/data/mysql-bin.000002 | mysql- u root-pamp / this is incremental recovery Perform all operations in the log file

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.