7.mysql数据库增量备份与恢复

===========================
1.备份数据意义及重要性
运维工作做两件事：
（1）保护公司的数据；
（2）给网站7*24小时提供服务。
访问量大而架构小要宕机；做单点、做集群是为网站7*24小时提供可靠服务。
对于数据来讲，最核心是数据库数据，当然也包括其他数据。对于绝大多数企业来讲：
失去数据就失去商机，失去产品，失去客户，公司倒闭......

===========================
2.mysqldump备份单个数据库及其参数使用
语法：mysqldump -u 用户名 -p 数据库名> 备份的文件名
（1）数据库备份命令mysqldump
mysql> use oldboy //备份数据库oldboy
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| test |
+------------------+
1 row in set (0.00 sec)

mysql> select * from oldboy.test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 老男孩 |
| 7 | 老男孩 |
| 8 | 老女孩 |
+----+-----------+
8 rows in set (0.08 sec)
mysql> quit
Bye
法一：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 oldboy -S /data/3306/mysql.sock >/opt/mysql_bak.sql //备份数据库oldboy
法二：设置字符集后备份--default-character-set=utf8
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --default-character-set=utf8 oldboy >/opt/mysql_bak.sql
[root@oldboyedu-01 ~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql //解读备份的数据库
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'meng'),(5,'ming'),(6,'老男孩'),(7,'老男孩'),(8,'老女孩');//批量插入，效率很高。
UNLOCK TABLES;
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show variables like '%character%'" //查看字符集
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
测试恢复数据库oldboy：
mysql数据库导入就相当于是执行sql语句的过程。
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "use oldboy;drop table test;"
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy </opt/mysql_bak.sql
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "select * from oldboy.test;"
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 老男孩 |
| 7 | 老男孩 |
| 8 | 老女孩 |
+----+-----------+

（2）参数-B作用是增加创建数据库和连接数据库的命令，还原时就不用再创建库了。
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy>/opt/mysql_bak_B.sql
[root@oldboyedu-01 opt]# egrep -v "#|\*|--|^$" /opt/mysql_bak_B.sql
USE `oldboy`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'meng'),(5,'ming'),(6,'老男孩'),(7,'老男孩'),(8,'老女孩');
UNLOCK TABLES;

[root@oldboyedu-01 ~]# cd /opt/
[root@oldboyedu-01 opt]# diff mysql_bak.sql mysql_bak_B.sql
18a19,26
> -- Current Database: `oldboy`
> --
>
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;
>
> USE `oldboy`;
>
> --
51c59
< -- Dump completed on 2018-01-13 22:06:42
---
> -- Dump completed on 2018-01-13 22:16:12

[root@oldboyedu-01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "drop database oldboy;"
[root@oldboyedu-01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| d3306 |
| mysql |
| oldboy_gbk |
| oldboy_utf8 |
| performance_schema |
[root@oldboyedu-01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock </opt/mysql_bak_B.sql
[root@oldboyedu-01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| d3306 |
| mysql |
| oldboy |
[root@oldboyedu-01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "select * from oldboy.test;"
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | meng |
| 5 | ming |
| 6 | 老男孩 |
| 7 | 老男孩 |
| 8 | 老女孩 |
+----+-----------+

（3）参数-compact作用是可以优化输出内容的大小，让容量更少，适合调试！多用于测试！
[root@oldboyedu-01 opt]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --compact -B oldboy>/opt/mysql_bak_B_compact.sql
[root@oldboyedu-01 opt]# less /opt/mysql_bak_B_compact.sql

（4）指定压缩命令压缩备份的mysql数据库
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy|gzip >/opt/mysql_bak.sql.gz
-rw-r--r-- 1 root root 1960 Jan 13 22:06 /opt/mysql_bak.sql
-rw-r--r-- 1 root root 821 Jan 13 22:36 /opt/mysql_bak.sql.gz

===========================
3.mysqldump逻辑备份的工作原理
（1）利用mysqldump命令备份数据的过程，实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出或者生成备份的文件的过程。
（2）备份多个数据库及多个参数
参数-B表示接多个库并且增加use db和create database db的信息。
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy oldboy_gbk|gzip >/opt/mysqlmul_bak.sql.gz

===========================
4.备份多个数据库的思想及多种方法
分库备份：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"|egrep -iv "database|infor|perfor"
d3306
mysql
oldboy
oldboy_gbk
oldboy_utf8
test
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"|egrep -iv "database|infor|perfor"|sed 's#^#mysqldump -uroot -poldboy123 -B #g'
mysqldump -uroot -poldboy123 -B d3306
mysqldump -uroot -poldboy123 -B mysql
mysqldump -uroot -poldboy123 -B oldboy
mysqldump -uroot -poldboy123 -B oldboy_gbk
mysqldump -uroot -poldboy123 -B oldboy_utf8
mysqldump -uroot -poldboy123 -B test
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"|grep -Eiv "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -poldboy123 -B \1 >/opt/\1.sql#g'
mysqldump -uroot -poldboy123 -B d3306 >/opt/d3306.sql
mysqldump -uroot -poldboy123 -B mysql >/opt/mysql.sql
mysqldump -uroot -poldboy123 -B oldboy >/opt/oldboy.sql
mysqldump -uroot -poldboy123 -B oldboy_gbk >/opt/oldboy_gbk.sql
mysqldump -uroot -poldboy123 -B oldboy_utf8 >/opt/oldboy_utf8.sql
mysqldump -uroot -poldboy123 -B test >/opt/test.sql
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"|grep -Eiv "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -poldboy123 -B \1 >/opt/\1.sql.gz#g'|bash
或者：
[root@oldboyedu-01 ~]# mkdir -p /opt/bak
法一：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"|grep -Eiv "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -poldboy123 --events -B \1|gzip >/opt/bak/\1.sql.gz#g'|bash

法二：
[root@oldboyedu-01 ~]# cat /service/script/fenku.sh
for dbname in `mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"|grep -Eiv "database|infor|perfor"`
do
mysqldump -uroot -poldboy123 --events -B $dbname|gzip >/opt/bak/${dbname}_bak.sql.gz
done
[root@oldboyedu-01 ~]# sh /service/script/fenku.sh

企业级MySQL分库分表备份策略以及实战操作精讲视频课程
http://edu.51cto.com/course/808.html

（3）分库备份的意义：
有时一个企业的数据库里会有多个库（例如：www.bbs.blog），但是出问题时候可能是某个库，如果在备份时把所有库都备份成一个数据库文件的话，恢复某一个的数据库时就比较麻烦了。

===========================
5.mysql单多表的备份实践及脚本批量备份
（1）备份单个表
语法：mysqldump -u用户名 -p密码数据库名数据表名备份的文件名
mysql> use oldboy
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| test |
+------------------+
1 row in set (0.00 sec)

mysql> create table test1(id int);
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| test |
| test1 |
+------------------+
2 rows in set (0.00 sec)
测试查看：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --compact oldboy
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --compact oldboy test test1

[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --compact oldboy test

（2）备份多个表
语法：mysqldump -u用户名 -p密码数据库名表名1 表名2 备份的文件名

（3）分表备份
和分库思想一样，每执行一条语句备份一个表，生成不同的数据文件即可。
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy test>/opt/oldboy_test.sql
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy test1>/opt/oldboy_test1.sql

缺点：文件多，碎。
a.备一个完整全备，再做一个分库分表备份；
b.脚本批量恢复多个sql文件。

===========================
6.只备份mysql表结构及只备份mysql数据
（1）只备份mysql表结构加参数-d
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --compact -d oldboy //两个表，无数据。
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

（2）只备份mysql表数据加参数-t
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --compact -t oldboy
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'meng'),(5,'ming'),(6,'老男孩'),(7,'老男孩'),(8,'老女孩');

（3）参数-B指定多个库，-A指定备份所有库
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events|gzip >/opt/a.sql.gz

（4）参数-F作用：属刷新binlog参数，自动切割binlog。
/data/3306/my.cnf 中：
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysqlbin_oldboy //mysql的增量恢复
[root@oldboyedu-01 ~]# ll /data/3306/
mysqlbin_oldboy.000009 ......
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B -F --events|gzip >/opt/a.sql.gz
[root@oldboyedu-01 ~]# ll /data/3306/
mysqlbin_oldboy.000015......
注意：此时mysqldump备份的是包括mysqlbin_oldboy.000009及其之前的数据，备份里已经有了。备份时注意一个位置点！
mysqlbin_oldboy.000010到mysqlbin_oldboy.000015才是我们需要增量备份的数据。

（5）参数--master-data=1作用：增加binlog日志文件名及对应的位置点。
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --master-data=1 --compact oldboy
CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin_oldboy.000015', MASTER_LOG_POS=107;

[root@oldboyedu-01 ~]# mysqlbinlog /data/3306/mysqlbin_oldboy.000001
//可查看，位置点就是当时的文件大小

-x,--lock-all-tables 锁表
-l,--lock-tables 只读锁表
--single-transaction 适合innodb事务数据库备份。
InnoDB表在备份时通常启用选项--single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ以确保本次会话（dump）时不会看到其他会话已经提交了数据。
a.innodb备份命令：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --master-data=2 --events --single-transaction|gzip >/opt/all.sql.gz
b.myisam备份命令：
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --master-data=2 --events -x|gzip >/opt/all.sql.gz

分享：mysqldump的几个主要选项探究
http://imysql.cn/2008_10_24_deep_into_mysqldump_options

===========================
7.企业生产场景不同引擎备份命令

===========================
8.利用source恢复mysql数据实践
（1）利用source恢复mysql数据库需要登录数据库
mysql> show databases;
mysql> drop database oldboy;
Query OK, 2 rows affected (0.16 sec)

===========================
9.分库备份后mysql如何分库恢复实践
（1）
[root@oldboyedu-01 opt]# gzip -d mysql_bak.sql.gz //解压
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy </opt/mysql_bak.sql //恢复

（2）分库分表备份的数据如何恢复？
可以通过脚本读指定的库和表，调用mysql命令恢复。
[root@oldboyedu-01 bak]# ll
total 24
-rw-r--r-- 1 root root 20 Jan 13 23:52 d3306_bak.sql.gz
-rw-r--r-- 1 root root 20 Jan 13 23:52 mysql_bak.sql.gz
-rw-r--r-- 1 root root 20 Jan 13 23:52 oldboy_bak.sql.gz
-rw-r--r-- 1 root root 20 Jan 13 23:52 oldboy_gbk_bak.sql.gz
-rw-r--r-- 1 root root 20 Jan 13 23:52 oldboy_utf8_bak.sql.gz
-rw-r--r-- 1 root root 20 Jan 13 23:52 test_bak.sql.gz
[root@oldboyedu-01 bak]# gzip -d *
[root@oldboyedu-01 bak]# ll
total 0
-rw-r--r-- 1 root root 0 Jan 13 23:52 d3306_bak.sql
-rw-r--r-- 1 root root 0 Jan 13 23:52 mysql_bak.sql
-rw-r--r-- 1 root root 0 Jan 13 23:52 oldboy_bak.sql
-rw-r--r-- 1 root root 0 Jan 13 23:52 oldboy_gbk_bak.sql
-rw-r--r-- 1 root root 0 Jan 13 23:52 oldboy_utf8_bak.sql
-rw-r--r-- 1 root root 0 Jan 13 23:52 test_bak.sql
[root@oldboyedu-01 bak]# ls *.sql|sed 's#_bak.sql##g'
d3306
mysql
oldboy
oldboy_gbk
oldboy_utf8
test
[root@oldboyedu-01 bak]# for dbname in `ls *.sql|sed 's#_bak.sql##g'`;do mysql -uroot -poldboy123 -S /data/3306/mysql.sock <${dbname}_bak.sql;done
[root@oldboyedu-01 bak]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show databases;"

（3）查看数据库的参数信息之二进制日志是否开启
法一：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show variables;"|grep log_bin
log_bin ON
log_bin_trust_function_creators OFF
sql_log_bin ON
法二：
[root@oldboyedu-01 ~]# grep log-bin /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysqlbin_oldboy

（4）查看当前会话的数据库状态信息
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show status;"

（5）查看在整个数据库运行状态信息
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show global status;"
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show global status;"|grep select
Com_insert_select 0
Com_replace_select 0
Com_select 463 //统计select执行次数

（4）不重启数据库修改数据库参数，重启后还能生效！
设置该缓冲区大小：
[root@oldboyedu-01 ~]# grep "key_buffer" /data/3306/my.cnf
key_buffer_size = 16M MYISAM引擎存放索引的缓冲区
mysql> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.15 sec)

mysql> show variables like 'key_buffer%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)
接着修改配置文件：
[root@oldboyedu-01 ~]# grep "key_buffer" /data/3306/my.cnf
key_buffer_size = 32M

===========================
11.mysqlbinlog命令介绍及实践
（1）mysqlbinlog命令：
mysqlbinlog-->解析mysql的binlog日志
（2）mysql的binlog日志：
[root@oldboyedu-01 3306]# cat mysqlbin_oldboy.index
/data/3306/mysqlbin_oldboy.000001
/data/3306/mysqlbin_oldboy.000002
......
/data/3306/mysqlbin_oldboy.000015
（3）mysql的binlog日志作用：
用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录。
会记录所有库、表的变化。

数据恢复实际上是分为两部分：
a.全备；
b.全备到出问题的时刻的增量。
两者都恢复，数据才是完整的！

<1>所有库备份：
[root@oldboyedu-01 3306]# mysqlbinlog mysqlbin_oldboy.000015 >/opt/all.sql

<2>拆库备份/-d参数截取指定库的binlog：
[root@oldboyedu-01 3306]# mysqlbinlog -d oldboy mysqlbin_oldboy.000015 >/opt/oldboy.sql //众多库中拆出oldboy库进行备份。
[root@oldboyedu-01 opt]# vimdiff all.sql oldboy.sql //对比！
[root@oldboyedu-01 opt]# mysqlbinlog --help

a.指定开始位置和结束位置：
[root@oldboyedu-01 3306]# mysqlbinlog mysqlbin_oldboy.000015 --start-position=3057 --stop-position=3162 -r /opt/pos.sql
[root@oldboyedu-01 3306]# less /opt/pos.sql

b.指定开始时间和结束时间：
[root@oldboyedu-01 3306]# mysqlbinlog mysqlbin_oldboy.000015 --start-datetime='2018-01-14 15:56:23' --stop-datetime='2018-01-14 15:56:38' -r /opt/time.sql
[root@oldboyedu-01 3306]# grep ‘insert’ /opt/time.sql
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
insert into test(name) values('baodao')

开启binlog功能：
[root@oldboyedu-01 ~]# grep 'log-bin' /data/3306/my.cnf
#log-bin = /data/3306/mysql-bin
log-bin = /data/3306/mysqlbin_oldboy

总结：
mysql
mysqladmin
mysqldump
mysqlbinlog

案例：某电商网站数据库特大故障解决
http://blog.51cto.com/oldboy/1431161

===========================
12.mysqldump之--master-data参数
说明：mysql语句文件中--表示注释。
（1）参数--master-data=2
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --master-data=2 --events oldboy test

-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin_oldboy.000015', MASTER_LOG_POS=3189;

（2）参数--master-data=1
[root@oldboyedu-01 ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock --master-data=1 --events oldboy test

CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin_oldboy.000015', MASTER_LOG_POS=3189;
//告知从库从何恢复执行。

posted @ 2018-01-25 19:03 bkycrmn 阅读(156) 评论(0) 收藏举报

刷新页面返回顶部

7.mysql数据库增量备份与恢复

公告