mysql 的类RAC集群 group replication

复制在数据库架构是非常常用的一种架构，oracle有dataguard，goldengate， mysql有主从复制、主主复制等，还有些第三方的dds等，今天我们来说说mysql的复制。mysql里面最基本的复制一般就是主从复制，或者由此衍生出来的mmm, mha 或者配合其它插件如keepalived lvs去实现高可用或者负载均衡。对基本的主从复制当一个事务在master 提交成功时，会把binlog文件同步到从库服务器上落地为relay log给slave端执行，这样来实现数据库的灾备。但是这里一个问题，就是主库并不会像oracle dataguard 最大保护模式里面的机制来确认从库接收到binlog文件，因此就有可能出现这种情况，当主库commit一个事务后，数据库发生宕机，刚好它的binlog还没来得及传送到slave端，即使多个slave, 这个时候选任何一个slave端都会丢失这个事务，造成数据不一致情况, 有可能是由于网络原因或者负载过高，导致从库还没来急应用主库传过来的日志，如果此时主库挂掉，从库都会有数据丢失。因此mysql从5.5开始推出了半同步，同时在mysql5.6中使用after_commit主从还是有可能少一个事务，随着mysql5.7版本的发布，半同步复制技术升级全新的loss-less semi-synchronous replication 架构，复制技术在数据库一致性和性能上有了明显的提升。但是即使这样还有两个问题一是所有写操作全部集中在master上，master的很可能出现性能问题，二是如果主挂了，应用需要手动切到第二个节点上在这个日趋自动化的年代，这是一件多么的悲哀的事情，看看人家oracle 的rac做的多成熟，虽然偶尔有些问题，但是基本可以做到无缝切换，因此mysql后面推出了mysql group replication,简称mgr,如果用一句话概括mgr，可以说mgr一个非共享存储的rac.

mgr 基本有三大特点：一是多主，在同一个group里边的所有实例，每一个实例可以执行写操作，也就是每个实例都执行Read-Write；

二是弹性，同个Group Replication中，节点的加入或者移除都是自动调整，如果新加入一个节点，该节点会自动从Group的其他节点同步数据，直到与其他节点一致；如果移除一个节点，那么剩下的实例会自动更新；三是性能提高 更高性能的同步机制；但是和rac不同的是rac用的共享存储，而mgr则是本地节点都有一份拷贝数据。而相同的是两种 技术同样都用来了心跳、故障探测、广播等技术。

由于篇幅，这里就不介绍mgr的原理和运维细节，先来一个简单的环境搭建手册。

清除旧版本mysql:

userdel -r mysql

cd /usr/bin/

rm -rf mysql*

cd /etc/init.d/

rm -rf mysql*

rm /etc/my.cnf -rf

设置/etc/hosts

192.168.8.8 db1

192.168.8.9 db2

192.168.8.10 db3

关闭不必要的服务

chkconfig sendmail off

chkconfig isdn off

chkconfig pcmcia off

chkconfig iptables off

chkconfig mdmonitor off

chkconfig rhnsd off

chkconfig smartd off

chkconfig cups off

chkconfig cups-config-daemon off

chkconfig iiim off

chkconfig httpd off

chkconfig squid off

chkconfig smb off

chkconfig ip6tables off

chkconfig gpm off

chkconfig xend off

chkconfig bluetooth off

chkconfig hidd off

chkconfig pcscd off

chkconfig iscsi off

chkconfig iscsid off

chkconfig avahi-daemon off

chkconfig tog-pegasus off

chkconfig yum-updatesd off

chkconfig irqbalance off

chkconfig mcstrans off

chkconfig NetworkManager off

chkconfig cpuspeed off

chkconfig irqbalance off

chkconfig bmc-watchdog off

把软件传到/usr/local目录下

tar xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

mv mysql-5.7.18-linux-glibc2.5-x86_64 mysql

创建目录和用户

mkdir -p /data/mysql/binlog

mkdir -p /data/mysql/data

mkdir -p /data/mysql/log

groupadd -g 505 mysql

useradd -u 505 -g mysql mysql

chown -R mysql:mysql /usr/local/mysql

chown -R mysql:mysql /data/mysql/

设置参数文件

cat /etc/my.cnf

[client]

port = 3317

default_character_set = utf8

socket = /data/mysql/3317.sock

[mysqld]

basedir = /usr/local/mysql

datadir = /data/mysql/data

socket = /data/mysql/3317.sock

pid-file = /data/mysql/3317.pid

server_id=12001

expire_logs_days=7

max_binlog_size =204800000

user = mysql

default_storage_engine = InnoDB

character_set_server = utf8

skip_external_locking

skip_host_cache

skip_name_resolve

autocommit = on

max_allowed_packet = 256M

max_connections = 1000

max_user_connections = 1000

max_connect_errors = 1000000

wait_timeout = 1800

transaction_isolation =repeatable-read

explicit_defaults_for_timestamp = 1

query_cache_type = 0

query_cache_size = 0

thread_cache_size = 50

open_files_limit = 65535

tmpdir = /tmp

slave_load_tmpdir = /tmp

tmp_table_size = 16M

max_heap_table_size = 16M

read_buffer_size = 8M

read_rnd_buffer_size = 10M

sort_buffer_size = 16M

join_buffer_size = 16M

key_buffer_size = 32M

interactive_timeout = 1800

innodb_open_files=2048

innodb_buffer_pool_size = 600M

innodb_sort_buffer_size = 16M

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

innodb_io_capacity = 800

innodb_io_capacity_max = 1600

innodb_purge_threads = 4

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_log_file_size = 200M

innodb_log_buffer_size = 16M

innodb_file_per_table=1

innodb_strict_mode = 1

innodb_flush_log_at_trx_commit = 1

innodb_log_files_in_group = 3

innodb_flush_method = O_DIRECT

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 4

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

binlog_gtid_simple_recovery=1

log_timestamps=system

transaction_write_set_extraction=MURMUR32

show_compatibility_56=on

innodb_data_home_dir=/data/mysql/data

log_bin = /data/mysql/binlog/bin.log

log_bin_index = /data/mysql/binlog/bin.index

expire_logs_days = 5

sync_binlog = 1

binlog_format="ROW"

log_bin_trust_function_creators = 1

binlog_cache_size = 2097152

auto_increment_increment = 2

auto_increment_offset = 1

log_error = /data/mysql/log/error.log

log_queries_not_using_indexes = 1

slow_query_log = 1

slow_query_log_file = /data/mysql/log/slowquery.log

long_query_time = 2

min_examined_row_limit = 100

log_slave_updates=1

relay_log = /data/mysql/binlog/rela-log

relay_log_index = /data/mysql/binlog/rela-log.index

innodb_use_native_aio=1

innodb_stats_persistent = 1

innodb_spin_wait_delay= 6

innodb_adaptive_flushing = 1

innodb_flush_neighbors = 0

innodb_read_io_threads = 4

innodb_write_io_threads = 4

innodb_io_capacity = 4000

innodb_purge_threads=1

innodb_adaptive_hash_index=0

table_open_cache_instances=16

symbolic-links=0

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 4

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 500M

innodb_purge_rseg_truncate_frequency = 128

binlog_gtid_simple_recovery=1

log_timestamps=system

transaction_write_set_extraction=MURMUR32

show_compatibility_56=on

--below is needed for GR

gtid_mode = on

enforce_gtid_consistency=ON

master_info_repository=TABLE

relay_log_info_repository=TABLE

binlog_checksum=NONE

log_slave_updates=ON

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

初始化数据库

su - mysql

cd /usr/local/mysql

./bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

添加启动服务

cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql

chown mysql:mysql /etc/rc.d/init.d/mysql

chmod 755 /etc/rc.d/init.d/mysql

chkconfig mysql on

启动数据库

service mysql start

修改启动密码

SET PASSWORD = PASSWORD('123456');

flush privileges

这样三台单机数据库就启动好了

创建复制账号(三台都要执行)：

安装插件:

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

show plugins;

确保binlog_format是row格式。

mysql> show variables like 'binlog_format';

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

| Variable_name | Value |

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

| binlog_format | ROW |

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

1 row in set (0.00 sec)

修改配置文件配置

db1上的my.cnf配置：

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = "5f847ff2-d701-11e6-819c-b8ca3af6e36c"

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = "db1:23306"

loose-group_replication_group_seeds = "db1:23306,db2:23307,db3:23308"

loose-group_replication_bootstrap_group = off

loose-group_replication_single_primary_mode = true

loose-group_replication_enforce_update_everywhere_checks = false

db2上的my.cnf配置：

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = "5f847ff2-d701-11e6-819c-b8ca3af6e36c"

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = "db2:23307"

loose-group_replication_group_seeds = "db1:23306,db2:23307,db3:23308"

loose-group_replication_bootstrap_group = off

loose-group_replication_single_primary_mode = true

loose-group_replication_enforce_update_everywhere_checks = false

db3上的my.cnf配置：

transaction_write_set_extraction = XXHASH64

loose-group_replication_group_name = "5f847ff2-d701-11e6-819c-b8ca3af6e36c"

loose-group_replication_start_on_boot = off

loose-group_replication_local_address = "db3:23308"

loose-group_replication_group_seeds = "db1:23306,db2:23307,db3:23308"

loose-group_replication_bootstrap_group = off

loose-group_replication_single_primary_mode = true

loose-group_replication_enforce_update_everywhere_checks = false

配置完后，重启3个db上的mysql服务

db1,db2, db3分别执行下面语句:

db1:

设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准，以后加入的就不需要设置。

mysql> SET GLOBAL group_replication_bootstrap_group = ON;

Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;

Query OK, 0 rows affected (1.03 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;

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

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | aaa68409-bbaa-11e7-b2ec-0800279dd24f | db1 | 3317 | ONLINE |

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

db2上启动group_replication：

Db2上mysql命令行上执行启动：

mysql> START GROUP_REPLICATION;

再去master库db1上，查看group_replication成员，会有db2的显示

mysql> SELECT * FROM performance_schema.replication_group_members;

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

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | 4ba44c33-bbab-11e7-b544-0800279dd24f | db2 | 3317 | ONLINE |

| group_replication_applier | aaa68409-bbaa-11e7-b2ec-0800279dd24f | db1 | 3317 | ONLINE |

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

2 rows in set (0.00 sec)

db3上启动group_replication：

-- Db3命令行上执行：

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON; ---启动start group_replication之前都要set此项

Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;

Query OK, 0 rows affected (1.99 sec)

-- 再去master库db1上，查看group_replication成员，会有db3的显示，而且已经是ONLINE了

mysql> SELECT * FROM performance_schema.replication_group_members;

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

| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |

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

| group_replication_applier | 4ba44c33-bbab-11e7-b544-0800279dd24f | db2 | 3317 | ONLINE |

| group_replication_applier | aaa68409-bbaa-11e7-b2ec-0800279dd24f | db1 | 3317 | ONLINE |

| group_replication_applier | dbdf5de4-bbad-11e7-b23d-0800279dd24f | db3 | 3306 | ONLINE |

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

验证集群复制功能

create table t1(id int, cn varchar(32));

alter table t1 modify id int primary key;

insert into t1 select 1,'a';

可以在三台数据库上发现数据都一样，关于mgr原理和运维细节，后面继续更新。