mysql-mgr+mysqlshell+mysqlrouter

2022-01-25 mysql 评论

初始化单实例：

安装包涉及：mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz,mysql-router-8.0.26-linux-glibc2.12-x86_64.tar，mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
mkdir /mysqldb
groupadd mysql
useradd -u 54321 -g mysql -G wheel mysql
echo “Ora-.2520” | passwd –stdin mysql &> /dev/null
yum install sshpass -y
sshpass -p ‘HN-hepc@2020’ scp -P 10022 -r /soft/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz cluster2:/mysqldb
sshpass -p ‘HN-hepc@2020’ scp -P 10022 -r /soft/mysql-router-8.0.26-linux-glibc2.12-x86_64.tar cluster2:/mysqldb
sshpass -p ‘HN-hepc@2020’ scp -P 10022 -r /soft/mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz cluster2:/mysqldb

xz -d /mysqldb/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
tar -xvf /mysqldb/mysql-8.0.26-linux-glibc2.12-x86_64.tar -C /mysqldb
mv /mysqldb/mysql-8.0.26-linux-glibc2.12-x86_64 /usr/local/mysql
mkdir -p /mysqldb/mysql13306/data/
mkdir -p /mysqldb/mysql13306/binlog/
mkdir -p /mysqldb/mysql13306/plugin/
cp /usr/local/mysql/lib/plugin/group_replication.so /mysqldb/mysql13306/plugin/
cp /usr/local/mysql/lib/plugin/mysql_clone.so /mysqldb/mysql13306/plugin/

chmod -R 775 /mysqldb/
chown -R mysql:mysql /mysqldb

[mysqld_safe]
log-error=/mysqldb/mysql13306/mysqld.err
pid-file=/mysqldb/mysql13306/mysqld.pid
EOF

chmod 664 /etc/my13306.cnf
cat /etc/my13306.cnf
初始化单实例，并记录密码：
/usr/local/mysql/bin/mysqld –defaults-file=/etc/my13306.cnf –user=mysql –initialize

touch /mysqldb/mysql13306/mysqld.err
chown mysql:mysql /mysqldb/mysql13306/mysqld.err
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my13306.cnf &

cat >> /root/.bash_profile << EOF
export MYSQL_BASE=/usr/local/mysql
export PATH=$PATH:$MYSQL_BASE/bin
EOF
source /root/.bash_profile
tw:q!0a5lGp?
555jBBq5qi-U
gjr)z_w<P3HI

/usr/local/mysql/bin/mysql -uroot -p’!3qbVv%dzw9t’ –socket=/tmp/mysql13306.sock
flush privileges;
alter user ‘root‘@’localhost’ identified by “Mydb@2020”;
update mysql.user set host=’%’ where user=’root’;
flush privileges;
exit;
数据库关闭：
ps -ef | grep mysql| grep -v grep | awk ‘{print “kill -9 “$2}’ | sh
数据库开启：
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my13306.cnf &
/usr/local/mysql/bin/mysql -h127.0.0.1 -P13306 -uroot -p‘Mydb@2020’
system cp /usr/local/mysql/lib/plugin/group_replication.so /mysqldb/mysql13306/plugin/
system cp /usr/local/mysql/lib/plugin/mysql_clone.so /mysqldb/mysql13306/plugin/
安装插件：
INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;
INSTALL PLUGIN CLONE SONAME “mysql_clone.so”;

以上是所有节点都需执行

–主库添加相关账号和授权
CREATE USER clone_user@’%’ IDENTIFIED BY “clone_pass”;
GRANT BACKUP_ADMIN ON . to clone_user;
GRANT SELECT ON performance_schema.* TO clone_user;
GRANT EXECUTE ON . to clone_user;
flush privileges;

其余节点：
CREATE USER clone_user@’%’ IDENTIFIED BY “clone_pass”;
GRANT CLONE_ADMIN ON . to clone_user;
GRANT SELECT ON performance_schema.* TO clone_user;
GRANT EXECUTE ON . to clone_user;
SET GLOBAL clone_valid_donor_list = “172.26.160.198:13306”;
SHOW VARIABLES LIKE ‘clone_valid_donor_list’;
grant CLONE_ADMIN on . to ‘root‘@’%’;
CLONE INSTANCE FROM ‘clone_user‘@172.26.160.198:13306 IDENTIFIED BY “clone_pass”;
执行后数据库将重启，无需紧张。
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = ‘clone’;

搭建：mgr
– 创建一个复制用的账号
create user rpl_user@’%’ IDENTIFIED WITH mysql_native_password BY ‘rpl@2020’;
grant replication client,replication slave on . to rpl_user@’%’ ;

cat > /etc/my13306.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql/
datadir=/mysqldb/mysql13306/data
plugin_dir=/mysqldb/mysql13306/plugin
log-bin=/mysqldb/mysql13306/binlog/mysql-bin
log-bin-index=/mysqldb/mysql13306/binlog/binlog.index
log_error_verbosity = 3
log-error=/mysqldb/mysql13306/log/error.log
server-id=100
port=3307
server_id=100
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
disabled_storage_engines=”MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
binlog_transaction_dependency_tracking=writeset
transaction_write_set_extraction=XXHASH64
slave-parallel-workers=8
slave-preserve-commit-order=1
slave-parallel-type=LOGICAL_CLOCK
plugin_load_add=’mysql_clone.so’
plugin_load_add=’group_replication.so’

group_replication_group_name=”34d9b756-100f-11ea-a5ff-005056842fb1”
group_replication_start_on_boot=OFF
group_replication_local_address= “172.26.160.198:33061”
group_replication_group_seeds= “172.26.160.198:33061,172.26.160.199:33061,172.26.160.200:33061”
group_replication_bootstrap_group=OFF
group_replication_single_primary_mode=ON
group_replication_exit_state_action=READ_ONLY
group_replication_unreachable_majority_timeout=5
group_replication_compression_threshold=131072
group_replication_transaction_size_limit=20971520
group_replication_recovery_get_public_key=on
group_replication_ip_whitelist=’172.26.160.0/24’
EOF

注意：需要注意修改server-id，各节点的值不能重复。
注意：group_replication_local_address= “21.57.32.1:33061”
group_replication_group_seeds= “21.57.32.1:33061,21.57.32.2:33061,21.57.32.3:33061”
这里的端口和数据库本身的3306端口值是不一样的。
实例2相关配置为：
group_replication_local_address= “21.57.32.2:33061”
group_replication_group_seeds= “21.57.32.1:33061,21.57.32.2:33061,21.57.32.3:33061”

group_replication_group_name必须是有效的UUID。使用SELECT UUID（）来生成UUID。
group_replication_start_on_boot=off表示在启动时服务器时不自动启动组复制。这在设置组复制时很重要，因为它确保您可以在手动启动插件之前配置服务器。
配置成员后，可以将group_replication_start_on_boot设置为on，以便组复制在服务器启动时自动启动。
修改完后重启数据库。

安装mysqlshell：
ls /soft/mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz

tar -zxvf /soft/mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz

mv /soft/mysql-shell-8.0.26-linux-glibc2.12-x86-64bit /usr/local/mysqlshell

vim .bash_profile
export PATH=$PATH:$MYSQL_BASE/bin:/usr/local/mysqlshell/bin
注：集群新建默认端口将为数据库端口加1，如数据库端口3306 ，集群端口为33061

mysqlsh rpl_user@cluster1:3307
mysqlsh rpl_user@cluster2:3307
mysqlsh rpl_user@cluster3:3307

dba.configureInstance()；#预检查节点是否可以创建集群（此命令需在所有节点都执行）
在主节点上进行集群创建：
dba.createCluster(‘MgrCluster’);

var cluster = dba.getCluster(‘MgrCluster’);

剩余节点将节点加入到集群
cluster.addInstance(‘rpl_user@cluster2:3307’);
cluster.addInstance(‘rpl_user@cluster3:3307’);

cluster.describe();
{
“clusterName”: “MgrCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“topology”: [
{
“address”: “cluster1:3307”,
“label”: “cluster1:3307”,
“role”: “HA”
},
{
“address”: “cluster2:3307”,
“label”: “cluster2:3307”,
“role”: “HA”
},
{
“address”: “cluster3:3307”,
“label”: “cluster3:3307”,
“role”: “HA”
}
],
“topologyMode”: “Single-Primary”
}
}

检查确认：
SELECT * FROM performance_schema.replication_group_members;

对在线的MGR集群添加一个新节点
上面的MGR集群已经上线了，现在想添加一个新节点 GreatSQL-04:3306，步骤和上面基本上一样，三步走：

用有管理权限的账号登入MySQL实例（用mysqlsh客户端）。
执行 dba.configureInstance() 函数预处理。
执行 cluster.addInstance() 函数加入集群。
如果是想删除一个节点，则改成执行 cluster.removeInstance() 函数即可。

检查确认
SELECT * FROM performance_schema.replication_group_members;
数据同步测试
create database dan;
use dan;
create table t(id int not null unique);
insert into t(id) values(1);

Query OK, 1 row affected (0.00 sec)

注意：表必须有主键或者必须有非空唯一键，否则虽然表创建不会报错，但插入数据时会报错：ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin。

故障测试
模拟只关闭主节点
主节点：
其中一个从节点会自动变为可写模式(read_only,super_read_only会从on自动变为off)。
数据仍能正常同步。不影响整个集群的可用。
将原主节点启动后，需要手动启动下组复制，原主库变为了一个新备库。
read_only,super_read_only从off自动变为on。宕机期间现主库新增的数据会自动同步过来。
ps -ef | grep mysql| grep -v grep | awk ‘{print “kill -9 “$2}’ | sh
登录其他节点，查看信息：
SELECT * FROM performance_schema.replication_group_members;

数据库开启：
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my13306.cnf &

安装mysqlrouter：

xz -d /mysqldb/mysql-router-8.0.26-linux-glibc2.12-x86_64.tar.xz
tar -xvf /mysqldb/mysql-router-8.0.26-linux-glibc2.12-x86_64.tar -C /mysqldb
mv /mysqldb/mysql-router-8.0.26-linux-glibc2.12-x86_64 /usr/local/mysql-router-8.0.26
mkdir -p /mysql/mysql-router-8.0.26/log
mkdir -p /mysql/mysql-router-8.0.26/data
chown -R mysql:mysql /mysql/mysql-router-8.0.26

cat >> /etc/mysqlrouter.conf << EOF
[DEFAULT]

logging_folder =/mysqldb/mysqlrouter/log
plugin_folder =/usr/local/mysql-router-8.0.26/lib/mysqlrouter
data_folder=/mysqldb/mysqlrouter/data

[logger]

level = INFO

[routing:primary]

bind_address = 0.0.0.0
bind_port = 13001
destinations = 21.57.32.1:13306,121.57.32.2:13306,21.57.32.3:13306
routing_strategy = first-available

[routing:secondary]

bind_address = 0.0.0.0
bind_port = 13002
destinations = 121.57.32.2:13306,21.57.32.3:13306
routing_strategy = round-robin
EOF

round-robin：为了实现负载平衡，每个新连接都以循环方式连接到下一个可用的服务器。
first-available:
新连接将从目标列表路由到第一个可用服务器。如果失败，则使用下一个可用服务器。此循环一直持续到所有服务器都不可用为止。所以对于单主模式的MGR来说，first-available模式下，destination里IP的顺序很重要。

sshpass -p ‘HN-hepc@2020’ scp -P 10022 -r /etc/mysqlrouter.conf 21.57.32.3:/etc
sshpass -p ‘HN-hepc@2020’ scp -P 10022 -r /etc/mysqlrouter.conf 21.57.32.3:/etc

chown -R mysql:mysql /etc/mysqlrouter.conf

mysqlrouter -c /etc/mysqlrouter.conf &
/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my13306.cnf &

vi /etc/profile

在PATH=添加mysqlrouter所在路径/usr/local/mysql-router-8.0.26/bin/，如：
PATH=$PATH:$JAVA_BIN:$ES_HOME:/usr/local/mysql-router-8.0.26/bin/
source .bash_profile
验证是否安装成功：
mysqlrouter –help

将服务注册为开机自启动：
echo “/usr/local/mysql/bin/mysqld_safe –defaults-file=/etc/my13306.cnf &” >> /etc/rc.local
chmod +x /etc/rc.local

echo “mysqlrouter -c /etc/mysqlrouter.conf &” >> /etc/rc.local
chmod +x /etc/rc.local

本文链接： http://gladly.wang/2022/01/25/mysql-mgr-mysqlshell-mysqlrouter/

版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！

gladlyDBA

个人简介。