Mysql-MGR-router安装配置

数据库 , 评论

Mysql-MGR-router安装配置

环境

1
2
3
4
5
6
7
8
10.77.114.192  mgr01
10.77.114.192  mgr02
10.77.114.192  mgr03

mysql 8.0.34
mysql shell 8.0.34
mysql router 8.0.34
centos 7.9

操作系统配置

包括防火墙、selinux、内核优化参数等
（略）

安装mysqlserver

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
## 卸载mariadb
rpm -e $(rpm -qa |grep maria) --nodeps

## rpm 安装
yum localinstall -y $RPM_PATH/mysql-community-common-8*.rpm
yum localinstall -y $RPM_PATH/mysql-community-client-plugins-8*.rpm
yum localinstall -y $RPM_PATH/mysql-community-libs-8*.rpm
yum localinstall -y $RPM_PATH/mysql-community-libs-compat-8*.rpm
yum localinstall -y $RPM_PATH/mysql-community-client-8*.rpm
yum localinstall -y $RPM_PATH/mysql-community-icu-data-files-8*.rpm
yum localinstall -y $RPM_PATH/mysql-community-server-8*.rpm

## 启动mysql
systemctl enable mysqld
systemctl start mysqld

## 拿到初始密码，并修改密码
mysql_passwd=$(grep password /var/log/mysqld.log | awk -F'root@localhost: ' '{print $2}'|tail -1) 

cat <<'EOF' > /tmp/temp.sql
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Abc123!!!';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
flush privileges;
EOF

mysql -uroot -p$mysql_passwd -b --connect-expired-password < /tmp/temp.sql
rm -rf /tmp/temp.sql

## copy data file, copy my.cnf
systemctl stop mysqld
\cp -f $INSTALL_PATH/other/my.cnf  /etc/my.cnf

if [ -d /data/mysql ]; then mv /data/mysql /data/mysql_$(date +"%Y%m%d%H%M%S"); fi
mv /var/lib/mysql /data/mysql ; chown -R mysql:mysql /data/mysql

配置文件my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137

[client]
#default-character-set = utf8mb4
host = localhost
user = root
port = 33066
password = Abc123!!!
socket=/data/mysql/mysql.sock

[mysqld]
# basic settings #
default-storage-engine= InnoDB
server_id = 1
#basedir = /app/mysql8.0.34
datadir = /data/mysql/
socket =  /data/mysql/mysql.sock
pid_file = /data/mysql/mysqld.pid
port = 33066
default-time_zone = '+8:00'
character_set_server = utf8mb4
explicit_defaults_for_timestamp = 1
autocommit = 1
transaction_isolation = READ-COMMITTED
#secure_file_priv = "/data/mysql/"
max_allowed_packet = 64M
lower_case_table_names = 0
default_authentication_plugin = mysql_native_password
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

# connection #
back_log = 500
interactive_timeout = 300
wait_timeout = 1800
lock_wait_timeout = 300
max_user_connections = 800
skip_name_resolve = 1
max_connections = 2500
max_connect_errors = 1000

#table cache performance settings
#table_open_cache = 1024
#table_definition_cache = 1024
#table_open_cache_instances = 16

#session memory settings #
#read_buffer_size = 16M
#read_rnd_buffer_size = 32M
#sort_buffer_size = 32M
#tmp_table_size = 64M
#join_buffer_size = 128M
#thread_cache_size = 256

# log settings #
slow_query_log = ON
slow_query_log_file = /data/mysql/slow.log
log_error = /data/mysql/mysqld.log
log_error_verbosity = 3
#log_bin = /data/mysqldb/binlog/mysql_bin
#log_bin_index = /data/mysqldb/binlog/mysql_binlog.index
# general_log_file = /data/mysql/generallog/general.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
#log_slow_slave_statements = 1
#expire_logs_days = 15
binlog_expire_logs_seconds = 2592000
long_query_time = 5
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 1000
#log_bin_trust_function_creators = 1
log_replica_updates = 1
mysqlx_port = 33060
#mysqlx_socket = /data/mysqldb/socket/mysqlx.sock

# innodb settings #
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 4
innodb_log_buffer_size = 100M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 20
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_purge_threads = 4
innodb_thread_concurrency = 200
innodb_print_all_deadlocks = 1
innodb_print_ddl_logs = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 32M
innodb_write_io_threads = 16
innodb_read_io_threads = 16 
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 1G
innodb_open_files = 4096
innodb_buffer_pool_dump_pct = 25
innodb_page_cleaners = 16
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
innodb_flush_log_at_trx_commit = 1
log_timestamps = UTC

# replication settings #
sync_binlog = 1
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log_recovery = 1
#relay_log = /data/mysqldb/relaylog/relay.log
#relay_log_index = /data/mysqldb/relaylog/mysql_relay.index
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
binlog_gtid_simple_recovery = 1
replica_preserve_commit_order = 1
binlog_rows_query_log_events = 1
replica_transaction_retries = 10

# group replication settings
transaction_write_set_extraction = XXHASH64
binlog_checksum = NONE
loose_group_replication_group_name = "957e8af0-bc63-11ea-bb19-005056a52572"
loose_group_replication_start_on_boot = off
loose_group_replication_local_address = "10.77.114.192:24901"
loose_group_replication_group_seeds = "10.77.114.192:24901,10.77.114.193:24901,10.77.114.194:24901"
loose_group_replication_bootstrap_group = off
loose_group_replication_ip_whitelist = "10.77.114.0/24"
loose_group_replication_member_expel_timeout=30
report_host = 10.77.114.192
report_port = 33066
#read_only=1
#super_read_only=1
binlog_transaction_dependency_tracking = WRITESET

每个节点需要更改的是

1
2
3
server_id = 1
loose_group_replication_local_address = "10.77.114.192:24901"
report_host = 10.77.114.192

启动mysql

1
systemctl start mysqld

开启组复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
## 安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
show plugins;

## node1 执行
SET SQL_LOG_BIN=0;
CREATE USER rep@'%' IDENTIFIED BY 'Abc123!!!';
GRANT REPLICATION SLAVE ON *.* TO rep@'%';
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rep', MASTER_PASSWORD='Abc123!!!' FOR CHANNEL 'group_replication_recovery';

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

## node2/node3 执行

SET SQL_LOG_BIN=0;
CREATE USER rep@'%' IDENTIFIED BY 'Abc123!!!';
GRANT REPLICATION SLAVE ON *.* TO rep@'%';
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rep', MASTER_PASSWORD='Abc123!!!' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;

## 成功后查询，应该可以看到3个节点
SELECT * FROM performance_schema.replication_group_members;

安装mysql-shell

1
2
3
4
5
6
7
8
9
10
yum localinstall -y mysql-shell-8*.rpm

## node1登录
mysqlsh root@10.77.114.192:33066 --py

dba.create_cluster('mysqlrouter')

## 查看集群状态
c=dba.get_cluster()
c.status()

安装mysql-router

1
2
3
4
5
6
7
8
9
10
11
12
13
## rpm安装
yum localinstall -y mysql-router-8*.rpm

## 初始化
mysqlrouter  --bootstrap root@10.77.114.192:33066 --force

## 重启
systemctl enable mysqlrouter
systemctl restart mysqlrouter

## 链接测试
mysql -u root -p 10.77.114.192 6446  //读写
mysql -u root -p 10.77.114.192 6447  //只读

总结

至此，一个高可用，单主的mysql mgr集群已搭建完毕，应用可按需链接读写6446或只读 6447，也可将3台机器安装mysql-router，使用nginx反向代理

biglovewheat老年佛系运维

老年佛系运维 | biglovewheat@126.com