一、简介
这次给大家介绍下MySQL官方最新版本5.7.17中GA的新功能 Group Replication 。
但是,在不同服务器上并发执行的事务之间可能存在冲突。通过在称为认证的过程中检查两个不同的并发事务的写集合来检测这样的冲突。如果在不同的服务器上执行的两个并发事务更新同一行,则会出现冲突。解析过程指出,首先订购的事务在所有服务器上提交,而顺序第二次中止的事务将在源服务器上回滚,并由组中的其他服务器删除。这实际上是一个分布式的第一个提交赢的规则。
MySQL组复制协议
最后,组复制是一种无共享复制方案,其中每个服务器都有自己的整个数据副本。
上图描述了MySQL组复制协议,并通过将其与MySQL复制(或甚至MySQL半同步复制)进行比较,您可以看到一些差异。注意,为了清楚起见,这个图片中缺少一些基本的共识和Paxos相关消息。
介绍就到这,本文中我将一步一步的安装部署group_replication的三个节点,并让你看到它的功能和特性,如果看完全文,你十分的感兴趣的话,可以去mysql的Group Replication主页去查看更详细的信息。
二、正式部署
1. 环境介绍
basedir = /usr/local/mysql
(PS: 这点还是要吐槽一下的,mysql官方basedir如果不在这个目录的话,mysql.server也不好使,这个实验也不成功,我们还是勉强先放在这)
端口号 | 数据及日志目录 | Group_Replication通讯端口 |
3306 | /data/mysql/mysql_3306/{data,logs,tmp} | 33061 |
3307 | /data/mysql/mysql_3307/{data,logs,tmp} | 33062 |
3308 | /data/mysql/mysql_3308/{data,logs,tmp} | 33063 |
2. 初始化
①、下载and解压缩,并把mysql放到指定地方(标准目录: /usr/local/mysql)
cd /opt/ wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql
②、 创建数据库需要的数据、日志和临时目录并赋权:
mkdir -p /data/mysql/{mysql_3306,mysql_3307,mysql_3308}/{data,logs,tmp} chown -R mysql.mysql /data/mysql/
3. 初始化
安装机器: 192.168.0.162 (单机多实例安装)
配置文件说明:
端口号 | 配置文件 |
3306 | /data/mysql/mysql_3306/my3306.cnf |
3307 | /data/mysql/mysql_3306/my3307.cnf |
3308 | /data/mysql/mysql_3306/my3308.cnf |
[client] port = 3306 socket = /tmp/mysql3306.sock [mysql] prompt = mysql [\d]> default_character_set = utf8 no-auto-rehash [mysqld] #misc user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql_3306/data port = 3306 socket = /tmp/mysql3306.sock event_scheduler = 0 tmpdir=/data/mysql/mysql_3306/tmp #timeout interactive_timeout = 43200 wait_timeout = 43200 #character set character-set-server = utf8 open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000 # explicit_defaults_for_timestamp #logs log-output=file slow_query_log = 1 slow_query_log_file = slow.log log-error = error.log log_error_verbosity=3 pid-file = mysql.pid long_query_time = 1 #log-slow-admin-statements = 1 #log-queries-not-using-indexes = 1 log-slow-slave-statements = 1 #binlog binlog_format = row server-id = 1343306 log-bin = /data/mysql/mysql_3306/logs/mysql-bin binlog_cache_size = 1M max_binlog_size = 200M max_binlog_cache_size = 2G sync_binlog = 0 expire_logs_days = 10 #group replication server_id=1013306 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON binlog_format=ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="13855fca-d2ab-11e6-8f37-005056b8286c" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.0.162:33061" loose-group_replication_group_seeds= "192.168.0.162:33061,192.168.0.162:33071,192.168.0.162:33081" loose-group_replication_bootstrap_group= off loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on #relay log skip_slave_start = 1 max_relay_log_size = 500M relay_log_purge = 1 relay_log_recovery = 1 #slave-skip-errors=1032,1053,1062 #buffers & cache table_open_cache = 2048 table_definition_cache = 2048 table_open_cache = 2048 max_heap_table_size = 96M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 256 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M #myisam myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 #innodb innodb_buffer_pool_size = 500M innodb_buffer_pool_instances = 1 innodb_data_file_path = ibdata1:100M:autoextend innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 64M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_file_per_table = 1 innodb_rollback_on_timeout innodb_status_file = 1 innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED innodb_flush_method = O_DIRECT
其中比较重要的地方,也是安装Group_replication必须要有的配置项,需要注意一下:
#group replication server_id=1013306 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON binlog_format=ROW transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="13855fca-d2ab-11e6-8f37-005056b8286c" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.0.162:33061" loose-group_replication_group_seeds= "192.168.0.162:33061,192.168.0.162:33071,192.168.0.162:33081" loose-group_replication_bootstrap_group= off loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on
在这里有一些技术细节要说明一下:
上面的三个配置文件省略了所有不必要的配置项、但是看起来还是有点多、这些都是mgr环境要求的。
server_id 每个实例都要不要样
loose-group_replication_group_name:为mgr高可用组起一个名字,必须是有效的UUID。在二进制日志中为组复制事件设置GTID时,将在内部使用此UUID。使用SELECT UUID()
生成一个UUID。
loose-group_replication_local_address:mgr各实例之前都是要进行通信的、这个配置项设置的就是本实例所监听的ip:端口
loose-group_replication_group_seeds:各mgr实例所监听的ip:端口信息
其他配置参数,请参考官方文档:
https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html
4. 启动Group_replication第一个节点
初始化3306实例:
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my3306.cnf --initialize-insecure
启动3306实例:
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my3306.cnf &
进入mysql进行change操作:
/usr/local/mysql/bin/mysql -u root -S /tmp/mysql3306.sock mysql> SET SQL_LOG_BIN=0; mysql> CREATE USER rpl_user@'%'; mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'; mysql> SET SQL_LOG_BIN=1; mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | |... ... ... | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+
mysql> SET GLOBAL group_replication_bootstrap_group=ON; #只在第一个节点使用
mysql> START GROUP_REPLICATION;
确认节点加入情况:
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ | group_replication_applier | 68ce93ca-d292-11e6-bdf9-005056b8286c | localhost.localdomain | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ 1 rows in set (0.00 sec)
mysql> create database boom; mysql> use boom; mysql> create table boomballa(id int not null,name varchar(32),primary key(id)); mysql> insert into boomballa(id,name) values(1,'boomballa.top'); mysql> insert into boomballa(id,name) values(2,'myblog');
查看端口是否启动
root@ubuntu:/data/mysql/mysql_3306# netstat -anpt | grep 33061 tcp 0 0 0.0.0.0:33061 0.0.0.0:* LISTEN 1897/mysqld
5. 启动Group_replication第二个节点
复制配置文件:
# 复制配置文件 cd /data/mysql/mysql_3307/ cp ../mysql_3306/my3306.cnf my3307.cnf # 编辑配置文件 vim my3307.cnf #将替换3306为3307
loose-group_replication_local_address= "192.168.0.162:33071" loose-group_replication_group_seeds= "192.168.0.162:33061,192.168.0.162:33071,192.168.0.162:33081"
注意:loose-group_replication_local_address配置参数,必须要在loose-group_replication_group_seeds里面!
否则会出现3092错误
初始化并启动实例:
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my3307.cnf --initialize-insecure /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my3307.cnf &
mysql> SET SQL_LOG_BIN=0; mysql> CREATE USER rpl_user@'%'; mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'; mysql> SET SQL_LOG_BIN=1; mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery'; mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; mysql> START GROUP_REPLICATION; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ | group_replication_applier | 5aaa8529-d296-11e6-a7be-005056b8286c | localhost.localdomain | 3307 | ONLINE | | group_replication_applier | 68ce93ca-d292-11e6-bdf9-005056b8286c | localhost.localdomain | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ 2 rows in set (0.00 sec)
root@ubuntu:/data/mysql/mysql_3306# netstat -anpt | grep 33061 tcp 0 0 0.0.0.0:33071 0.0.0.0:* LISTEN 1950/mysqld
6. 第三节点安装配置
这里省略了,参考第二个节点。注意:替换端口为3308
安装好了以后的状态应该是(三节点上查询结果都是如此):
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ | group_replication_applier | 5aaa8529-d296-11e6-a7be-005056b8286c | localhost.localdomain | 3307 | ONLINE | | group_replication_applier | 68ce93ca-d292-11e6-bdf9-005056b8286c | localhost.localdomain | 3306 | ONLINE | | group_replication_applier | af93afd1-d297-11e6-b8e9-005056b8286c | localhost.localdomain | 3308 | ONLINE | +---------------------------+--------------------------------------+-----------------------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> use boom Database changed mysql> select * from boomballa; +----+---------------+ | id | name | +----+---------------+ | 1 | boomballa.top | | 2 | myblog | +----+---------------+ 2 rows in set (0.00 sec)
确认一下:
[root@localhost ~]# echo "select * from boom.boomballa;"|/usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock id name 1 boomballa.top 2 myblog [root@localhost ~]# echo "select * from boom.boomballa;"|/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock id name 1 boomballa.top 2 myblog [root@localhost ~]# echo "select * from boom.boomballa;"|/usr/local/mysql/bin/mysql -S /tmp/mysql3308.sock id name 1 boomballa.top 2 myblog
三、测试
查看mysql进程
root@ubuntu:/data/mysql/mysql_3306# ps -aux | grep mysql mysql 1897 0.1 6.3 1718160 255244 pts/0 Sl 16:06 0:08 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my3306.cnf mysql 1950 0.1 6.2 1705688 252724 pts/0 Sl 16:07 0:08 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my3307.cnf mysql 2147 0.2 6.2 1709152 251420 pts/1 Sl 16:25 0:09 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3308/my3308.cnf root 2228 0.0 0.0 14224 928 pts/1 S+ 17:35 0:00 grep --color=auto mysql
设置第一个mysql实例密码,设置密码为root
/usr/local/mysql/bin/mysqladmin -u root -S /tmp/mysql3306.sock -p password root
授权root用户远程连接
/usr/local/mysql/bin/mysql -u root -S /tmp/mysql3306.sock -proot mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option; mysql> flush privileges;
测试其他端口,比如:3307和3308,也应该是可以查看表数据!
在3306修改表数据,在3307查看数据,会立即同步的
关闭其中一个节点
关闭3308的进程
kill 2147
再次查看3306和3307表数据,是ok的。
在3306修改表数据,3307也是会同步的
启动3308实例
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3308/my3308.cnf &
查看3308数据,发现数据没有更新过来
进入到3308,开启GROUP_REPLICATION
/usr/local/mysql/bin/mysql -u root -S /tmp/mysql3308.sock -proot mysql> START GROUP_REPLICATION;