mysql8 mgr集群+proxysql读写分离
修改服务器主机名并写入/etc/host,每台执行,如果有多台依次往后加
hostnamectl set-hostname s1
hostnamectl set-hostname s2
hostnamectl set-hostname s3
echo'192.168.2.233 s1
192.168.2.234 s2
192.168.2.235 s3'>>/etc/hosts引导组部署
创建mysql用户
groupadd -g 1000 mysql
useradd -r -s /sbin/nologin -u 1000 -g mysql mysql
MySQL环境变量配置
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
创建mysql数据目录
mkdir -p /mysql/data
chown -R mysql.mysql /mysql
解压mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz到/usr/local并重命名为mysql
tar -xvf mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz -C /usr/local
mv mysql-8.0.36-linux-glibc2.12-x86_64 mysql
mysql配置文件/etc/my.cnf
[mysqld]
max_connections = 1024
max_allowed_packet = 1024M
default-authentication-plugin=mysql_native_password
default_time_zone = "+8:00"
admin_address = '127.0.0.1'
admin_port = 33062
character_set_server = UTF8MB4
log_error = /mysql/data/mysqld.log
socket = /mysql/data/mysql.sock
datadir = /mysql/data
slow_query_log = 1
slow_query_log_file = /mysql/data/slow.log
long_query_time = 6
log-bin = binlog
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
#group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#group_replication_start_on_boot=ofalseff
#group_replication_local_address= "s1:33061"
#group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
#group_replication_bootstrap_group=off
[mysql]
socket = /mysql/data/mysql.sock
no-auto-rehash
prompt="\\u@\\h:\\d \\r:\\m:\\s>"
设置mysql自启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
mysql数据初始化
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data
在mysql配置文件 /etc/my.cnf中[mysqld]下添加skip-grant-tables = 1使用无密码登录
启动mysql
service mysqld start
登陆mysql，然后flush privileges，并修改密码，重置binglog日志改密码:
alter user root@'localhost' identified by 'koala123';
reset master
删除/etc/my.cnf中[mysqld]下添加skip-grant-tables = 1并重启mysql
service mysqld restart
创建复制账号
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Koala123';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
打开mysql配置文件里面的注释项(以下配置)并重启mysql
#group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#group_replication_start_on_boot=off
#group_replication_local_address= "s1:33061"
#group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
#group_replication_bootstrap_group=off
service mysqld restart
启动引导组复制
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;实例1
创建mysql用户
groupadd -g 1000 mysql
useradd -r -s /sbin/nologin -u 1000 -g mysql mysql
MySQL环境变量配置
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
创建mysql数据目录
mkdir -p /mysql/data
chown -R mysql.mysql
在mysql配置文件 /etc/my.cnf中[mysqld]下添加skip-grant-tab/mysql
解压mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz到/usr/local并重命名为mysql
tar -xvf mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz -C /usr/local
mv mysql-8.0.36-linux-glibc2.12-x86_64 mysql
mysql配置文件/etc/my.cnf
[mysqld]
max_connections = 1024
max_allowed_packet = 1024M
default-authentication-plugin=mysql_native_password
default_time_zone = "+8:00"
admin_address = '127.0.0.1'
admin_port = 33062
character_set_server = UTF8MB4
log_error = /mysql/data/mysqld.log
socket = /mysql/data/mysql.sock
datadir = /mysql/data
slow_query_log = 1
slow_query_log_file = /mysql/data/slow.log
long_query_time = 6
log-bin = binlog
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
在mysql配置文件 /etc/my.cnf中[mysqld]下添加skip-grant-tab
enforce_gtid_consistency=ON
#group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#group_replication_start_on_boot=off
#group_replication_local_address= "s2:33061"
#group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
#group_replication_bootstrap_group=off
[mysql]
socket = /mysql/data/mysql.sock
no-auto-rehash
prompt="\\u@\\h:\\d \\r:\\m:\\s>"
设置mysql自启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
mysql数据初始化
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data
在mysql配置文件 /etc/my.cnf中[mysqld]下添加skip-grant-tables = 1使用无密码登录
启动mysql
service mysqld start
登陆mysql，然后flush privileges，并修改密码，重置binglog日志改密码:
alter user root@'localhost' identified by 'koala123';
reset master
删除/etc/my.cnf中[mysqld]下添加skip-grant-tables = 1并重启mysql
service mysqld restart删除/etc/my.cnf中[mysqld]下添加skip-grant-tables = 1并重启mysql
service mysqld restart
创建复制账号
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Koala123';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
打开mysql配置文件里面的注释项(下配置)并重启mysql
#group_replication_group_name="aaafalseaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#group_replication_start_on_boot=off
#group_replication_local_address= "s2:33061"
#group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
#group_replication_bootstrap_group=off
service mysqld restart
启动组复制
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='Koala123' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;实例2
创建mysql用户
groupadd -g 1000 mysql
useradd -r -s /sbin/nologin -u 1000 -g mysql mysql
MySQL环境变量配置
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
mysql mgr 多主集群搭建
创建mysql数据目录
mkdir -p /mysql/data
chown -R mysql.mysql /mysql
解压mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz到/usr/local并重命名为mysql
tar -xvf mysql-8.0.36-linux-glibc2.12-x86_64.tar.xz -C /usr/local
mv mysql-8.0.36-linux-glibc2.12-x86_64 mysql
mysql配置文件/etc/my.cnf
[mysqld]
max_connections = 1024
max_allowed_packet = 1024M
default-authentication-plugin=mysql_native_password
default_time_zone = "+8:00"
admin_address = '127.0.0.1'
admin_port = 33062
character_set_server = UTF8MB4
log_error = /mysql/data/mysqld.log
socket = /mysql/data/mysql.sock
datadir = /mysql/data
slow_query_log = 1
slow_query_log_file = /mysql/data/slow.log
long_query_time = 6
log-bin = binlog
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
#group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#group_replication_start_on_boot=off
#group_replication_local_address= "s3:33061"
#group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
#group_replication_bootstrap_group=off
[mysql]
socket = /mysql/data/mysql.sock
no-auto-rehash
prompt="\\u@\\h:\\d \\r:\\m:\\s>"
设置mysql自启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
mysql数据初始化
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data
在mysql配置文件 /etc/my.cnf中[mysqld]下添加skip-grant-tables = 1使用无密码登录
启动mysql
service mysqld start
登陆mysql，然后flush privileges,并修改密码，重置binglog日志改密码:
alter user root@'localhost' identified by 'koala123';
reset master
删除/etc/my.cnf中[mysqld]下添加skip-grant-tables = 1并重启mysql
service mysqld restart
创建复制账号
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Koala123';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
GRANT CONNECTION_ADMIN ON *.* TO rpl_user@'%';
GRANT BACKUP_ADMIN ON *.* TO rpl_user@'%';
GRANT GROUP_REPLICATION_STREAM ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
安装组复制插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
打开mysql配置文件里面的注释项(以下配置)并重启mysql
#group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#group_replication_start_on_boot=off
#group_replication_local_address= "s3:33061"
#group_replication_group_seeds= "s1:33061,s2:33061,s3:33061"
#group_replication_bootstrap_group=off
service mysqld restart
启动组复制
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpl_user', SOURCE_PASSWORD='Koala123' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;查看集群成员
SELECT * FROM performance_schema.replication_group_members;
切换到多主模式(可选如果需要多主集群)
MGR切换模式需要重新启动组复制，因些需要在所有节点上先关闭组复制，设置 group_replication_single_primary_mode=OFF 等参数，再启动组复制。
# 停止组复制(所有节点执行)：
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
# 随便选择某个节点执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他节点执行
START GROUP_REPLICATION;
# 查看组信息，所有节点的 MEMBER_ROLE 都为 PRIMARY
SELECT * FROM performance_schema.replication_group_members;切回单主模式(可选如果切换过多主模式)
# 所有节点执行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;proxysql 建立%用户但提示ip密码不对
# 主节点（192.168.56.101）执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 从节点（192.168.56.102、192.168.56.103）执行
START GROUP_REPLICATION;
# 查看MGR组信息
SELECT * FROM performance_schema.replication_group_members;创建proxysql用户
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@22_33_00";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@22_33_00";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;proxysql配置
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key
EOF
yum install proxysql
proxysql配置
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
threads=10
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
admin-hash_passwords=true
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor@22_33_00"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
-- 在MySQL命令行或者直接在ProxySQL管理接口中执行
-- 添加主服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 's1', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, 's2', 3306);
-- 设置主服务器的权重，确保负载均衡
UPDATE mysql_servers SET weight=1 WHERE hostname = 's1';
UPDATE mysql_servers SET weight=1 WHERE hostname = 's2';
-- 设置写节点的状态为ONLINE
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- 添加从服务器到主机组1
INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup) VALUES (1, 2);
-- 设置从服务器的权重，确保负载均衡
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 's3', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (2, 'slave2_ip', 3306);
UPDATE mysql_servers SET weight=1 WHERE hostname = 's3';
UPDATE mysql_servers SET weight=1 WHERE hostname = 'slave2_ip';
-- 应用配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- 设置读写分离的规则
INSERT INTO mysql_query_rules(active, match_digest, destination_hostgroup) VALUES (1, '^SELECT.*FOR UPDATE$', 1);
INSERT INTO mysql_query_rules(active, match_digest, destination_hostgroup) VALUES (1, '^SELECT', 2);
-- 应用配置
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
INSERT INTO mysql_users(username, password, default_hostgroup, active) VALUES('proxysql', 'proxysql', 1, 1);
load mysql users to runtime;
save mysql users to disk;- 感谢你赐予我前进的力量
-
微信 
支付宝
赞赏者名单
因为你们的支持让我意识到写文章的价值🙏
本文是原创文章，采用 CC BY-NC-ND 4.0 协议，完整转载请注明来自 运维小白
阅读建议
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果
