一、Mysql 5.6 新特性

二、GITD 详解

三、多线程复制基于库

四、Mysql 5.6 复制管理工具

五、具体配置过程


一、Mysql 5.6 新特性

MySQL 5.6 主要在查询性能的优化、InnoDB改进以支持高吞吐量的事务、NoSQL风格的API、分区功能的改进、数据复制的改进,增加 PERFORMANCE_SCHEMA 库以获得数据库性能信息等。

1.查询性能优化

      优化 WHERE 语句改进索引条件的处理性能,Multi-Range Read:通过随机数据访问来提升 SSD 上的数据读取速度,优化文件排序:对一些组合了 ORDER BY non_indexed_column 和 LIMIT x 的SQL语句,该特性将大大加速此类语句的执行速度。

2.InnoDB 的改进

      MySQL 5.6 完全集成 InnoDB 作为默认的存储引擎。同时 5.6 版本在使用 InnoDB 上的很多细节做了改进,详情请看这里。

3.提供 NoSQL 风格的 API

      该功能主要适用于将 MySQL 来作为 NoSQL 使用,而 MySQL 使用的是 memcached 兼容的 API。通过该接口程序访问数据可直达 InnoDB 存储引擎,而无需通过 MySQL 对 SQL 的转换过程,大大提升了数据访问的性能。

4.分区的改进


显式分区数据查询,例如:


SELECT * FROM employees PARTITION (p0, p2);

DELETE FROM employees PARTITION (p0, p1);

UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = 'Jill';

SELECT e.id, s.city FROM employees AS e JOIN stores PARTITION (p1) AS s ...;



分区数据的导入导出,此功能用于快速的将某个表迁移到分区上:ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

5.复制功能的改进

      支持多线程复制,事实上是针对每个database开启相应的独立线程。即每个库有一个单独的(sql thread)如果线上业务中,只有一个database或者绝大多数压力集中在个别database的话,多线程并发复制特性就没有意义了。

      支持启用GTID,对运维人员来说应该是一件令人高兴的事情,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向,而不是很有经验的运维,往往会将其找错,造成主从同步复制报错,在mysql5.6里,无须再知道binlog和POS点,需要知道master的IP、端口,账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步。

6.大大增强 PERFORMANCE_SCHEMA 数据库

      降低了数据库开销、表IO的信息汇集和监控、表锁信息汇集和监控、会话和用户级别的监控、全局性能信息汇总。

二、GITD 详解

MySQL 5.6 的新特性之一,是加入了全局事务 ID (GTID) 来强化数据库的主备一致性,故障恢复,以及容错能力。

1.什么是GTID?

      官方文档:http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html在这篇文档里,我们可以知道全局事务 ID 的官方定义是:GTID = source_id:transaction_id

MySQL 5.6 中,每一个 GTID 代表一个数据库事务。在上面的定义中,source_id 表示执行事务的主库 uuid(server_uuid),transaction_id 是一个从 1 开始的自增计数,表示在这个主库上执行的第 n 个事务。MySQL 会保证事务与 GTID 之间的 1 : 1 映射。

      例如,下面就是一个 GTID:3E11FA47-71CA-11E1-9E33-C80AA9429562:50 表示在以 "3E11FA47-71CA-11E1-9E33-C80AA9429562" 为唯一标示的 MySQL 实例上执行的第 50 个数据库事务。很容易理解,MySQL 只要保证每台数据库的 server_uuid 全局唯一,以及每台数据库生成的 transaction_id 自身唯一,就能保证 GTID 的全局唯一性。

2.什么是server_uuid?

      MySQL 5.6 用 128 位的 server_uuid 代替了原本的 32 位 server_id 的大部分功能。原因很简单,server_id 依赖于 my.cnf 的手工配置,有可能产生冲突 —— 而自动产生 128 位 uuid 的算法可以保证所有的 MySQL uuid 都不会冲突。

      在首次启动时 MySQL 会调用 generate_server_uuid() 自动生成一个 server_uuid,并且保存到 auto.cnf 文件 —— 这个文件目前存在的唯一目的就是保存 server_uuid。



[root@master data]# ll

总用量 110624

-rw-rw---- 1 mysql mysql       56 8月  26 13:57 auto.cnf

-rw-rw---- 1 mysql mysql 12582912 8月  26 14:00 ibdata1

-rw-rw---- 1 mysql mysql 50331648 8月  26 14:00 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 8月  26 13:44 ib_logfile1

-rw-rw---- 1 mysql mysql      143 8月  26 14:00 master-bin.000001

-rw-rw---- 1 mysql mysql      120 8月  26 14:00 master-bin.000002

-rw-rw---- 1 mysql mysql       40 8月  26 14:00 master-bin.index

-rw-rw---- 1 mysql mysql        5 8月  26 14:00 master.test.com.pid

drwx------ 2 mysql mysql     4096 8月  26 13:44 mysql

drwx------ 2 mysql mysql     4096 8月  26 13:44 performance_schema

drwx------ 2 mysql mysql     4096 8月  26 13:44 test

[root@master data]# cat auto.cnf

[auto]

server-uuid=6b27d8b7-0e14-11e3-9eab-000c291192e4



在 MySQL 再次启动时会读取 auto.cnf 文件,继续使用上次生成的 server_uuid。使用 SHOW 命令可以查看 MySQL 实例当前使用的 server_uuid?:SHOW GLOBAL VARIABLES LIKE 'server_uuid';它是一个 MySQL 5.6 global variables,文档链接在这里: server_uuid? 全局唯一的 server_uuid 的一个好处是:可以解决由 server_id 配置冲突带来的 MySQL 主备复制的异常终止(BUG #33815?)

      在MySQL 5.6,Slave 向 Master 申请 binlog 时,会首先发送自己的 server_uuid,Master 用 Slave 发送的 server_uuid 代替 server_id (MySQL 5.6 之前的方式)作为 kill_zombie_dump_threads 的参数,终止冲突或者僵死的 BINLOG_DUMP 线程。

三、多线程复制基于库

      MySQL 5.6之前的版本,同步复制是单线程的,队列的,只能一个一个执行,在5.6里,可以做到多个库之间的多线程复制,例如数据库里,存放着用户表,商品表,价格表,订单表,那么将每个业务表单独放在一个库里,这时就可以做到多线程复制,但一个库里的表,多线程复制是无效的。

注,每个数据库仅能使用一个线程,复制涉及到多个数据库时多线程复制才有意义。

四、Mysql 5.6 复制管理工具

官方下载地址:http://dev.mysql.com/downloads/tools/utilities/#downloads

注,这里只简单的介绍一下,具体的工具使用,不具体说明,使用方法 命令—help

mysqlreplicate 快速启动复制

mysqlrplcheck 快速检查复制环境

mysqlrplshow 显示复制拓扑

mysqlfailover 故障转移

mysqlrpladmim 管理工具

五、具体配置过程


操作系统

CentOS 6.4 x86_64

软件版本

Mysql 5.6.12



配置mysql主从复制

(1).配置选项说明

要在MySQL 5.6中使用复制功能,其服务配置段[mysqld]中于少应该定义如下选项,

binlog-format:二进制日志的格式,有row、statement和mixed几种类型;需要注意的是:当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,现在MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致;

log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host:用于启动GTID及满足附属的其它需求;

master-info-repository和relay-log-info-repository:启用此两项,可用于实现在崩溃时保证二进制及从服务器安全的功能;

sync-master-info:启用之可确保无信息丢失;

slave-paralles-workers:设定从服务器的SQL线程数;0表示关闭多线程复制功能;

binlog-checksum、master-verify-checksum和slave-sql-verify-checksum:启用复制有关的所有校验功能;

binlog-rows-query-log-events:启用之可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度;

log-bin:启用二进制日志,这是保证复制功能的基本前提;

server-id:同一个复制拓扑中的所有服务器的id号必须惟一。




(2).配置主服务器master


binlog-format=Mixed

log-bin=master-bin.log

log-bin-index=master-bin.index

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log-events=1

server-id=1

sync_binlog=1

report-port=3306

port=3306

datadir=/usr/local/mysql/data

socket=/tmp/mysqld.sock

binlog-do-db=newido

binlog-do-db=tickets

binlog-ignore-db=mysql

binlog-ignore-db=information-schema


4).查看gtid的相关信息

[root@master mysql]# mysql -h127.0.0.1

Welcome to the MySQL monitor.  Commands end with; or \g.

Your MySQL connection id is1

Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle isa registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;'or '\h'forhelp. Type '\c'to clear the current input statement.

mysql> show global variables like '%gtid%';

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

| Variable_name            | Value |

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

| enforce_gtid_consistency | ON    |

| gtid_executed            |       |

| gtid_mode                | ON    | #说明gti功能已启动

| gtid_owned               |       |

| gtid_purged              |       |

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

5rows inset(0.10sec)



(5).创建有复制权限的用户

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.18.%'IDENTIFIED BY 'replpass';

Query OK, 0 rows affected (0.44 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.03 sec)



(6).配置从服务器slave


binlog-format=Mixed

log-bin=slave-bin.log

log-bin-index=slave-bin.index

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=2

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log-events=1

server-id=2

sync_binlog=1

report-port=3306

port=3306

datadir=/usr/local/mysql/data

socket=/tmp/mysqld.sock

replicate-do-db=newido

replicate-do-db=tickets

replicate-ignore-db=mysql

replicate-ignore-db=information-schema

replicate-ignore-db=performance_schema



(7).重新启动mysql

[root@slave mysql]# service mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL...... SUCCESS!



(8).在从服务器上使用主mysql上创建的账号密码登录并进行复制


mysql> change master to master_host='192.168.18.201', master_user='repluser',master_password='replpass',master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.24 sec)

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.04 sec)


(9).查看一下复制状态

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.18.201

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 151

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 363

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes  #IO线程与SQL线程都是yes,说明复制启动完成。

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 151

Relay_Log_Space: 561

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 6b27d8b7-0e14-11e3-9eab-000c291192e4

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has readall relay log; waiting forthe slave I/Othread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 1

1 row inset(0.00 sec)




(10).测试一下主从复制

master:


mysql> create database mydb;

Query OK, 1 row affected (0.05 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test|

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

5 rows inset(0.00 sec)



slave:


mysql> show databases;

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

| Database           |

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

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test|

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

5 rows inset(0.00 sec)



(11).查看一下复制状态


mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.18.201

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 293

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 505

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 293

Relay_Log_Space: 703

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 6b27d8b7-0e14-11e3-9eab-000c291192e4

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has readall relay log; waiting forthe slave I/Othread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6b27d8b7-0e14-11e3-9eab-000c291192e4:1

Executed_Gtid_Set: 53c8fa53-0e16-11e3-9eb8-000c29b8df6a:1-2,

6b27d8b7-0e14-11e3-9eab-000c291192e4:1

Auto_Position: 1

1 row inset(0.00 sec)