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

MySQL backup and recovery

Type of data backup

1. Physical backup

Cold backup: a backup operation that occurs when the database is shut down.

Hot backup: backup operation while the database is running, this method depends on the log files of the database.

Warm backup: a backup operation that occurs when a database locks a table (not writable but readable).

2. Logical backup

Backups of database logical components, such as tables, are represented as logical database structures (create database,create table statements) and

Content (insert statements or split text files), this type of backup is suitable for small amounts of data that can be edited with data values or table structures, or without

Recreate data on the same machine architecture

3. From the point of view of database backup strategy, database backup can be divided into full backup, differential backup and incremental backup.

Full backup: every complete backup of the database, that is, the backup of the whole database, the overall backup of database structure and file structure, is the basis of differential backup and incremental backup.

Differential backup: back up all files that have been modified since the last full backup. The backup time point is from the last full backup. The amount of backup data will become larger and larger. When you restore the data, you only need to

Restore the last full backup and the most recent differential backup

Incremental backup: only those files that were modified after the last full or incremental backup will be backed up, based on the time of the last full or incremental backup.

As a result, the amount of data backed up is small, the space occupied is small, and the backup speed is fast, but it is necessary to restore the last full backup to the last incremental backup.

Restore

Common backup methods of database

Physical cold backup

Tar command

Dedicated backup tool

Mysqldump

Mysqlhotcopy

Binary log

Third-party tool backup

Percona XtraBackup

Xtrabackup

1. Database full backup operation

1. Physical cold backup and recovery; use the tar command to package the database folder directly, and stop the mysql service before backup

Back up the database; create a / backup directory as the backup database path, and use tar to create backup files. The entire database folder backup belongs to the full backup [root@localhost ~] # systemctl stop mysqld.service [root@localhost ~] # mkdir / backup [root@localhost ~] # tar zcf / backup/mysql_all-$ (date +% F). Tar.gz / usr/local/mysql/data/ [root@localhost ~] # ls-1 / backup/ Total usage 736 RWantha-1 root root 751542 August 15 08:40 mysql_all-2018-08-15.tar.gz restore the database Do the following to transfer the database file / usr/local/mysql/data/ to the bak directory to simulate the failure. [root@localhost ~] # mkdir / bak [root@localhost ~] # mv / usr/local/mysql/data/ / bak/ perform the following operations to recover data from backup files [root@localhost ~] # mkdir restore/ [root@localhost ~] # tar zxf / backup/mysql_all-2018-08-15.tar.gz-C restore/ [root@localhost ~] # mv restore/usr/local/mysql/data/ / usr/local/mysql/ [root@localhost ~] # systemctl start mysqld.service

2. Mysqldump backup and recovery

Backup the database; when exporting data using the mysqldump command, it will be displayed directly on the terminal by default. To save to a file, you need to combine the'> 'redirection of shell.

Output operation. The format of the command is as follows.

Format 1; back up some tables in the specified database

Mysqldump [options] Library name [Table name 1] [Table name 2]... > / backup path / backup File name

Format 2; back up one or more complete libraries (including all tables)

Mysqldump [options]-- databases library name 1 [library name 2]... > / backup path / backup file name

Format 3; back up all libraries in the mysql database

Mysqldump [options]-- all-databases > / backup path / backup file name

Among them, the common options include "- u"- p", which are used to specify the user name and password of the database, respectively.

For example, the following actions export the user table in the mysql library to mysql-user.sql and the entire benet library using format 1 and format 2, respectively

Is a benet.sql file, and all operations are authenticated as root users

[root@localhost ~] # mysqldump-u root-p mysql user > mysql-user.sql

Enter password: # # root password in mysql

[root@localhost ~] # mysqldump-u root-p-- databases benet > benet.sqlEnter password: # # root password in mysql should use format 3 if you need to back up all libraries in the entire mysql server. When the amount of data exported is large, you can add the "--opt" option to optimize execution speed. For example, doing the following creates a backup file, all-data.sql, which contains all the libraries in the MySQL server. [root@localhost ~] # mysqldump-u root-p-- opt-- all-databases > all-databases.sqlEnter password: # # check the root password of the backup file in the SQL script text file exported by the mysqldump tool, where the "/ *... * /" section or the line starting with-- indicates the comment information. You can view the contents of a file using grep, less, cat, and other text tools. For example Do the following to filter out the database operation statements [root@localhost ~] # grep-v "^ -" benet.sql | grep-v "^ /" | grep-v "^ $" CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `benet` / *!! 40100 DEFAULT CHARACTER SET utf8 * /; USE `benet`; DROP TABLE IF EXISTS `users`; CREATE TABLE `users` (`user_ name` char (15) NOT NULL, `user_ passwd` char (40100) DEFAULT', PRIMARY KEY (`user_ name`) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `users` WRITE INSERT INTO `users` VALUES ('lisi','123123'), (' zhangsan','*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1'); UNLOCK TABLES; restore database SQL backup scripts exported using the mysqldump command can be imported through the mysql command when you need to restore, in the following format. Mysql [option] [Library name] [Table name]

< /备份路径/备份文件名当备份文件中只包含表的备份，而不包含创建库的语句时，执行导入操作时必须制定库名，且目标库必须存在。例如；以下操作可以从备份文件mysql-user.sql中将表导入test库。其中"-e"选项是用于指定连接MySQL执行后的命令，命令执行后自动退出。[root@localhost ~]# mysql -u root -p test < mysql-user.sql Enter password: ##mysql中root密码 [root@localhost ~]# mysql -u root -p -e ' show tables from test;'Enter password: ##mysql中root密码+----------------+| Tables_in_test |+----------------+| user |+----------------+ 若备份文件中已经包含完整的数据库信息，则执行导入操作时无需指定库名。例如指定一下操作可以从备份文件benet.sql恢复到benet库。[root@localhost ~]# mysql -u root -p -e ' drop database benet;' ##删除benet库，模拟数据库故障Enter password: [root@localhost ~]# mysql -u root -p -e ' show databases;' ##查看到benet库已经不存在了Enter password: +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+[root@localhost ~]# mysql -u root -p < ~/benet.sqlEnter password: [root@localhost ~]# mysql -u root -p -e ' show databases;'Enter password: +--------------------+| Database |+--------------------+| information_schema || benet || mysql || performance_schema || test |+--------------------+ 二、增量备份 特点；与完整备份不同，增量备份没有重复数据，备份量不大，时间短，但恢复麻烦，需要上次完整备份及完整备份之 后的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复，mysql中没有提供直接增量备份的方法，可以通过 MySQL提供的我二进制日志（binary logs）间接实现增量备份。 二进制日志要进行MySQL的增量备份，首先要开启二进制功能，开启MySQL的二进制日志功能的实现方法有很多种，最常用的是在MySQL配置文件的mysqld项下加入"log-bin=/文件路径/文件名"前缀，如；log-bin=/usr/local/mysql/mysql-bin,然后重启mysqld.service服务，就可以看到指定路径下的二进制文件文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如；mysql-bin.000001[root@localhost ~]# vim /etc/my.cnf ##修改配置文件，启用二进制功能log-bin=/usr/local/mysql/mysql-bin:wq[root@localhost ~]# systemctl restart mysqld.service [root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*-rw-rw---- 1 mysql mysql 120 8月 15 10:35 /usr/local/mysql/mysql-bin.000001-rw-rw---- 1 mysql mysql 34 8月 15 10:35 /usr/local/mysql/mysql-bin.index MySQL增量恢复 常用的增量恢复方法有三种；一般恢复，基于位置的恢复，基于时间点的恢复 一般恢复；将所有备份的二进制日志内容全部恢复，格式命令如下mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u root -p基于位置的恢复；数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，命令格式如下所示。格式一；恢复数据到指定位置mysqlbinlog --stop-position='459' 二进制日志 | mysql -u root -p ******格式二；从指定的位置开始恢复数据mysqlbinlog --start-position='459' 二进制日志 | mysql -u root -p ******基于时间点的恢复；跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况。格式1；从日志开头截止到某个时间点恢复mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户 -p 密码格式2；从某个时间点到日志结尾恢复mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户 -p 密码格式3；从某个时间点到某个时间点恢复mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户 -p 密码下面我们通过一个企业级的备份案例讲解增量备份与恢复的具体操作。 三、企业备份案例 需求描述；北京移动公司的用户信息数据库为client，用户资费数据表为user_info，每周需要进行完整备份，每天需要进行增量备份， 新增加的用户信息表如下 ----------------------------------------------------------------------- ××× 姓名 性别 用户ID号 资费----------------------------------------------------------------------- 000006 张三 男 016 10 000007 李四 女 017 19 000008 王五 女 018 23 000009 赵六 男 019 37 000010 孙七 男 020 36----------------------------------------------------------------------- 一般恢复 1）添加数据库，表，录入信息 [root@localhost ~]# mysql -u root -p Enter password: mysql>

Create database client

Query OK, 1 row affected (0.00 sec

Mysql > use client

Database changed

Mysql > create table user_info (× × char (20) not null, name char (20) not null, gender char (4), user ID number char (10) not null, tariff int (10))

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into user_info values; Query OK, 1 row affected (0.00 sec) mysql > insert into user_info values; Query OK, 1 row affected (0.01 sec) mysql > insert into user_info values (000008 sec) Query OK, 1 row affected (0.00 sec) mysql > select * from user_info +-+ | × × | name | Sex | user's ID number | tariff | +-+ -+ | 000006 | Zhang San | male | 016 | 10 | | 000007 | Li Si | female | 017 | 91 | 000008 | Wang Wu | female | 018 | 23 | + -+ 3 rows in set (0.00 sec)

2) make a full backup first

To easily verify the resilience of binary log increments, make a full backup of the client.user_ info table that inserts three rows of data, and then under the Linux system command

Execute the "mysqladmin-uroot-p123123 flush-logs" command or execute "flush logs" at the "mysql >" command prompt to generate a new binary log.

[root@localhost ~] # mysqldump-uroot-p client user_info > / mysql_bak/client_userinfo-$ (date +% F). SqlEnter password: [root@localhost ~] # ls / mysql_bak/client_userinfo-2018-08-15.sql [root@localhost ~] # mysqladmin-uroot-p flush-logsEnter password: [root@localhost ~] # ls-1 / usr/local/mysql/mysql-bin.*-rw-rw---- 1 mysql mysql 1252 August 15 11:50 / Usr/local/mysql/mysql-bin.000001-rw-rw---- 1 mysql mysql 120 August 15 11:50 / usr/local/mysql/mysql-bin.000002-rw-rw---- 1 mysql mysql 68 August 15 11:50 / usr/local/mysql/mysql-bin.index

3) continue to enter new data and make incremental backups

Add two new lines of data, and execute the "mysqladmin-uroot-p flush-logs" command to refresh the binary log for incremental backup

In this way, the operation of inserting two user data is retained in the binary log file mysql-bin.000003.

Mysql > use client

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changedmysql > insert into user_info values; Query OK, 1 row affected (0.00 sec) mysql > insert into user_info values; Query OK, 1 row affected (0.00 sec) mysql > select * from user_info +-+ | × × | name | Sex | user's ID number | tariff | +-+ -+ | 000006 | Zhang San | male | 016 | 10 | 000007 | Li Si | female | 017 | 91 | 000008 | Wang Wu | female | 018 | 23 | 000009 | Zhao Liu | male | 019 | 37 | 000010 | Sun Qi | male | 020 | 36 | +- -+ 5 rows in set (0.00 sec) [root@localhost] # ls-1 / usr/local/mysql/mysql-bin.*-rw-rw---- 1 mysql mysql 1252 August 15 11:50 / usr/local/mysql/mysql-bin.000001-rw-rw---- 1 mysql mysql 679 August 15 12:02 / usr/local/mysql/mysql-bin.000002-rw-rw---- 1 mysql mysql 120 August 15 12:02 / usr/local/mysql/mysql-bin.000003-rw-rw---- 1 mysql mysql 102 August 15 12:02 / usr/local/mysql/mysql-bin.index

4) simulated misoperation to delete user_ info table

[root@localhost] # mysql-uroot-p-e 'drop table client.user_info;'

Enter password:

[root@localhost] # mysql-uroot-p-e 'select * from client.user_info;'

Enter password:

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

5) restore operation

"when performing a restore operation, you need to perform a full restore first and then perform an incremental restore."

[root@localhost] # mysql-u root-p client < / backup_bak/client_userinfo-2018-08-18.sql

Enter password:

[root@localhost] # mysql-u root-p-e 'select from client.user_info;'

Enter password:

+-+

| | × × | name | Sex | user's ID number | tariff |

+-+

| | 000006 | Zhang San | male | 016 | 10 | |

| | 000007 | Li Si | female | 017 | 91 | |

| | 000008 | Wang Wu | female | 018 | 23 | |

+-+

General recovery

[root@localhost ~] # mysqlbinlog-- no-defaults / usr/local/mysql/mysql-bin.000002 | mysql- u root-p

Enter password:

[root@localhost] # mysql-u root-p-e 'select from client.user_info;'

Enter password:

+-+

| | × × | name | Sex | user's ID number | tariff |

+-+

| | 000006 | Zhang San | male | 016 | 10 | |

| | 000007 | Li Si | female | 017 | 91 | |

| | 000008 | Wang Wu | female | 018 | 23 | |

| | 000009 | Zhao Liu | male | 019 | 37 |

| | 000010 | Sun Qi | male | 000010 | 36 |

+-+

Location-based recovery [root@localhost ~] # mysqlbinlog-- no-defaults / usr/local/mysql/mysql-bin.000002 # # View the contents of the binary file to restore mysqlbinlog: unknown option'--no'

[root@localhost] # mysqlbinlog-- no-defaults / usr/local/mysql/mysql-bin.000002

/! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1/

/! 40019 SET @ @ session.max_insert_delayed_threads=0/

/! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/

DELIMITER /! /

# at 4

# 180818 14:16:21 server id 1 end_log_pos 120 CRC32 0xf20e69c2 Start: binlog v 4, server v 5.6.36-log created 180818 14:16:21

BINLOG'

Tbl3Ww8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcJp

DvI=

'/! /

# at 120

# 180818 14:17:53 server id 1 end_log_pos 203 CRC32 0x122cbd0b Query thread_id=4 exec_time=0 error_code=0

SET timestamp 1534573073

SET @ @ session.pseudoclinic thread readership idling 4max session /

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards modewords 1075838976 Universe /

SET @ @ session.auto_increment_increment=1, @ @ session. Autoincrement increment offset1 /

/!\ C utf8 /! /

SET @ @ session.session. Collationalization setting setting client.33 recording session.collationalization connection.33 recorder session. Collationalization serversion33Universe

SET @ @ session. Session. LCC timetables namespace 0 _ swap /

SET @ @ session. Collationalization databases

BEGIN

/! /

# at 203

# 180818 14:17:53 server id 1 end_log_pos 345 CRC32 0xa4c70118 Query thread_id=4 exec_time=0 error_code=0

Use client/!/

SET timestamp 1534573073

Insert into user_info values ('000009,' Zhao Liu', 'male', '019', '019', 'Zhao Liu,' 019)

/! /

# at 345

# 180818 14:17:53 server id 1 end_log_pos 376 CRC32 0x7599a729 Xid = 46

COMMIT/!/

# at 376

# 180818 14:18:04 server id 1 end_log_pos 459 CRC32 0x1ff3f3f2 Query thread_id=4 exec_time=0 error_code=0

SET timestamp 1534573084Universe

BEGIN

/! /

# at 459

# 180818 14:18:04 server id 1 end_log_pos 601 CRC32 0xea02a0b2 Query thread_id=4 exec_time=0 error_code=0

SET timestamp 1534573084Universe

Insert into user_info values ('000010', 'Sun Qi', 'male', '020', '020', 'Sun Qi', '020'

/! /

# at 601

# 180818 14:18:04 server id 1 end_log_pos 632 CRC32 0xc9b66df7 Xid = 47

COMMIT/!/

# at 632

# 180818 14:19:23 server id 1 end_log_pos 679 CRC32 0x2bf94bf2 Rotate to mysql-bin.000003 pos: 4

DELIMITER

# End of log file

ROLLBACK / added by mysqlbinlog /

/! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/

/! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0/

Location-based recovery: method 1

[root@localhost] # mysqlbinlog-- no-defaults-- stop-position='459' / usr/local/mysql/mysql-bin.000002 | mysql- u root-p

Enter password:

[root@localhost] # mysql-u root-p-e 'select * from client.user_info;'

Enter password:

+-+

| | × × | name | Sex | user's ID number | tariff |

+-+

| | 000006 | Zhang San | male | 016 | 10 | |

| | 000007 | Li Si | female | 017 | 91 | |

| | 000008 | Wang Wu | female | 018 | 23 | |

| | 000009 | Zhao Liu | male | 019 | 37 |

+-+

Location-based recovery: method 2

[root@localhost] # mysql-u root-p-e 'drop table client.user_info;'

Enter password:

[root@localhost] # mysql-u root-p client < / backup_bak/client_userinfo-2018-08-18.sql

Enter password:

[root@localhost] # mysqlbinlog-- no-defaults-- start-position='459' / usr/local/mysql/mysql-bin.000002 | mysql- u root-p

Enter password:

[root@localhost] # mysql-u root-p-e 'select * from client.user_info;'

Enter password:

+-+

| | × × | name | Sex | user's ID number | tariff |

+-+

| | 000006 | Zhang San | male | 016 | 10 | |

| | 000007 | Li Si | female | 017 | 91 | |

| | 000008 | Wang Wu | female | 018 | 23 | |

| | 000010 | Sun Qi | male | 000010 | 36 |

+-+

Based on point in time

Method 1: restore from the beginning of the log to a certain point in time

[root@localhost] # mysql-u root-p-e 'drop table client.user_info;'

Enter password:

[root@localhost] # mysql-u root-p client < / backup_bak/client_userinfo-2018-08-18.sql

Enter password:

[root@localhost ~] # mysqlbinlog-- no-defaults-- stop-datetime='2018-08-18 1414 no-defaults 1815 04' / usr/local/mysql/mysql-bin.000002 | mysql- u root-p

Enter password:

[root@localhost] # mysql-u root-p-e 'select * from client.user_info;'

Enter password:

+-+

| | × × | name | Sex | user's ID number | tariff |

+-+

| | 000006 | Zhang San | male | 016 | 10 | |

| | 000007 | Li Si | female | 017 | 91 | |

| | 000008 | Wang Wu | female | 018 | 23 | |

| | 000009 | Zhao Liu | male | 019 | 37 |

+-+

Method 3: time point to time point [root@localhost] # mysql-u root-p-e 'drop table client.user_info 'Enter password: [root@localhost ~] # mysql- u root-p client < / backup_bak/client_userinfo-2018-08-18.sqlEnter password: [root@localhost ~] # mysqlbinlog-- no-defaults-- start-datetime='2018-08-18 14 14 mysql- 04'-- stop-datetime='2018-08-18 14 14 mysql- 19mysql- 23'/ usr/local/mysql/mysql-bin.000002 | mysql- u root-pEnter password: [root@localhost ~] # mysql- u Root-p-e 'select * from client.user_info 'Enter password: +-+ | × × | name | Sex | user's ID number | tariff | +- -+-+ | 000006 | Zhang San | male | 016 | 10 | 000007 | Li Si | female | 017 | 91 | 000008 | Wang Wu | female | 018 | 23 | 000010 | Sun Qi | male | 020 | 36 | +- -+

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.