MySQL备份与增量备份
01）MySQL备份常用参数
| -B |
1.会备份创建和删除库的语句；2.备份多个库； |
| --compact |
去注释，适合调试 |
| gzip |
压缩：# mysqldump -uroot -pqb123 -S /data/3306/mysql.sock -F -B --master-data=2 mydb|gzip > /server/backup/bak_$(date +%F).sql.gz |
| -A |
备份所有库 |
| -F |
刷新binlog日志 |
| --master-data=1/2 |
增加binlog日志文件及对应的位置点 当为2的时候表示在dump过程中记录主库的binlog和pos点，并在dump中注释这一行；为1表示不注释 在general_log中： flush tables with read lock(分为读锁和写锁) show master status; unlock tables;(如果是不锁表备份，使用了--single-transaction，那么在开启事务之后再解锁) |
| -l |
锁表 |
| -d |
备份表结构 |
| -t |
备份表数据 |
| --single-transaction |
适合innodb数据库引擎进行备份 设置这个参数后，整个dump过程使用的都是同一个连接id，这个连接id的作用是不影响其他连接 （set session transaction isolation level repeatable read） 这个参数的作用是，设置事务的隔离级别为可重复读，即repeatable read,这样就可以保证在一个事务中所有相同的查询读取到相同的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表数据并提交，对该dump线程的数据并没有影响。 (start transaction /*with consistent snapshot*/) 这是开启了一个事务，并设置with consistent snapshot（一致的快照），如果只是可重复读，那么在开启事务的时候，还没有dump数据时，这时其他线程修改了数据，那么这时第一次查询到的结果是其他线程提交后的结果，而with consistent snapshot能保证事务开启的时候，第一次查询的结果就是事务开启时的数据。 |
| --events |
记录数据库事件 |
myisam引擎压根不支持事务，所以不能用--single-transaction操作；myisam实现数据一致采用参数--lock-all-tables,这样在flush tables with read lock后，知道整个dump结束，才会unlock tables;
如果myisam强制使用--single-transaction，做备份和恢复，会导致：
01）对于基于某个时间的恢复，会导致数据重复；
02）对于建库建表，会报错；
03）对应更新操作，可能会操作两次，数据会乱；
02）MySQL恢复
解压备份时压缩的文件：
gzip -d 文件名;
恢复方法：
mysql -uroot -p*** < 备份文件完整路径
不登录sql进行操作的方法：
通过echo实现（这个比较常见）
echo "show databases;" | mysql -uroot -p'qb' -S /data/3308/mysql.sock
提示：此法适合单行字符串比较少的情况。
通过mysql -e参数实现
mysql -u root -p'oldboy' -S /data1/3307/mysql.sock -e "show databases;"
查看一些数据库信息总结：
| Show status |
查看当前状态的数据库状态信息 |
| Show global status |
查看整个数据库运行状态信息 |
| Show processlist |
查看正在执行的sql语句 |
| Show full processlist |
查看全部正在执行的完整sql语句 |
| Show global key_buffer_size=1 |
|
| Show variables; |
查看 数据库的参数信息 |
mysql -u root -p'oldboy' -S /data1/3307/mysql.sock -e "show full processlist;"|grep -v Sleep
03）分库备份脚本实现
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
#!/bin/bash
BAKPATH=
/opt/dbbak
MYUSER=root
MYPASS=qb123
SOCKET=
/data/3306/mysql
.sock
MYCMD=
"mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP=
"mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -B -F -R"
[ ! -d $BAKPATH ] &&
mkdir
-p $BAKPATH
DBLIST=`$MYCMD -e
"show databases;"
|
sed
1d|
egrep
-
v
"_schema|mysql"
`
for
dbname
in
$DBLIST
do
$MYDUMP $dbname|
gzip
>$BAKPATH/${dbname}_$(
date
+%F).sql.gz
done
|
04）分库分表备份脚本实现
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
#!/bin/bash
BAKPATH=
/opt/dbbak
MYUSER=root
MYPASS=qb123
SOCKET=
/data/3306/mysql
.sock
MYCMD=
"mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP=
"mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -F -R"
[ ! -d $BAKPATH ] &&
mkdir
-p $BAKPATH
DBLIST=`$MYCMD -e
"show databases;"
|
sed
1d|
egrep
-
v
"_schema|mysql"
`
for
dbname
in
$DBLIST
do
TBLIST=`$MYCMD -e
"show tables from $dbname"
|
sed
1d`
for
tbname
in
$TBLIST
do
[ ! -d $BAKPATH/${dbname} ] &&
mkdir
-p $BAKPATH/${dbname}
$MYDUMP $dbname $tbname|
gzip
>$BAKPATH/${dbname}/${dbname}_${tbname}_$(
date
+%F).sql.gz
done
done
|
05）当误删数据库时，恢复数据库操作（停库恢复）
通过防火墙禁止web等应用程序写数据或者锁表。让主库停止更新，然后进行恢复。
首先要做到常规全备和增量备份：
|
1
|
# mysqldump -uroot -pqb123 -S /data/3306/mysql.sock -F -B --master-data=2 mydb|gzip > /server/backup/bak_$(date +%F).sql.gz
|
发生了删除数据库的操作：
进行检查增量备份，由于全备加了-F所以之后增量备份的数据在最后一个binlog中mysql-bin.000003
|
1
2
3
4
|
[root@qbPC backup]
# cd /data/3306/
[root@qbPC 3306]
# ls
data mysql mysql-bin.000002 mysql-bin.index mysql_qb3306.err
my.cnf mysql-bin.000001 mysql-bin.000003 mysqld.pid mysql.sock
|
然后进行恢复：
|
1
2
3
|
[root@qbPC backup]
# gzip -d bak_2017-05-27.sql.gz
[root@qbPC 3306]
# grep -i "change" /server/backup/bak_2017-05-27.sql
-- CHANGE MASTER TO MASTER_LOG_FILE=
'mysql-bin.000003'
, MASTER_LOG_POS=107;
|
更新binlog。
|
1
2
3
4
5
6
7
8
9
|
[root@qbPC 3306]
# mysqladmin -uroot -pqb123 -S /data/3306/mysql.sock flush-logs
[root@qbPC 3306]
# cp mysql-bin.000003 /server/backup/
[root@qbPC 3306]
# cd -
/server/backup
[root@qbPC backup]
# ls
bak_2017-05-27.sql bak.sql.gz mysql-bin.000003
[root@qbPC backup]
# mysqlbinlog -d mydb mysql-bin.000003 > bin.sql
[root@qbPC backup]
# vim bin.sql
进去删掉错误操作的sql语句。
|
进行恢复
|
1
2
3
|
[root@qbPC backup]
# mysql -uroot -pqb123 -S /data/3306/mysql.sock <bak_2017-05-27.sql
[root@qbPC backup]
# mysql -uroot -pqb123 -S /data/3306/mysql.sock mydb <bin.sql
完成！
|
06）被误删除数据库，不停库恢复
使用主从复制延迟方法！
07）被误删除数据库，不停库恢复
l 停止一个从库，然后主库刷新binlog，把增量备份mysql-bin.000003的数据恢复成bin.sql（去掉drop语句）；
l 把全备以及增量恢复到从库；
l 刷新以后的binlog数据mysql-bin.000004；
l 这个时候停止主库，快速把刷新以后到binlog数据恢复到从库；然后切换从库为主库提供服务。
l 问题：当不停主库，将从库快速切换到主库的时候，就可能有crud操作，这个时候将主库之前刷新的数据mysql-bin.000004恢复到现在的主库的时候，可能会有不可预知的问题；最好的方案是将现在主库停一下，快速恢复数据然后开启服务，尽可能少停止主库。
08) 解析MySQL的binlog日志
l 日志作用：用来记录mysql内部对数据库更新内容的操作记录（不会记录select语句）
l Binlog拆库： mysqlbinlog -d mydb mysql-bin.000003 > bin.sql（-d指定拆的库名）
l 指定时间和位置点恢复：mysqlbinlog mysql-bin.000001 --start-position=356 --stop-position=456 -r pos.sql
（指定时间一般不准确）
l Mysqlbinlog不能使用：
原因：my.cnf配置文件[client]下面配置了default-character-set=utf8，这是mysql的一个bug；
解决：变成loose-default-character-set=utf8或者将其注释。（修改my.cnf之后不需要重启服务，因为mysqlbinlog是从磁盘上读my.cnf，不是从内存读）
09）防止人为误操作MySQL数据库
参考http://oldboy.blog.51cto.com/2561410/1321061
------------------------------------------------------------------------
1、mysql帮助说明
|
1
2
3
|
[oldboy_c64 ~]
# mysql --help|grep dummy
-U, --i-am-a-dummy Synonym
for
option --safe-updates, -U.
i-am-a-dummy FALSE
|
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序就会拒绝执行
2、指定-U登录测试
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[oldboy_c64 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 14
Server version: 5.5.32-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and
/or
its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and
/or
its
affiliates. Other names may be trademarks of their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> delete from oldboy.student;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> quit
Bye
|
提示：不加条件无法删除，目的达到。
3、做成别名防止老大和DBA误操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[oldboy_c64 ~]
# alias mysql='mysql -U'
[oldboy_c64 ~]
# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection
id
is 15
Server version: 5.5.32-log MySQL Community Server (GPL)
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
mysql> delete from oldboy.student;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> delete from oldboy.student where Sno=5;
Query OK, 1 row affected (0.02 sec)
mysql> quit
Bye
[oldboy_c64 ~]
# echo "alias mysql='mysql -U'" >>/etc/profile
[oldboy_c64 ~]
# . /etc/profile
[oldboy_c64 ~]
# tail -1 /etc/profile
alias
mysql=
'mysql -U'
|
结论：
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行
