Mysql 集群技术

已遇丶释槐鸟

于 2024-08-29 21:27:28 发布

阅读量624

点赞数 10

分类专栏： RHCE---红帽认证工程师文章标签： mysql 数据库

本文链接：https://blog.csdn.net/ZYT021226/article/details/141679873

版权

RHCE---红帽认证工程师专栏收录该内容

12 篇文章 0 订阅

订阅专栏

mysql-router（mysql路由）

在Linux下部署mysql

安装依赖性：

[root@mysql-node10 ~]# dnf install cmake gcc-c++ openssl-devel \
ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el7_4.x86_64.rpm rpcgen.x86_64

下载并解压源码包

[root@mysql-node10 ~]# tar zxf mysql-boost-5.7.44.tar.gz
[root@mysql-node10 ~]# cd /root/mysql-5.7.44

源码编译安装mysql

[root@mysql-node10 mysql-5.7.44]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定安装路径
-DMYSQL_DATADIR=/data/mysql \ #指定数据目录
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ #指定套接字文件
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #指定启用INNODB存储引擎，默认
用myisam
-DWITH_EXTRA_CHARSETS=all \ #扩展字符集
-DDEFAULT_CHARSET=utf8mb4 \ #指定默认字符集
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \ #指定默认校验字符集
-DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/ #指定c++库依赖
[root@mysql-node10 mysql-5.7.44]# make -j2 #-j2 表示有几个
核心就跑几个进程
[root@mysql-node10 mysql-5.7.44# make install

在Linux下部署mysql

4GB 2核

官网：MySQL

然后cmake --help

缺少了C的编译工具

yum install gcc gcc-c++ -y

继续检测

删了之后重新检测

安装一下libtirpc软件

用两个核编译

回车开始编译

make install

部署mysql

#生成启动脚本
[root@node10 ~]# dnf install initscripts-10.11.6-1.el9.x86_64 -y
[root@node10 ~]# cd /usr/local/mysql/support-files/
[root@node10 support-files]# cp mysql.server /etc/init.d/mysqld
#修改环境变量
[root@node10 ~]# vim ~/.bash_profile
export PATH=$PATH:/usr/local/mysql/bin
[root@node10 ~]# source ~/.bash_profile
#生成数据目录
[root@mysql-node1 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 ~]# mkdir -p /data/mysql
[root@mysql-node1 ~]# chown mysql.mysql /data/mysql/
#修改配置文件
[root@node10 my.cnf.d]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql #指定数据目录
socket=/data/mysql/mysql.sock #指定套接字
symbolic-links=0 #数据只能存放到数据目录中，禁止链接到数据目录
#数据库初始化建立mysql基本数据
[root@node10 ~]# mysqld --initialize --user=mysql
[root@node10 ~]# /etc/init.d/mysqld start
[root@node10 ~]# chkconfig mysqld on
#数据库安全初始化
[root@node10 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: #输入当前密码
The existing password for the user account root has expired. Please set a new
password.
New password: #输入新密码
Re-enter new password: #重复密码
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: no #是否启用密码插件
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no
#是否要重置密码
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No)
: y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

测试：

[root@node10 ~]# mysql -uroot -plee
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.44 Source distribution
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> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

mysql启动脚本

修改环境变量

用户初始化

hsgDn8OYrO/:

记录初始化的密码

echo …… >> passwd

启动mysql

开机自启动mysql

mysql的安全初始化

拷贝

mysql的组从复制

配置mastesr

[root@mysql-node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1
[root@mysql-node10 ~]# /etc/init.d/mysqld restart
#进入数据库配置用户权限
[root@mysql-node10 ~]# mysql -plee
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.44-log Source distribution
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> CREATE USER 'repl'@'%' IDENTIFIED BY 'lee'; ##生成专门用来做复制的用
户，此用户是用于slave端做认证用
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; ##对这个用户进行授权
mysql> SHOW MASTER STATUS; ##查看master的状态
+------------------+----------+--------------+------------------+----------------
---+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
---+
| mysql-bin.000001 | 350 | | |
|
+------------------+----------+--------------+------------------+----------------
---+
1 row in set (0.00 sec)
[root@mysql-node10 ~]# cd /data/mysql/
[root@mysql-node10 mysql]# mysqlbinlog mysql-bin.000001 -vv ##查看二进制日志

配置salve

[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
[root@mysql-node2 ~]# mysql -plee
mysql> CHANGE MASTER TO
MASTER_HOST='172.25.254.10',MASTER_USER='repl',MASTER_PASSWORD='lee',MASTER_LOG_F
ILE='mysql-bin.000001',MASTER_LOG_POS=350;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 350
Relay_Log_File: mysql-node2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 350
Relay_Log_Space: 533
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 888d2164-4b05-11ef-a049-000c299355ea
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more
updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec

测试：

[root@mysql-node10 ~]# mysql -plee
mysql> CREATE DATABASE lee;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE lee.userlist (
-> username varchar(20) not null,
-> password varchar(50) not null
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO lee.userlist VALUE ('lee','123');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
+----------+----------+
1 row in set (0.00 sec)
在slave中查看数据是否有同步过来
[root@mysql-node2 ~]# mysql -plee
mysql> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee | 123 |
+----------+----------+
1 row in set (0.00 sec)

在 slave 阶段中默认情况下是开启了写功能的，但是建议关闭 slave 节点的写功能来保证数据一致性

vim /etc/mysql.cnf
[mysqld]
super_read_only=on

开启二进制日志

change master to master_host='172.25.254.10',master_user='repl',master_passw         ord='123',master_log_file='mysql-bin.000001',master_log_pos=595;

↑数据传输数据回放

当有数据时添加slave2

#完成基础配置
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=3
[root@mysql-node3 ~]# /etc/init.d/mysqld restart

#从master节点备份数据
[root@mysql-node1 ~]# mysqldump -uroot -plee lee > lee.sql

#利用master节点中备份出来的lee.sql在slave2中拉平数据
[root@mysql-node3 ~]# mysql -uroot -plee -e "create database lee;"
[root@mysql-node3 ~]# mysql -uroot -p lee <lee.sql
[root@mysql-node3 ~]# mysql -uroot -plee -e "select * from lee.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
+----------+----------+

#配置slave2的slave功能
#在master中查询日志pos
mysql -uroot -plee -e "SHOW MASTER STATUS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+----------------
---+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
---+
| mysql-bin.000001 | 1251 | | |
|
+------------------+----------+--------------+------------------+----------------
---+
[root@mysql-node3 ~]# mysql -uroot -plee
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='lee', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1251;
mysql> start slave;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.25.254.30
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1251
Relay_Log_File: mysql-node3-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1251
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1130
Last_IO_Error: error connecting to master
'repl@172.25.254.30:3306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more
updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 240727 19:14:25
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

测试：

[root@mysql-node1 ~]# mysql -uroot -plee -e "INSERT INTO lee.userlist
VALUES('user3','123');"
[root@mysql-node2 mysql]# mysql -uroot -plee -e 'select * from lee.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+
[root@mysql-node3 ~]# mysql -uroot -plee -e 'select * from lee.userlist;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 123 |
| user3 | 123 |
+----------+----------+

延迟复制

#在slave端
mysql> STOP SLAVE SQL_THREAD;
mysql> CHANGE MASTER TO MASTER_DELAY=60;
mysql> START SLAVE SQL_THREAD;
mysql> SHOW SLAVE STATUS\G;
Master_Server_Id: 1
Master_UUID: db2d8c92-4dc2-11ef-b6b0-000c299355ea
Master_Info_File: /data/mysql/master.info
SQL_Delay: 60 ##延迟效果
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more
updates
Master_Retry_Count: 86400

测试：

在 master 中写入数据后过了延迟时间才能被查询到

慢查询日志

开启慢查询日志

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> SET long_query_time=4;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES like "long%";
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES like "slow%";
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON | ##慢查询日志开启
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.01 sec)
[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log #慢查询日志
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started
with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument

测试慢查询

mysql> select sleep (10);
[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started
with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
# Time: 2024-07-29T17:04:07.612704Z
# User@Host: root[root] @ localhost [] Id: 8
# Query_time: 10.000773 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1722272647;
select sleep (10);

mysql的并行复制

在slaves中设定
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK #基于组提交，
slave-parallel-workers=16 #开启线程数量
master_info_repository=TABLE #master信息在表中记录，默认记录
在/data/mysql//master.info
relay_log_info_repository=TABLE #回放日志信息在表中记录，默认记录
在/data/mysql/relay-log.info
relay_log_recovery=ON #日志回放恢复功能开启
[root@mysql-node2 ~]# /etc/init.d/mysql start
Starting MySQL. SUCCESS!

半同步模式

半同步模式原理

1. 用户线程写入完成后 master 中的 dump 会把日志推送到 slave 端

2.slave 中的 io 线程接收后保存到 relaylog 中继日志

3. 保存完成后 slave 向 master 端返回 ack

4. 在未接受到 slave 的 ack 时 master 端时不做提交的，一直处于等待当收到 ack 后提交到存储引擎

5. 在 5.6 版本中用到的时 after_commit 模式， after_commit 模式时先提交在等待 ack 返回后输出 ok

gtid模式

[root@mysql-node1 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000003
@@省略内容@@
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mysql-node2 ~]# mysql -plee
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 768c6b91-4c01-11ef-a514-000c299355ea | 1 | 1 |
+--------------------------------------+----------------+--------------+
1 row in set (0.00 sec)

设置gtid

#在master端和slave端开启gtid模式
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@mysql-node1 ~]# /etc/init.d/mysqld restart
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=2
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=3
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@mysql-node3 ~]# /etc/init.d/mysqld restart
#停止slave端
[root@mysql-node2 ~]# mysql -p
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# mysql -p
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
#开启slave端的gtid
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 580
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 768c6b91-4c01-11ef-a514-000c299355ea
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more
updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1 #功能开启
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

启用半同步模式

在master端配置启用半同步模式

[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 #开启半同步功能
symbolic-links=0
[root@mysql-node1 ~]# mysql -p lee
#安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#查看插件情况
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
+----------------------+---------------+
1 row in set (0.01 sec)
#打开半同步功能
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
#查看半同步功能状态
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> show plugins

在slave端开启半同步功能

[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1 #开启半同步功能
symbolic-links=0
[root@mysql-node2 ~]# mysql -plee
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD; #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD; ##重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)

测试

在master端写入数据

[root@mysql-node1 ~]# mysql -p lee
mysql> insert into lee.userlist values ('user4','123');
Query OK, 1 row affected (0.01 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 | #未同步数据0笔
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 981 |
| Rpl_semi_sync_master_tx_wait_time | 981 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 | #已同步数据1笔
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

模拟故障：

#在slave端
[root@mysql-node2 ~]# mysql -plee
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# mysql -plee
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
#在master端插入数据
mysql> insert into lee.userlist values ('user5','555');
Query OK, 1 row affected (10.00 sec) #10秒超时
mysql> SHOW STATUS LIKE 'Rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 | #一笔数据为同步
| Rpl_semi_sync_master_status | OFF | #自动转为异步模式，当
slave恢复
| Rpl_semi_sync_master_timefunc_failures | 0 | #会自动恢复
| Rpl_semi_sync_master_tx_avg_wait_time | 981 |
| Rpl_semi_sync_master_tx_wait_time | 981 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

mysql高可用之组复制 (MGR)

编辑主配置文件：

#在mysql-node10中
[root@mysql-node10 ~]# rm -fr /data/mysql/
[root@mysql-node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=1 #配置server唯一标识号
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #禁用指定存储
引擎
gtid_mode=ON #启用全局事件标识
enforce_gtid_consistency=ON #强制gtid一致
master_info_repository=TABLE #复制事件数据到表中而不记录在数据目录中
relay_log_info_repository=TABLE
binlog_checksum=NONE #禁止对二进制日志校验
log_slave_updates=ON #打开数据库中继，
#当slave中sql线程读取日志后也会写入到自己的binlog中
log_bin=binlog #重新指定log名称
binlog_format=ROW #使用行日志格式
plugin_load_add='group_replication.so' #加载组复制插件
transaction_write_set_extraction=XXHASH64 #把每个事件编码为加密散列
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #通知插件正
式加入
#或创建的组
名
#名称为
uuid格式
group_replication_start_on_boot=off #在server启动时不自动启动组复
制
group_replication_local_address="172.25.254.10:33061" #指定插件接受其他成员的信息端
口
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061，
172.25.254.30:33061" #本地地址允许访
问成员列表
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" #主机白名单
#不随系统自启而启动，只在初始成员主机中手动开启，
#需要在两种情况下做设定：1.初始化建组时 2.关闭并重新启动整个组时
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF #使用多主模式
group_replication_enforce_update_everywhere_checks=ON #组同步中有任何改变
检测更新
group_replication_allow_local_disjoint_gtids_join=1 #放弃自己信息以
master事件为主
[root@mysql-node10 ~]# mysqld --user=mysql --initialize
[root@mysql-node10 ~]# /etc/init.d/mysqld start
[root@mysql-node10 ~]# mysql -uroot -p初始化后生成的密码 -e "alter user
root@localhost identified by 'lee';"
#配置sql
[root@mysql-node10 ~]# mysql -plee
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
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='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL
'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
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 warning (2.19 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;

# 在复制配置文件到 myql-node20 和 mysql-node30

[root@mysql-node20 & 30 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf
[root@mysql-node20 & 30 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf
#修改mysql—node20和mysl-node30中的配置
[root@mysql-node20 & 30 ~]# rm -fr /data/mysql/
[root@mysql-node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2 #在30上写3
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
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.20:33061" #在30上要写30
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061，
172.25.254.30:33061"
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"
group_replication_bootstrap_group=off
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
[root@mysql-node10 ~]# mysqld --user=mysql --initialize
[root@mysql-node10 ~]# /etc/init.d/mysqld start
[root@mysql-node10 ~]# mysql -uroot -p初始化后生成的密码 -e "alter user
root@localhost identified by 'lee';"
#配置sql
[root@mysql-node20 & 30 ~]# mysql -plee
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
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='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL
'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;

测试：

在每个节点都可以完成读写

#在mysql-node10中
[root@mysql-node10 ~]# mysql -p
mysql> CREATE DATABASE lee;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE lee.userlist(
-> username VARCHAR(10) PRIMARY KEY NOT NULL,
-> password VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO lee.userlist VALUES ('user1','111');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
+----------+----------+
1 row in set (0.00 sec)
#在mysql-node20中
[root@mysql-node20 ~]# mysql -p
mysql> INSERT INTO lee.userlist values ('user2','222');
Query OK, 1 row affected (0.00 sec)
mysql> select * from lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
+----------+----------+
2 rows in set (0.00 sec)
#mysql—node30中
[root@mysql-node30 ~]# mysql -p
mysql> INSERT INTO lee.userlist values ('user3','333');
Query OK, 1 row affected (0.00 sec)
mysql> select * from lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 111 |
| user2 | 222 |
| user3 | 333 |
+----------+----------+
3 rows in set (0.00 sec)

mysql-router（mysql路由）

#安装mysql-router
[root@mysql-router ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm

#配置mysql-router
[root@mysql-router ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306
routing_strategy = first-available
[root@mysql-router ~]# systemctl start mysqlrouter.service

测试：

#建立测试用户
mysql> CREATE USER lee@'%' IDENTIFIED BY 'lee';
mysql> GRANT ALL ON lee.* TO lee@'%';

#查看调度效果
[root@mysql-node10 & 20 & 30 ~]# watch -1 lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 9879 mysql 22u IPv6 56697 0t0 TCP *:mysql (LISTEN)

[root@mysql-router ~]# mysql -ulee -plee -h 172.25.254.40 -P 7001

mysql router 并不能限制数据库的读写，访问分流

MHA部署实施

搭建主两从架构

#在master节点中
[root@mysql-node10 ~]# /etc/init.d/mysqld stop
[root@mysql-node10 ~]# rm -fr /data/mysql/*
[root@mysql-node10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@mysql-node10 ~]# mysqld --user mysql --initialize
[root@mysql-node10 ~]# /etc/init.d/mysqld start
[root@mysql-node10 ~]# mysql_secure_installation
[root@mysql-node10 ~]# mysql -p
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
#在slave1和slave2中
[root@mysql-node20 & 30 ~]# /etc/init.d/mysqld stop
[root@mysql-node20 & 30 ~]# rm -fr /data/mysql/*
[root@mysql-node20 & 30 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=1
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@mysql-node20 & 30 ~]# mysqld --user mysql --initialize
[root@mysql-node20 & 300 ~]# /etc/init.d/mysqld start
[root@mysql-node20 & 30 ~]# mysql_secure_installation
[root@mysql-node20 & 30 ~]# mysql -p
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)

安装MHA所需要的软件

#在MHA中
[root@mysql-mha ~]# unzip MHA-7.zip
[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-Mail-Sender-0.8.23-
1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz perl-Mail-Sendmail-0.79-
21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm perl-MIME-Lite-3.030-
1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-MIME-Types-1.38-
2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-Net-Telnet-3.03-
19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-Parallel-ForkManager-1.18-
2.el7.noarch.rpm
[root@mysql-mha MHA-7]# yum install *.rpm -y
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm
root@172.25.254.10:/mnt
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm
root@172.25.254.20:/mnt
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm
root@172.25.254.30:/mnt
#在sql-node中
[root@mysql-node10 ~]# yum install /mnt/mha4mysql-node-0.58-
0.el7.centos.noarch.rpm -y
[root@mysql-node20 ~]# yum install /mnt/mha4mysql-node-0.58-
0.el7.centos.noarch.rpm -y
[root@mysql-node30 ~]# yum install /mnt/mha4mysql-node-0.58-
0.el7.centos.noarch.rpm -y