MySQL 8.0.34 MGR部署教程
基于MySQL 8.0.34 MGR部署
一、环境概述
| OS | IP | Hostname | MySQL Version | DB Port | MGR Port |
| redhat 7.9 | 172.20.10.3 | s1 | 8.0.34 | 3306 | 33061 |
| redhat 7.9 | 172.20.10.4 | s2 | 8.0.34 | 3306 | 33061 |
| redhat 7.9 | 172.20.10.5 | s3 | 8.0.34 | 3306 | 33061 |
二、在各个节点安装MySQL数据库
1）卸载自带mysql、或mariadb
rpm -qa | grep mysql rpm -qa | grep mariadb rpm -e --nodeps 软件名
2）配置hosts
172.20.10.3 s1 172.20.10.4 s2 172.20.10.5 s3
3）关闭防火墙，并设置为不自动启动
systemctl stop firewalld
systemctl disable firewalld
4）创建mysql组和用户
groupadd mysql useradd -r -g mysql -s /sbin/false mysql
5）开始安装mysql
安装前准备工作
将mysql上传至/soft路径下并解压 [root@s1 soft]# tar -xvf mysql-8.0.34-linux-glibc2.17-x86_64.tar.gz [root@s1 soft]# mv mysql-8.0.34-linux-glibc2.17-x86_64 mysql [root@s1 mysql]# mkdir -p mysql/{data,logs} [root@s1 mysql]# chown -R mysql:mysql /soft/mysql [root@s1 mysql]# chmod -R 755 /soft/mysql
编写my.cnf配置文件
[root@s1 mysql]# vim /etc/my.cnf 添加如下代码 [mysql] socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 basedir=/soft/mysql datadir=/soft/mysql/data socket=/tmp/mysql.sock log_timestamps=system log_error=/soft/mysql/logs/error.log disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,MEMORY sql_require_primary_key=ON report_host=172.20.10.3 report_port=3306 server_id =330603 log_bin =/soft/mysql/logs/mysql-bin binlog_format =ROW gtid_mode =ON enforce_gtid_consistency =ON log_slave_updates =ON master_info_repository =TABLE relay_log_info_repository =TABLE binlog_transaction_dependency_tracking =WRITESET super_read_only =ON transaction_write_set_extraction =XXHASH64 replica_parallel_type =LOGICAL_CLOCK replica_parallel_workers =4 replica_preserve_commit_order =ON plugin_load_add =group_replication.so loose_group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose_group_replication_start_on_boot =OFF loose_group_replication_local_address =172.20.10.3:33061 loose_group_replication_group_seeds =172.20.10.3:33061,172.20.10.4:33061,172.20.10.5:33061 loose_group_replication_bootstrap_group =OFF loose_group_replication_recovery_get_public_key =ON
初始化数据库
[root@s1 mysql]# /soft/mysql/bin/mysqld --initialize --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data
创建软链接并启动数据库
[root@s1 mysql]# ln -vs /soft/mysql/support-files/mysql.server /etc/init.d/mysqld [root@s1 mysql]# /etc/init.d/mysqld start
环境变量配置
[root@s2 mysql]# vim ~/.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin:/soft/mysql/bin export PATH
[root@s2 mysql]# source ~/.bash_profile
登陆mysql修改初始密码
初始密码存放于log_error中定义的文件里面 [root@s1 data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.34 Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter user 'root'@'localhost' identified by 'root@123'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@s1 data]#
到这里mysql就已经安装完毕了，另外2个节点略过
节点2参数
[mysql] socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 basedir=/soft/mysql datadir=/soft/mysql/data socket=/tmp/mysql.sock log_timestamps=system log_error=/soft/mysql/logs/error.log disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,MEMORY sql_require_primary_key=ON report_host=172.20.10.4 report_port=3306 server_id =330604 log_bin =/soft/mysql/logs/mysql-bin binlog_format =ROW gtid_mode =ON enforce_gtid_consistency =ON log_slave_updates =ON master_info_repository =TABLE relay_log_info_repository =TABLE binlog_transaction_dependency_tracking =WRITESET super_read_only =ON transaction_write_set_extraction =XXHASH64 replica_parallel_type =LOGICAL_CLOCK replica_parallel_workers =4 replica_preserve_commit_order =ON plugin_load_add =group_replication.so loose_group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose_group_replication_start_on_boot =OFF loose_group_replication_local_address =172.20.10.4:33061 loose_group_replication_group_seeds =172.20.10.3:33061,172.20.10.4:33061,172.20.10.3:33061 loose_group_replication_bootstrap_group =OFF loose_group_replication_recovery_get_public_key =ON
节点3参数
[mysql] socket=/tmp/mysql.sock [mysqld] user=mysql port=3306 basedir=/soft/mysql datadir=/soft/mysql/data socket=/tmp/mysql.sock log_timestamps=system log_error=/soft/mysql/logs/error.log disabled_storage_engines=MyISAM,BLACKHOLE,FEDERATED,MEMORY sql_require_primary_key=ON report_host=172.20.10.5 report_port=3306 server_id =330605 log_bin =/soft/mysql/logs/mysql-bin binlog_format =ROW gtid_mode =ON enforce_gtid_consistency =ON log_slave_updates =ON master_info_repository =TABLE relay_log_info_repository =TABLE binlog_transaction_dependency_tracking =WRITESET super_read_only =ON transaction_write_set_extraction =XXHASH64 replica_parallel_type =LOGICAL_CLOCK replica_parallel_workers =4 replica_preserve_commit_order =ON plugin_load_add =group_replication.so loose_group_replication_group_name ="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose_group_replication_start_on_boot =OFF loose_group_replication_local_address =172.20.10.5:33061 loose_group_replication_group_seeds =172.20.10.3:33061,172.20.10.4:33061,172.20.10.5:33061 loose_group_replication_bootstrap_group =OFF loose_group_replication_recovery_get_public_key =ON
三、配置MGR复制组
1）查看插件是否都正常加载了
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 | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | 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 | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ndbcluster | DISABLED | STORAGE ENGINE | NULL | GPL | | ndbinfo | DISABLED | STORAGE ENGINE | NULL | GPL | | ndb_transid_mysql_connection_map | DISABLED | INFORMATION SCHEMA | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------------+----------+--------------------+----------------------+---------+ 49 rows in set (0.00 sec) mysql>
2）初始化MGR复制组
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.11 sec) mysql> set global group_replication_bootstrap_group=off; Query OK, 0 rows affected (0.00 sec) mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+--------------+-------------+ | member_id | member_host | member_port | member_state | member_role | +--------------------------------------+-------------+-------------+--------------+-------------+ | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 | 3306 | ONLINE | PRIMARY | +--------------------------------------+-------------+-------------+--------------+-------------+ 1 row in set (0.00 sec)
3）创建用于复制的用户
mysql> create user 'repl'@'%' identified with 'mysql_native_password' by 'repl@123'; Query OK, 0 rows affected (0.01 sec) mysql> grant replication slave,backup_admin on *.* to 'repl'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
4）配置复制通道
mysql> change master to master_user='repl',master_password='repl@123' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 5 warnings (0.01 sec) mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+--------------+-------------+ | member_id | member_host | member_port | member_state | member_role | +--------------------------------------+-------------+-------------+--------------+-------------+ | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 | 3306 | ONLINE | PRIMARY | +--------------------------------------+-------------+-------------+--------------+-------------+ 1 row in set (0.00 sec)
四、为MGR添加新的成员
在节点2、节点3上分别执行一下步骤，将节点加入MGR复制组内
mysql> change master to master_user='repl',master_password='repl@123' for channel 'group_replication_recovery'; Query OK, 0 rows affected, 5 warnings (0.01 sec) mysql> start group_replication; Query OK, 0 rows affected (1.73 sec) mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+--------------+-------------+ | member_id | member_host | member_port | member_state | member_role | +--------------------------------------+-------------+-------------+--------------+-------------+ | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 | 3306 | ONLINE | PRIMARY | | 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 | 3306 | ONLINE | SECONDARY | +--------------------------------------+-------------+-------------+--------------+-------------+ 2 rows in set (0.00 sec)
五、测试数据同步，若前面配置无任何问题，在1节点上写入的数据，在2、3节点均可看到，自行测试（略）
六、MGR复制组模式切换
1）单主模式切换多主模式
mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+--------------+-------------+ | member_id | member_host | member_port | member_state | member_role | +--------------------------------------+-------------+-------------+--------------+-------------+ | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 | 3306 | ONLINE | PRIMARY | | 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 | 3306 | ONLINE | SECONDARY | | 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 | 3306 | ONLINE | SECONDARY | +--------------------------------------+-------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) mysql> select group_replication_switch_to_multi_primary_mode(); +--------------------------------------------------+ | group_replication_switch_to_multi_primary_mode() | +--------------------------------------------------+ | Mode switched to multi-primary successfully. | +--------------------------------------------------+ 1 row in set (0.00 sec) mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+--------------+-------------+ | member_id | member_host | member_port | member_state | member_role | +--------------------------------------+-------------+-------------+--------------+-------------+ | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 | 3306 | ONLINE | PRIMARY | | 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 | 3306 | ONLINE | PRIMARY | | 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 | 3306 | ONLINE | PRIMARY | +--------------------------------------+-------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec)
2）多主模式切换单主模式
mysql> select group_replication_switch_to_single_primary_mode('141e2b7e-6e25-11ee-9483-0800276dee47'); +-----------------------------------------------------------------------------------------+ | group_replication_switch_to_single_primary_mode('141e2b7e-6e25-11ee-9483-0800276dee47') | +-----------------------------------------------------------------------------------------+ | Mode switched to single-primary successfully. | +-----------------------------------------------------------------------------------------+ 1 row in set (0.02 sec) mysql> select member_id,member_host,member_port,member_state,member_role from performance_schema.replication_group_members; +--------------------------------------+-------------+-------------+--------------+-------------+ | member_id | member_host | member_port | member_state | member_role | +--------------------------------------+-------------+-------------+--------------+-------------+ | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 | 3306 | ONLINE | PRIMARY | | 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 | 3306 | ONLINE | SECONDARY | | 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 | 3306 | ONLINE | SECONDARY | +--------------------------------------+-------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec)
查看切换进度
select event_name,work_completed,work_estimated from performance_schema.events_stages_current where event_name like '%stage/group_rpl%' \G;
七、MGR复制组维护
1）监控MGR复制组
监控主要基于performance_schema中的6张表实现：
replication_group_members
replication_group_member_stats
replication_connection_status
replication_applier_status
replication_applier_status_by_coordinator
replication_applier_status_by_worker
查看组成员信息
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ | group_replication_applier | 141e2b7e-6e25-11ee-9483-0800276dee47 | 172.20.10.3 | 3306 | ONLINE | PRIMARY | 8.0.34 | XCom | | group_replication_applier | 2f1a648d-6e28-11ee-939d-080027602436 | 172.20.10.5 | 3306 | ONLINE | SECONDARY | 8.0.34 | XCom | | group_replication_applier | 40b9ee07-6e27-11ee-9fb7-080027f800a3 | 172.20.10.4 | 3306 | ONLINE | SECONDARY | 8.0.34 | XCom | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+ 3 rows in set (0.00 sec)
2）查看组复制的状态
mysql> select * from performance_schema.replication_group_member_stats\G; *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 16976984379765337:3 MEMBER_ID: 141e2b7e-6e25-11ee-9483-0800276dee47 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-18 LAST_CONFLICT_FREE_TRANSACTION: COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 3 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 16976984379765337:3 MEMBER_ID: 2f1a648d-6e28-11ee-939d-080027602436 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-18 LAST_CONFLICT_FREE_TRANSACTION: COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 0 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 16976984379765337:3 MEMBER_ID: 40b9ee07-6e27-11ee-9fb7-080027f800a3 COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 0 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-18 LAST_CONFLICT_FREE_TRANSACTION: COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 1 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 3 rows in set (0.00 sec) ERROR: No query specified
3）查看secondary节点延迟情况
mysql> select case when min_commit_timstamp is null then 0 -> else unix_timestamp(now(6))-unix_timestamp(min_commit_timstamp) -> end as seconds_behind_master -> from ( -> select min(applying_transaction_original_commit_timestamp) as min_commit_timstamp -> from performance_schema.replication_applier_status_by_worker -> where applying_transaction<>'') t; +-----------------------+ | seconds_behind_master | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.01 sec)
4）查看内存使用情况
mysql> select name,enabled from performance_schema.setup_instruments where name like 'memory/group_rpl%'; +-------------------------------------------------------------------+---------+ | name | enabled | +-------------------------------------------------------------------+---------+ | memory/group_rpl/write_set_encoded | YES | | memory/group_rpl/certification_data | YES | | memory/group_rpl/certification_data_gc | YES | | memory/group_rpl/certification_info | YES | | memory/group_rpl/transaction_data | YES | | memory/group_rpl/sql_service_command_data | YES | | memory/group_rpl/mysql_thread_queued_task | YES | | memory/group_rpl/message_service_queue | YES | | memory/group_rpl/message_service_received_message | YES | | memory/group_rpl/group_member_info | YES | | memory/group_rpl/consistent_members_that_must_prepare_transaction | YES | | memory/group_rpl/consistent_transactions | YES | | memory/group_rpl/consistent_transactions_prepared | YES | | memory/group_rpl/consistent_transactions_waiting | YES | | memory/group_rpl/consistent_transactions_delayed_view_change | YES | | memory/group_rpl/GCS_XCom::xcom_cache | YES | | memory/group_rpl/Gcs_message_data::m_buffer | YES | +-------------------------------------------------------------------+---------+ 17 rows in set (0.00 sec)
以上就是搭建MGR的相关操作！
