MySQL阶段六——MySQL备份与增量备份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）对应更新操作，可能会操作两次，数据会乱；

解压备份时压缩的文件：

gzip -d 文件名;

恢复方法：

mysql -uroot -p*** < 备份文件完整路径

通过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

<code>BAKPATH=</code><code>/opt/dbbak</code>

<code>MYUSER=root</code>

<code>MYPASS=qb123</code>

<code>SOCKET=</code><code>/data/3306/mysql</code><code>.sock</code>

<code>MYCMD=</code><code>"mysql -u$MYUSER -p$MYPASS -S $SOCKET"</code>

<code>MYDUMP=</code><code>"mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -B -F -R"</code>

<code>[ ! -d $BAKPATH ] && </code><code>mkdir</code> <code>-p $BAKPATH</code>

<code>DBLIST=`$MYCMD -e </code><code>"show databases;"</code><code>|</code><code>sed</code> <code>1d|</code><code>egrep</code> <code>-</code><code>v</code> <code>"_schema|mysql"</code><code>`</code>

<code>for</code> <code>dbname </code><code>in</code> <code>$DBLIST</code>

<code> </code><code>$MYDUMP $dbname|</code><code>gzip</code> <code>>$BAKPATH/${dbname}_$(</code><code>date</code> <code>+%F).sql.gz</code>

<code>MYDUMP=</code><code>"mysqldump -u$MYUSER -p$MYPASS -S $SOCKET -x -F -R"</code>

<code> </code><code>TBLIST=`$MYCMD -e </code><code>"show tables from $dbname"</code><code>|</code><code>sed</code> <code>1d`</code>

<code> </code><code>for</code> <code>tbname </code><code>in</code> <code>$TBLIST</code>

<code> </code><code>[ ! -d $BAKPATH/${dbname} ] && </code><code>mkdir</code> <code>-p $BAKPATH/${dbname}</code>

<code> </code><code>$MYDUMP $dbname $tbname|</code><code>gzip</code> <code>>$BAKPATH/${dbname}/${dbname}_${tbname}_$(</code><code>date</code> <code>+%F).sql.gz</code>

通过防火墙禁止web等应用程序写数据或者锁表。让主库停止更新，然后进行恢复。

首先要做到常规全备和增量备份：

<code># mysqldump -uroot -pqb123 -S /data/3306/mysql.sock -F -B --master-data=2 mydb|gzip > /server/backup/bak_$(date +%F).sql.gz</code>

发生了删除数据库的操作：

进行检查增量备份，由于全备加了-F所以之后增量备份的数据在最后一个binlog中mysql-bin.000003

<code>[root@qbPC backup]</code><code># cd /data/3306/</code>

<code>data mysql mysql-bin.000002 mysql-bin.index mysql_qb3306.err</code>

<code>my.cnf mysql-bin.000001 mysql-bin.000003 mysqld.pid mysql.sock</code>

然后进行恢复：

<code>[root@qbPC backup]</code><code># gzip -d bak_2017-05-27.sql.gz</code>

<code>[root@qbPC 3306]</code><code># grep -i "change" /server/backup/bak_2017-05-27.sql</code>

<code>-- CHANGE MASTER TO MASTER_LOG_FILE=</code><code>'mysql-bin.000003'</code><code>, MASTER_LOG_POS=107;</code>

更新binlog。

<code>[root@qbPC 3306]</code><code># mysqladmin -uroot -pqb123 -S /data/3306/mysql.sock flush-logs</code>

<code>[root@qbPC 3306]</code><code># cp mysql-bin.000003 /server/backup/</code>

<code>/server/backup</code>

<code>[root@qbPC backup]</code><code># ls</code>

<code>bak_2017-05-27.sql bak.sql.gz mysql-bin.000003</code>

<code>[root@qbPC backup]</code><code># mysqlbinlog -d mydb mysql-bin.000003 > bin.sql</code>

<code>[root@qbPC backup]</code><code># vim bin.sql</code>

<code>进去删掉错误操作的sql语句。</code>

进行恢复

<code>[root@qbPC backup]</code><code># mysql -uroot -pqb123 -S /data/3306/mysql.sock <bak_2017-05-27.sql</code>

<code>[root@qbPC backup]</code><code># mysql -uroot -pqb123 -S /data/3306/mysql.sock mydb <bin.sql</code>

使用主从复制延迟方法！

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数据库

------------------------------------------------------------------------

1、mysql帮助说明

<code>[oldboy_c64 ~]</code><code># mysql --help|grep dummy </code>

<code> </code><code>-U, --i-am-a-dummy Synonym </code><code>for</code> <code>option --safe-updates, -U.</code>

<code>i-am-a-dummy FALSE</code>

在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序就会拒绝执行

2、指定-U登录测试

<code>[oldboy_c64 ~]</code><code># mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U</code>

<code>Welcome to the MySQL monitor. Commands end with ; or \g.</code>

<code>Your MySQL connection </code><code>id</code> <code>is 14</code>

<code>Server version: 5.5.32-log MySQL Community Server (GPL)</code>

<code>Oracle is a registered trademark of Oracle Corporation and</code><code>/or</code> <code>its</code>

<code>affiliates. Other names may be trademarks of their respective</code>

<code>owners.</code>

<code>Type </code><code>'help;'</code> <code>or </code><code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>to </code><code>clear</code> <code>the current input statement.</code>

<code>mysql> delete from oldboy.student;</code>

<code>ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column</code>

<code>mysql> quit</code>

提示：不加条件无法删除，目的达到。

3、做成别名防止老大和DBA误操作

<code>[oldboy_c64 ~]</code><code># alias mysql='mysql -U'</code>

<code>[oldboy_c64 ~]</code><code># mysql -uroot -poldboy123 -S /data/3306/mysql.sock</code>

<code>Your MySQL connection </code><code>id</code> <code>is 15</code>

<code>mysql> delete from oldboy.student where Sno=5;</code>

<code>Query OK, 1 row affected (0.02 sec)</code>

<code>[oldboy_c64 ~]</code><code># echo "alias mysql='mysql -U'" >>/etc/profile</code>

<code>[oldboy_c64 ~]</code><code># . /etc/profile</code>

<code>[oldboy_c64 ~]</code><code># tail -1 /etc/profile</code>

<code>alias</code> <code>mysql=</code><code>'mysql -U'</code>

结论：

在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行

本文转自叫我北北 51CTO博客，原文链接:http://blog.51cto.com/qinbin/1930364

MySQL阶段六——MySQL备份与增量备份MySQL备份与增量备份

继续阅读

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述