mysql+ third-party software backup

First, use Binlog log

Binlog log: Binary log one of the MSSQL database service log files

1, configure the MSQAL server, ensure that you can log in with a password

2, adjust the/etc/my.cnf configuration, and restart the service

[[email protected] ~] #vim/etc/my.cof

Log-bin-index=mysql-bin//Enable binary log and specify prefix

Server_id=1

Binlog_format=statement//In Mysql5.7, the Binlog log format defaults to row

3. Restart Service

[email protected] ~]#systemctl Restart Mysqld

4. View

[Email protected] ~]# ls/var/lib/mysql/mysql-bin.*

/var/lib/mysql/mysql-bin.000001/var/lib/mysql/mysql-bin.index

View Binlog Log Content

[Email protected] mysql]# Mysqlbinlog mysql-111-bin.000001

Restarting the MySQL service program will generate a new log:

Second, using Binlog log Redo database operation

1. Perform database table add operation

Create DB1 Library tb1 table, table structure customization:

mysql> CREATE DATABASE db1;

mysql> use DB1;

Database changed

Mysql> CREATE TABLE tb1 (

-ID Int (4) Not null,name varchar (24)

);

2, insert 3 table records:

Mysql> INSERT into Tb1 VALUES

(1, ' Jack '),

(2, ' kenthy '),

(3, ' Bob ');

3. Delete the 3 table records added in the previous step

Perform delete all table record operations:

Mysql> DELETE from TB1;

4, through the Binlog Log Recovery table records

View mysql-bin.000001 Log Content

[Email protected] ~]# mysqlbinlog/var/lib/mysql/mysql-bin.000001

5. Execute SQL commands in the specified POS node range to recover data

Note: The specific recovery needs to look at the log information

[Email protected] ~]# mysqlbinlog \

--start-datetime= "2018-1-20 20:06:55" \

--stop-datetime= "2018-1-20 20:07:23" \

/var/lib/mysql/mysql-bin.000002 | Mysql-u Root-p binding pipeline to MSYQL command perform import redo

Enter Password://Verify Password

6. Confirm the recovery result

Mysql> SELECT * from DB1.TB1;

+----+--------+

| ID | name |

+----+--------+

| 1 | Jack |

| 2 | Kenthy |

| 3 | Bob |

+----+--------+

3 Rows in Set (0.00 sec)

third-party software to do incremental backup xtrabackup

1. Install the package

[Email protected] ~]# Rpm-qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm

[[Email protected] ~] #rpm –IVH libev-4.15-1.el6.rf.x86_64.rpm

2. Install dependent packages

[Email protected] ~]# yum-y install Perl-dbd-mysql perl-digest-md5

Note: If you do not install these dependent packages, you will get an error when installing Percona-xtrabackup directly:

3, confirm the installation of the main program/script

[Email protected] ~]# RPM-QL percona-xtrabackup-24-2.4.6-2.el7.x86_64

/usr/bin/innobackupex

/usr/bin/xbcloud

/usr/bin/xbcloud_osenv

/usr/bin/xbcrypt

/usr/bin/xbstream

/usr/bin/xtrabackup

......

......

Ii. using Xtrabackup to perform a database backup

"Primary Format"

--host Host Name

--port Port

--user User Name

--password Password

--databases= "Library name"

--databases= "Library 1 Library 2"

--databases= "Library. Table"

--no-timestamp do not use timestamps for filenames

1, make a full backup

[Email protected] ~]# Innobackupex--user=root--password=123456/backup/–no-timestamp

Confirm Backup of good file data:

[[Email protected] ~] #ls/backup/

2. Make an incremental backup (based on the full backup of the previous step)

Feel free to do something new or change the library table, such as creating a new MYTB table in the DB1 library:

mysql> use DB1;

Mysql> CREATE TABLE MYTB (id int (4), name varchar (24));

Mysql> INSERT into Tb1 VALUES

(1, ' bon '),

(2, ' Bo '),

Mysql> SELECT * from TB1;

+------+------+

| ID | name |

+------+------+

| 1 | Bob |

| 2 | Bo |

+------+-----

3, once saved to the/backup full backup as the basis, make an incremental backup, save to/incr01/

[Email protected] ~]# Innobackupex--user=root--password=123456 \

--incremental/incr01--incremental-basedir=/backup/--no-timestamp

View backup Information

[Email protected] ~]# ls/incr01/

4, compare the size of full backup, incremental backup:

[Email protected] ~]# du-sh/backup//incr01/

142m/backup///Full backup size

3.5M/INCR01///Incremental backup size

Iii. Preparing the database directory for recovery

1. Prepare to restore "full backup"

after the preparation is complete, the final/backup can be used to rebuild the MySQL server. This case,

A "--apply-log--redo-only" preparation operation is required to ensure data consistency:

[[Email protected] ~] #innobackupex--user=root--password=123456 \

--apply-log--redo-only/backup/

2. Prepare to restore "incremental backup"

[[Email protected] ~] #innobackupex--user=root--password=123456 \

--apply-log--redo-only/backup/--incremental-dir=/incr01

3. Close the MySQL service and delete the files under/var/lib/mysql/, assuming the data is deleted

[[Email protected] ~] #systemctl Stop mysqld

[[Email protected] ~] #rm-rf/var/lib/mysql

4. Restore "Full backup + incremental backup"

after the preparation is complete, the/backup is eventually used to rebuild the MySQL server,

But in this case, you need to merge the data of the related incremental backup in advance

[Email protected] ~]# Innobackupex--user=root--password=123456--copy-back/backup/

5, modify the/var/lib/mysql/under the main and the genus Group

[[Email protected] ~] #chown-R mysql:mysql/var/lib/mysql

[[Email protected] ~] #systemctl start Mysqld.service

[[Email protected] ~] #mysql-uroot-p123456-e "SELECT * from Db1.tb1"

+------+------+

| ID | name |

+------+------+

| 1 | Bob |

| 2 | Bo |

+------+------+

mysql+ third-party software backup