备案控制台
探索云世界
开发者社区 数据库 文章 正文

Mysqldump5.6的新特性

2017-11-08 1079
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介：

一、Mysqldump备份恢复案列

1、Mysqldump命令的使用介绍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# mysqldump --help
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
-u                   #指定用户名
-p                   #指定密码
-h                   #指定主机地址
-S                   #指定socket文件
--flush-logs             #执行日志flush滚动
--lock-all-tables    #锁定所有表
--master-data            #该选项将会记录binlog的日志位置与文件名并追加到文件中，如果为1将                          会输出CHANGE MASTER命令，主从下有用
--triggers       #备份触发器的
--events         #备份数据库的事件调度器的
--routines       #备份存储过程和函数的
--single-transaction     #如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备；--single-transaction不要和--lock-all-tables一起使用，因为--single-transaction可以实现热备,会自动锁表和刷新日志。

2、故障模拟测试

(1)首先在数据库进行一些创建库和表的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> create database Allentuns;
Query OK, 1 row affected (0.00 sec)
 
mysql> use Allentuns;
Database changed
mysql> create table tab1( id  int,name char(20),age int);
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into tab1 values(1, 'jerry' ,24);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into tab1 values(2, 'kimi' ,12);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tab1 values(3, 'jay' ,35);
Query OK, 1 row affected (0.01 sec)
 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000023 |     3440 |              |                  | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-140 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row  in  set  (0.00 sec)

(2)对操作的数据库进行备份

1
2
# mkdir /bak
# mysqldump -uroot -p -h 127.0.0.1 --databases Allentuns --events --triggers --master-data=2 --flush-logs --lock-all-tables > /bak/Allentuns_full_`date +%F`.sql

(3)向Allentuns数据库中添加数据来模拟第一次增量备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> use Allentuns
Database changed
mysql> create table tab2 like tab1;
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into tab2 values(10, 'java' ,20);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into tab2 values(11, 'python' ,40);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tab2 values(13, 'php' ,10);
Query OK, 1 row affected (0.00 sec)
 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000024 |     1306 |              |                  | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-144 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row  in  set  (0.00 sec)

(4)为了使模拟的效果更接近于生产环境，在这里我们滚动二进制日志，使产生的数据记录信息保存在不同的二进制日志当中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> use Allentuns
Database changed
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into tab2 values(14, 'C++' ,14);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tab2 values(15, 'ruby' ,15);
Query OK, 1 row affected (0.01 sec)
 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000025 |      833 |              |                  | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-146 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row  in  set  (0.00 sec)

(5)将二进制日志中产生的新记录信息输出到sql脚本当中

1
2
3
4
5
6
7
8
9
10
11
12
13
# cat /bak/Allentuns_full_2014-11-30.sql |grep "mysql-bin"
-- CHANGE MASTER TO MASTER_LOG_FILE= 'mysql-bin.000024' , MASTER_LOG_POS=191;
{以上是对Allentuns数据库做全备那一时刻对应的二进制文件和所处的位置}
# mysql -uroot -p -e "show master status;"
Enter password: 
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000025 |      833 |              |                  | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-146 |
+------------------+----------+--------------+------------------+--------------------------------------------+
{以上是第一次做增量备份后后那一时刻对应的二进制文件和所处的位置}
# cd /mydata/data
# mysqlbinlog --skip-gtids --start-position=191 mysql-bin.000024 mysql-bin.000025 > /bak/incremental_`date +%F-%H-%M-%S`.sql

(6)向Allentuns数据库中添加数据来模拟第二次增量备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> use Allentuns;
Database changed
mysql> create table tab3 like tab1;
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into tab3 values(21, 'zhengyansheng' ,24);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tab3 values(22, 'wangtianyu' ,22);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tab3 values(23, 'zhengziyu' ,20);
Query OK, 1 row affected (0.01 sec)
 
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000025 |     1986 |              |                  | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-150 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row  in  set  (0.00 sec)

(7)删除Allentuns库来模拟发生故障

1
2
3
4
5
6
7
8
9
10
mysql> drop database Allentuns;
Query OK, 3 rows affected (0.02 sec)
 
mysql> show master status;     #删除Allentuns数据库时没有滚动日志，那么使用“drop”的命令就记录在这个二进制日志当中
+------------------+----------+--------------+------------------+--------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                          |
+------------------+----------+--------------+------------------+--------------------------------------------+
| mysql-bin.000025 |     2141 |              |                  | aa9cd47a-77b1-11e4-94b2-000c299bb5af:1-151 |
+------------------+----------+--------------+------------------+--------------------------------------------+
1 row  in  set  (0.00 sec)

(8)查看删除Allentuns数据库对应的二进制日志文件，并找到删除之前的那个点的Position

1
2
3
4
5
6
# mysqlbinlog /mydata/data/mysql-bin.000025
----------------
# at 2034 #这个就是我们要的值2034
#141130 20:44:35 server id 1  end_log_pos 2141 CRC32 0x23d014a1   Query   thread_id=12    exec_time=0 error_code=0
SET TIMESTAMP=1417351475/*!*/;
drop database Allentuns

(9)通过mysqlbinlog导出二进制日志在drop之前的sql脚本

1
# mysqlbinlog  --skip-gtids --start-position=833 --stop-position=2034 /mydata/data/mysql-bin.000025 > /bak/incremental_2034.sql

(10)开始做数据库恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql>  set  sql_log_bin=0;
mysql>  source  /bak/Allentuns_full_2014-11-30 .sql;
mysql>  source  /bak/incremental_2014-11-30-20-42-25 .sql;
mysql>  source  /bak/incremental_2034 .sql;
mysql> use Allentuns;
Database changed
mysql> show tables;
+---------------------+
| Tables_in_Allentuns |
+---------------------+
| tab1                |
| tab2                |
| tab3                |
+---------------------+
3 rows  in  set  (0.00 sec)
 
mysql>  select  * from tab1;
+------+-------+------+
|  id    | name  | age  |
+------+-------+------+
|    1 | jerry |   24 |
|    2 | kimi  |   12 |
|    3 | jay   |   35 |
+------+-------+------+
3 rows  in  set  (0.00 sec)
 
mysql>  select  * from tab2;
+------+--------+------+
|  id    | name   | age  |
+------+--------+------+
|   10 | java   |   20 |
|   11 | python |   40 |
|   13 | php    |   10 |
|   14 | C++    |   14 |
|   15 | ruby   |   15 |
+------+--------+------+
5 rows  in  set  (0.00 sec)
 
mysql>  select  * from tab3;
+------+---------------+------+
|  id    | name          | age  |
+------+---------------+------+
|   21 | zhengyansheng |   24 |
|   22 | wangtianyu    |   22 |
|   23 | zhengziyu     |   20 |
+------+---------------+------+
3 rows  in  set  (0.00 sec)

二、Mysql5.6 新特性1

最近在群上讨论Mysql5.6的话题越来越多了，自从Oracle收购Mysql之后；Oracle对Mysql的存储引擎做了很大的改进，在Mysql5.5之前Mysql的默认存储引擎MyISAM，这种存储引擎不支持事物，现在5.6版本默认的存储引擎就是InnoDB，此版本并引入了GTID特性，也就是GTID的特性让我在上面花了一天的时间

在这个难题上，最后去看官方文档才得到答案。

首先来看一个错误截图，这个错误时由于用mysqlbinlog做二进制日志导出后，查看库中的表信息的时候报的一个错误，如下图所示：

wKioL1R7G9rzlrQeAAJhzgWngas162.jpg我们来看官方给出的解决思路和方法

wKiom1R7G72B4i0yAAUpHRr_ZpM500.jpg

三、Mysql5.6分库分表备份脚本的新特性2

1、Mysql基于mysqldump的分库备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#!/bin/bash
#Author:Allentuns
#Tel:13260071987
 
db_user= 'root'
db_passwd=123456
db_host=192.168.0.104
db_none= "information_schema|mysql|performance_schema"
db_command=`mysql -u$db_user -p$db_passwd -h $db_host -e  "show databases;"  | sed  '1d'  | egrep  - v  $db_none`
db_dump= "mysqldump -u$db_user -p$db_passwd -h $db_host --master-data=2 --flush-logs --lock-all-tables"
db_file= '/mydata/mysqlbak'
 
if  [ ! -d $db_file ]; then
         mkdir  -p $db_file
fi
 
for  i  in  $db_command;
do
         $db_dump $i | gzip  > $db_file/${i}_$( date  +%F-%H-%M-%S).sql.gz
done


2、Mysql基于mysqldump的分表备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#!/bin/bash
#Author:Allentuns
#Tel:13260071987
 
db_user=root
db_passwd=123456
db_host=localhost
db_none= "information_schema|mysql|performance_schema"
db_connect= "mysql -u$db_user -p$db_passwd -h $db_host"
db_command=`mysql -u$db_user -p$db_passwd -h $db_host -e  "show databases;"  | sed  '1d'  | egrep  - v  $db_none`
db_dump= "mysqldump -u$db_user -p$db_passwd -h $db_host --master-data=2 --flush-logs --lock-all-tables"
db_file= '/mydata/mysqlbak'
 
if  [ ! -d $db_file ]; then
         mkdir  -p $db_file
fi
 
for  db  in  $db_command
do
         if  [ ! -d $db_file/$db ]; then
                 mkdir  -p $db_file/$db
         fi
         for  table  in  `$db_connect -e  "show tables from $db;" | sed  '1d' `
         do
                 $db_dump $db $table | gzip  > $db_file/${db}/${table}_$( date  +%F-%H-%M-%S).sql.gz
         done
done

3、在命令行或脚本中如果传递密码就会报一下警告信息

wKioL1R7HZCAxlTOAACRPYn_Hsg729.jpg

这个也是版本5.6的新特性，如果以明文方式显示密码都会报警告信息的，解决办法就是将其加入到客户端的配置文件中，然后不用输入账户和密码了，这样就能避免发出警告信息。

解决办法来源互联网：http://www.68idc.cn/help/jiabenmake/qita/2014010766686.html

1
2
3
4
5
6
cat  > ~/.my.cnf << EOF
[mysqldump]
user=root
password=123456
host=127.0.0.1
EOF

四、补充一点

1、lvm逻辑卷

逻辑卷管理器lvm，想必大家一点都不陌生吧！几乎绝大多数我们都会把数据库的数据文件放在lvm卷上，1是为了保证磁盘可以在线动态的扩容，2是为了利用lvm的快照功能可以实现对Mysql数据库做热备，我会在后续博客补充和案列演示。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
创建逻辑卷
1.首先将磁盘创建成逻辑卷
# fdisk /dev/sdb
/dev/sdb1                1        2610    20964793+  8e  Linux LVM
2.将分区转换成pv物理卷
# pvcreate /dev/sdb1      #将分区/dev/sdb1转换成物理卷
# pvs                      #查看物理卷信息
3.将pv物理卷加入到vg卷组
# vgcreate myvg /dev/sdb1    #将物理卷加入到卷组中
# vgs                      #查看卷组信息
4.从卷组vg中划分出lv逻辑卷
# lvcreate -n mydata -L 10G myvg
# lvs
5.格式化lv逻辑卷
# mkfs.ext4 /dev/myvg/mydata
6.创建挂载目录并赋予mysql权限
# mkdir -p /mydata/data
# chown -R mysql.mysql /mydata/data/
7.开机自动挂载
# echo "/dev/myvg/mydata    /mydata/data   ext4    defaults        0 0" >> /etc/fstab
# mount -a
8.查看挂载信息
# mount

************2014-12-12补充***************

场景：在用mysqldump对测试数据做备份的时候，出来这样的提示

wKioL1SKnw_hgzzjAAHAV0ePoTg142.jpg


解决办法：

1
2
3
# vim /etc/my.cnf 
[mysqldump]
set -gtid-purged=OFF





     本文转自zys467754239 51CTO博客，原文链接：http://blog.51cto.com/467754239/1584844，如需转载请自行联系原作者




文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
数据库
SQL
监控
MySQL
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
技术小胖子
目录
相关文章
wyyyyyk
|
10月前
|
存储 SQL JSON
18_MySQL8其它新特性
18_MySQL8其它新特性
wyyyyyk
42 0
Cisyam
|
10月前
|
关系型数据库 MySQL
第18章_MySQL8其它新特性（下）
第18章_MySQL8其它新特性
Cisyam
60 0
Cisyam
|
10月前
|
存储 SQL JSON
第18章_MySQL8其它新特性（上）
第18章_MySQL8其它新特性
Cisyam
62 0
liuyunshengsir
|
SQL 存储 JSON
MySQL 8.0新特性
MySQL 8.0新特性
liuyunshengsir
535 0
TimeFriends
Mysql8.0新特性
Mysql8.0新特性
TimeFriends
125 0
游客vvhlkjynxortk
mysql8新特性（二）
mysql8新特性（二）
游客vvhlkjynxortk
85 0
游客vvhlkjynxortk
|
存储 SQL JSON
mysql8新特性（一）
mysql8新特性（一）
游客vvhlkjynxortk
404 0
mysql8新特性（一）
MySQL技术
|
SQL JSON 关系型数据库
MySQL8.0新特性集锦
作者：偏执的工匠 原文：https://www.jianshu.com/p/be29467c2b0c
MySQL技术
141 0
重庆八怪
|
存储 SQL 关系型数据库
MySQL 8部分新特性（8.0.17）
参考官方文档，记录了部分我关注的新特性。本文仅仅是大概记录而已，准确描述参考官方文档： MySQL 8.0 Reference Manual 欢迎关注我的《深入理解MySQL主从原理 32讲 》，如下： 数据字典使用事务性存储引擎。
重庆八怪
1402 0
技术小能手
|
存储 SQL 网络协议
MySQL 8.0的新特性（二）
技术小能手
5199 0