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

The main function of Mysql backup is to solve problems quickly, efficiently and safely in the event of hardware failures, software failures, natural disasters, *, and human misoperation, so as to avoid unnecessary losses.

What do I need to back up for Mysql backup?

1. Data to be used

2. Binary log, InnoDB transaction log

3. Storage code; for example: stored procedures, stored functions, triggers, time schedulers

4. Configuration file of the server

Key points to pay attention to when backing up Mysql:

1. Tolerate the maximum amount of data lost

2. How long will it take to recover the data

3. That data needs to be recovered

4. After making a backup, you must do a restore test to test the availability of the backup.

Factors to consider when backing up Mysql

1. How long does it take to hold the lock

2. The duration of the backup process

3. Whether the load of the system business will be increased during backup

4. The duration of the recovery process

Type of Mysql backup

Divided in terms of datasets:

Full backup: backing up the entire dataset

Partial backup: only a subset of data is backed up

Divided by the angle of the timeline:

Full backup: backing up the entire dataset

Incremental backup: backing up data that has changed since the last full backup or incremental backup

Differential backup: backing up data that has changed since the last full backup

Note: although incremental backups save more disk space than differential backups, incremental backups are more troublesome than differential backups in restoring data

It is divided according to whether the server business system can be online:

Hot backup: both read and write operations can be performed

Warm backup: read operation can be performed, but write operation cannot be performed.

Cold backup: read and write operations cannot be performed

Note: InnoDB storage engine supports both hot backup and warm backup, while MyISAM storage engine only supports warm backup, not hot backup.

Physical backup: directly copy data files for backup

Logical backup: export data from the database for backup; logical backup has nothing to do with the storage engine. If you are backing up the InnoDB storage engine, it is possible to use it on the MyISAM storage engine.

The method of Mysql backup:

1. Mysqldump+ copy binlog to achieve: use mysqldump to achieve full backup of data, copy event within a specified time range in binlog to achieve incremental backup.

2. Lvm2+ replication binlog: use lvm2 snapshots and command tools such as cp to achieve physical backup, full backup, and copy event within the scope of pointing events in binlog to achieve incremental backup

3. Xtrabackup: backup tool provided by Percona. Hot backup, full backup and incremental backup are supported for InnoDB, and warm backup and full backup are supported for MySIAM.

First, copy binlog based on mysqldump+ to realize backup

Prepare the environment:

Node 1

IP:172.18.42.100

Node 2

IP:172.18.42.111

1. Deploy Node 1

(1) install the mysql service, edit its binaries and make a full backup

[root@localhost ~] # yum install mysql-server-y # # install the Mysql service [root@localhost ~] # vim / etc/my.cnf # # Edit its configuration file [mysqld] innodb_file_per_table = ONlog_bin=mysql-bin # # enable binaries [root@localhost ~] # mysqldump-uroot-- lock-tables-- master-data=2-- databases Mydata > db1.sql # # make a full backup before modifying the database # #-lock-tables: lock the specified database It is recommended to use # #-lock-all-tables: lock all databases when backing up a single database, and # #-master-data=1 when backing up multiple databases | 2 # 1: record as change master to statement, this statement will not be annotated # 2: record as change master to statement, this statement will be annotated #-- databases: specify the database to be backed up When there are multiple databases, spaces can be used to separate [root@localhost ~] # less db1.sql-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1106 # # recording the binary log of the current backup and the location of the event in the binary log

(2) modify the data in the database and use "mysqlbinlog" to generate incremental backup

Mysql > select * from db1 # # check data once before modifying data +-+ | ID | Name Age +-+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 +-+ mysql > insert into db1 (ID,Name,Age) values (4) 'RuLai',999) Mysql > insert into db1 (ID,Name,Age) values; mysql > select * from db1 # # check the data once +-+ | ID | Name Age +-+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 | 4 | RuLai 999 | 5 | YuDi 888 +-+- -- +-+ [root@localhost ~] # mysqlbinlog-uroot-- start-position=1106 / var/lib/mysql/mysql-bin.000003 > binary_log # # generate incremental backup # #-- start-position: pos [root@localhost ~] # less binary_log # # indicate the event to view the incremental backup file insert into db1 (ID Name,Age) values # # records the operations related to table modification / *! * / # at 1223,160612 21:44:35 server id 1 end_log_pos 1339 Query thread_id=9 exec_time=0 error_code=0SET timestamp 1465739075 values insert into db1 (ID,Name,Age) values / *; # at 1339mm 160612 21:45:41 server id 1 end_log_pos 1441 Query thread_id=9 exec_time=0 error_code=0SET timestamp 1465739141

2. Deploy Node 2

(1) provide mysql service and send the full backup file "db1.sql" and incremental backup file "binary_log" of node 1 to node 2

[root@localhost ~] # yum install mysql-server-y [root@localhost ~] # scp db1.sql binary_log root@172.18.42.111:/root

(2) Node 2 imports "db1.sql" and views its data

[root@localhost ~] # mysql

Select * from db1;+----+ | ID | Name Age+----+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 +-+ # # data is not our modified data

(3) suppose node 1 is dead, and node 2 needs to restore the data that node 1 currently modified the database. In this case, we only need node 2 to import the "binary_log" incremental backup file.

[root@localhost ~] # mysql

Select * from db1 # # after importing incremental backups The data of node 2 is the same as that of node 1 after modifying the database. +-+ | ID | Name Age +-+ | 1 | BaiGu jing 100 | 2 | SunDa shen 200 | 3 | ZhuBa jie 150 | 4 | RuLai 999 | 5 | YuDi 888 +-+ -+

Second, copy binlog based on lvm2+ to realize backup.

Prepare the environment:

Node 1

IP:172.18.42.100

Node 2

IP:172.18.42.111

1. Deploy Node 1

(1) install the mysql service, scroll the log and record

[root@localhost ~] # yum install mysql-server-y [root@localhost ~] # service mysqld start [root@localhost ~] # mysql- e "show master status" > binary_file # # I won't say how to turn on the binary log memo [root@localhost ~] # cat binary_file File Position Binlog_Do_DB Binlog_Ignore_DBmysql-bin.000004 208 # # records the currently used binary log and where the events are located Pos [root@localhost ~] # service mysqld stop # # in binary log shut down the mysql service after logging

(2) create a lvm snapshot and mount it

[root@localhost ~] # pvcreate / dev/sda5 # # create a physical volume Physical volume "/ dev/sda5" successfully created [root@localhost ~] # vgcreate wxpp / dev/sda5 # # create a volume group named "wxpp" Volume group "wxpp" successfully created [root@localhost ~] # lvcreate-L + 5G-n wxxp1 wxpp # # create a logical volume with a size of 5G File system named "wxpp1" Logical volume "wxxp1" created. [root@localhost ~] # mke2fs-t ext4 / dev/wxpp/wxxp1 # # format logical volumes as "ext4" [root@localhost ~] # mount / dev/wxpp/wxxp1 / data/ # # Mount logical volumes to the "/ data" directory [root@localhost ~] # mkdir / data/mysql # # create a directory where mysql databases store data Note: this path must be the same as the path pointed to by "datadir" in the configuration file [root@localhost ~] # chown mysql.mysql / data/mysql # # give mysql users the right to store data [root@localhost ~] # ll / data/drwxr-xr-x 2 mysql mysql 4096 Jun 12 22:20 / data/mysqldrwx- 2 root root 16384 Jun 12 22:18 lost+found [root@localhost ~] # service mysqld start # # start mysql service [root@localhost] # ll / data/mysql/ # # generated a large number of log files total 21272 Jun 12 22:25 Mydatadrwx- 2 mysql mysql 4096 Jun 12 22 ibdata1-rw-rw-----1 mysql mysql 10485760 Jun 12 22:28 ibdata1-rw-rw---- 1 mysql mysql 5242880 Jun 12 22:28 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Jun 12 22:24 ib_logfile1drwx- 2 Jun 12 22:25 : 24 mysql-rw-rw---- 1 mysql mysql 19758 Jun 12 22:24 mysql-bin.000001-rw-rw---- 1 mysql mysql 765307 Jun 12 22:24 mysql-bin.000002-rw-rw---- 1 mysql mysql 1190 Jun 12 22:28 mysql-bin.000003-rw-rw---- 1 mysql mysql 106 Jun 12 22:28 mysql-bin.000004-rw-rw---- 1 mysql mysql 76 Jun 12 22:28 mysql-bin .indexdrwx-2 mysql mysql 4096 Jun 12 22:24 testmysql > flush tables with read lock # # apply read lock to data to prevent data from changing [root@localhost ~] # lvcreate-L + 1G-s-n binary_log / dev/wxpp/wxxp1 # # create a lvm snapshot with a size of 1G Name "binary_log" [root@localhost ~] # mount / dev/wxpp/binary_log / mnt/ # # Mount the snapshot to the "/ mnt" directory [root@localhost ~] # ll / mnt/mysql/ total 21272Lay RWMuk-1 mysql mysql 10485760 Jun 12 22:28 ibdata1-rw-rw---- 1 mysql mysql 5242880 Jun 12 22:28 ib_logfile0-rw-rw---- 1 mysql mysql 5242880 Jun 12 22:24 ib_logfile1drwx -2 mysql mysql 4096 Jun 12 22:25 Mydatadrwx- 2 mysql mysql 4096 Jun 12 22:24 mysql-rw-rw---- 1 mysql mysql 19758 Jun 12 22:24 mysql-bin.000001-rw-rw---- 1 mysql mysql 765307 Jun 12 22:24 mysql-bin.000002-rw-rw---- 1 mysql mysql 1190 Jun 12 22:28 mysql-bin.000003-rw-rw---- 1 mysql mysql 19758 Jun 12 22 28 mysql-bin.000004-rw-rw---- 1 mysql mysql 76 Jun 12 22:28 mysql-bin.indexdrwx- 2 mysql mysql 4096 Jun 12 22:24 testmysql > unlock tables # # releasing write Lock

(3) generate incremental backup

Mysql > select * from db1; # check data once +-+ | ID | Name Age +-+ | 1 | MaGe 100 | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +-+ mysql > delete from db1 where ID=1;mysql > select * from db1 +-+ | ID | Name Age +-+ | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +-+ [root@localhost ~] # mysqlbinlog-- start-position=208 / data/mysql/mysql-bin.000004 > backup.sql # # generate incremental backup files

2. Deploy Node 2

(1) send the files under the "/ mnt/mysql" directory under the snapshot of node 1 and the incremental backup files to node 2

[root@localhost ~] # scp-r / mnt/mysql/* backup.sql root@172.18.42.111:/data/mysql/ [root@localhost ~] # chown-R mysql.mysql / data/mysql/ # # change the permission to "mysql" [root@localhost ~] # ll / data/mysql/total 21272Lir mysql mysql 10485760 May 9 22:33 ibdata1-rw-r- 1 mysql mysql 5242880 May 9 22:33 ib_logfile0 -rw-r- 1 mysql mysql 5242880 May 9 22:33 ib_logfile1drwx- 2 mysql mysql 4096 May 9 22:33 Mydatadrwx- 2 mysql mysql 4096 May 9 22:33 mysql-rw-r- 1 mysql mysql 19758 May 9 22:33 mysql-bin.000001-rw-r- 1 mysql mysql 765307 May 9 22:33 mysql-bin.000002-rw-r- 1 mysql mysql 1190 May 9 22:33 mysql-bin.000003-rw-r- 1 mysql mysql 208 May 9 22:33 mysql-bin.000004-rw-r- 1 mysql mysql 76 May 9 22:33 mysql-bin.indexdrwx- 2 mysql mysql 4096 May 9 22:33 test

(2) start the mysql service and view its database

[root@localhost ~] # service mysqld startmysql > select * from db1; # # is not the modified data +-+ | ID | Name Age +-+ | 1 | MaGe 100 | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +-+

(3) suppose node 1 crashes the database due to human misoperation, then let node 2 import the incremental backup "backup.sql".

[root@localhost ~] # mysql

Select * from db1;+----+ | ID | Name Age +-+ | 2 | Lweim 200 | 3 | Wzx 300 | 4 | wxpp 400 +-+

(4) remove lvm snapshot

[root@localhost ~] # umount / mnt [root@localhost ~] # lvremove / dev/wxpp/binary_log Logical volume "binary_log" successfully removed

When there are a large number of databases to be replicated, using snapshots is the fastest and most effective way to prevent excessive read locks on the database so as to avoid unnecessary losses.

Third, realize backup and restore based on xtrabackup command tool.

Prepare the environment

Node 1

IP:172.18.42.100

Node 2

IP:172.18.42.111

1. Deploy Node 1

(1) install the "xtrabackup" tool

[root@node0 ~] # yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm-y # # download the rpm package and install [root@node0] # rpm-ql percona-xtrabackup # # View the files generated by percona-xtrabackup / usr/bin/innobackupex # # We mainly use this command / usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/xbstream/usr/bin/xtrabackup/ Usr/share/doc/percona-xtrabackup-2.3.2/usr/share/doc/percona-xtrabackup-2.3.2/COPYING/usr/share/man/man1/innobackupex.1.gz/usr/share/man/man1/xbcrypt.1.gz/usr/share/man/man1/xbstream.1.gz/usr/share/man/man1/xtrabackup.1.gz

(2) first, make sure that the storage engine is innodb and that "innodb_file_per_table" is ON.

MariaDB [Mydata] > show table status\ GpoliName: db1Engine: InnoDBMariaDB [Mydata] > show global variables like "% innodb%"; innodb_file_per_table ON # # ensure that each table uses a separate tablespace, and if not specified, all tables in all databases will be placed in the same space

(3) realize the backup of the whole database

[root@node0 ~] # innobackupex-- user=root / backup/ # # make a backup of the entire database and put it in the "/ backup" directory Note: if you need a password to log in to mysql Then you need to specify the password [root@node0 ~] # ll / backup/2016-04-20mm 10-34-41/total 18460 root root RWUV r-1 root root 385 Apr 20 10:34 backup-my.cnf-rw-r- 1 root root 18874368 Apr 20 10:34 ibdata1drwx- 2 root root 47 Apr 20 10:34 Mydatadrwx- 2 root root 4096 Apr 20 10:34 mysqldrwx- 2 Root root 4096 Apr 20 10:34 performance_schemadrwx- 2 root root 19 Apr 20 10:34 test-rw-r- 1 root root 21 Apr 20 10:34 xtrabackup_binlog_info-rw-r- 1 root root 113 Apr 20 10:34 xtrabackup_checkpoints-rw-r- 1 root root 457 Apr 20 10:34 xtrabackup_info-rw-r- 1 root root 2560 Apr 20 10:34 xtrabackup_logfile

(4) modify data to generate incremental backup

MariaDB [Mydata] > select * from db1; # check the data once before modification | id +-+ | 1 | 2 | 3 +-+ MariaDB [Mydata] > insert into db1 values (100,200); # # insert two "ID" MariaDB [Mydata] > select * from db1 +-+ | id +-+ | 1 | 2 | 3 | 100,200 +-+ [root@node0 ~] # innobackupex-- incremental / backup-- incremental-basedir=/backup/2016-04-20 backup 10-34-41 / # # generate incremental backups # #-- incremental: in which path to store incremental backups # #-- incremental-basedir: based on whom to do incremental backups [root@node0 ~] # ll / backup/drwx- 6 root root 4096 Apr 20 10:34 2016-04-20 backup 10-34-41 # # full backup file drwx- 6 root root 4096 Apr 20 10:41 2016-04-20 backup 10-41-42 # # incremental backup file

(5) organize the full backup data and import the incremental backup into the full backup.

[root@node0 ~] # innobackupex-- apply-log-- redo-only / backup/2016-04-20 backup 10-34-41 / # organize a full backup [root@node0 ~] # innobackupex-- apply-log-- redo-only / backup/2016-04-20 backup 10-34-41 /-increment-dir=/backup/2016-04-20 10-41-42 # # Import an incremental backup into a full backup

2. Deploy Node 2

(1) install xtrabackup tools

[root@node0] # yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm-y

(2) send the full backup directory of the imported incremental backup on node 1 to node 2. If node 1 dies, we can restore it based on the full backup of the imported incremental backup.

[root@node0 ~] # scp-r / backup/2016-04-20th 10-34-41 / root@172.18.42.201:/root [root@node1 ~] # innobackupex-- copy-back 2016-04-20 * 10-34-41 / # Export data [root@node1 ~] # chown-R mysql.mysql / data/ # # change the permission to mysql [root@node1 ~] # ll / data/mysql/total 18448drwx2 root root 6 Apr 20 11:02 2016-04-20 years 10-34-41 UV RW root root r-1 root root 18874368 Apr 20 11:02 ibdata1drwx- 2 root root 47 Apr 20 11:02 Mydatadrwx- 2 root root 4096 Apr 20 11:02 mysqldrwx- 2 root root 4096 Apr 20 11:02 performance_schemadrwx- 2 root root 19 Apr 20 11:02 test-rw-r- -1 root root 23 Apr 20 11:02 xtrabackup_binlog_pos_innodb-rw-r- 1 root root 457 Apr 20 11:02 xtrabackup_ in [root @ node1 ~] # systemctl start mariadb.serviceMariaDB [Mydata] > select * from db1 +-+ | id +-+ | 1 | 2 | 3 | 100 | 200 +-+

Summary of the question:

1. When using mysqldump+ to copy the binlog method, it is best not to put the data files and binaries on the same disk, and you need to back up the binaries

2. When using the lvm2+ replication binlog method, when creating a snapshot, it is best to impose a read lock on the database to avoid data changes, and at the same time make sure that the owner and group of the file is mysql

3. When using the xtrabackup method, the directory in which mysql stores data must be the same as the "datadir" in the configuration file, and make sure that the owner and group of the file is mysql.

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.