MySQL备份恢复

1、直接CP备份(冷备)恢复

检查备份前数据库状态mysql> show databases;mysql> use test;mysql> show tables;向所有表施加读锁mysql> flush tables with read lock;mkdir newbackcp -r /mysqldata/test/ /backup/newback/ls /backup/newback/rm -rf /mysqldata/test/重启mysql数据库，检查数据库[root@DualMySQL-DA mysqldata]# service mysql restartShutting down MySQL..                                      [  OK  ]Starting MySQL...                                          [  OK  ]mysql> show databases;还原备份文件，并重启生效cp -r /backup/newback/test/ /mysqldata/chown -R mysql:mysql /mysqldata/test/[root@DualMySQL-DA mysqldata]# service mysql restartShutting down MySQL..                                      [  OK  ]Starting MySQL.                                            [  OK  ]mysql> show databases;还原后需要设置数据库目录的操作权限，否则报如下错误mysql> use test;Database changedmysql> show tables;ERROR 1018 (HY000): Can't read dir of './test/' (errno: 13 - Permission denied)mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;注：加读锁以后所有的数据库都不能执行insert,update,delete,drop,create等操作，可以执行selectmysql> insert into t1 values (4,'物理',90);ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lockmysql> update t1 set subject='物理' where studentno=3;ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lockmysql> delete from t1  where studentno=3;ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lockmysql> drop table random_value_1000w;ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lockmysql> create database test2;ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lockmysql> delete from r_user;ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

FLUSH TABLES WITH READ LOCK

这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。解锁的语句也是unlock tables

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

这个命令是表级别的锁定，可以定制锁定某一个表。例如： lock  tables test read; 不影响其他表的写操作。解锁语句也是unlock tables。

这两个语句在执行的时候都需要注意个特点，就是 隐式提交的语句。在退出mysql终端的时候都会隐式的执行unlock tables。也就是如果要让表锁定生效就必须一直保持对话。

MYSQL的read lock和wirte lock

read-lock:  允许其他并发的读请求，但阻塞写请求，即可以同时读，但不允许任何写。也叫共享锁

write-lock: 不允许其他并发的读和写请求，是排他的(exclusive)。也叫独占锁

FLUSH TABLES WITH READ LOCK简称(FTWRL)，该命令主要用于备份工具获取一致性备份(数据与binlog位点匹配)。由于FTWRL总共需要持有两把全局的MDL锁，并且还需要关闭所有表对象，因此这个命令的杀伤性很大，执行命令时容易导致库hang住。如果是主库，则业务无法正常访问；如果是备库，则会导致SQL线程卡住，主备延迟。

FTWRL主要包括3个步骤：

上全局读锁(lock_global_read_lock)

清理表缓存(close_cached_tables)

上全局COMMIT锁(make_global_read_lock_block_commit)

简单的自动备份脚本service mysql stopcp -r /mysqldata/test/  /backup/newback/dbfile/service mysql start简单的自动还原脚本service mysql stoprm -rf /mysqldata/test/cp -r  /backup/newback/dbfile/test/ /mysqldata/chown -R mysql:mysql /mysqldata/test/service mysql start

mysqldump+bin_log备份(逻辑)

mysqldump可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取得所有的模式（Schema）并且将其转换成DDL语法（CREATE语句，即数据库定义语句），取得所有的数据，并且从这些数据中创建INSERT语句。

导出单个数据库test，多个数据库，默认配置只导出了数据库中表的定义及数据，没有数据库的定义语句，存储过程和函数也未导出，所以在此导入该脚本时是与原库表对象合并的结果。可以手动创建新库，导入到新库里。

mysqldump -uroot -p -S /mysqlfile/mysql.sock  test > test_0107.sqlmysql -uroot -p test < test_0107.sqlmysqldump -uroot -p -S /mysqlfile/mysql.sock --databases  test test2 > test_test2_0107.sqlmysql -uroot -p < test_test2_0107.sql导出指定数据库test的指定表t7,t3和t7,包含函数和存储过程mysqldump -uroot -p -S /mysqlfile/mysql.sock  test t7 > db_test_t7.sqlmysqldump -uroot -p -S /mysqlfile/mysql.sock  test t3 t7 > test_t3_t7_0107.sqlmysqldump -uroot -p -S /mysqlfile/mysql.sock -R  test t3 t7 > test_t3_t7_r_0107.sql导出指定数据库test的指定表t7中指定条件的数据，实际测试用-w,-where会有错误mysqldump -uroot -p -S /mysqlfile/mysql.sock -w "id=1"  test t7 > db_test_t7_whereid1.sqlmysqldump -uroot -p -S /mysqlfile/mysql.sock --where="id=1"  test t7 > db_test_t7_whereid2.sql常用选项，这些选项简写的时候使用“-”，完整拼接写法时“--”，完整写法一般会有“=”对应的值--add-drop-database Add a DROP DATABASE before each create.--add-drop-table    Add a DROP TABLE before each create.                    (Defaults to on; use --skip-add-drop-table to disable.)--add-locks         Add locks around INSERT statements.                    (Defaults to on; use --skip-add-locks to disable.)-c, --complete-insert                    Use complete insert statements.-F, --flush-logs    Flush logs file in server before starting dump.-x, --lock-all-tables                    Locks all tables across all databases. This is achieved                    by taking a global read lock for the duration of the                    whole dump. Automatically turns --single-transaction and                    --lock-tables off.-l, --lock-tables   Lock all tables for read.                    (Defaults to on; use --skip-lock-tables to disable.)-n, --no-create-db  Suppress the CREATE DATABASE ... IF EXISTS statement that                    normally is output for each dumped database if                    --all-databases or --databases is given.-t, --no-create-info                    Don't write table creation info.-d, --no-data       No row information.-T, --tab=name      Create tab-separated textfile for each table to given                    path. (Create .sql and .txt files.) NOTE: This only works                    if mysqldump is run on the same machine as the mysqld                    server.--fields-terminated-by=name                    Fields in the output file are terminated by the given                    string.-R, --routines      Dump stored routines (functions and procedures).实例：mysqldump -uroot -p -S /mysqlfile/mysql.sock -w "id=1"  --no-create-info  --fields-terminated-by=, --tab=/backup/newback/dumpbackup/  test t7mysqldump+binlog备份完整实例mysqldump完整备份，备份时使用单事务模式，刷新日志，备份存储过程和函数，指定数据库选项，添加删除数据库选项，否则只对数据库中的对象进行merge合并。mysqldump -uroot -p -S /mysqlfile/mysql.sock  --single-transaction  --flush-logs  --routines  --add-drop-database --databases test test2 > /backup/newback/dumpbackup/test_test2.sql还原备份，需要断开所有连接到需要还原的数据库，同时还原数据库中如果包含有不能正常识别的文件时会提示错误，需要手动清理游离文件。使用mysqlbinlog恢复日志。还原前会重新生成新日志文件。[root@DualMySQL-DA dumpbackup]# mysql -uroot -p < test_test2.sql                           Enter password:ERROR 1010 (HY000) at line 370: Error dropping database (can't rmdir './test2', errno: 39)[root@DualMySQL-DA ~]# ls /mysqldata/test2/tt2.ibd[root@DualMySQL-DA ~]# rm -rf /mysqldata/test2/tt2.ibd[root@DualMySQL-DA dumpbackup]# mysql -uroot -p < test_test2.sqlEnter password:[root@DualMySQL-DA dumpbackup]# mysqlbinlog  /mysqlfile/mysql-bin.000033 | mysql -uroot -pEnter password:mysqldump是一个客户端的逻辑备份工具, 可以生成一个重现创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备mysql> show variables like '%bin%';/etc/my.cnf 配置文件中的log-bin其他选项:-E, --events: 备份事件调度器-R, --routines: 备份存储过程和存储函数--triggers: 备份表的触发器; --skip-triggers --master-date[=value] 1: 记录为CHANGE MASTER TO 语句、语句不被注释2: 记录为注释的CHANGE MASTER TO语句基于二进制还原只能全库还原--flush-logs: 日志滚动锁定表完成后执行日志滚动备份数据库下指定的表mysqldump -uroot -p -S /mysqlfile/mysql.sock --lock-all-tables  test t1 t2 > /backup/newback/dumpbackup/test_t1_t2.sql不加 --lock-all-tables 会报如下错误[root@DualMySQL-DA dumpbackup]# mysqldump -uroot -p -S /mysqlfile/mysql.sock  test t1 t2 > /backup/newback/dumpbackup/test_t1_t2.sqlEnter password:mysqldump: Got error: 1205: Lock wait timeout exceeded; try restarting transaction when doing LOCK TABLES还原备份mysql> source /backup/newback/dumpbackup/test_t1_t2.sql备份指定数据库mysqldump -u root -p  --databases  test test2  --master-data=2 --events  --routines --lock-all-tables  -S /mysqlfile/mysql.sock > /backup/newback/dumpbackup/test_test2.sql备份全库[root@DualMySQL-DA dumpbackup]# mysqldump -uroot -p --all-databases --lock-all-tables -S /mysqlfile/mysql.sock > /backup/newback/dumpbackup/all_databases.sqlMysqldump+bin_log备份导出指定的库，source 导入时提示，不能用source进行还原ERROR 1050 (42S01): Table '`test2`.`tt2`' already existsQuery OK, 0 rows affected (0.00 sec)ERROR 1146 (42S02): Table 'test2.tt2' doesn't existERROR 1146 (42S02): Table 'test2.tt2' doesn't existERROR 1146 (42S02): Table 'test2.tt2' doesn't existERROR 1146 (42S02): Table 'test2.tt2' doesn't exist最终只有库没有数据表Bin-log备份直接拷贝cp /mysqlfile/mysql-bin.000026  /backup/newback/dumpbackup/mysqlbinlogMySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更。binlog日志属于二进制文件，我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复或不完全恢复。mysqlbinlog可以基于时间点，position等方式实现不完全恢复或时点恢复。可以从支持本地或远程方式提取binlog日志。可以基于server_id，以及基于数据库级别提取日志，不支持表级别。二进制日志的开启show variables like '%log_bin%';/etc/my.cnflog-bin                        = /mysqlfile/mysql-binSQL命令show binlog events提取binarylog信息show variables like '%version%';show binary logs;flush logs;show binlog events in 'mysql-bin.000035';show binlog events in 'mysql-bin.000035'  from 120;show master status;mysqlbinlog方式提取日志mysqlbinlog  /mysqlfile/mysql-bin.000035mysqlbinlog  /mysqlfile/mysql-bin.000035  | grep createmysqlbinlog  --start-position="120" --stop-position="318" /mysqlfile/mysql-bin.000035mysqlbinlog  --start-position="120" --stop-position="318" /mysqlfile/mysql-bin.000035 | grep "# at"mysqlbinlog  --start-position="120" --stop-position="318" /mysqlfile/mysql-bin.000035 | mysql -uroot -pmysqlbinlog  --start-position="120" --stop-position="318" /mysqlfile/mysql-bin.000035 --result-file=extract.sqlmysqlbinlog  --start-position="120" /mysqlfile/mysql-bin.000034  /mysqlfile/mysql-bin.000035  --result-file=extract.sqlmysqlbinlog  --start-position="120" --database=test2 /mysqlfile/mysql-bin.000034  /mysqlfile/mysql-bin.000035  --result-file=extract.sqlmysqlbinlog操作选项-d, --database=name   List entries for just this database (local log only).--start-datetime=name                      Start reading the binlog at first event having a datetime                      equal or posterior to the argument; the argument must be                      a date and time in the local time zone, in any format                      accepted by the MySQL server for DATETIME and TIMESTAMP                      types, for example: 2004-12-25 11:25:56 (you should                      probably use quotes for your shell to set it properly).-j, --start-position=#                      Start reading the binlog at position N. Applies to the                      first binlog passed on the command line.--stop-datetime=name                      Stop reading the binlog at first event having a datetime                      equal or posterior to the argument; the argument must be                      a date and time in the local time zone, in any format                      accepted by the MySQL server for DATETIME and TIMESTAMP                      types, for example: 2004-12-25 11:25:56 (you should                      probably use quotes for your shell to set it properly).--stop-position=#   Stop reading the binlog at position N. Applies to the                      last binlog passed on the command line.-r, --result-file=name                      Direct output to a given file. With --raw this is a                      prefix for the file names.

xtrabackup工具（热备）

安装配置

Xtrabackup在备份恢复过程中需要读取配置文件，查找各种目录，所有无法在远程客户机上运行，只能在服务器本地部署。

需要设置为每个表单个表空间，否则无法实现单表或者单库导出，如果是中途修改需要dump数据库，清空数据目录，重新初始化数据库，修改配置后再导入。

show variables like '%innodb_file_per_table%';vim/etc/my.cnfinnodb_file_per_table= 1

xtrabackup_checkpoints 检查点文件，记录备份类型，备份状态（full-backuped，incremental，full-prepared）

LSN（Log Sequence Number） 日志序列号，判断全备还是增量备份。

工作原理如下：

首先完成一个完全备份，并记录下此时检查点的LSN(Log Sequence Number)。

在进程增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。

首先，在logfile中找到并记录最后一个checkpoint(“last checkpoint LSN”)，然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile；接着，开始拷贝全部的数据文 件.ibd；在拷贝全部数据文件结束之后，才停止拷贝logfile。

因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。

cat /etc/redhat-releaseRed Hat Enterprise Linux Server release 6.5 (Santiago)rpm -ivh MySQL-shared-compat-5.6.32-1.linux_glibc2.5.x86_64.rpmrpm -ivh perl-DBD-MySQL-4.013-3.el6.x86_64.rpmrpm -ivh libev-4.03-3.el6.x86_64.rpmrpm -ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpminnobackupex  --help配置备份专用用户grant reload,lock tables,process,replication client on  *.* to 'testuser'@'%' identified by 'system';flush privileges;全备，可以将日志重定向输出2>innobackupex  --user=testuser --password=system  /backup/newback/xtrabackup/innobackupex  --user=testuser --password=system  /backup/newback/xtrabackup/  2> /backup/newback/xtrabackup/backup.logDML第一次增量备份，增量基础是全备innobackupex  --user=testuser --password=system --incremental  /backup/newback/xtrabackup/  --incremental-basedir=/backup/newback/xtrabackup/2017-01-02_23-32-02/第二次增量备份，增量基础是第一次增量innobackupex  --user=testuser --password=system --incremental  /backup/newback/xtrabackup/  --incremental-basedir=/backup/newback/xtrabackup/2017-01-02_23-35-00/备份binlog，生产系统建议全部备份cp /mysqlfile/mysql-bin.000026  /backup/newback/xtrabackup/cp /mysqlfile/mysql-bin.000027  /backup/newback/xtrabackup/cp /mysqlfile/mysql-bin.000028  /backup/newback/xtrabackup/

插入一个看到的实验现象，把库备份完成后，直接MV数据库数据文件目录，未停止服务前仍然可以正常操作，包括创建对象，对象会存在于MV后的目录里。

开始准备还原恢复，此时正常情况下MySQL服务是关闭的。所备份的数据，不能立即用来恢复，还需要对其做一次将所有的事物日志中的事物已提交的事物进行同步至数据文件，将未提交的事物进行回滚，这个过程被称为预处理过程，因此备份处理的数据如果没有做好预处理的话是不能拿来恢复的。

在prepare过程中，XtraBackup使用复制到的transactions log对备份出来的innodb data file进行crash recovery。XtraBackup每次读写1MB的数据，在复制transactions log的时候，每次读写512KB的数据

预处理全备innobackupex  --apply-log --redo-only /backup/newback/xtrabackup/2017-01-02_23-32-02/预处理第一次增量innobackupex  --apply-log --redo-only /backup/newback/xtrabackup/2017-01-02_23-32-02  --incremental-dir=/backup/newback/xtrabackup/2017-01-02_23-35-00/预处理第二次增量，如果是最后一次增量不用设置--redo-onlyinnobackupex  --apply-log             /backup/newback/xtrabackup/2017-01-02_23-32-02  --incremental-dir=/backup/newback/xtrabackup/2017-01-02_23-37-44/生成增量binlog,通过查看最后一次增量的xtrabackup_info文件确认增量备份结束时使用的日志及日志位置，从此位置开始利日志文件生成SQL脚本。如果有多个binlog文件可以一块生成sql文件mysqlbinlog  --start-position=553 /backup/newback/xtrabackup/mysql-bin.000028 > /backup/newback/xtrabackup/incremental.sqlmysqlbinlog  --start-position=821  /backup/newback/xtrabackup/mysql-bin.000026 /backup/newback/xtrabackup/mysql-bin.000027 /backup/newback/xtrabackup/mysql-bin.000028 > /backup/newback/xtrabackup/threeheyi_start.sql还原，之前的准备已经把增量备份全部整合到了全备里，还原只用一次还原全备目录即可。还原恢复时先将服务停掉，清空数据库目录。innobackupex  --copy-back /backup/newback/xtrabackup/2017-01-02_23-32-02/修改数据目录权限chown -R mysql.mysql /mysqldata/启动mysql服务恢复binlog，此处建议登录MySQL后关闭sql_log_bin，恢复完脚本后再打开。mysql> SET sql_log_bin=OFF;mysql> source /backup/newback/xtrabackup/incremental.sqlmysql> SET sql_log_bin=ON;检查确认配置备份脚本《innobackup_full.sh》，《innobackup_full_incremental.sh》

Master-Slave Replication

略