这篇文章主要讲解了“MySQL8.0.13组复制的安装步骤”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL8.0.13组复制的安装步骤”吧!
环境描述:
主机:
192.168.0.51 alpha-mysql-0-51 主节点
192.168.0.52 alpha-mysql-0-52 从节点
192.168.16.15 zhsq-mysql16-15 从节点
操作系统版本:CentOS release 6.8
开始在主节点上安装组复制:
第一个主节点(192.168.0.51)上的配置文件内容:
[root@alpha-mysql-0-51 ~]#cat /etc/my.cnf
[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION
server_id=14051
port=3306
user=mysql
character_set_server=utf8mb4
skip_name_resolve
max_connections=100
basedir=/usr/local/mysql-8.0.13
datadir=/home/mysql/data
socket=/tmp/mysql.sock
pid-file=/home/mysql/mysqld.pid
#transaction_isolation=read-committed
default_storage_engine=innodb
max_allowed_packet=128M
max_heap_table_size=64M
tmp_table_size=64M
read_buffer_size=2M
sort_buffer_size=2M
read_rnd_buffer_size=4M
open_files_limit=81920
table_open_cache=10000
table_definition_cache=10000
secure-file-priv = NULL
#secure_file_priv=''
wait_timeout=86400
default_authentication_plugin=mysql_native_password
log_error=/home/mysql/log/mysqld.err
log_timestamps=system
slow_query_log=1
slow_query_log_file=/home/mysql/slow_query.log
long_query_time=3
log_bin=/home/mysql/data/mysql-bin
binlog_format=row
binlog_row_image=minimal
binlog_rows_query_log_events
binlog_error_action=ABORT_SERVER
#expire_logs_days=1 --disabled from mysql8.0
binlog_expire_logs_seconds=86400
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=/home/mysql/data/relay-log
relay_log_recovery=ON
log_slave_updates
skip_slave_start
innodb_open_files=8000
innodb_buffer_pool_size=4G
innodb_max_dirty_pages_pct=90
innodb_buffer_pool_instances=8
innodb_buffer_pool_dump_at_shutdown=ON
innodb_lock_wait_timeout=120
innodb_io_capacity=1000
innodb_io_capacity_max=2000
innodb_flush_method=O_DIRECT
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=100M
innodb_log_buffer_size=10M
innodb_log_files_in_group=3
innodb_purge_threads=4
innodb_thread_concurrency=0
innodb_print_all_deadlocks=ON
innodb_deadlock_detect=ON
innodb_strict_mode=ON
innodb_sort_buffer_size=64M
innodb_read_io_threads=6
innodb_write_io_threads=6
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.0.51:24901"
loose-group_replication_group_seeds= "10.186.0.51:24901,192.168.0.52:24902,192.168.16.15:24903"
loose-group_replication_bootstrap_group= off
[mysql]
socket=/tmp/mysql.sock
default-character-set = utf8mb4
prompt="\\u@\\h :\\d\\r:\\m:\\s>"
[mysqldump]
socket=/tmp/mysql.sock
[mysqladmin]
socket=/tmp/mysql.sock
备注:
loose-group_replication_start_on_boot=off 这句的意思是在mysqld启动时不自动启动组复制,如果想自动启动,可以改成on
初始化mysql服务:
# mysqld --initialize-insecure --user=mysql
启动第一个节点上的mysql8.0:
# mysqld_safe --user=mysql &
[2] 27593
[root@alpha-mongo-140-51 /home/mysql]#2018-11-07T06:17:06.255849Z mysqld_safe Logging to '/home/mysql/log/mysqld.err'.
2018-11-07T06:17:06.323053Z mysqld_safe Starting mysqld daemon with databases from /home/mysql/data
创建复制用户并安装组复制插件:
mysql>set sql_log_bin=0;
Query OK, 0 rows affected (0.01 sec)
mysql>CREATE USER repl@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
备注:设置sql_log_bin=0这个步骤很重要,创建用户操作不能记录到binlog文件中,否则会报错:
[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
mysql>GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql>CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.34 sec)
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
设置白名单(局域网内机器不在一个网段时候需要设置):
mysql>set global group_replication_ip_whitelist="192.168.0.51,192.168.0.52,192.168.16.15";
Query OK, 0 rows affected (0.00 sec)
查看白名单:
mysql>show global variables like '%white%';
+--------------------------------+----------------------------------------------+
| Variable_name | Value |
+--------------------------------+----------------------------------------------+
| group_replication_ip_whitelist | 192.168.0.51,192.168.0.52,192.168.16.15 |
+--------------------------------+----------------------------------------------+
1 row in set (0.01 sec)
mysql>SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME LIKE '%group%' \G
*************************** 1. row ***************************
PLUGIN_NAME: group_replication
PLUGIN_VERSION: 1.1
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: GROUP REPLICATION
PLUGIN_TYPE_VERSION: 1.2
PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.9
PLUGIN_AUTHOR: ORACLE
PLUGIN_DESCRIPTION: Group Replication (1.1.0)
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.10 sec)
查看已安装的插件:
mysql>show plugins;
+---------------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+---------------------------------+----------+--------------------+----------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| mysqlx | ACTIVE | DAEMON | NULL | GPL |
| mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| ngram | ACTIVE | FTPARSER | NULL | GPL |
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+---------------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.01 sec)
备注:查看最后一行,group_replication ACTIVE表明组复制插件已安装。
开启第一个节点组复制:
mysql>SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
启动组复制:
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
报错了,日志里的告警信息如下:
[Warning] [MY-011682] [Repl] Plugin group_replication reported: 'Group Replication requires slave-preserve-commit-order to be set to ON when using more than 1 applier threads.'
提示需要设置参数slave-preserve-commit-order,设置此参数是为了控制Slave上的binlog提交顺序和Master上的binlog的提交顺序一样,保证GTID的顺序。
mysql>set global slave_preserve_commit_order=on;
Query OK, 0 rows affected (0.00 sec)
再次启动group replication:
root@localhost :(none)02:23:14>START GROUP_REPLICATION;
Query OK, 0 rows affected (3.47 sec)
启动成功,日志如下:
2018-11-07T14:23:22.130524+08:00 9 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2018-11-07T14:23:22.351635+08:00 259 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
查看组复制成员:
mysql>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | a0d757d3-e254-11e8-a3f7-525400bf555b | alpha-mysql-0-51 | 3306 | ONLINE | PRIMARY | 8.0.13 |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
1 row in set (0.02 sec)
创建测试数据:
mysql>create database test;
Query OK, 1 row affected (0.05 sec)
mysql>use test
Database changed
mysql>create table test(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.15 sec)
mysql>insert into test values(1,'lovepeihy');
Query OK, 1 row affected (0.07 sec)
第二个节点(从节点)配置文件:
[root@alpha-mysql-0-52 /]#cat /etc/my.cnf
[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION
server_id=14052
port=3306
user=mysql
character_set_server=utf8mb4
skip_name_resolve
max_connections=100
basedir=/usr/local/mysql-8.0.13
datadir=/home/mysql/data
socket=/tmp/mysql.sock
pid-file=/home/mysql/mysqld.pid
#transaction_isolation=read-committed
default_storage_engine=innodb
max_allowed_packet=128M
max_heap_table_size=64M
tmp_table_size=64M
read_buffer_size=2M
sort_buffer_size=2M
read_rnd_buffer_size=4M
open_files_limit=81920
table_open_cache=10000
table_definition_cache=10000
secure-file-priv = NULL
#secure_file_priv=''
wait_timeout=86400
default_authentication_plugin=mysql_native_password
log_error=/home/mysql/log/mysqld.err
log_timestamps=system
slow_query_log=1
slow_query_log_file=/home/mysql/slow_query.log
long_query_time=3
log_bin=/home/mysql/data/mysql-bin
binlog_format=row
#binlog_checksum=NONE
binlog_row_image=minimal
binlog_rows_query_log_events
binlog_error_action=ABORT_SERVER
#expire_logs_days=1 --disabled in mysql8.0
binlog_expire_logs_seconds=86400
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=8
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log=/home/mysql/data/relay-log
relay_log_recovery=ON
log_slave_updates
skip_slave_start
#key_buffer_size=4M
#bulk_insert_buffer_size=4M
#myisam_sort_buffer_size=6M
#myisam_max_sort_file_size=10G
#myisam_repair_threads=1
#myisam_recover_options=default
innodb_open_files=8000
#innodb_page_size=8192
innodb_buffer_pool_size=4G
innodb_max_dirty_pages_pct=90
#innodb_buffer_pool_dump_pct=40
innodb_buffer_pool_instances=8
#innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_dump_at_shutdown=ON
innodb_lock_wait_timeout=120
innodb_io_capacity=1000
innodb_io_capacity_max=2000
innodb_flush_method=O_DIRECT
#innodb_file_format=Barracuda
innodb_file_per_table=1
#innodb_undo_directory=/undolog/
#innodb_undo_logs=128
#innodb_undo_tablespaces=3
#innodb_undo_log_truncate=1
#innodb_max_undo_log_size=2G
#innodb_purge_rseg_truncate_frequency=128
#innodb_flush_neighbors=2
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=100M
innodb_log_buffer_size=10M
innodb_log_files_in_group=3
innodb_purge_threads=4
innodb_thread_concurrency=0
innodb_print_all_deadlocks=ON
innodb_deadlock_detect=ON
innodb_strict_mode=ON
innodb_sort_buffer_size=64M
innodb_read_io_threads=6
innodb_write_io_threads=6
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
slave_preserve_commit_order=ON
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.0.52:24902"
loose-group_replication_group_seeds= "192.168.0.51:24901,192.168.0.52:24902,192.168.16.15:24903"
loose-group_replication_bootstrap_group= off
[mysql]
socket=/tmp/mysql.sock
default-character-set = utf8mb4
prompt="\\u@\\h :\\d\\r:\\m:\\s>"
[mysqldump]
socket=/tmp/mysql.sock
[mysqladmin]
socket=/tmp/mysql.sock
初始化第二个节点服务:
# mysqld --initialize-insecure --user=mysql
启动第二个节点上的mysql8.0:
# mysqld_safe --user=mysql &
重复第一个节点创建用户并安装组复制插件的步骤:
mysql>set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql>CREATE USER repl@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql>GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql>CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.28 sec)
mysql>set global group_replication_ip_whitelist="192.168.0.51,192.168.0.52,192.168.16.15";
Query OK, 0 rows affected (0.00 sec)
mysql>show variables like '%white%';
+--------------------------------+----------------------------------------------+
| Variable_name | Value |
+--------------------------------+----------------------------------------------+
| group_replication_ip_whitelist | 192.168.0.51,192.168.0.52,192.168.16.15 |
+--------------------------------+----------------------------------------------+
1 row in set (0.01 sec)
root@localhost :(none)02:37:26>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
root@localhost :(none)02:37:37>START GROUP_REPLICATION;
Query OK, 0 rows affected (4.05 sec)
root@localhost :(none)02:37:59>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | a0d757d3-e254-11e8-a3f7-525400bf555b | alpha-mysql-0-51 | 3306 | ONLINE | PRIMARY | 8.0.13 |
| group_replication_applier | ff85c59b-e256-11e8-9c48-52540098ed65 | alpha-mysql-0-52 | 3306 | ONLINE | SECONDARY | 8.0.13 |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.09 sec)
查看主节点创建的数据:
root@localhost :(none)02:46:38>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.04 sec)
root@localhost :test02:46:42>show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
root@localhost :test02:46:45>desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
root@localhost :test02:46:47>select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | lovepeihy |
+----+-----------+
1 row in set (0.00 sec)
第三个节点按照第二个节点同样的方法做:
最终查出来的结果如下:
root@localhost :test03:59:14>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 9f9cf39f-e262-11e8-9394-525400a6c4f1 | zhsq-mysql16-15 | 3306 | ONLINE | SECONDARY | 8.0.13 |
| group_replication_applier | d6235934-e261-11e8-b243-52540098ed65 | alpha-mysql-0-52 | 3306 | ONLINE | SECONDARY | 8.0.13 |
| group_replication_applier | da5643f1-e25f-11e8-b0ee-525400bf555b | alpha-mysql-0-51 | 3306 | ONLINE | PRIMARY | 8.0.13 |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
注意:如果在从节点上插入数据,会报错:
mysql>insert into test select * from test;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
至此,MySQL 8.0.13的组复制安装完毕。
补充:
mysql的组复制配置有关的参数解释:
gtid_mode=ON ###是否打开GTID模式
enforce_gtid_consistency=ON ###是否强制事务一致
binlog_checksum=NONE ###是否开启binlog校验功能,设置成不开启
slave_preserve_commit_order=ON ###控制Slave上的binlog提交顺序和Master上的binlog的提交顺序一样,保证GTID的顺序
transaction_write_set_extraction=XXHASH64 ###开启主键信息采集功能,8.0.2开始默认值为XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" ###设置组名,随便起,但是不能与UUID重复
loose-group_replication_start_on_boot=off ###MySQL SERVER启动时不自动启动组复制
loose-group_replication_local_address= "192.168.0.51:24901" ###设置成员的本地地址,后面端口号为组复制的端口号
loose-group_replication_group_seeds= "192.186.140.51:24901,192.168.0.52:24902,192.168.16.15:24903" ###设置种子成员的地址,有几台机器设置几个
loose-group_replication_bootstrap_group= off ###配置是否自动引导组
感谢各位的阅读,以上就是“MySQL8.0.13组复制的安装步骤”的内容了,经过本文的学习后,相信大家对MySQL8.0.13组复制的安装步骤这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。