MySQL backup and recovery (below)

MySQL backup and restore

1. Logical backup

1. Review what is logical backup

Logical backup is to export the database, data table or data to a text file.

2. Logical backup tool

mysqldump: Provides data backup at the entire database level, database level, and table level

mysqldump + binlog binary log to achieve incremental backup

3. Logic export and import

☆ Export (data backup)

Regardless of the storage engine, the following method itself is a data export method and can be used to assist backup. It can back up one or several columns of a table. The backup isdata record.

Case: Logically export the data records in the tb_student data table

# mysql -uroot -p
Enter password:123
mysql> select * into outfile '/tmp/sqlbak/tb_student.txt' from db_itheima.tb_student;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The main reason for the above problems is that we did not specify the path specified when the MySQL logic is exported.

# vim my.cnf
[mysqld]
...
secure_file_priv=/tmp/sqlbak/

# service mysql_3306 restart
mysql> select * into outfile '/tmp/sqlbak/tb_student.txt' from db_itheima.tb_student;
ERROR 1 (HY000): Can't create/write to file '/tmp/sqlbak/tb_student.txt' (Errcode: 13 - Permission denied)

The main reason for the above problems is that the mysql account does not have write permissions to /tmp/sqlbak

# setfacl -m u:mysql:rwx /tmp/sqlbak

☆ Import (data restoration)

# mysql -uroot -p
Enter password:123
mysql> load data local infile '/tmp/sqlbak/tb_student.txt' into table tb_student;
或
# mysqlimport dbname /path/file -p  =>   要求,导出的文件必须和数据表名称完全一致
例如:
# mysqlimport db_itheima /tmp/sqlbak/tb_student.txt -p
Enter password:123
Typical application scenario: You can import some regular text files into the data table

Case: Import the data in the /etc/passwd file into the password data table

The first step: create a password data table, password is a reserved keyword of mysql, it is recommended to use ``two back apostrophes

mysql> use db_itheima;
mysql> CREATE TABLE `password` (
  `uname` varchar(50) DEFAULT NULL,
  `pass` char(2) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `home` varchar(50) DEFAULT NULL,
  `shell` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 2: Copy the /etc/passwd file to the /tmp/sqlbak directory and name it password.txt

# cp /etc/passwd /tmp/sqlbak/password.txt

Step 3: Use mysqlimport to write the contents of the password.txt file to the data table

# mysqlimport db_itheima --fields-terminated-by=':' --lines-terminated-by='\n' /tmp/sqlbak/password.txt -p

选项说明
--fields-terminated-by=':',指定导出文件的分隔符为冒号:
--lines-terminated-by='\n',指定每一行的结尾使用的符号,\n代表换行符

4. Extension: logical backup and restore tool (support remote connection)

MySQL actually supports a lot of management software (Windows version): MySQL-Front, Navicat

The first step: select MySQL connection

Insert picture description here

Step 2: Enter MySQL connection information

Insert picture description here

Step 3: Function demonstration => Create database, create data table, insert data...

The fourth step: database backup and restore

① Export and import of logic

Insert picture description here

② Backup and restore of data table

Insert picture description here

③ Backup and restore of database

Insert picture description here

Two, physical backup

1. Introduction to xtrabackup backup

####㈠ xtrabackup advantages and disadvantages

advantage:

  1. Backup process == fast and reliable (==because it is a physical backup);
  2. stand byIncremental backup, More flexible
  3. The backup process will not interrupt the ongoing transaction ;
  4. Can save disk space and traffic based on compression and other functions;
  5. Automatically realize backup inspection;
  6. Fast restore speed;

Disadvantages:

  1. Can onlyInnodb table incremental backup, When the myisam table is incrementally backed up, it is fully prepared
  2. Before innobackupex backs up MyISAM tables, READ LOCK must be added to the entire database to block write operations. If the backup is performed on the slave database, it will affect the master-slave synchronization and cause delay. The backup of InnoDB tables will not block reads and writes.

####㈡ xtrabackup backup principle

  1. innobackupex will firststart upOnextrabackup_logBackground detection process, real-time detection of mysqlredo logIf redo is found to have a new log written, immediately write the log to the log filextrabackup_login.
  2. physicalcopyInnodb data file and system tablespace file idbdata1Go to the corresponding place with the default timestamp as the backup directory
  3. After copying, executeflush table with read lockThe operation is performed on the whole database lock table to prepare to backup non-InnoDB files
  4. Physically copy .frm.myd.myi and other non-InnoDB engine files to the backup directory
  5. View the location of the binary log
  6. Unlock tables
  7. Stopxtrabackup_log process
Insert picture description here

Extension: redo log log?

The transaction log, or redo log, exists as ib_logfile0, ib_logfile1 by default in mysql.

####㈢ Principles of xtrabackup backup and recovery

Insert picture description here

The specific text description is as follows (understand):

  • A redo log file is maintained internally in InnoDB, which can also be called a transaction log file. The transaction log will store every record modification of InnoDB table data. When InnoDB starts, InnoDB checks the data file and transaction log, and performs two steps: it applies (rolls forward) the committed transaction log to the data file, and rolls back the modified but uncommitted data.
  • xtrabackup will remember the log sequence number (LSN) when it starts, and copy all data files. The copy process takes some time, so if the data file is changed during this period, the database will be at a different point in time. At this time, xtrabackup will run a background process to monitor the transaction log and copy the latest changes from the transaction log. xtrabackup must continue to do this operation, because the transaction log is written in rotation and repeated, and the transaction log can be reused. So xtrabackup keeps recording the modification of each data file in the transaction log since it is started.
  • The above is the backup process of xtrabackup. Next is the prepare process. In this process, xtrabackup uses the previously copied transaction log to perform disaster recovery on each data file (just like MySQL did when it first started). When this process is over, the database can be restored.
  • The above process is implemented in the compiled binary program of xtrabackup. The program innobackupex allows us to back up MyISAM tables and frm files to increase convenience and functionality. Innobackupex will start xtrabackup until xtrabackup copies the data files, and then execute FLUSH TABLES WITH READ LOCK to prevent new writes in and flash the MyISAM table data to the hard disk, then copy the MyISAM data files, and finally release the lock.
  • Backing up MyISAM and InnoDB tables will eventually be consistent. After the prepare process is over, the InnoDB table data has been rolled forward to the point where the entire backup ends, instead of being rolled back to the point when xtrabackup just started. This point in time is the same as the point in time when FLUSH TABLES WITH READ LOCK is executed, so the MyISAM table data is synchronized with the InnoDB table data. Similar to Oracle's recover and restore, InnoDB's prepare process can be called recover (recovery), and MyISAM's data replication process can be called restore (restore).
  • After Mysql5.7.3, you need to add the server-id option to open the binary log, otherwise an error will be reported

2. Get xtrabackup backup tool

3. Installation of xtabackup software

Step 1: Upload the software package and dependent libraries to the Linux server

Step 2: Install dependent libraries

# rpm -ivh libev-4.15-3.el7.x86_64.rpm

Step 3: Install xtrabackup software

# yum install percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm -y
# rpm -ql percona-xtrabackup-24

4. xtrabackup full database backup and recovery

The core idea of ​​backup:

① Use innobackupex to make a full backup of all the libraries in the database. After the backup is completed, it cannot be restored immediately (the data is incomplete, and the data in the process of 12:00 ~ 12:30 is missing)

② Preparatory stage, Xtrabackup_log generated during the backup processApply to full backup set

③ Simulate fault (delete data) => rm -rf data/*

④ Mobilize operation and maintenance engineers to restore the entire database

⑤ Test verification

Step 1: Prepare the data

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table t1(id int,name varchar(10)) engine=myisam;
mysql> insert into t1 values (1,'吕布');
mysql> create table t2(id int,name varchar(10)) engine=innodb;
mysql> insert into t2 values (1,'貂蝉');

Step 2: Prepare a database backup account and activate the corresponding permissions

创建备份用户admin,并授予相应权限
mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
mysql> flush privileges;

说明:
在数据库中需要以下权限:
RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK
REPLICATION CLIENT权限:为了获取binary log位置
PROCESS权限:显示有关在服务器中执行的线程的信息(即有关会话执行的语句的信息),允许使用SHOW ENGINE

Step 3: Use the innobackupex tool to perform a full database backup

# innobackupex --user=admin --password=123 /full_xtrabackup
说明:备份目录默认会自动创建,也可以手动创建

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

Error in the first run:

200829 15:48:59  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'admin'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','admin',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.
200829 15:48:59 Connecting to MySQL server host: localhost, user: admin, password: set, port: not set, socket: not set
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).

The main reason for the above problems is that our mysql.sock is not in the /var/lib/mysql directory. Why does it automatically connect to mysql.sock in the /var/lib/mysql directory?

Reason 1: There may be a my.cnf file in the /etc directory, which affects the execution of innobackupex.

Reason 2: innobackupex has its own default configuration and reads the /var/lib/mysql/mysql.sock file by default

solution:

Solution 1: Create a soft link for your socket file and place it in the /var/lib/mysql/mysql.sock file

# mkdir /var/lib/mysql
# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

Option 2: Add a -S option to innobackupex to execute the socket

# innobackupex -S /tmp/mysql.sock --user=admin --password=123 /full_xtrabackup

Step 4: Preparatory phase, integrate the logs generated during the backup period into the full backup

# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2020-08-29_15-59-22

Step 5: Simulate database failure

# rm -rf /mysql_3306/data/*
# pkill mysqld

Step 6: Quickly restore the data in the database

# rm -rf /mysql_3306/data/*
# innobackupex --copy-back /full_xtrabackup/2020-08-29_15-59-22

The first recovery error:

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)
Error: datadir must be specified.

The main reason for the above problems is that the innobackupex tool cannot find the data directory in MySQL

Solution: Pass the my.cnf configuration file to innobackupex and let it automatically recognize the datadir in this file

# innobackupex --defaults-file=/mysql_3306/my.cnf --copy-back /full_xtrabackup/2020-08-29_15-59-22

Step 7: When restoring data, you must remember to change the file owner and group permissions in the /mysql_3306/data directory, otherwise mysql cannot be started

# chown -R mysql.mysql /mysql_3306/data

Step 8: Start MySQL and test whether it is normal

# service mysql_3306 start
# mysql -p
Enter password:123

5. Incremental backup and recovery of xtrabackup

What is increment? There must be a prerequisite for the increment, and there must be a full amount first.

Insert picture description here

① Full backup

② Integrate the logs generated by the full backup into the full backup

③ Perform data addition, deletion and modification operations

④ Incremental backup

⑤ Integrate all the log files generated by incremental backup and incremental backup into full backup

⑥ Simulated failure

⑦ Data recovery

⑧ Start the database, test and verify the data

Step 1: Prepare the data

mysql> create database db_itheima default charset=utf8;
mysql> use db_itheima;
mysql> create table t1(id int,name varchar(10)) engine=myisam;
mysql> insert into t1 values (1,'吕布');
mysql> create table t2(id int,name varchar(10)) engine=innodb;
mysql> insert into t2 values (1,'貂蝉');

Step 2: Create an account dedicated to backup

创建备份用户admin,并授予相应权限
mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
mysql> flush privileges;

说明:
在数据库中需要以下权限:
RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK
REPLICATION CLIENT权限:为了获取binary log位置
PROCESS权限:显示有关在服务器中执行的线程的信息(即有关会话执行的语句的信息),允许使用SHOW ENGINE

Step 3: Full backup

# rm -rf /full_xtrabackup/*
# innobackupex --user=admin --password=123 /full_xtrabackup

Step 4: Integrate the logs generated during the full preparation process (pay special attention)

# innobackupex --user=admin --password=123 --apply-log --redo-only /full_xtrabackup/2020-08-29_17-06-48/

选项说明:
--apply-log 表示整合日志
--redo-only	表示只应用已经提交的事务,不回滚未提交的事务(12:00 ~ 12:30产生很多事务操作,事务处理=>开启事务,成功了提交事务,写入硬盘;失败了回滚事务,不写入硬盘)
注意:如果已经回滚了未提交事务,那么就无法再应用增量备份。
Preparing an Incremental Backup with innobackupex Preparing incremental backups is a bit different than full ones. This is, perhaps, the stage where more attention is needed:
• First, only the committed transactions must be replayed on each backup. This will merge the base full backup with the incremental ones.
• Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.
For the Prepare phase of incremental backup, there are two points to note, one is the transaction need to be submitted replayed, an uncommitted transaction requires rollback.
If you replay the committed transactions and rollback the uncommitted ones on the base backup, you will not be able to add the incremental ones. If you do this on an incremental one, you won't be able to add data from that moment and the remaining increments. Having this in mind, the procedure is very straight-forward using the --redo-only option, starting with the base backup:
If the committed transaction is replayed in the Prepare phase and the uncommitted transaction is rolled back, then Subsequent incremental backups cannot be added to the current full backup. Therefore, the –redo-only option should be used for all preparations in the Prepare phase.
--Redo-only should be used when merging all incrementals except the last one. That's why the previous line doesn't contain the --redo-only option. Even if the --redo-only was used on the last step, backup would still be consistent but in that case server would perform the rollback phase.
For situations where there are multiple increments, only the last increment does not need to use the --redo-only option. If used, rollback will be completed when the server is started.

Step 5: Modify the data (add, delete, modify, and let it generate incremental data)

# mysql -p
Enter password:123
mysql> insert into db_itheima.t1 values (2,'王朗');
mysql> insert into db_itheima.t1 values (3,'袁术');

Step 6: Make incremental backups

# innobackupex --user=admin --password=123 --incremental /incre_backup --incremental-basedir=/full_xtrabackup/2020-08-29_17-06-48

选项说明:
--incremental 增量备份目录
--incremental-basedir 这个增量是相对于哪个全量的

Step 7: Integrate the data and log files generated by the incremental backup into the full backup

# innobackupex --user=admin --password=123 --apply-log /full_xtrabackup/2020-08-29_17-06-48 --incremental-dir=/incre_backup/2020-08-29_17-23-43

说明:
1.--redo-only除了最后一个不用加之外,其他的增量应用都要加,最后一个应用的时候可以直接进入回滚未提交事务阶段;如果加了也没事,服务启动的时候会进入recovery过程,来回滚
2.应用增量备份的时候只能按照备份的顺序来应用。如果应用顺序错误,那么备份就不可用。如果无法确定顺序,可以使用xtrabackup-checkpoints来确定顺序。

周天做了一个全量,周一、周二、周三、周四、周五、周六全部做增量

At this point, the incremental backup is all over!

Step 8: Simulate database failure

# rm -rf /mysql_3306/data/*
# pkill mysqld

Step 9: Data Recovery

# innobackupex --defaults-file=/mysql_3306/my.cnf --user=admin --password=123 --copy-back /full_xtrabackup/2020-08-29_17-06-48

# chown -R mysql.mysql /mysql_3306/data

Start the database, test to verify whether it is normal

# service mysql_3306 start
# mysql -p
Enter password:123

eckpoints to determine the order.

Do a full amount on Sunday, and do an incremental amount on Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday.


到此,增量备份就全部结束了!

第八步:模拟数据库故障

```powershell
# rm -rf /mysql_3306/data/*
# pkill mysqld

Step 9: Data Recovery

# innobackupex --defaults-file=/mysql_3306/my.cnf --user=admin --password=123 --copy-back /full_xtrabackup/2020-08-29_17-06-48

# chown -R mysql.mysql /mysql_3306/data

Start the database, test to verify whether it is normal

# service mysql_3306 start
# mysql -p
Enter password:123