MySQL8.0.13组复制的安装步调
发布时间：2022-01-18 15:29:24 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了MySQL8.0.13组复制的安装步骤，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习MySQL8.0.13组复制的安装步骤吧！ 环境描述: 主机: 192.168.0.51 alpha-mysql-0-51 主节点 192.168.0.52 al
|
这篇文章主要讲解了“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 :dr: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 :dr: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 & （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
