基于逻辑备份工具mysqldump备份

备份方式一：基于逻辑备份工具mysqldump备份

一、mysqldump: MySQL客户端工具；逻辑备份工具，查询请求转换为批量插入的insert语句，保存为SQL脚本；

备份整个服务器，单个或部分数据库，单个或部分表，表中某些行，存储过程，存储函数，触发器；

能自动记录备份时的二进制日志文件及相应position；

1.1直接在mysqldump命令中加读锁

-x --lock-all-tables 在备份前锁定所有表，仅对需备份的数据库

-l --lock-tables 在备份前锁定单表，备一张前锁一张，但多表间的时间不一致不能保证数据一致。因此只有在备份单表时使用此选项

[root@station253 data]# mysqldump -uroot -predhat --databases school cactidb --lock-all-tables > /backup/school_cactidb_full.sql

mysql> show master status;

| mysql-bin.000001 | 1314 | | |

还原数据库，记得先删除源库

mysql> source /backup/school_cactidb_full.sql;

1.2--master-data[=#]

这个参数在建立slave数据库的时候会用到，默认为0，不做CHANGE MASTER TO这个语句，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，file和position记录的位置就是slave从master端复制文件的起始位置。默认情况下这个值是1

当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是不会有上面那个作用了，自动注释掉。

[root@station253 data]# mysqldump -uroot -predhat --master-data=2 --databases school cactidb --lock-all-tables > /backup/school_cactidb_full_3.sql

[root@station253 data]# cat /backup/school_cactidb_full_3.sql | grep 'CHANGE MASTER'

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1314;

1.3 mysqldump

--lock-all-tables 锁定所有表，所有用户不能执行写表。对于InnoDB引擎锁定所有表未必立即生效，buffer pool还未能写入磁盘。对于全是InnoDB引擎的数据库使用另外一个--single-transaction: 基于此选项能使mysql为mysqldump创建一个单一大事务应用于全部数据库，对每张innoDB表创建快照，基于快照实现热备InnoDB表；

由此，不需要同时使用--lock-all-tables；但会长时间占用系统资源

完整的备份恢复过程：

备份备份策略：例如每周一次完全备份+每天一次增量备份（每天定时滚动后备份当天产生二进制日志）

mysqldump+二进制日志

备份步骤：

1.完全备份

2.修库并增量日志备份

3.修库并即时点日志备份

1.3.1周一做完整备份

[root@station253 data]# mysqldump -uroot -predhat --single-transaction --master-data=2 --databases cactidb > /backup/cactidb_`date +%F`.sql

[root@station253 backup]# ll cactidb_2014-05-22.sql

-rw-r--r-- 1 root root 2123 5月 22 23:59 cactidb_2014-05-22.sql

mysql> show master status; 查看二进制日志的当前位置

| mysql-bin.000001 | 1314 | | |

mysql> show binlog events in 'mysql-bin.000001';

| mysql-bin.000001 | 1108 | Query | 1 | 1216 | use `cactidb`; insert into tb1 values(1,'Tom'),(2,'Mary')

| mysql-bin.000001 | 1243 | Query | 1 | 1314 | FLUSH TABLES

1.3.2第二天新建表，导出上次完全备份到昨天增量二进制日志

mysql> use cactidb;

mysql> CREATE TABLE tb4(id INT);

mysql> INSERT INTO tb4 VALUES (1),(2),(4);

mysql> show master status; 查看二进制日志的当前位置

| mysql-bin.000001 | 1602 | | |

导出上次完全备份到昨天增量二进制日志

[root@station253 data]# mysqlbinlog --start-position=1314 --stop-position=1602 mysql-bin.000001 > /backup/cactidb_`date +%F_%H`.sql

[root@station253 backup]# cat cactidb_2014-05-23_00.sql

# at 1314

#140523 0:12:00 server id 1 end_log_pos 1404 Query thread_id=3 exec_time=0 error_code=0

use `cactidb`/*!*/;

# at 1575

#140523 0:12:19 server id 1 end_log_pos 1602 Xid = 201

1.3.3第三天新建表，导出上次增量备份到即时点日志

mysql> use cactidb;

mysql> INSERT INTO tb4 values (5),(9); 未备份的操作

mysql> show master status; 查看二进制日志的当前位置

| mysql-bin.000001 | 1796 | | |

mysql> show binlog events in 'mysql-bin.000001'; 查看二进制日志的详细内容

| mysql-bin.000001 | 1673 | Query | 1 | 1769 | use `cactidb`; INSERT INTO tb4 values (5),(9) | mysql-bin.000001 | 1769 | Xid | 1 | 1796| COMMIT /* xid=208 */

1.3.4模拟数据库崩溃

mysql> drop database cactidb;

mysql> show binlog events in 'mysql-bin.000001';

| mysql-bin.000001 | 1796 | Query | 1 | 1883 | drop database cactidb

1.4恢复数据库步骤

1. 导出上增点到即时点的二进制日志

2. 重启并关闭二进制日志

3. 恢复完全备份，增量日志，即时点日志

4. 打开二进制日志

1.4.1记录未备份的二进制日志，先找出从上次增量备份结束1602到DB崩溃前drop database 1796的二进制日志

[root@station253 data]# mysqlbinlog --start-position=1602 --stop-position=1796 mysql-bin.000001 > /tmp/cactidb.sql

1.4.2模拟服务器崩溃，重启服务并重新登录

[root@station253 data]# ls mysql-bin.*

mysql-bin.000001 mysql-bin.index

[root@station253 data]# service mysqld restart

[root@station253 data]# ls mysql-bin.*

mysql-bin.000001 mysql-bin.000002 mysql-bin.index

[root@station253 ~]# mysql -uroot -predhat

mysql> SET sql_log_bin=0;关闭二进制日志 ,防止导入时产生重复日志

mysql> show master status;

| mysql-bin.000002 | 107 | | |

mysql> show binlog events in 'mysql-bin.000002';

| mysql-bin.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.33-log, Binlog ver: 4 |

#mysql> flush logs; 滚动出新日志，如为记录能在位于页首，这里可以不用

1.4.3 恢复备份和日志

1.恢复完全备份

[root@station253 ~]# mysql -uroot -predhat < /backup/cactidb_2014-05-22.sql

2.恢复增量二进制日志

[root@station253 ~]# mysql -uroot -predhat < /backup/cactidb_2014-05-23_00.sql

3.恢复未备份的二进制日志

[root@station253 ~]# mysql -uroot -predhat < /tmp/cactidb.sql

4.检查恢复的数据库

mysql> use cactidb;

Database changed

mysql> show tables;

| Tables_in_cactidb |

| tb1 |

| tb4 |

mysql> select * from tb4;

| id |

| 1 |

| 2 |

| 4 |

| 5 |

| 9 |

mysql> show master status;

| mysql-bin.000002 | 1473 | | |

mysql> show binlog events in 'mysql-bin.000002';

| mysql-bin.000002 | 1350 | Query | 1 | 1446 | use `cactidb`; INSERT INTO tb4 values (5),(9) | mysql-bin.000002 | 1446 | Xid | 1 | 1473 | COMMIT /* xid=86 */

1.4.4 打开二进制日志

mysql> SET sql_log_bin=1;

本文出自 “aha45” 博客，请务必保留此出处http://manfred12.blog.51cto.com/137312/1415693

秒客网

基于逻辑备份工具mysqldump备份

相关文章