MySQL5.6和5.5的一些区别地方

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

5.6的改进地方:

1、在5.5和之前的版本mysql中,主从配置的话,要在从节点配置change master to 指明binlog和POS。而在5.6及以后

,这2个参数就可以省略掉。MySQL可以通过内部的GTID机制自动找点同步。我们只要指明master的IP、用户名和密码、端口即可。

2、5.6支持多线程复制

在5.5里,同步复制是单线程、队列的,只能一个的执行。而在5.6里,多个库可以同时进行复制(注意:同一个库内仍是不能多线程了)。

5.6里会涉及到UUID这个参数

MySQL [(none)]>show variables like '%uuid%';

+---------------+--------------------------------------+

| Variable_name | Value                                |

+---------------+--------------------------------------+

| server_uuid   | ca910cf0-3aec-11e6-9319-b888e3dcfeb8 |

+---------------+--------------------------------------+

1 row in set (0.00 sec)

注意:在mysql初次启动时候会自动生成这个UIID,写入到auto.cnf中,官方不建议修改这个值。并且server_uuid和GTID有密切关系。

GTID:全局事务标识符

     使用这个功能时,每次事务提交都会在binlog里生成唯一的标识符,它由UUID和事务ID组成。首次提交的事务ID为1,以后依次递增。

     开启GTID时,slave做同步复制时,无需找到binlog日志和POS点。直接

GTID写法:

change master to 

     master_HOST=192.168.2.100,

     master_PORT=2206,

     master_USER=repluser,

     master_PASSWORD='123456',

     master_AUTO_POSITION=1;


另外传统的写法:

CHANGE MASTER TO

  MASTER_HOST='master2.mycompany.com',

  MASTER_USER='replication',

  MASTER_PASSWORD='bigs3cret',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='master2-bin.001',

  MASTER_LOG_POS=4,

  MASTER_CONNECT_RETRY=10;

  

如果之前启用过了GTID,那么就不能不能再使用传统的change master to的方式了,会报错,如下:

 ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

wKiom1d03SWgvd1KAAA7n1_WA44465.png



GTID的工作流程

1、在master上提交一个事务,并写入到binlog

2、binlog发送到slave上,slave接收并写入relay log,slave读取到这个GTID,并设置gtid_next的值。例如:

     set @@SESSION.GTID_NEXT='B0869D03-D332223-35454:3';

     然后告诉slave接下来的事务必须使用GTID,并写入到自己的binlog中。

3、slave检查并确认这个gtid没有被使用,如果没有被使用,那么开始执行这个事务并写入到它自己的binlog里。

4、由于gtid_next的值不是空的,slave不会尝试去生成一个新的gtid,而是通过主从同步来获的GTID。

     另外,要使用GTID方式进行主从同步的话,还要在my.cnf里面加上如下的配置:

     [mysqld]

     log-bin=mysql-bin

     binlog_format = mixed

     log_slave_updates = ON

     gtid-mode = ON

     enforce_gtid_consistency = ON

     然后在master上导出mysqldump -uroot -proot -q --single-transaction -R -E --triggers -B hellodb > /root/hello.sql

     在slave上导入mysql -uroot -proot < /root/hello.sql

     在slave上配置change master to指向(如下6行代码):

change master to 

     master_HOST=192.168.2.100,

     master_PORT=3306,

     master_USER=repluser,

     master_PASSWORD='123456',

     master_AUTO_POSITION=1;


GTID的局限性:

1、GTID的复制是基于事务的,不支持MyISAM,这可能导致多个GTID分配各同一个事务。

2、对create table ... select语句不支持。因为该语句会被拆分成create table和insert 两个事务,并且如果这两个事务被分配了同一个GTID,将会导致insert被备库忽略掉。

3、不支持创建、删除临时表


多线程复制演示:

在slave上执行下面几条命令:

> stop slave;

> set global  slave_parallel_workers = 4;

> start slave;

> show full processlist;可以看到有4个线程 Waiting for an event from Coordinator

如果此时在主上有大量的insert操作,可以在slave上执行> select * from mysql.slave_worker_info\G 应该可以查看到worker_id在不断变化,说明是多线程复制在起作用了。


说明:slave_parallel_workers 即可实现在slave上多线程并发复制。不过,它只能支持一个实例下多个 database 间的并发复制,并不能真正做到多表并发复制。因此在较大并发负载时,slave还是没有办法及时追上master,需要想办法进行优化(比如:尽量将一个库中的表按照业务逻辑拆分成多个库来保存,这样在写操作时候,slave就能开启多线程复制,减少了同步的时延。)

此外,建议修改my.cnf,增加2行(默认这个info_file是文件的,不写入数据库的)

relay_log_info_repository = table

master_info_repository = table

单单这样还不够,默认这2张表是MyISAM的,不安全还要转换下

> alter table slave_master_info engine innodb;

> alter table slave_relay_log_info engine innodb;

> alter table slave_worker_info engine innodb;

这样的话,可防止表损坏,在损坏后可以自行修复。


GTID模式下的主从复制,同步时候报错不能跳过的解决方法:

假如在slave上看到同步报错“从节点的XXX键不存在”

我们可以尝试使用5.5上的老方法

> stop slave;

> set global sql_slave_skip_counter=1

> start slave;

执行的时候会发现报错了,提示如下:

wKioL1d03TiDgnIaAAAhwTF6_W4390.png


可以看出运行在GTID模式下,不支持sql_slave_skip_counter这种方式跳过的。


那么可以如下方法来跳过:

> show  slave status\G查看如下2行的信息:

  Retrieved_Gtid_Set: ca910cf0-3aec-11e6-9319-b888e3dcfeb8:1-2

  Executed_Gtid_Set: ca910cf0-3aec-11e6-9319-b888e3dcfeb8:1

第一行表示收到的事务,第二行表示已经执行完的事务。也就是说执行到Retrieved_Gtid_Set时候发生错误了。

因此,我们直接单单跳过这个事务即可。

> stop slave;

> set GTID_NEXT='ca910cf0-3aec-11e6-9319-b888e3dcfeb8:2';     就是这种写法,不要加什么1-2这些玩意

> begin;

> commit;

> set GTID_NEXT="AUTOMATIC";      #把gtid_next设置回来

> start slave;

> show slave status\G   验证下是否IO/SQL都是YES状态。


GTID模式转换为传统模式的方法及注意点:

要转换成传统模式,需要在my.cnf里面注释掉下面2行:

# gtid-mode=ON

# enforce_gtid_consistency = ON

然后重启MySQL。

登进mysql,执行类似如下命令:

> stop slave;

> CHANGE MASTER TO

     MASTER_HOST='master2.mycompany.com',

      MASTER_USER='replication',

     MASTER_PASSWORD='bigs3cret',

     MASTER_PORT=3306,

     MASTER_LOG_FILE='master2-bin.001',

     MASTER_LOG_POS=4,

     MASTER_CONNECT_RETRY=10;

     结果报错了,如下图:

wKioL1d03UvA1q_VAAAwoKwkQzY134.png

解决方法:

change master to MASTER_AUTO_POSITION=0;       # 关闭这个参数,这个参数是GTID复制才用到的。

> CHANGE MASTER TO

        MASTER_HOST = '192.168.2.11',

        MASTER_USER='repluser',

        MASTER_PASSWORD='123456',

        MASTER_PORT=3306,

        MASTER_LOG_FILE='mysql-bin.000012',

        MASTER_LOG_POS=500,

        MASTER_CONNECT_RETRY=10;

> start slave;

> show slave status\G 验证下是否IO/SQL都是YES状态。











本文转自 lirulei90 51CTO博客,原文链接:http://blog.51cto.com/lee90/1794646,如需转载请自行联系原作者
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
1月前
|
存储 关系型数据库 MySQL
MySQL中的int(10)、char(10)与varchar(10)的类型和区别
在选择正确的数据类型时,需要仔细考虑每列的数据特点及应用程序的使用情况。合理的数据类型选择可以优化存储空间的使用,提高查询速度和数据库的整体性能。
179 14
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库中的 char 与 varchar的区别是什么
MySQL中的char和varchar均用于存储字符串,但有显著区别。char为定长类型,固定长度,存储空间始终为设定值,适合长度固定的数据如手机号。varchar为变长类型,仅占用实际数据所需空间,适合长度不固定的内容如用户名。二者在性能与空间利用上各有优劣,应根据实际场景合理选择。
230 0
|
10月前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志和二进制日志是确保数据库稳定性和可靠性的关键组件。重做日志主要用于事务的持久性和原子性,通过记录数据页的物理修改信息来恢复未提交的事务;而二进制日志记录SQL语句的逻辑变化,支持数据复制、恢复和审计。两者在写入时机、存储方式及配置参数等方面存在显著差异。
206 6
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
10月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
1720 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
8月前
|
SQL Oracle 关系型数据库
MySQL 和 Oracle 的区别?
本文对比了Oracle和MySQL数据库的多个方面。Oracle适用于大型数据库,支持高并发和大访问量,市场占有率为40%,安装占用空间较大,约3G;而MySQL适合中小型应用,是开源免费的,安装仅需152M。两者在主键生成、字符串处理、SQL语句、事务处理等方面存在差异。Oracle功能更为强大,尤其在企业级应用中表现突出,而MySQL则以简单易用见长。
968 7
MySQL 和 Oracle 的区别?
|
存储 关系型数据库 MySQL
ES的全文索引和MySQL的全文索引有什么区别?如何选择?
【8月更文挑战第26天】ES的全文索引和MySQL的全文索引有什么区别?如何选择?
1435 5
|
7月前
|
存储 关系型数据库 MySQL
MYSQL支持的存储引擎有哪些, 有什么区别
MYSQL存储引擎有很多, 常用的就二种 : MyISAM和InnerDB , 者两种存储引擎的区别 ; ● MyISAM支持256TB的数据存储 , InnerDB只支持64TB的数据存储 ● MyISAM 不支持事务 , InnerDB支持事务 ● MyISAM 不支持外键 , InnerDB支持外键
|
7月前
|
SQL 存储 关系型数据库
简单聊聊MySQL的三大日志(Redo Log、Binlog和Undo Log)各有什么区别
在MySQL数据库管理中,理解Redo Log(重做日志)、Binlog(二进制日志)和Undo Log(回滚日志)至关重要。Redo Log确保数据持久性和崩溃恢复;Binlog用于主从复制和数据恢复,记录逻辑操作;Undo Log支持事务的原子性和隔离性,实现回滚与MVCC。三者协同工作,保障事务ACID特性。文章还详细解析了日志写入流程及可能的异常情况,帮助深入理解数据库日志机制。
886 0
|
10月前
|
存储 关系型数据库 MySQL
MySQL引擎InnoDB和MyISAM的区别?
InnoDB是MySQL默认的事务型存储引擎,支持事务、行级锁、MVCC、在线热备份等特性,主索引为聚簇索引,适用于高并发、高可靠性的场景。MyISAM设计简单,支持压缩表、空间索引,但不支持事务和行级锁,适合读多写少、不要求事务的场景。
155 9

推荐镜像

更多