MySQL Backup and recovery

mysql backup type:

1 depending on the backup, the server is online and is divided into:

1) Hot-standby-the server is online and read and write unaffected.

2) Win Bei--The server is online, but requires a lock table, and is readable and not writable.

3) Cold--server offline, read and write abort.

2 by backup method, divided into:

Physical backup-copy data files, characterized by faster backup and recovery. Large footprint, suitable for big data backup.

Logical backup--export data to a file, slow, may lose the precision of floating point numbers, suitable for a small amount of data scenarios.

The biggest advantage of logical backups is that they can be backed up in the same way for various storage engines. While physical backups are different, different storage engines have different backup methods, so for databases mixed with different storage engines, it's easier to use logical backups.

Backup tool for MySQL:

1 mysqldump--Logical Backup tool, Win Bei, for InnoDB can be implemented hot standby.

Options:

--master-data=0|1|2

0: Do not log binaries and their locations

1: Record location in Change Master to, can be used for recovery after direct start from the server

2: Record location in Change Master to, but is commented by default

-X,--lock-all-tables: Add this parameter to lock all tables before backing up

-f,--flush-logs: Flush Binary Log

--databases: Backing up specified libraries and tables

-A,--all-databases: backing up all libraries and tables

1) Backup and recovery

Export some of the tables in the specified library: Export the test01, test02 two tables in the test library:

[Email protected] ~]# mysqldump-uroot-p test test01 test02 >/root/test_$ (Date +%f). sql

Recovery: When you restore a table, you must indicate the library because there are no statements in the backup file that have been built, and if the library does not exist, you need to establish the database first

[Email protected] ~]# mysql-uroot-p test </root/test_2017-05-01.sql

Export one or more databases: Export the test YEWU Xiaoshou database

[Email protected] ~]# mysqldump-uroot-p--databases test Yewu Xiaoshou >/root/test_yewu_xiaoshou_$ (Date +%f). sql

Recovery: You do not need to specify a library name when restoring because the full library information is already contained in the backup file

[Email protected] ~]# Mysql-uroot-p < Test_yewu_xiaoshou_2017-05-01.sql

Back up all the libraries in the MySQL database:

[Email protected] ~]# mysqldump-uroot-p--all-databases > alldatabase_$ (Date +%f). sql

Recovery:

[Email protected] ~]# Mysql-uroot-p < Alldatabase_2017-05-01.sql

2) Example of production environment

A 10:00 backing up the database

[Email protected] ~]# mysqldump-uroot-p--lock-all-tables--flush-logs--master-data=2--databases Test > test_$ (DAT e +%f). sql

The contents of table test01 are as follows:

Mysql> select * from test01;

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

| ID | name |

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

| 1 | Zhan |

| 2 | Liso |

| 3 | Wang |

| 5 | Kang |

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

b adding new data to the table

mysql> INSERT into test01 values (' + ', ' Dan ');

Query OK, 1 row affected (2.56 sec)

Mysql> select * from test01;

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

| ID | name |

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

| 1 | Zhan |

| 2 | Liso |

| 3 | Wang |

| 5 | Kang |

| 18 | Dan |

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

5 rows in Set (0.00 sec)

C 2:00, the table in the database was mistakenly deleted, start to restore the backup, first restore full standby

[Email protected] ~]# mysql-uroot-p test < Test_2017-05-01.sql

Enter Password:

Mysql> select * from test01;

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

| ID | name |

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

| 1 | Zhan |

| 2 | Liso |

| 3 | Wang |

| 5 | Kang |

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

4 rows in Set (0.00 sec)

By the above results, a row with ID 18 is missing

d Use the Mysqlbinlog command to restore the Binlog since the mysqldump backup

View the location of the Binlog log when mysqldump backup

[email protected] ~]# less Test_2017-05-01.sql

--MySQL dump 10.13 distrib 5.6.30, for Linux (i686)

--

--Host:localhost Database:test

-- ------------------------------------------------------

--Server version 5.6.30-log

/*!40101 SET @[email protected] @CHARACTER_SET_CLIENT */;

/*!40101 SET @[email protected] @CHARACTER_SET_RESULTS */;

/*!40101 SET @[email protected] @COLLATION_CONNECTION */;

/*!40101 SET NAMES UTF8 */;

/*!40103 SET @[email protected] @TIME_ZONE */;

/*!40103 SET time_zone= ' +00:00 ' */;

/*!40014 SET @[email protected] @UNIQUE_CHECKS, unique_checks=0 */;

/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;

/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' no_auto_value_on_zero ' */;

/*!40111 SET @[email protected] @SQL_NOTES, sql_notes=0 */;

--

--Position to start replication or point-in-time recovery from

--

--Change MASTER to master_log_file= ' localhost-bin.000003 ', master_log_pos=120;

--

--Current Database: ' Test '

--

CREATE DATABASE/*!32312 IF not exists*/' test '/*!40100 DEFAULT CHARACTER SET UTF8 */;

Use ' test ';

--

--Table structure for table ' test01 '

--

Back up binary logs

[[Email protected] ~] #mysqlbinlog localhost-bin.000003 >/root/binlog.sql

Delete Error SQL statement in binary log

Vim Binlog.sql

...

DROP TABLE ' test01 '

DROP TABLE IF EXISTS ' test01 '

...

Recovery:

Mysql-uroot-p Test < Backbinlog.sql

To view the recovered table:

Mysql> select * from test01;

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

| ID | name |

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

| 1 | Zhan |

| 2 | Liso |

| 3 | Wang |

| 5 | Kang |

| 18 | Dan |

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

5 rows in Set (0.00 sec)

2 xtrabackup-- to the InnoDB to Do data Backup tool , backup mode is physical backup, and support hot-standby,

1) installation, can be installed well in advance Epel Yum source

[Email protected] ~]# wget [[email protected] ~]# RPM-IVH https://www.percona.com/downloads/XtraBackup/ percona-xtrabackup-2.3.8/binary/redhat/6/i386/percona-xtrabackup-2.3.8-1.el6.i686.rpm

[Email protected] ~]# yum-y localinstall percona-xtrabackup-2.3.8-1.el6.i686.rpm

2) Backup and recovery

Backup:

A Create a backup account:

Mysql> Grant Reload,lock tables,replication Client on * * to ' dbbak ' @ ' localhost ' identified by ' bk2016 ';

b full backup, socket can not be specified, unless inconsistent with default

[Email protected] ~]# Innobackupex--socket=/tmp/mysqld.sock--user=dbbak--password=bk2016/root/

...

170501 23:20:35 Backup created in directory '/root/2017-05-01_23-20-25/'

MySQL binlog position:filename ' localhost-bin.000003 ', Position ' 15651 '

170501 23:20:35 [xx] Writing backup-my.cnf

170501 23:20:35 [xx] ... done

170501 23:20:35 [xx] Writing Xtrabackup_info

170501 23:20:35 [xx] ... done

Xtrabackup:transaction Log of LSN (2031876) to (2031876) was copied.

170501 23:20:35 completed ok!

[Email protected] ~]# CD 2017-05-01_23-20-25/#查看备份后的目录

[[email protected] 2017-05-01_23-20-25]# ls

BACKUP-MY.CNF ibdata1 mysql performance_schema test Xiaoshou xtrabackup_binlog_info xtrabackup_checkpoints Xtrabac Kup_info Xtrabackup_logfile Yewu

Backup Directory Description:

xtrabackup_checkpoints--backup types (such as full and incremental), backup status, LSN (log sequence number) range information

xtrabackup_my.cnf--configuration options information used for backup commands

Xtrabackup_binlog_info--mysql the binary log file currently used by the server and the location of the binary log event up to the moment of backup

C Preprocess The backup file, prepare a full backup, add memory options,--use-memory=1g

[Email protected] ~]# Innobackupex--apply-log/root/2017-05-01_23-20-25

D incremental backup via binary after data change

[email protected] 2017-05-01_23-20-25]# cat Xtrabackup_binlog_info

localhost-bin.00000315651

[Email protected] ~]# mysqlbinlog/usr/data/mysql/localhost-bin.000003 >/root/bin$ (Date +%f). sql

Recovery:

A stop service

[[Email protected] ~]# service mysqld stop

b Recovery fully prepared, MySQL data directory must be empty, otherwise it will be an error

[Email protected] ~]# Innobackupex--copy-back--user=dbbak--password=bk2016/root/2017-05-01_23-20-25/

[Email protected] mysql]# cd/usr/local/mysql/#修改数据目录的属主属组

[Email protected] mysql]# chown-r Mysql.mysql *

C Start-up service

[[Email protected] mysql]# service mysqld start

D Recovering incremental backups from binary files

Mysql> set sql_log_bin=0; #暂时关闭二进制日志

Query OK, 0 rows affected (0.03 sec)

Mysql> Source/root/bin2017-05-01.sql;

...

Query OK, 1 row Affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.01 sec)

Mysql> set sql_log_bin=1; #开启二进制日志

Query OK, 0 rows Affected (0.00 sec)

After e recovery is complete, re-make a full backup

Import and export of tables

This article from "10,000 years too long, seize" blog, please be sure to keep this source http://zengwj1949.blog.51cto.com/10747365/1921030

MySQL Backup and recovery