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

First, use binlog logs

Binlog log: a type of binary log MsSQL database service log file

1. Configure the MsQAL server to ensure that you can log in with a password

2. Adjust / etc/my.cnf configuration and restart the service

[root@mysql-1 ~] # vim / etc/my.cof

Log-bin-index=mysql-bin / / enable binary logging and specify a prefix

Server_id=1

Binlog_format=STATEMENT / / in Mysql5.7, the binlog log format defaults to ROW

3. Restart the service

[root@mysql-1 ~] # systemctl restart mysqld

4. View

[root@mysql-1 ~] # ls / var/lib/mysql/mysql-bin.*

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

View binlog log contents

[root@mysql-1 mysql] # mysqlbinlog mysql-111-bin.000001

Restart the MySQL service program and a new log is generated:

Second, redo database operations using binlog logs

1. Perform database table add operation

Create db1 ·Library tb1 table with custom table structure:

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

-> (1pm Jack')

-> (2)

-> (3 recorder Bob')

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

Perform a delete all table records operation:

Mysql > DELETE FROM tb1

4. Restore table records through binlog log

View mysql-bin.000001 log contents

[root@mysql-1 ~] # mysqlbinlog / var/lib/mysql/mysql-bin.000001

5. Execute the sql command within the specified range of Pos nodes to recover data

Note: you need to see the log information for specific recovery.

[root@mysql-1 ~] # 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 combined with pipeline to msyql command to 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, third-party software to do incremental backup XtraBackup

1. Install the software package

[root@mysql-1] # rpm-qpi percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm

[root@mysql-1] # rpm-ivh libev-4.15-1.el6.rf.x86_64.rpm

2. Install the dependency package

[root@mysql-1 ~] # yum-y install perl-DBD-MySQL perl-Digest-MD5

Note: if these dependent packages are not installed, an error will be reported when installing percona-xtrabackup directly:

3. Confirm the main programs / scripts installed

[root@mysql-1] # 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

.

.

Second, use XtraBackup to perform database backup

[main format]

-- host hostname

-- port port

-- user user name

-- password password

-- databases= "Library name"

-- databases= "Library 1 Library 2"

-- databases= "Library. Table"

-- no-timestamp does not use timestamps as file names

1. Make a full backup

[root@mysql-1] # innobackupex-- user=root-- password=123456 / backup/-no-timestamp

Confirm the backed up file data:

[root@mysql-1 ~] # ls / backup/

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

Feel free to add or change database tables, 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)

-> (2)

Mysql > SELECT * FROM tb1

+-+ +

| | id | name |

+-+ +

| | 1 | bob |

| | 2 | bo |

+-

3. Based on the previous full backup saved to / backup, make an incremental backup and save it to / incr01/

[root@mysql-1 ~] # innobackupex-- user=root-- password=123456\

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

View backup information

[root@mysql-1 ~] # ls / incr01/

4. Compare the size of full backup and incremental backup:

[root@mysql-1 ~] # du-sh / backup/ / incr01/

Size of 142m / backup/ full backup

Size of 3.5m / incr01/ incremental backup

Prepare the database directory for recovery

1. Prepare to restore a "full backup"

After the preparation is complete, the final / backup can be used to rebuild the MySQL server. In this case,

You need to do a "--apply-log-- redo-only" preparatory operation to ensure data consistency:

[root@mysql-1 ~] # innobackupex-- user=root-- password=123456\

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

2. Prepare to restore "incremental backup"

[root@mysql-1 ~] # 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 that the data is deleted.

[root@mysql-1 ~] # systemctl stop mysqld

[root@msyql-1 ~] # rm-rf / var/lib/mysql

4. Restore "full backup + incremental backup"

After the preparation is completed, eventually / backup is still used to rebuild the MySQL server.

However, in this case, it is necessary to merge the data of the relevant incremental backups in advance.

[root@mysql-1] # innobackupex-- user=root-- password=123456-- copy-back / backup/

5. Modify the file owner and subordinate group under / var/lib/mysql/

[root@mysql-1] # chown-R mysql:mysql / var/lib/mysql

[root@mysql-1 ~] # systemctl start mysqld.service

[root@mysql-1] # mysql- uroot-p123456-e "select * from db1.tb1"

+-+ +

| | id | name |

+-+ +

| | 1 | bob |

| | 2 | bo |

+-+ +

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.