mysql备份及增量数据恢复&参数相关命令

1.备份

1.1）备份的基本语句

mysqldump -u 用户名 -p 密码  数据库名>备份的文件名

#可见备份记录的内容为drop,create,insert这类SQL语句，实质上是逻辑备份，且做更改的时候需要锁表；插入的时候一个个插入，减少IO。

【补充】为保证备份内容不乱码，需保证客服端、服务端乃至Linux端的字符集

1.2）备份选项 -B

 备份添加-B即增加了建库和连库的过程，如果drop掉了database，则通过此方法才会自动建库（不需指定库名）

 1.3）备份选项 gzip

 对于备份，可以采取压缩备份，减少备份文件所占空间

 

1.4）备份多个库

备多个库可以直接将多个库列出来。对于mysqldump的更多参数，可以将help的内容打出来研究~：

1.5）分库备份的脚本实现

mysql -uroot -p456 -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -p456 --events -B 1|gzip >/opt/bak/1.sql.gz#g'|bash

#-E为不确认大小写

分库分表通过for循环实现（简易版）：

vi fenku.sh

1.6）备份指定库中的某个或某几个表

 备份单个表

备份多个表

分表备份缺点：文件多，很碎；因此建议如下：

1.备一个完整全备，再做一个分库分表备份

2.脚本批量恢复多个SQL文件

1.7）备份选项 -d  &  -t

备份数据库表结构               #由备份过程可以看到没有数据的存入

备份数据库数据

1.8）备份选项 -A

备份整个库的内容

1.9）备份选项 -F

在备份中切割binlog

【补充】如果做增量恢复时，不指定-F切割日志，可以通过--master-data重新定义全备日志开始备份的位置：

若--master-data=2 后面要加-x，保持数据一致      （此方法为基于myisam）   #mysqldump -uroot -p456 -A -B --master-data=2 -x --events|gzip >/opt/all.sql.gz

或--master-data=2 后面加--single-transaction        （此方法为基于innodb）   #mysqldump -uroot -p456 -A -B --master-data=2 --events --single-transaction|gzip >/opt/all.sql.gz（可以看到备份开始的文件和POS点，即恢复的点）

 -x :lock tables off

 -l :lock all tables for read

 --single-transaction ：适合innodb事务数据库备份

2.增量恢复

2.1）在未登陆mysql库的情况下进行增量恢复

2.2）在登陆mysql库后通过source命令恢复

2.3）分库备份恢复可以通过脚本实现

在bak目录下

gzip -d *

ls *.sql|sed 's#_bak.sql##g'

for dbname in 'ls *.sql|sed 's#_bak.sql##g'' ; do mysql -uroot -p456 < ${dbname}_bak.sql;done

3.mysql参数相关

3.1）查看连接到数据库的用户，也可以看到进程状态

mysql -uroot -p456 -e "show full processlist";

3.2）查配置（或没在配置文件中配置的参数）

3.3）查看全局状态（包括一些性能的统计等）

【example】

#等同于在mysql里执行 show global status like '%select%'，但与show variables like '%select%'不同

3.4）修改参数

如：更改索引缓存空间

在数据库里修改                                                                     # set global更改参数值，对于ON/OFF这种开关型参数不适用！

在my.cnf中更改，通过数据库重启生效                                   #在配置文件中修改后，数据库里也会自动更改

4.解析mysql binlog 增量备份

命令：mysqlbinlog

binlog的作用？用来记录mysql数据库的增、删、改、查等凡是造成数据库有更新的操作都会记录，而select 或show不会记录

用mysqlbinlog实现拆库恢复（用-d指定数据库）：

mysqlbinlog -d oldboy mysql-bin.000020 >oldboy.sql

less oldboy.sql

可以指定binlog中的起始位置进行恢复：

mysqlbinlog  mysqlbin.000020 --start-position=365 --stop-position=456 -r pos.sql

而不建议指定时间点，因为一秒钟有可能发生多个操作（--start-datetime）。

2018年10月30日

祝好！