一,二进制日志文件学习
(1)mysqlbinlog 解析mysql 二进制日志文件
(2)二进制日志只记录对MYSQL数据有改变的日志,所有库所有表的日志
(3)mysqlbinlog mysql.000014 --start-position=207--stop-position=316 -r pos.sql 指定位置点恢复
(4)mysqlbinlog mysql.000014--start-datetime='2017-12-03 16:05:42' --stop-datetime='2017-12-03 16:05:51' -rtime.sql 指定时间点恢复
(5)master-date: 找到二进制日志备份的点,
例:mysqldump -uroot -phnas_9800 -A -B -S/data/3306/mysql.sock --single-transaction --master-data=2 --events>/opt/zjn1.sql
在zjn.sql中找到master-data的位置点
master-data=1 从库的配置中不需要找位置点
(6)-d 拆库恢复
二,备份的命令和学习
(1)egrep -v"#|\*|--|^$" zjn.sql 查看备份的内容
(2)mysqldump-uroot -phnas_9800 zjn_utf8 -S /data/3306/mysql.sock >/opt/zjn.sql备份数据库
(3)mysql -u root-phnas_9800 -S /data/3306/mysql.sock zjn_utf8</opt/zjn.sql 恢复备份数据库。
(4)mysqldump-uroot -phnas_9800 -B zjn_utf8 -S /data/3306/mysql.sock >/opt/zjn_b.sql加B参数 备份了创建数据库的命令。mysql -u root -phnas_9800 -S/data/3306/mysql.sock </opt/zjn_b.sql 可以直接还原
(5)mysqldump-uroot -phnas_9800 -B zjn_utf8 -S /data/3306/mysql.sock | gzip>/opt/zjn_b_gz.sql.gz压缩后备份
(6)分库备份
mysql -u root -phnas_9800 -S /data/3306/mysql.sock -e"show databases;"|grep -Evi "database|infor|perfor"| sed's/^/mysqldump -u root -phnas_9800 -S \/data\/3306\/mysql.sock -B/g'
在系统里显示数据库并用sed添加命令。
mysql -u root -phnas_9800 -S /data/3306/mysql.sock -e"show databases;"|grep -Evi "database|infor|perfor"| sed's/\([a-z].*\)/mysqldump -u root -phnas_9800 -S \/data\/3306\/mysql.sock -B \1|gzip >\/opt\/\1.sql.gz/'|bash
(7)分表备份
思路,2个for循环,然后mysqldump -uroot -phnas_9800 -S /data/3306/mysql.sock zjn_utf8 student
(8)只备份表结构等
mysqldump -t 只备份表数据
mysqldump -d 只备份表结构
mysqldump -A备份整个数据库
mysqldump -F,切割binlog
三,MYSQL主从读写分离配置
(1)grantreplication slave on *.* to 'rep'@'192.168.%.%' identified by 'hnas_9800'; 在主库上授权,给从库分配账号和权限
(2)flush tablewith read unlock 设置写锁
(3)mysqldump-uroot -phnas_9800 -S /data/3306/mysql.sock -F -A -B --events --master-data=1--lock-all-tables >/opt/rep.sql(--lock-all-tables这个参数是用来锁表的)
(4)mysql -uroot-phnas_9800 -S /data/3307/mysql.sock </opt/rep.sql 将数据导入从库
(5)配置:(放在从库)
CHANGE MASTER TO
MASTER_HOST='192.168.44.110',
MASTER_PORT=3307,
MASTER_USER='rep',
MASTER_PASSWORD='hnas_9800',
MASTER_LOG_FILE='mysql.000002',
MASTER_LOG_POS=107; 在master-data=1加深的字体不需要打了
(6) start slave;
(7)show slavestatus\G 看到Slave_IO_Running: Yes Slave_SQL_Running: Yes就是成功
(8)读写分离
binlog_ignore_db mater端忽略bin日志
binlog_do_db master端记录
replicate-ignore-db slave端不记录
(9)排错
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1
配置:slave-skip-error=1032,1062,1007
skip-name-reslove 忽略名字解析
(10)备注:
master.info 是Slave_IO用来记录位置点的
relay-log.info是Slave_SQL用来读取位置点的
切换binlog 导致文件变化无影响,mysqldump -F 切换binlog文件。
show processlist 可以查看mysql 进程
若从库有SQL_error错误,可以使用set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;解决
四,一主多从切换实例
1,环境(多实例)
(1)192.168.44.1103306 master
(2)192.168.44.1103307 slave
(3)192.168.44.1103308 slave
2,模拟3306宕机场景
(1)sh mysql stop
(2)查看slave的状态:Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
3,切换的场景
(1)3307和3308上执行stop slave io_thread;
(2)showprocesslist;观察到Slave has read all relay log; waitingfor moreupdates即为从库更新完毕。
4,选择主库3307
(1)stop slave;
(2)删除master-info和slave-info,log-slaves-updates ,read-only=1都要注释掉
(3)reset master;
(4)show binarylogs;看到有最新的binlog
(5)grantreplication slave on *.* to 'rep'@'192.168.%.%' identified by 'hnas_9800'; 创建授权用户
5,3308上操作
(1)CHANGE MASTERTO
MASTER_HOST='192.168.44.110',
MASTER_PORT=3307,
MASTER_USER='rep',
MASTER_PASSWORD='hnas_9800',
MASTER_LOG_FILE='mysql.000002',
MASTER_LOG_POS=107;
(2)start slave
6,程序的ip需要更改,不同读写权限的用户也需要更改
7,修复3306后设置为从库,并设置读写分离
五,mysql误删数据后的恢复实例
一,场景,对数据库误操作,drop database
二,布骤
(1)每日做一次全量备份
mysqldump -uroot -phnas_9800 -S /data/3307/mysql.sock-F -A -B --events --master-data=2 >/opt/rep_$(date +%F).sql
(2)误删数据的操作发生前数据库已写入部分数据
insert into t1(name,sex,address)values('lfq','f','f');
insert into t1 (name,sex,address)values('zt','f','f');
insert into t1(name,sex,address)values('zsj','f','f');
(3)误删数据
drop database zjn_utf8;
(4)发现问题,立刻停库(锁表)
(5)刷新binlog,找到错误点。
mysqladmin -uroot -phnas_9800 -S /data/3307/mysql.sockflush-logs 刷新binlog 产生新的binlog文件。
mysqlbinlog mysql.000015 >bin.sql vim bin.sql 去掉错误的操作
( 6)恢复
mysql -u root -phnas_9800 -S /data/3307/mysql.sock<rep_2017-12-09.sql 恢复全备
mysql -u root -phnas_9800 -S /data/3307/mysql.sockzjn_utf8<bin.sql 恢复增量备份的日志