MySQL逻辑备份mysqldump
MySQL 备份之 mysqldump
mysqldump
mysqldump工具备份:
本质:导出的是SQL语句文件
优点:不论是什么存储引擎,都可以用mysqldump备成SQL语句
缺点:速度较慢,导入时可能会出现格式不兼容的突发情况,无法做增量备份和累计增量备份
提供三种级别的备份,表级,库级和全库级
Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS]
说明:
如果备份对象下的数据库绝大多数都是myisam类型表,为了保证数据的一致性,备份时需要锁定表
如果是针对innodb的表进行备份由于innodb是事务型的引擎,会话与会话之间是隔离的,所以备份的时候不影响数据库的正常使用,无需锁表
--lock-tables 如果备份的数据库里的表与其他库没有关系的话,那么只需要锁定该库下的表就可以了 --lock-all-tables 如果备份的数据库里的表与其他库有关系的话,那么需要锁定整个mysql数据库的所有库下的所有表 --flush-logs, -F 开始备份前刷新日志 --flush-privileges 备份包含mysql数据库时刷新授权表 --lock-all-tables, -x MyISAM 一致性 服务可用性 --lock-tables, -l 备份前锁表 --single-transaction 适用InnoDB引擎,保证一致性 服务可用性 --master-data=1|2 该选项将会记录binlog的日志位置与文件名并追加到文件中
表级备份 / 恢复
表级备份:
[root@Admin ~]# mysqldump -p123 db01 table01 > /tmp/mysqlback/table01.sql # 备份单个表 [root@Admin ~]# mysqldump -p123 db01 table01 table02 > /tmp/mysqlback/table01_02.sql # 备份多个表
表级恢复:
[root@Admin ~]# mysql -p123 db01 < /tmp/mysqlback/table01.sql 或者在mysql数据库内使用source命令来执行外部的sql文件 mysql> source /tmp/mysqlback/table01.sql
库级备份 / 恢复
库级备份:
[root@Admin ~]# mysqldump --databases db01 -p123 > /tmp/mysqlback/db01.sql # 备份单个库 [root@Admin ~]# mysqldump --databases db01 db02 -p123 > /tmp/mysqlback/db01_02.sql # 备份多个库
表级恢复:
[root@Admin ~]# mysql -p123 < /tmp/mysqlback/db01.sql mysql> source /tmp/mysqlback/db01_02.sql
全库备份 / 恢复
全库级备份:
考虑到数据库有innodb,也有其他类型的表,那么就只能锁表备份
[root@Admin ~]# mysqldump -p123 --lock-tables --all-databases > /tmp/mysqlback/alldb.sql
全库级恢复:
[root@Admin ~]# mysql -p123 < /tmp/mysqlback/alldb.sql mysql> source /tmp/mysqlback/alldb.sql
注意:如果是在终端直接在数据目录里面将数据全部删除, 再恢复数据的话就需要初始化才能恢复。
mysqldump+binlog
完全备份(mysqldump)+增量备份(binlog)
适用于中小型数据库;通过结合二进制日志文件,把数据库恢复到最新的状态
二进制日志默认会记录下所有对数据库变化的操作
二进制日志文件中会记录某个操作的详细SQL语句,还有执行的时候环境,时间,以及该记录在二进制日志文件的起始和结束点pos值
error log # 错误日志,记录mysql服务端在运行时产生的错误信息,以及mysql启动和关闭的日志信息(排错) slow log # 慢查询日志,慢查询时间阀值,以秒为单位,如果超过这个阀值就是慢查询(调优) bin log # 二进制日志 ,记录对数据库增、删、改的SQL操作,可以使用这个日志做增量备份(备份) Relay log # 中继日志(主从复制日志)从机器上从主机器复制过来日志,根据日志来同步数据(复制)
配置二进制日志
查看二进制日志是否开启:
mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | log_bin | OFF | | log_bin_basename | /data/DB/mysql-bin | | log_bin_index | /data/DB/mysql-bin.index | | log_bin_trust_function_creators | ON | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------+ 6 rows in set (0.00 sec)
log_bin |OFF 关闭 ON 开启
修改配置文件
[root@Admin ~]# vim /etc/my.cnf log-bin=/var/lib/mysql/mysql56-bin.log log-bin (可直接这样写就ok) [root@Admin ~]# service mysqld restart Shutting down MySQL.... [确定] Starting MySQL..... [确定]
再次查看开启:
mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------+ | Variable_name | Value | +---------------------------------+--------------------------+ | log_bin | ON | | log_bin_basename | /data/DB/mysql-bin | | log_bin_index | /data/DB/mysql-bin.index | | log_bin_trust_function_creators | ON | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------+ 6 rows in set (0.00 sec)
mysqlbinlog
--start-datetime=name 开始的时间 --stop-datetime=name 结束的时间 --start-position=# 开始的位置(POS) --stop-position=# 结束的位置
示例1
备份
先做全量备份,然后更新数据并误操作,数据恢复
[root@Admin ~]# mysqldump -p123 --flush-logs --master-data=2 --all-databases > /tmp/mysqlback/all_back.sql
- --flush-logs 备份时先将内存中日志写回磁盘,然后截断日志,并产生新的日志文件
- --master-data=2 该选项将二进制日志的位置和文件名写入到备份文件,等于2表示CHANGE
- MASTER语句被写成SQL注释;1表示没有注释,默认是1.
查看完整备份文件中的字段
[root@Admin ~]# vim /tmp/mysqlback/all_back.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=120;
数据更改
添加几条数据,然后随便删除一个库
更改完后查看mysql-bin.000008日志文件找到误删除的POS值
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[root@Admin DB]# mysqlbinlog --no-defaults mysql-bin.000008 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180520 3:55:12 server id 1 end_log_pos 120 CRC32 0x077f82c8 Start: binlog v 4, server v 5.6.31-log created 180520 3:55:12 # Warning: this binlog is either in use or was not closed properly. BINLOG ' IIEAWw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAciC fwc= '/*!*/; # at 120 #180520 3:56:46 server id 1 end_log_pos 201 CRC32 0xa954edb5 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1526759806/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 201 #180520 3:56:46 server id 1 end_log_pos 316 CRC32 0xc34378c8 Query thread_id=1 exec_time=0 error_code=0 use `login`/*!*/; SET TIMESTAMP=1526759806/*!*/; insert into t1(id,name) values(6,'eee') /*!*/; # at 316 #180520 3:56:46 server id 1 end_log_pos 347 CRC32 0xea43bde1 Xid = 1227 COMMIT/*!*/; # at 347 #180520 3:56:53 server id 1 end_log_pos 428 CRC32 0x5fd30851 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1526759813/*!*/; BEGIN /*!*/; # at 428 #180520 3:56:53 server id 1 end_log_pos 543 CRC32 0x97402f36 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1526759813/*!*/; insert into t1(id,name) values(8,'aaa') /*!*/; # at 543 #180520 3:56:53 server id 1 end_log_pos 574 CRC32 0xfa2cc4ba Xid = 1228 COMMIT/*!*/; # at 574 #180520 3:57:00 server id 1 end_log_pos 655 CRC32 0x7ba6913f Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1526759820/*!*/; BEGIN /*!*/; # at 655 #180520 3:57:00 server id 1 end_log_pos 771 CRC32 0x7856052f Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1526759820/*!*/; insert into t1(id,name) values(10,'bbb') /*!*/; # at 771 #180520 3:57:00 server id 1 end_log_pos 802 CRC32 0x0b597d2b Xid = 1229 COMMIT/*!*/; # at 802 #180520 3:57:19 server id 1 end_log_pos 894 CRC32 0x47136864 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1526759839/*!*/; drop database db01 /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
通过查看二进制日志,由于我刚刚是删除的一个db01库,删除db01库的那条操作上面的POS值是at 802, 所以我应该恢复到802
恢复
先进行全库恢复
[root@Admin ~]# mysql -p123 < /tmp/mysqlback/all_back.sql
[root@Admin ~]# mysqlbinlog --start-position=120 --stop-position=802 /data/DB/mysql-bin.000008 |mysql -p123
恢复完成后进入数据库查看是否存在刚刚添加的数据
总结
mysqldump+binlog做增量备份——>通过binlog日志恢复到最新状态
- 当前数据库必须开启二进制日志(修改配置文件)
- 使用mysqldump工具做全库备份
- 更新数据
- 直接恢复
- 使用全库备份恢复
- 使用binlog日志恢复到最新状态