如何实现mysql读写分离
1.通过程序实现读写分类(性能 效率最佳)
php和java都可以通过设置多个连接文件轻松实现对db的读写分离,即当select时,就去连读库的连接文件,当update,insert,delete时就去连写库的连接文件. 2.mysql-proxy, amoeba等代理软件也可以实现读写分离 3.开发dbproxy主从同步原理
是异步的,逻辑的
主库
必须开启binlog io线程 从库 io线程 sql线程master: ip port user/pass bin-file bin-position
1.从库2个线程,主库1个线程
2.ip port user/pass bin-file bin-position 3.开启开关前,确保主从库基于某个位置点以前一致. 4.master建立同步账号 5.start salve建库建表
create database people;use people;create table p1 (id int,name char(40));insert into p1 values(1,'maotai');insert into p1 values(2,'毛台');insert into p1 values(3,'maomao');insert into p1 values(4,'毛毛');
备份数据库
mysqldump -uroot -p123456 people > /opt/people_bak.sqlegrep -v "#|\*|--|^$" /opt/people_bak.sql
[root@n1 ~]# egrep -v "#|\*|--|^$" /opt/people_bak.sqlDROP TABLE IF EXISTS `p1`;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `p1` WRITE;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');UNLOCK TABLES;
注: 从上面看到,
- LOCK TABLES插数据时候,锁表了,禁止其他修改.
- insert语句整合成了一条
- 导出的都是一些sql语句
http://www.cnblogs.com/iiiiiher/p/8205915.html
SET NAMES 'latin1'; SET character_set_client = latin1;SET character_set_connection = latin1;SET character_set_database = latin1;SET character_set_results = latin1;SET character_set_server = latin1;SET character_set_system = latin1;
删表后恢复
[root@n1 etc]# mysql -uroot -p123456 -e 'use people;drop table p1';[root@n1 etc]# mysql -uroot -p123456 people < /opt/people_bak.sql
- 不加-B[root@n1 ~]# mysqldump -uroot -p123456 people > /opt/people_bak.sql[root@n1 ~]# egrep -v "#|\*|--|^$" /opt/people_bak.sqlDROP TABLE IF EXISTS `p1`;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `p1` WRITE;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');UNLOCK TABLES;- 加上-B多了: USE `people`;[root@n1 ~]# mysqldump -uroot -p123456 -B people > /opt/people_bak_B.sql[root@n1 ~]# egrep -v "#|\*|--|^$" /opt/people_bak_B.sqlCREATE DATABASE /*!32312 IF NOT EXISTS*/ `people` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `people`;DROP TABLE IF EXISTS `p1`;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `p1` WRITE;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');UNLOCK TABLES;- 导入时候不用指定库了.[root@n1 etc]# mysql -uroot -p123456 -e 'drop database people;[root@n1 etc]# mysql -uroot -p123456 < /opt/people_bak.sql-B: - sql多了建库语句和use语句- 指定多个库备份
--compact debug时候用,忽略了一些东西.
[root@n1 ~]# mysqldump -uroot -p123456 -B --compact peoplemysqldump: [Warning] Using a password on the command line interface can be insecure.CREATE DATABASE /*!32312 IF NOT EXISTS*/ `people` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `people`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');
gzip压缩
[root@n1 ~]# mysqldump -uroot -p123456 -B people|gzip > /opt/people_bak_B.sql.gz[root@n1 ~]# ls -lh /opt/people_bak_B.sql.gz /opt/people_bak.sql -rw-r--r-- 1 root root 761 Mar 20 20:20 /opt/people_bak_B.sql.gz-rw-r--r-- 1 root root 1.9K Mar 20 20:08 /opt/people_bak.sql
小结: 备份库时
-B gzip 压缩mysqldump原理
将db里的数据,以sql语句的形式导出.
恢复过程: 即将sql语句重新执行的一个过程.-B备份同时多个库
create database people2;use people;create table p1 (id int,name char(40));insert into p1 values(1,'maotai');insert into p1 values(2,'毛台');insert into p1 values(3,'maomao');insert into p1 values(4,'毛毛');
- 同时备份多个库mysqldump -uroot -p123456 -B people people2 > /opt/people_bak_multi.sql- 恢复mysql -uroot -p123456 -e 'drop database people;drop database people2';mysql -uroot -p123456 < /opt/people_bak_multi.sql
分库备份
mysqldump -uroot -p123456 -B people > /opt/people_bak_B.sqlmysqldump -uroot -p123456 -B people2 > /opt/people2_bak_B.sql
[root@n1 etc]# mysql -uroot -p123456 -e 'show databases'|grep -Evi "database|infor|mysql|per|sys"|sed 's#^#mysqldump -uroot -p123456 -B #g'mysqldump -uroot -p123456 -B peoplemysqldump -uroot -p123456 -B people2
方法1: sed后向引用
[root@n1 etc]# mysql -uroot -p123456 -e 'show databases'|grep -Evi "database|infor|mysql|per|sys"|sed -r 's#^([a-z].*)#mysqldump -uroot -p123456 -B \1|gzip > /opt/\1.gz #g'mysqldump -uroot -p123456 -B people|gzip > /opt/people.gz mysqldump -uroot -p123456 -B people2|gzip > /opt/people2.gz
mysql -uroot -p123456 -e 'show databases'|grep -Evi "database|infor|mysql|per|sys"|sed -r 's#^([a-z].*)#mysqldump -uroot -p123456 -B --events \1|gzip > /opt/\1.gz #g'|bash
方法2: for循环
http://edu.51cto.com/course/808.htmlmkdir /sql/for dbname in `mysql -uroot -p123456 -e 'show databases'|grep -Evi "database|infor|mysql|per|sys"`;do mysqldump -uroot -p123456 -B --events ${dbname}|gzip > /sql/${dbname}_sql.gzdone 分库的意义: 恢复某个库时候有优势.
备份单个表
mysqldump -u 用户名 -p 数据库库名 表名 > 备份的文件名mysqldump -uroot -p123456 people p1 > /opt/people_p1_bak_B.sql
创建两张表
create database people;use people;create table p1 (id int,name char(40));insert into p1 values(1,'maotai');insert into p1 values(2,'毛台');insert into p1 values(3,'maomao');insert into p1 values(4,'毛毛');create table p2 (id int,name char(40));insert into p2 values(1,'maotai');insert into p2 values(2,'毛台');insert into p2 values(3,'maomao');insert into p2 values(4,'毛毛');
mysqldump -uroot -p123456 --compact people p1 [root@n1 ~]# mysqldump -uroot -p123456 --compact people p1;mysqldump: [Warning] Using a password on the command line interface can be insecure./*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛'),(1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');
备份多个表
mysqldump -u 用户名 -p 数据库库名 表1 表2 > 备份的文件名mysqldump -uroot -p123456 people --compact p1 p2 --compact
[root@n1 ~]# mysqldump -uroot -p123456 people --compact p1 p2 --compactmysqldump: [Warning] Using a password on the command line interface can be insecure./*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛'),(1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p2` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p2` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');
企业需求: 仅恢复某个表,上述方法不适合.
mysqldump -uroot -p123456 people --compact p1 --compactmysqldump -uroot -p123456 people --compact p2 --compact
两个for循环,解决分库分表备份
db_list=`mysql -uroot -p123456 -e 'show databases'|grep -Evi "database|infor|mysql|perfo|sys"`for dbname in $db_list;do tb_list=`mysql -uroot -p123456 -e "use ${dbname};show tables;"|grep -Evi 'Tabl'` for tbname in ${tb_list};do mysqldump -uroot -p123456 ${dbname} ${tbname}|gzip > /sql/${dbname}_${tbname}_bak.sql.gz donedone 生产情况:
1.一个整备+一个分库分表备份. 2.脚本恢复-d仅备份表结构
- 加上-d即备份表结构mysqldump -uroot -p123456 --compact -d people2
[root@n1 sql]# mysqldump -uroot -p123456 --compact -d people2mysqldump: [Warning] Using a password on the command line interface can be insecure./*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p2` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;
-t仅备份数据
mysqldump -uroot -p123456 --compact -t people2
[root@n1 sql]# mysqldump -uroot -p123456 --compact -t people2mysqldump: [Warning] Using a password on the command line interface can be insecure.INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛'),(1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');INSERT INTO `p2` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛'),(1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');
-A备份db里所有库
mysqldump -uroot -p'123456' -A -B |gzip > /opt/all.sql.gz
-F刷新binlog(重新生成一个新的binlog)
打开binlog
log-bin = mysql-bin
[root@n1 mysql]# ll /usr/local/mysql/data/total 122972....-rw-r----- 1 mysql mysql 154 Mar 20 21:16 mysql-bin.000001-rw-r----- 1 mysql mysql 19 Mar 20 21:16 mysql-bin.index
mysqldump -uroot -p'123456' -A -B --events -F|gzip > /opt/all.sql.gz
[root@n1 mysql]# ll /usr/local/mysql/data/total 122984...-rw-r----- 1 mysql mysql 201 Mar 20 21:16 mysql-bin.000001-rw-r----- 1 mysql mysql 201 Mar 20 21:16 mysql-bin.000002-rw-r----- 1 mysql mysql 201 Mar 20 21:16 mysql-bin.000003-rw-r----- 1 mysql mysql 154 Mar 20 21:16 mysql-bin.000004-rw-r----- 1 mysql mysql 76 Mar 20 21:16 mysql-bin.index
全备份+增量备份,-F为了找准确备份点.
--master-data=1(不带注释): 自动加了binlog和位置点.
主从同步时候有用,可以保证不刷新binlog找到全备的位置点.
[root@n1 mysql]# mysqldump -uroot -p123456 --master-data=1 --compact peoplemysqldump: [Warning] Using a password on the command line interface can be insecure.CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154; ## 自动加了binlog和位置点./*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛'),(1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p2` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p2` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');
[root@n1 data]# mysqlbinlog mysql-bin.000002mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
解决:
注释掉[client]#default-character-set = utf8重启mysql即可
- 位置点: 一般是当时的文件大小- 时间全备后,按照这个时间点增量同步[root@n1 data]# mysqlbinlog ./mysql-bin.000004/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#180320 21:16:59 server id 1 end_log_pos 123 CRC32 0x8bd89c27 Start: binlog v 4, server v 5.7.17-log created 180320 21:16:59BINLOG 'ywmxWg8BAAAAdwAAAHsAAAAAAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAASec2Is='/*!*/;# at 123#180320 21:16:59 server id 1 end_log_pos 154 CRC32 0x3dd3cd98 Previous-GTIDs# [empty]# at 154#180320 21:26:14 server id 1 end_log_pos 177 CRC32 0xf878c05e StopSET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
--master-data=2带注释
[root@n1 data]# mysqldump -uroot -p123456 --master-data=2 --compact peoplemysqldump: [Warning] Using a password on the command line interface can be insecure.-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p1` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p1` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛'),(1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `p2` ( `id` int(11) DEFAULT NULL, `name` char(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*!40101 SET character_set_client = @saved_cs_client */;INSERT INTO `p2` VALUES (1,'maotai'),(2,'毛台'),(3,'maomao'),(4,'毛毛');
mysqldump关键参数小结
mysqldump --help
| 参数 | 英文 | 解释 |
|---|---|---|
| -B | --databases | 指定多个库备份,增加建库语句和use语句 |
| --compact | - | 调试时后,精简注释(生产不用) |
| -A | --all-databases | --all-databases: 备份所有库 |
| -F | --flush-logs | --flush-logs: 刷新binlog |
| --master-data | - | 增加binlog日志文件名和对应的位置点 |
| -l | --lock-all-tables | 锁表 |
| -x | --lock-tables | uobiao |
| -d | --no-data | 只备份表结构 |
| -t | --no-create-info | 只是备份数据 |
| --single-transaction | - | 适合innodb事务型数据库(为保证数据一致性) |
常规的备份(自己玩一玩)
如果db有事务,索引等,需要额外加一些别的参数
myisam备份命令:mysqldump -uroot -p123456 -A -B -F --master-info=2 -x --events|gzip > all.sql.gzinnodb(推荐)备份命令:mysqldump -uroot -p123456 -A -B -F --master-info=2 --single-transaction --events|gzip > all.sql.gz--single-transaction: dump时候即使有数据提交,也看不到.不影响本次dump
dba推荐生产使用备份命令:
myisam备份命令:mysqldump -uroot -p123456 -A -F --flush-privileges --triggers --routines --events --hex-blob --master-info=1 -x |gzip > all.sql.gzinnodb(推荐)备份命令:mysqldump -uroot -p123456 -A -F --flush-privileges --triggers --routines --events --hex-blob --master-info=1 --single-transaction |gzip > all.sql.gz-R, --routines Dump stored routines (functions and procedures). #生产一般会加上
db恢复实战:登录mysql source恢复
mysql> system ls /optall.sql.gz people_bak_B.sql people_bak_multi.sql people.gzpeople2.gz people_bak_B.sql.gz people_bak.sqlmysql> source /opt/people_bak_B.sql;Query OK, 0 rows affected (0.00 sec)...
通过sh命令恢复
- 如果备份时没有加-B[root@n1 data]# mysql -uroot -p123456 < /opt/people_bak.sql ERROR 1046 (3D000) at line 22: No database selected[root@n1 data]# mysql -uroot -p123456 people < /opt/people_bak.sql- 如果加了-B: 无需制定库[root@n1 data]# mysql -uroot -p123456 < /opt/people_bak_B.sql
压缩包恢复
- 先解压后恢复[root@n1 opt]# gzip -d people2.gz #-d源文件被干掉了
- 先解压[root@n1 opt]# lltotal 8-rw-r--r-- 1 root root 515 Mar 20 20:32 people2.sql.gz-rw-r--r-- 1 root root 765 Mar 20 20:32 people.sql.gz[root@n1 opt]# [root@n1 opt]# gzip -d *[root@n1 opt]# lspeople2.sql people.sql- 去掉后缀,得到表名[root@n1 opt]# ls *|sed 's#.sql##g'people2people- 循环表名,逐个恢复[root@n1 opt]# for tbname in `ls *|sed 's#.sql##g'`;do mysql -uroot -p123456 < ${tbname}.sql;done -e 非登录执行sql命令
[root@n1 opt]# mysql -uroot -p123456 -e "set names=latin1;show databases;"|grep -Evi "Dat|info|perf|sys"mysql: [Warning] Using a password on the command line interface can be insecure.mysqlpeoplepeople2
查看当前sql连接数: show processlist
[root@n1 opt]# mysql -uroot -p123456 -e "show processlist;"执行多次发现同一个语句, 是慢查询,找出sql语句, 让开发建索引.- 查看完整的sql语句[root@n1 opt]# mysql -uroot -p123456 -e "show full processlist;"
[root@n1 opt]# mysql -uroot -p123456 -e "show full processlist;"mysql: [Warning] Using a password on the command line interface can be insecure.+----+------+-----------+------+---------+------+----------+-----------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+------+---------+------+----------+-----------------------+| 20 | root | localhost | NULL | Query | 0 | starting | show full processlist |+----+------+-----------+------+---------+------+----------+-----------------------+State可能是sleep状态,sleep过多也不行
当然，更根本的方法，还是从以上三点排查之：
1. 程序中，不使用持久链接，即使用mysql_connect而不是pconnect。- 程序执行完毕，应该显式调用mysql_close 3. 只能逐步分析系统的SQL查询，找到查询过慢的SQL,优化之
mysql -u root -p'123456' -e "show full processlist;"|grep -v Sleep
查看全局参数(优化:涉及到调优): show global status
- 查看计数器insert[root@n1 opt]# mysql -uroot -p123456 -e "show global status;"|grep insertmysql: [Warning] Using a password on the command line interface can be insecure.Com_insert 4Com_insert_select 0Delayed_insert_threads 0Innodb_rows_inserted 32Qcache_inserts 0- 插入数据mysql> insert into p1 values(1,'mm');Query OK, 1 row affected (0.32 sec)[root@n1 opt]# mysql -uroot -p123456 -e "show global status;"|grep insertmysql: [Warning] Using a password on the command line interface can be insecure.Com_insert 5Com_insert_select 0Delayed_insert_threads 0Innodb_rows_inserted 33
状态命令小结
| 命令 | 说明 |
|---|---|
| show status; | 查看当前会话的数据库状态信息 |
| show global status; | 查看整个数据库运行状态信息,很重要面分析并作好监控 |
| show processlist; | 查看正在执行的sql语句,看不全. |
| show full processlist; | 查看正在执行的sql语句,全. |
| set global key_buffer_size = 32777218; | 不重启调整db参数,重启后失效 |
| show variables; | 查看db参数信息,如my,cnf参数生效情况 |
数据库连接慢问题解决,查处慢查询语句,优化建索引.
mysqbinlog的作用是?
什么是mysqlbinlog?
mysql-bin.000001mysql-bin.000002mysql-bin.000003mysql-bin.000004mysql-bin.000005
mysqlbinlog记录的内容是?
对db的更改都记录, 查询不记录.
mysql-bin.index用来记录mysql内部的增删改查等对mysql数据库有更新的内容的记录.mysql> insert into p1 values (4,'mmc');Query OK, 1 row affected (0.32 sec)mysql> update p1 set id=10 where id=4;Query OK, 3 rows affected (2.25 sec)Rows matched: 3 Changed: 3 Warnings: 0mysql> select * from p1;
- 本来应该可以看到更改语句的,但是5.7好像转码了
[root@n1 data]# mysqlbinlog mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#180320 22:33:17 server id 1 end_log_pos 123 CRC32 0xcf6a9f58 Start: binlog v 4, server v 5.7.17-log created 180320 22:33:17 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'rRuxWg8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACtG7FaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAVifas8='/*!*/;# at 123#180320 22:33:17 server id 1 end_log_pos 154 CRC32 0x20d87df7 Previous-GTIDs# [empty]# at 154#180320 22:33:42 server id 1 end_log_pos 219 CRC32 0x13fa964d Anonymous_GTID last_committed=0 sequence_number=1SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 219#180320 22:33:42 server id 1 end_log_pos 293 CRC32 0xb9dcb30b Query thread_id=43 exec_time=0 error_code=0SET TIMESTAMP=1521556422/*!*/;SET @@session.pseudo_thread_id=43/*!*/;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 293#180320 22:33:42 server id 1 end_log_pos 343 CRC32 0xd9acc6fb Table_map: `people`.`p1` mapped to number 233# at 343#180320 22:33:42 server id 1 end_log_pos 387 CRC32 0xc74cf4da Write_rows: table id 233 flags: STMT_END_FBINLOG 'xhuxWhMBAAAAMgAAAFcBAAAAAOkAAAAAAAEABnBlb3BsZQACcDEAAgP+Av54A/vGrNk=xhuxWh4BAAAALAAAAIMBAAAAAOkAAAAAAAEAAgAC//wEAAAAA21tY9r0TMc='/*!*/;# at 387#180320 22:33:42 server id 1 end_log_pos 418 CRC32 0x038a9985 Xid = 334COMMIT/*!*/;# at 418#180320 22:34:22 server id 1 end_log_pos 483 CRC32 0x3b47deee Anonymous_GTID last_committed=1 sequence_number=2SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 483#180320 22:34:22 server id 1 end_log_pos 557 CRC32 0x0b8ff166 Query thread_id=43 exec_time=0 error_code=0SET TIMESTAMP=1521556462/*!*/;BEGIN/*!*/;# at 557#180320 22:34:22 server id 1 end_log_pos 607 CRC32 0xcd32d9ae Table_map: `people`.`p1` mapped to number 233# at 607#180320 22:34:22 server id 1 end_log_pos 709 CRC32 0x3409c7f6 Update_rows: table id 233 flags: STMT_END_FBINLOG '7huxWhMBAAAAMgAAAF8CAAAAAOkAAAAAAAEABnBlb3BsZQACcDEAAgP+Av54A67ZMs0=7huxWh8BAAAAZgAAAMUCAAAAAOkAAAAAAAEAAgAC///8BAAAAAbmr5vmr5v8CgAAAAbmr5vmr5v8BAAAAAbmr5vmr5v8CgAAAAbmr5vmr5v8BAAAAANtbWP8CgAAAANtbWP2xwk0'/*!*/;# at 709#180320 22:34:22 server id 1 end_log_pos 740 CRC32 0xb5fac9b2 Xid = 335COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
通过binlog恢复db实战
停掉mysql(会刷新binlog or reset master)
开始msyql,建库
create database people3;use people3;create table p1 (id int,name char(40));insert into p1 values(1,'maotai');insert into p1 values(2,'毛台');insert into p1 values(3,'maomao');insert into p1 values(4,'毛毛');
- 查看最新的binlog(5.7没看到insert,)
[root@n1 data]# mysqlbinlog mysql-bin.000003/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#180320 22:41:19 server id 1 end_log_pos 123 CRC32 0xc1ff049e Start: binlog v 4, server v 5.7.17-log created 180320 22:41:19 at startup# Warning: this binlog is.....
- 导出sql,恢复
mysqlbinlog mysql-bin.000003 > people3.sqlmysql> drop database people3;mysql -uroot -p123456 < people3.sql
早上10点库被某人删掉恢复案例: 全备+增量恢复
mysqldump -uroot -p123456 --routines --single_transaction --master-data=2 --databases weibo > weibo.sql
--master-data=1和--master-data=2的区别
生产备份一般的=2,目的是能够找到全备的一个位置点,方便增量备份,而非让它启到什么实际作用
--master-data=1CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;--master-data=2-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;