Multiple ways to back up the MySQL database

The backup of MySQL database is the most serious, in the production process, the database will be due to hardware failure, software failure, hacker attacks, misoperation and other data loss, but after a sophisticated backup, fully able to restore data over.

One, Backup tool

Here are a few common ways to backup

CP: Physical Backup tool for all storage engines, cold, full backup, partial backup

Mysqldump: Logical Backup tool for all storage engines, support for Win Bei, full backup, partial backup, hot standby for InnoDB Storage Engine

Xtrabackup: A very powerful innodb/xtradb hot standby tool that supports full backup, incremental backup,

Second, CP Backup and Recovery

We'll look at the data first, and we'll experiment with the student table in the test library

Mysql> SELECT * FROM test.student;+------+------+| ID |    Name |+------+------+| 1 | A |+------+------+1 row in Set (0.00 sec)

Copy MySQL data to the specified directory

Mkdir/backupcp-a/var/lib/mysql/*/backup

Simulate lost data, delete MySQL data

rm-rf/var/lib/mysql/*

Restart the database to find data loss (this is the Yum installation can restart the database directly, if it is compiled installation also need to initialize)

Service mysqld restartmysql> select * from Test.student; ERROR 1146 (42S02): Table ' test.student ' doesn ' t existmysql>

Restore the backed-up data to the data directory

Cp-a/backup/*/var/lib/mysql/

Log in to the database and find that the data has been restored back

Mysql> SELECT * FROM test.student;+------+------+| ID |    Name |+------+------+| 1 | A |+------+------+1 row in Set (0.00 sec) mysql>

Three, mysqldump backup and recovery

We'll look at the data first, and we'll experiment with the student table in the test library

Mysql> SELECT * FROM test.student;+------+------+| ID |    Name |+------+------+| 1 |    A | | 2 | b |+------+------+2 rows in Set (0.00 sec)

Modify MY.CNF, add Log_bin, restart database

Vim/etc/my.cnf[mysqld]log_bin=mysql-binservice mysqld Restart

Backup data using mysqldump, and parameter description

Mysqldump-uroot-a--events--master-data=2--single-transaction >/opt/all.sql-a full standby, no need to create a library--events backup time to restore --master-data record time point--single-transaction lock Table Backup

Inserting new data

mysql> use test;mysql> INSERT INTO student values (3, ' C '); Query OK, 1 row Affected (0.00 sec) mysql> SELECT * FROM student;+------+------+| ID |    Name |+------+------+| 1 |    A | | 2 |    B | | 3 | C |+------+------+3 rows in Set (0.00 sec)

Analog data loss, mis-operation

mysql> drop database test; Query OK, 1 row affected (0.01 sec) mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL |+--------------------+2 rows in Set (0.00 sec)

Start recovery with mysqldump full plus incremental backup

1, turn off logging

Set global sql_log_bin=0;

2, restore full standby, check the data situation

Mysql-uroot </opt/all.sqlmysql> select * FROM test.student;+------+------+| ID |    Name |+------+------+| 1 |    A | | 2 | b |+------+------+2 rows in Set (0.00 sec)

3, take advantage of point-in-time recovery incremental recovery, to see the start point of/opt/all.sql, in the file has a string similar to the following

--Change MASTER to master_log_file= ' mysql-bin.000018 ', master_log_pos=705

At the point of view of the wrong operation

mysqlbinlog/var/lib/mysql/mysql-bin.000018 # at 869#161102 18:36:09 Server ID 1 end_log_pos 896 Xid = 4088commit/*!*/;# At 896#161102 18:36:25 Server ID 1 end_log_pos 977 querythread_id=41exec_time=0error_code=0set TIMESTAMP=1478082985/*!* /;d ROP Database test

Restore based on point of start and end point

mysqlbinlog/var/lib/mysql/mysql-bin.000018--start-position=705--stop-position=896 | Mysql-uroot

4, restore complete, view data

Mysql> SELECT * FROM test.student;+------+------+| ID |    Name |+------+------+| 1 |    A | | 2 |    B | | 3 | C |+------+------+3 rows in Set (0.00 sec)

Four, xtrabackup the full amount of backup and recovery

1, download tool, and install

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/ percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

Yum Install percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

2, create backup directory, and backup

Mkdir/exbackupinnobackupex--user=root/exbackup/innobackupex--apply-log/exbackup/2016-11-02_20-14-08/

3. View Backup Files

[[email protected] 3306]# ll /exbackup/2016-11-02_20-14-08/total dosage  30760drwx------.   2 root root     4096 11 Month   2 20:49 2016-11-02_ 20-49-56-rw-r-----.  1 root root      386 11 Month   2  20:14 backup-my.cnf-rw-r-----.  1 root root 18874368 11 Month   2  20:21 ibdata1-rw-r--r--.  1 root root  5242880 11 Month   2 20:21  ib_logfile0-rw-r--r--.  1 root root  5242880 11 Month   2 20:21  ib_logfile1drwx------.  2 root root     4096 11 Month    2 20:14 MYSQLDRWX------.  2 root root     4096 11 Month    2 20:14 OLDBOYDRWX------.  2 root root     4096  November   2 20:14 PERFORMANCE_SCHEMADRWX------.  2 root root     4096 11 Month   2 20:14 test-rw-r-----.  1 root root        21 11 month   2 20:14 xtrabackup_binlog_info-rw-r--r--.  1 root root        23 11 Month   2 20:21 xtrabackup_binlog_pos_ Innodb-rw-r-----.  1 root root      113 11 Month   2  20:21 xtrabackup_checkpoints-rw-r-----.  1 root root       511 11 Moon   2 20:14 xtrabackup_info-rw-r-----.  1 root root   2097152 11 Month   2 20:21 xtrabackup_logfile

4, Analog data loss, recovery

Rm-rf/data/*mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema |+--------------------+1 row in Set (0.00 sec)

5. Use Backup files, restore data, restart database, view data

Innobackupex --copy-back /exbackup/2016-11-02_20-14-08/ chown -r  mysql:mysql  /data/*service mysqld restartmysql> show databases;+----------------------------- -+| database                      |+------------------------------+| information_schema            | |   #mysql50 #2016-11-02_20-49-56 | |  mysql                         | |  oldboy                        | |  performance_schema           | |  test          &Nbsp;              |+------------------- -----------+6 rows in set  (0.00 SEC)

Five, xtrabackup backup and recovery increments

1, we have done all the above, we add new data to do incremental backup experiment

mysql> show databases;+------------------------------+| database                      |+------ ------------------------+| information_schema            | |   #mysql50 #2016-11-02_20-49-56 | |  mysql                         | |  oldboy                        | |  performance_schema           | |  t1                            | |  t2                            | |  test                          |+------------------------------+

2, incremental backup, note: The next incremental--incremental-basedir= the most recent incremental backup path

Innobackupex--incremental/exbackup/--incremental-basedir=/exbackup/2016-11-02_20-14-08/innobackupex--apply-log --redo-only/exbackup/2016-11-02_20-14-08/innobackupex--apply-log--redo-only/exbackup/2016-11-02_20-14-08/-- Incremental-basedir=/exbackup/2016-11-02_21-31-50/

3. View incremental Backups

[[email protected] exbackup]# ll/exbackup/total dosage 8drwx------. 7 root root 4096 November 2 20:49 2016-11-02_20-14-08drwx------. 9 root root 4096 November 2 21:32 2016-11-02_21-31-50

4, Analog data loss, recovery

Rm-rf/data/*mysql> Show databases;+--------------------+| Database |+--------------------+| Information_schema |+--------------------+1 row in Set (0.00 sec)

5. Use Backup files, restore data, restart database, view data

innobackupex --copy-back /exbackup/2016-11-02_20-14-08/   #直接通过全量进行增量的恢复chown  -R   mysql:mysql /data/*service mysqld restartmysql> show databases;+------------ ------------------+| database                      |+------------------------------+| information_ schema           | |   #mysql50 #2016-11-02_20-49-56 | |  mysql                         | |  oldboy                        | |  performance_schema           | |  t1                            | |  t2                            | |  test                          |+------------------------------+8 rows in  set  (0.01 SEC)

Six, xtrabackup Backup and recovery multi-instance

1, the steps are basically the same, just add some parameters

Innobackupex--defaults-file=/data/3306/my.cnf--user=root/exbackupinnobackupex--apply-log/exbackup/2016-11-02_ 20-14-08/innobackupex--defaults-file=/data/3306/my.cnf--copy-back/exbackup/2016-11-02_20-14-08/

2, incremental

Innobackupex--defaults-file=/data/3306/my.cnf--user=root--incremental/exbackup/--incremental-basedir=/exbackup /2016-11-02_20-14-08/innobackupex--apply-log--redo-only/exbackup/2016-11-02_20-14-08/innobackupex--apply-log-- redo-only/exbackup/2016-11-02_20-14-08/--incremental-dir=/exbackup/2016-11-02_21-31-50/innobackupex-- DEFAULTS-FILE=/DATA/3306/MY.CNF--copy-back/exbackup/2016-11-02_20-14-08/

Summary: Just more configuration file parameters, the other steps exactly the same, here does not spend space these operations, we can test, this article does not have too much explanation of the principle, all dry operation, want to know the principle of these backup methods, parameters of the Netizen will forgive me

This article is from the "Database learning process" blog, so be sure to keep this source http://yuemu.blog.51cto.com/10688235/1868733

Multiple ways to back up a MySQL database