mysql备份及相关知识

一，二进制日志文件学习

(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 恢复增量备份的日志