Mysql 备份与恢复

数据库备份的作用不言而喻，虽然不一定用的上，却还是必须要做的。

数据库备份可以从两个方向考虑备份，一个是直接备份存储后的数据文件 tar cp，称之为物理备份，这样做在数据回复时特别快直接挂载到数据目录即可，同时他拷贝数据文件时会很慢，而且不容易跨平台跨版本跨软件还不能分库分表，笨重适用于超过50G的大数据。常用的备份工具有xtrabackup（Innodb）， cp tar等 另一个则是备份数据库执行的SQL语句，恢复数据时，需要将sql语句在执行一边，这样做备份时会很快，恢复时就很慢，mysqldump，可以跨平台跨软件分库分表。

一、 mysqldump

- 1.1 全部备份 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock --all-databases #mysqldump实际上就是导出所有的sql语句 --add-databases 所有数据库 。。。。。。 LOCK TABLES `test2` WRITE; /*!40000 ALTER TABLE `test2` DISABLE KEYS */; /*!40000 ALTER TABLE `test2` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2017-05-07 10:32:45 。。。 - 1.2 分库分表备份 #使用-B 指定数据库名称 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock -B test_db #导出多个数据库 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock -B test_db test_db2 #导出表 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock test_db test #使用输出重定向保存sql语句 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock test_db test >./1.test_db-test.sql [root@Mysql_5 ~]# ll 1.test_db-test.sql -rw-r--r-- 1 root root 2145 May 7 12:56 1.test_db-test.sql [root@Mysql_5 ~]# cat 1.test_db-test.sql -- MySQL dump 10.13 Distrib 5.5.49, for Linux (x86_64) -- -- Host: localhost Database: test_db -- ------------------------------------------------------ -- Server version 5.5.49-log - 1.3 压缩保存 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock test_db test |gzip >./1.test_db-test.sql.gz [root@Mysql_5 ~]# ll 1.test_db-test.sql.gz -rw-r--r-- 1 root root 784 May 7 12:57 1.test_db-test.sql.gz [root@Mysql_5 ~]# 1.5 mysqldump 其他参数 -d 只备份库表结构 -T 分离库表和数据成不同的文件，数据是文本，非SQL语句 -A 备份所有库 -B 制定单个库

DATABASE TABLE 备份表

2.0 binlog mysql在写入数据到数据库时，同时会将sql语句记录到binlog中。

这个时间段丢失的数据就可以通过binlog恢复， 在mysqldump备份数据库时-F 刷新binlog ，这样就可以找到备份后的临界点，回复丢失的数据。

启动binlog记录日志

[root@Mysql_5 ~]# grep log-bin /data/3306/my.cnf log-bin = /data/3306/mysql-bin [root@Mysql_5 ~]# [root@Mysql_5 ~]# ll /data/3306/mysql-bin.* -rw-rw---- 1 mysql mysql 126 May 1 15:50 /data/3306/mysql-bin.000017 -rw-rw---- 1 mysql mysql 126 May 1 15:50 /data/3306/mysql-bin.000018 -rw-rw---- 1 mysql mysql 126 May 1 15:59 /data/3306/mysql-bin.000019 -rw-rw---- 1 mysql mysql 840 May 7 12:50 /data/3306/mysql-bin.index 备份数据中添加位置点信息 mysqldump -F 生成新的binlog文件，将来恢复时从这个文件开始 --master-data 在数据文件里添加CHANGE MASTER语句 binlog文件以及位置点信息。 =1 为可执行的CHANGE MASTER语句 =2 为注释的CHANGE MASTER语句 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock -F -B test_db --master-data=1 >test.sql [root@Mysql_5 ~]# sed -n '22p' test.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000046', MASTER_LOG_POS=107; [root@Mysql_5 ~]#

锁表 -x，–lock-all-table 加全局锁。myisam引擎，非事物引擎，混合引擎 -single-transaction 事务引擎innodb 备份，不用锁表 ，利用了事务的ACID 隔离性

1.4 生产场景不同引擎mysqldump备份命令

myisam引擎企业生产备份命令（适合所有引擎或混合引擎）: mysqldump -uroot -predhat -S /data/3306/mysql.sock -A -B -R –master-data=2 -x |gzip >/opt/alL__$(date +%F).sql.gz 提示：-F也可以不用，与–master-data有些重复。

innodb引擎企业生产备份命令:推荐使用的 mysqldump -uroot -predhat -S /data/3306/mysql.sock -A -B -R –master-data=2 –single-transaction |gzip >/opt/alL__$(date +%F).sql.gz

Mysql 恢复数据 将导出的数据库文件恢复到数据库中。 - mysql命令 [root@Mysql_5 ~]# mysqldump -S /data/3306/mysql.sock -uroot -predhat -B test_db >test_db.sql [root@Mysql_5 ~]# mysql -uroot -predhat -S /data/3307/mysql.sock <test_db.sql [root@Mysql_5 ~]# mysql -uroot -predhat -S /data/3307/mysql.sock -e "show databases;" # -e 非交互式执行sql语句。 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test_db | +--------------------+ [root@Mysql_5 ~]# #压缩恢复 [root@Mysql_5 ~]# mysqldump -S /data/3306/mysql.sock -uroot -predhat -B test_db |gzip >test_db.sql.gz [root@Mysql_5 ~]# zcat test_db.sql.gz |mysql -uroot -predhat -S /data/3307/mysql.sock [root@Mysql_5 ~]# mysql -uroot -predhat -S /data/3307/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test_db | +--------------------+ [root@Mysql_5 ~]# PS：如果全备了所有库但是只会恢复单个库时 可以将数据全部导入第三方数据库，dump出需要的库恢复。 如果dump时没有使用-B 指定库，而导入的mysql -server中没有要导入的db 需要先执行创建db 。 - source恢复 mysql> source test_db.sql; #这里是数据文件路径 这是相对路径 进入数据库前的位置。 使用source 恢复，和字符集关联很大 必须要一致，不然会乱码。 数据库---数据文件 查看线程状态 #查看完整的线程状态 mysql> show full processlist; +----+------+-----------+---------+---------+------+-------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+---------+---------+------+-------+-----------------------+ | 15 | root | localhost | test_db | Query | 0 | NULL | show full processlist | +----+------+-----------+---------+---------+------+-------+-----------------------+ 1 row in set (0.00 sec) #查看当前的线程状态 mysql> show processlist; +----+------+-----------+---------+---------+------+-------+-------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+---------+---------+------+-------+-------------------+ | 15 | root | localhost | test_db | Query | 0 | NULL | show processlist | +----+------+-----------+---------+---------+------+-------+-------------------+ 1 row in set (0.00 sec) mysql> #结束线程 mysql> kill 15;

mysql线程过多

mysql命令行 set global wait_timeout = 60; set global interactive_timeout = 60;

修改配置文件 [mysqld] interactive_timeout = 120 此参数设置后wait_timeout自动生效。 mysql的连接数的多少 wait_timeout = 120 每一个连接的超时时间

查看mysql变量及性能状态 mysql> show variables like '%slow%'; +---------------------+----------------------------------+ | Variable_name | Value | +---------------------+----------------------------------+ | log_slow_queries | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /data/3307/data/Mysql_5-slow.log | +---------------------+----------------------------------+ 4 rows in set (0.00 sec) mysql> mysql> show variables; +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | - 查看其他变量 mysql> show variables like '%server_id%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 3 rows in set (0.00 sec) mysql> - 不重启设置变量,重启生效 mysql> show variables like '%key_buffer%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | key_buffer_size | 16777216 | +-----------------+----------+ 1 row in set (0.00 sec) #当前生效 mysql> set global key_buffer_size = 1024*32; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%key_buffer%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | key_buffer_size | 32768 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> #下次重启生效 mysql> system grep key_buffer /data/3307/my.cnf key_buffer_size = 32K mysql> 写入配置文件 ; sql中 system 表示执行shell命令 数据库掌握命令 show processlist; 查看数据库正在执行的sql语句,可能无法看到完整的sql语句 show full processlist; 查看完整的正在执行的语句. kill ID; 杀死线程 show variables; 查看变量信息. show variables like ’ %…%’; 精确查看变量信息, % 通配符任意字符 set global key_buffer_size = 1024*32; 设置变量 当前生效 show session status; 查看当前会话的数据库状态信息. show global status; 查看全局的状态信息. show engine innodb status; 查看innodb 引擎的状态信息. show slave status; 查看主从复制的状态信息. /G;mysql 恢复练习 [root@localhost]_(none)>create database test; Query OK, 1 row affected (0.00 sec) [root@localhost]_(none)>use test; Database changed [root@localhost]_test> [root@localhost]_test>create table test2 (`id` int(4) NOT NULL ,`name` char(20) NOT NULL); Query OK, 0 rows affected (0.01 sec) [root@localhost]_test>insert into test.test2(id,name) values(1,'aa'); Query OK, 1 row affected (0.00 sec) [root@localhost]_test>insert into test.test2(id,name) values(2,'aaa'); Query OK, 1 row affected (0.00 sec) [root@localhost]_test>select * from test2; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | aaa | +----+------+ 2 rows in set (0.00 sec) [root@localhost]_test> [root@Mysql_5 ~]# mysqldump -S /data/3306/mysql.sock -uroot -predhat -B -R -F --single-transaction test |gzip >/tmp/$(date +%F).sql.gz [root@Mysql_5 ~]# ll /tmp/2017-05-08.sql.gz -rw-r--r-- 1 root root 743 May 8 09:14 /tmp/2017-05-08.sql.gz [root@Mysql_5 ~]# [root@localhost]_(none)>use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed [root@localhost]_test>insert into test.test2(id,name) values(3,'bb'); Query OK, 1 row affected (0.00 sec) [root@localhost]_test>insert into test.test2(id,name) values(4,'bbb'); Query OK, 1 row affected (0.00 sec) [root@localhost]_test>select * from test2; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | aaa | | 3 | bb | | 4 | bbb | +----+------+ 4 rows in set (0.00 sec) [root@localhost]_test> [root@localhost]_test>drop database test; Query OK, 1 row affected (0.01 sec) [root@localhost]_(none)> [root@localhost]_(none)>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) [root@Mysql_5 ~]# zcat /tmp/2017-05-08.sql.gz |my06 [root@Mysql_5 ~]# my06 -e "use test; select * from test2;" +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | aaa | +----+------+ [root@Mysql_5 ~]# #结合binlog 时间 和 备份时间找到刷新后的日志。 [root@Mysql_5 ~]# mysqlbinlog /data/3306/mysql-bin.000052 。。。。。。。。。。。 # at 107 [root@Mysql_5 ~]# mysqlbinlog /data/3306/mysql-bin.000052|grep drop drop database test [root@Mysql_5 ~]# [root@Mysql_5 ~]# sed -i '/drop/d' /tmp/1.sql [root@Mysql_5 ~]# grep 'drop' /tmp/1.sql [root@Mysql_5 ~]# [root@Mysql_5 ~]# my06 </tmp/1.sql [root@Mysql_5 ~]# my06 -e "select * from test.test2;" +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | aaa | | 3 | bb | | 4 | bbb | +----+------+ [root@Mysql_5 ~]# #这里是恢复的整个test库的数据，如果要恢复单个表 先导出test库的表结构到测试库，然后把1.sql恢复到新库，导出指定表的数据，恢复即可。

恢复bin log 也可根据出问题的时间进行恢复。 具体参考mysqlbinlog的其他参数。