知识点
一、 Mysql 在服务器中的部署方法
1、在Linux下部署mysql
1.1 安装依赖性：
[root@mysql-node10 ~]# yum install cmake gcc-c++ openssl-devel \ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64
1.2 下载并解压源码包
[root@mysql-node10 ~]# tar zxf mysql-boost-5.7.44.tar.gz[root@mysql-node10 ~]# cd /root/mysql-5.7.44
1.3 源码编译安装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
1.4 部署mysql
#生成启动脚本
#修改环境变量
#生成配置文件
#数据库初始化建立mysql基本数据
#数据库安全初始化
Securing the MySQL server deployment.Enter password for user root: # 输入当前密码The existing password for the user account root has expired. Please set a newpassword.New password: # 输入新密码Re-enter new password: # 重复密码VALIDATE PASSWORD PLUGIN can be used to test passwordsand improve security. It checks the strength of passwordand allows the users to set only those passwords which aresecure 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 havea user account created for them. This is intended only fortesting, and to make the installation go a bit smoother.You should remove them before moving into a productionenvironment.Remove anonymous users? (Press y|Y for Yes, any other key for No) : ySuccessNormally, root should only be allowed to connect from'localhost'. This ensures that someone cannot guess atthe root password from the network.Disallow root login remotely? (Press y|Y for Yes, any other key for No) : ySuccess.By default, MySQL comes with a database named 'test' thatanyone can access. This is also intended only for testing,and should be removed before moving into a productionenvironment.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 changesmade so far will take effect immediately.Reload privilege tables now? (Press y|Y for Yes, any other key for No) : ySuccess
测试：
二 、mysql的组从复制
1、 配置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 restart2、配置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*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.10Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 350Relay_Log_File: mysql-node2-relay-bin.000002Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 350Relay_Log_Space: 533Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 888d2164-4b05-11ef-a049-000c299355eaMaster_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for moreupdatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec
测试：
3、 当有数据时添加slave2
# 完成基础配置[root@mysql-node3 ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0server-id=3[root@mysql-node3 ~]# /etc/init.d/mysqld restart# 从 master 节点备份数据[root@mysql-node1 ~]# mysqldump -uroot -plee lee > lee.sql###########################################生产环境中备份时需要锁表，保证备份前后的数据一致mysql> FLUSH TABLES WITH READ LOCK;备份后再解锁mysql> UNLOCK TABLES;mysqldump 命令备份的数据文件，在还原时先 DROP TABLE ，需要合并数据时需要删除此语句---- Table structure for table `userlist`--DROP TABLE IF EXISTS `userlist`; # 需要合并数据时需要删除此语句/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;
mysql> SHOW SLAVE STATUS\G;*************************** 1. row ***************************Slave_IO_State: Connecting to masterMaster_Host: 172.25.254.30Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1251Relay_Log_File: mysql-node3-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_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: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1251Relay_Log_Space: 154Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 1130Last_IO_Error: error connecting to master'repl@172.25.254.30:3306' - retry-time: 60 retries: 1Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 0Master_UUID:Master_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for moreupdatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp: 240727 19:14:25Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 0Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1 row in set (0.00 sec)
测试：
4、延迟复制
# 在 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: 1Master_UUID: db2d8c92-4dc2-11ef-b6b0-000c299355eaMaster_Info_File: /data/mysql/master.infoSQL_Delay: 60 ## 延迟效果SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for moreupdatesMaster_Retry_Count: 86400
测试：
5、慢查询日志
- 慢查询，顾名思义，执行很慢的查询
- 当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个SQL语句就是需要优化的
- 慢查询被记录在慢查询日志里
- 慢查询日志默认是不开启的
- 如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。
6、mysql的并行复制
在 slaves 中设定[root@mysql-node2 ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.sockserver-id=2gtid_mode=ONenforce-gtid-consistency=ONslave-parallel-type=LOGICAL_CLOCK # 基于组提交，slave-parallel-workers=16 # 开启线程数量master_info_repository=TABLE #master 信息在表中记录，默认记在/data/mysql//master.inforelay_log_info_repository=TABLE # 回放日志信息在表中记录，默认记录在/data/mysql/relay-log.inforelay_log_recovery=ON # 日志回放恢复功能开启[root@mysql-node2 ~]# /etc/init.d/mysql startStarting MySQL. SUCCESS!
MySQL 组提交（Group commit）是一个性能优化特性，它允许在一个事务日志同步操作中将多个事务的日志记录一起写入。这样做可以减少磁盘I/O的次数，从而提高数据库的整体性能。
7、原理刨析
- 二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
- 从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
- 从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。
- 步骤1：Master将写操作记录到二进制日志（binlog）。
- 步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；
- 步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。
- i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地磁盘
- sql线程读取本地relog中继日志进行回放
8、 架构缺陷
主从架构采用的是异步机制master 更新完成后直接发送二进制日志到 slave ，但是 slaves 是否真正保存了数据 master 端不会检测master端直接保存二进制日志到磁盘当 master 端到 slave 端的网络出现问题时或者 master 端直接挂掉，二进制日志可能根本没有到达 slavemaster 出现问题 slave 端接管 master ，这个过程中数据就丢失了这样的问题出现就无法达到数据的强一致性，零数据丢失
三 半同步模式
1、半同步模式原理
2、gtid模式
设置gtid
#在master端和slave端开启gtid模式[root@mysql-node1 ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.sockserver-id=1log-bin=mysql-bingtid_mode=ONenforce-gtid-consistency=ONsymbolic-links=0[root@mysql-node1 ~]# /etc/init.d/mysqld restart[root@mysql-node2 ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.sockserver-id=2log-bin=mysql-bingtid_mode=ONenforce-gtid-consistency=ONsymbolic-links=0[root@mysql-node2 ~]# /etc/init.d/mysqld restart[root@mysql-node3 ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.sockserver-id=3log-bin=mysql-bingtid_mode=ONenforce-gtid-consistency=ONsymbolic-links=0[root@mysql-node3 ~]# /etc/init.d/mysqld restart
# 停止 slave 端[root@mysql-node2 ~]# mysql -pmysql> stop slave;Query OK, 0 rows affected (0.00 sec)[root@mysql-node3 ~]# mysql -pmysql> stop slave;Query OK, 0 rows affected (0.01 sec)
#开启slave端的gtid
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.10Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 154Relay_Log_File: mysql-node2-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 580Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 768c6b91-4c01-11ef-a514-000c299355eaMaster_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for moreupdatesMaster_Retry_Count: 86400Master_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)
3、启用半同步模式
在master端配置启用半同步模式
#安装半同步插件
#查看插件情况
#打开半同步功能
#查看半同步功能状态
在slave端开启半同步功能
4.测试
在master端写入数据
模拟故障：
#在slave端
#在master端插入数据
四 mysql高可用之组复制 (MGR)
MySQL Group Replication( 简称 MGR ) 是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务MySQL 组复制分单主模式和多主模式，传统的 mysql 复制技术仅解决了数据同步的问题，MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制MGR 由组通信系统 ( Group Communication System ， GCS ) 协议支持该系统提供故障检测机制、组成员服务以及安全且有序的消息传递
1、组复制流程
2、组复制单主和多主模式
single-primary mode(单写或单主模式)
multi-primary mode(多写或多主模式)
3、实现mysql组复制
#在mysql-node10中
[root@mysql-node10 ~]# rm -fr /data/mysql/[root@mysql-node10 ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0server-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=TABLEbinlog_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" # 主机白名单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 userroot@localhost identified by 'lee';"
#配置sql
#在复制配置文件到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/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0server-id=2 # 在 30 上写 3disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONlog_bin=binlogbinlog_format=ROWplugin_load_add='group_replication.so'transaction_write_set_extraction=XXHASH64group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"group_replication_start_on_boot=offgroup_replication_local_address="172.25.254.20:33061" # 在 30 上要写 30group_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=offgroup_replication_single_primary_mode=OFFgroup_replication_enforce_update_everywhere_checks=ONgroup_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 userroot@localhost identified by 'lee';"
#配置sql
测试：
#在mysql-node10中
#在mysql-node20中
#mysql—node30中
五、 mysql-router（mysql路由）
MySQL Router
Mysql route的部署方式
#安装mysql-route
[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.0bind_port = 7001destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306routing_strategy = round-robin[routing:rw]bind_address = 0.0.0.0bind_port = 7002destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306routing_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 :3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 9879 mysql 22u IPv6 56697 0t0 TCP *:mysql (LISTEN)[root@mysql-router ~]# mysql -ulee -plee -h 172.25.254.40 -P 7001Notemysql router 并不能限制数据库的读写，访问分流
六、mysql高可用之MHA
1、MHA概述
为什么要用MHA？
什么是 MHA？
- MHA（Master High Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。
- MHA 的出现就是解决MySQL 单点的问题。
- MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。
- MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。
MHA 的组成
- MHA由两部分组成:MHAManager (管理节点) MHA Node (数据库节点),
- MHA Manager 可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave 节点上。
- MHA Manager 会定时探测集群中的 master 节点。
- 当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的slave 重新指向新的 master。
MHA 的特点
- 自动故障切换过程中，MHA从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
- 使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性
- 目前MHA支持一主多从架构，最少三台服务，即一主两从
故障切换备选主库的算法
MHA工作原理
- 目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群必须最少有3台数据库服务器，一主二从，即一台充当Master，台充当备用Master，另一台充当从库。
- MHA Node 运行在每台 MySQL 服务器上
- MHAManager 会定时探测集群中的master 节点
- 当master 出现故障时，它可以自动将最新数据的slave 提升为新的master
- 然后将所有其他的slave 重新指向新的master，VIP自动漂移到新的master。
- 整个故障转移过程对应用程序完全透明。
2、MHA部署实施
2.1 搭建主两从架构
#在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/mysqlsocket=/data/mysql/mysql.sockserver-id=1log-bin=mysql-bingtid_mode=ONlog_slave_updates=ONenforce-gtid-consistency=ONsymbolic-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 -pmysql> 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/mysqlsocket=/data/mysql/mysql.sockserver-id=1log-bin=mysql-bingtid_mode=ONlog_slave_updates=ONenforce-gtid-consistency=ONsymbolic-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 -pmysql> 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)
2.2安装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- masterha_check_ssh #检查MHA的SSH配置状况
- masterha_check_repl #检查MySQL复制状况
- masterha_manger#启动MHA
- masterha_check_status#检测当前MHA运行状态
- masterha_master_monitor #检测master是否宕机
- masterha_master_switch #控制故障转移（自动或者手动）
- masterha_conf_host #添加或删除配置的server信息
- save_binary_logs #保存和复制master的二进制日志
- apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
- filter_mysqlbinlog #去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
- purge_relay_logs #清除中继日志（不会阻塞SQL线程）
2.3 配置MHA 的管理环境
1.生成配置目录和配置文件
[root@mysql-mha ~]# masterha_manager --helpUsage:masterha_manager --global_conf=/etc/masterha_default.cnf # 全局配置文件，记录公共设定--conf=/usr/local/masterha/conf/app1.cnf # 不同管理配置文件，记录各自配置See online reference(http://code.google.com/p/mysql-master-ha/wiki/masterha_manager) for details.因为我们当前只有一套主从，所以我们只需要写一个配置文件即可
rpm包中没有为我们准备配置文件的模板
可以解压源码包后在samples中找到配置文件的模板文件
#生成配置文件
[root@mysql-mha ~]# mkdir /etc/masterha
[root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@mysql-mha MHA-7]# cd mha4mysql-manager-0.58/samples/conf/
[root@mysql-mha conf]# cat masterha_default.cnf app1.cnf >
/etc/masterha/app1.cnf
#编辑配置文件
[root@mysql-mha ~]# vim /etc/masterha/app1.cnf
[server default]
user=root #mysql管理员用户，因为需要做自动化配置
password=lee #mysql密码
ssh_user=root #ssh远程登陆用户
repl_user=repl #mysql主从复制中负责认证的用户
repl_password=lee #mysql主从复制中负责认证的用户密码
master_binlog_dir= /data/mysql #二进制日志目录
remote_workdir=/tmp #远程工作目录
#此参数使为了提供冗余检测，方式是mha主机网络自身的问题无法连接数据库节点，应为集群之外的主机
secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s
172.25.254.11
ping_interval=3 #每隔3秒检测一次
#发生故障后调用的脚本，用来迁移vip
# master_ip_failover_script= /script/masterha/master_ip_failover
#电源管理脚本
# shutdown_script= /script/masterha/power_manager
#当发生故障后用此脚本发邮件或者告警通知
# report_script= /script/masterha/send_report
#在线切换时调用的vip迁移脚本，手动
# master_ip_online_change_script= /script/masterha/master_ip_online_change
manager_workdir=/etc/masterha #mha工作目录
manager_log=/var/etc/masterha/manager.log #mha日志
[server1]
hostname=172.25.254.10
candidate_master=1 #可能作为master的主机
check_repl_delay=0 ##默认情况下如果一个slave落后master 100M的relay logs的话
#MHA将不会选择该slave作为一个新的master
#因为对于这个slave的恢复需要花费很长时间
#通过设置check_repl_delay=0
#MHA触发切换在选择一个新的master的时候将会忽略复制延时
#这个参数对于设置了candidate_master=1的主机非常有用
#因为这个候选主在切换的过程中一定是新的master
[server2]
hostname=172.25.254.20
candidate_master=1 #可能作为master的主机
check_repl_delay=0
[server3]
hostname=172.25.254.30
no_master=1 #不会作为master的主机2.检测配置：
a）检测网络及ssh免密
[root@mysql-mha ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnfFri Aug 2 16:57:41 2024 - [warning] Global configuration file/etc/masterha_default.cnf not found. Skipping.Fri Aug 2 16:57:41 2024 - [info] Reading application default configuration from/etc/masterha/app1.cnf..Fri Aug 2 16:57:41 2024 - [info] Reading server configuration from/etc/masterha/app1.cnf..Fri Aug 2 16:57:41 2024 - [info] Starting SSH connection tests..Fri Aug 2 16:57:42 2024 - [debug]Fri Aug 2 16:57:41 2024 - [debug] Connecting via SSH fromroot@172.25.254.10(172.25.254.10:22) to root@172.25.254.20(172.25.254.20:22)..Fri Aug 2 16:57:41 2024 - [debug] ok.Fri Aug 2 16:57:41 2024 - [debug] Connecting via SSH fromroot@172.25.254.10(172.25.254.10:22) to root@172.25.254.30(172.25.254.30:22)..Fri Aug 2 16:57:41 2024 - [debug] ok.Fri Aug 2 16:57:42 2024 - [debug]Fri Aug 2 16:57:41 2024 - [debug] Connecting via SSH fromroot@172.25.254.20(172.25.254.20:22) to root@172.25.254.10(172.25.254.10:22).. Warning: Permanently added '172.25.254.10' (ECDSA) to the list of known hosts.Fri Aug 2 16:57:42 2024 - [debug] ok.Fri Aug 2 16:57:42 2024 - [debug] Connecting via SSH fromroot@172.25.254.20(172.25.254.20:22) to root@172.25.254.30(172.25.254.30:22)..Warning: Permanently added '172.25.254.30' (ECDSA) to the list of known hosts.Fri Aug 2 16:57:42 2024 - [debug] ok.Fri Aug 2 16:57:43 2024 - [debug]Fri Aug 2 16:57:42 2024 - [debug] Connecting via SSH fromroot@172.25.254.30(172.25.254.30:22) to root@172.25.254.10(172.25.254.10:22)..Warning: Permanently added '172.25.254.10' (ECDSA) to the list of known hosts.Fri Aug 2 16:57:42 2024 - [debug] ok.Fri Aug 2 16:57:42 2024 - [debug] Connecting via SSH fromroot@172.25.254.30(172.25.254.30:22) to root@172.25.254.20(172.25.254.20:22)..Warning: Permanently added '172.25.254.20' (ECDSA) to the list of known hosts.Fri Aug 2 16:57:42 2024 - [debug] ok.Fri Aug 2 16:57:43 2024 - [info] All SSH connection tests passed successfully.
b）检测数据主从复制情况
# 在数据节点 master 端mysql> GRANT ALL ON *.* TO root@'%' identified by 'lee'; # 允许 root 远程登陆# 执行检测[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnfFri Aug 2 17:04:20 2024 - [warning] Global configuration file/etc/masterha_default.cnf not found. Skipping.Fri Aug 2 17:04:20 2024 - [info] Reading application default configuration from/etc/masterha/app1.cnf..Fri Aug 2 17:04:20 2024 - [info] Reading server configuration from/etc/masterha/app1.cnf..Fri Aug 2 17:04:20 2024 - [info] MHA::MasterMonitor version 0.58.Fri Aug 2 17:04:21 2024 - [info] GTID failover mode = 1Fri Aug 2 17:04:21 2024 - [info] Dead Servers:Fri Aug 2 17:04:21 2024 - [info] Alive Servers:Fri Aug 2 17:04:21 2024 - [info] 172.25.254.10(172.25.254.10:3306)Fri Aug 2 17:04:21 2024 - [info] 172.25.254.20(172.25.254.20:3306)Fri Aug 2 17:04:21 2024 - [info] 172.25.254.30(172.25.254.30:3306)Fri Aug 2 17:04:21 2024 - [info] Alive Slaves:Fri Aug 2 17:04:21 2024 - [info] 172.25.254.20(172.25.254.20:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 17:04:21 2024 - [info] GTID ONFri Aug 2 17:04:21 2024 - [info] Replicating from172.25.254.10(172.25.254.10:3306)Fri Aug 2 17:04:21 2024 - [info] Primary candidate for the new Master(candidate_master is set)Fri Aug 2 17:04:21 2024 - [info] 172.25.254.30(172.25.254.30:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 17:04:21 2024 - [info] GTID ONFri Aug 2 17:04:21 2024 - [info] Replicating from172.25.254.10(172.25.254.10:3306)Fri Aug 2 17:04:21 2024 - [info] Not candidate for the new Master (no_masteris set)Fri Aug 2 17:04:21 2024 - [info] Current Alive Master:172.25.254.10(172.25.254.10:3306)Fri Aug 2 17:04:21 2024 - [info] Checking slave configurations..Fri Aug 2 17:04:21 2024 - [info] read_only=1 is not set on slave172.25.254.20(172.25.254.20:3306).Fri Aug 2 17:04:21 2024 - [info] read_only=1 is not set on slave172.25.254.30(172.25.254.30:3306).Fri Aug 2 17:04:21 2024 - [info] Checking replication filtering settings..Fri Aug 2 17:04:21 2024 - [info] binlog_do_db= , binlog_ignore_db=Fri Aug 2 17:04:21 2024 - [info] Replication filtering check ok.Fri Aug 2 17:04:21 2024 - [info] GTID (with auto-pos) is supported. Skipping allSSH and Node package checking.Fri Aug 2 17:04:21 2024 - [info] Checking SSH publickey authentication settingson the current master..Fri Aug 2 17:04:21 2024 - [info] HealthCheck: SSH to 172.25.254.10 is reachable.Fri Aug 2 17:04:21 2024 - [info]172.25.254.10(172.25.254.10:3306) (current master)+--172.25.254.20(172.25.254.20:3306)+--172.25.254.30(172.25.254.30:3306)Fri Aug 2 17:04:21 2024 - [info] Checking replication health on 172.25.254.20..Fri Aug 2 17:04:21 2024 - [info] ok.Fri Aug 2 17:04:21 2024 - [info] Checking replication health on 172.25.254.30..Fri Aug 2 17:04:21 2024 - [info] ok.Fri Aug 2 17:04:21 2024 - [warning] master_ip_failover_script is not defined.Fri Aug 2 17:04:21 2024 - [warning] shutdown_script is not defined.Fri Aug 2 17:04:21 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK
2.4 MHA的故障切换
MHA的故障切换过程
切换方式：
master未出现故障手动切换
# 在 master 数据节点还在正常工作情况下[root@mysql-mha ~]# masterha_master_switch \--conf=/etc/masterha/app1.cnf \ # 指定配置文件--master_state=alive \ # 指定 master 节点状态--new_master_host=172.25.254.20 \ # 指定新 master 节点--new_master_port=3306 \ # 执行新 master 节点端口--orig_master_is_new_slave \ # 原始 master 会变成新的 slave--running_updates_limit=10000 # 切换的超时时间
#切换过程如下：
[root@mysql-mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.20 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000Fri Aug 2 18:30:38 2024 - [info] MHA::MasterRotate version 0.58.Fri Aug 2 18:30:38 2024 - [info] Starting online master switch..Fri Aug 2 18:30:38 2024 - [info]Fri Aug 2 18:30:38 2024 - [info] * Phase 1: Configuration Check Phase..Fri Aug 2 18:30:38 2024 - [info]Fri Aug 2 18:30:38 2024 - [warning] Global configuration file/etc/masterha_default.cnf not found. Skipping.Fri Aug 2 18:30:38 2024 - [info] Reading application default configuration from/etc/masterha/app1.cnf..Fri Aug 2 18:30:38 2024 - [info] Reading server configuration from/etc/masterha/app1.cnf..Fri Aug 2 18:30:39 2024 - [info] GTID failover mode = 1Fri Aug 2 18:30:39 2024 - [info] Current Alive Master:172.25.254.10(172.25.254.10:3306)Fri Aug 2 18:30:39 2024 - [info] Alive Slaves:Fri Aug 2 18:30:39 2024 - [info] 172.25.254.20(172.25.254.20:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 18:30:39 2024 - [info] GTID ONFri Aug 2 18:30:39 2024 - [info] Replicating from172.25.254.10(172.25.254.10:3306)Fri Aug 2 18:30:39 2024 - [info] Primary candidate for the new Master(candidate_master is set)Fri Aug 2 18:30:39 2024 - [info] 172.25.254.30(172.25.254.30:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 18:30:39 2024 - [info] GTID ONFri Aug 2 18:30:39 2024 - [info] Replicating from172.25.254.10(172.25.254.10:3306)Fri Aug 2 18:30:39 2024 - [info] Not candidate for the new Master (no_master is set)It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master beforeswitching. Is it ok to execute on 172.25.254.10(172.25.254.10:3306)? (YES/no):yes 《《《《Fri Aug 2 18:30:40 2024 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. Thismay take long time..Fri Aug 2 18:30:40 2024 - [info] ok.Fri Aug 2 18:30:40 2024 - [info] Checking MHA is not monitoring or doing failover..Fri Aug 2 18:30:40 2024 - [info] Checking replication health on 172.25.254.20..Fri Aug 2 18:30:40 2024 - [info] ok. Fri Aug 2 18:30:40 2024 - [info] Checking replication health on 172.25.254.30..Fri Aug 2 18:30:40 2024 - [info] ok.Fri Aug 2 18:30:40 2024 - [info] 172.25.254.20 can be new master.Fri Aug 2 18:30:40 2024 - [info]From:172.25.254.10(172.25.254.10:3306) (current master)+--172.25.254.20(172.25.254.20:3306)+--172.25.254.30(172.25.254.30:3306)To:172.25.254.20(172.25.254.20:3306) (new master)+--172.25.254.30(172.25.254.30:3306)+--172.25.254.10(172.25.254.10:3306)Starting master switch from 172.25.254.10(172.25.254.10:3306) to172.25.254.20(172.25.254.20:3306)? (yes/NO): yesFri Aug 2 18:30:42 2024 - [info] Checking whether172.25.254.20(172.25.254.20:3306) is ok for the new master..Fri Aug 2 18:30:42 2024 - [info] ok.Fri Aug 2 18:30:42 2024 - [info] 172.25.254.10(172.25.254.10:3306): SHOW SLAVESTATUS returned empty result. To check replication filtering rules, temporarilyexecuting CHANGE MASTER to a dummy host.Fri Aug 2 18:30:42 2024 - [info] 172.25.254.10(172.25.254.10:3306): Resettingslave pointing to the dummy host.Fri Aug 2 18:30:42 2024 - [info] ** Phase 1: Configuration Check Phase completed.Fri Aug 2 18:30:42 2024 - [info]Fri Aug 2 18:30:42 2024 - [info] * Phase 2: Rejecting updates Phase..Fri Aug 2 18:30:42 2024 - [info]master_ip_online_change_script is not defined. If you do not disable writes onthe current master manually, applications keep writing on the current master. Isit ok to proceed? (yes/NO): yesFri Aug 2 18:30:43 2024 - [info] Locking all tables on the orig master to rejectupdates from everybody (including root):Fri Aug 2 18:30:43 2024 - [info] Executing FLUSH TABLES WITH READ LOCK..Fri Aug 2 18:30:43 2024 - [info] ok.Fri Aug 2 18:30:43 2024 - [info] Orig master binlog:pos is mysqlbin.000002:1275.Fri Aug 2 18:30:43 2024 - [info] Waiting to execute all relay logs on172.25.254.20(172.25.254.20:3306)..Fri Aug 2 18:30:43 2024 - [info] master_pos_wait(mysql-bin.000002:1275)completed on 172.25.254.20(172.25.254.20:3306). Executed 0 events.Fri Aug 2 18:30:43 2024 - [info] done.Fri Aug 2 18:30:43 2024 - [info] Getting new master's binlog name and position..Fri Aug 2 18:30:43 2024 - [info] mysql-bin.000002:1519Fri Aug 2 18:30:43 2024 - [info] All other slaves should start replication fromhere. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.20',MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl',MASTER_PASSWORD='xxx';Fri Aug 2 18:30:43 2024 - [info]Fri Aug 2 18:30:43 2024 - [info] * Switching slaves in parallel..Fri Aug 2 18:30:43 2024 - [info]Fri Aug 2 18:30:43 2024 - [info] -- Slave switch on host172.25.254.30(172.25.254.30:3306) started, pid: 41941Fri Aug 2 18:30:43 2024 - [info]Fri Aug 2 18:30:45 2024 - [info] Log messages from 172.25.254.30 ... Fri Aug 2 18:30:45 2024 - [info]Fri Aug 2 18:30:43 2024 - [info] Waiting to execute all relay logs on172.25.254.30(172.25.254.30:3306)..Fri Aug 2 18:30:43 2024 - [info] master_pos_wait(mysql-bin.000002:1275)completed on 172.25.254.30(172.25.254.30:3306). Executed 0 events.Fri Aug 2 18:30:43 2024 - [info] done.Fri Aug 2 18:30:43 2024 - [info] Resetting slave172.25.254.30(172.25.254.30:3306) and starting replication from the new master172.25.254.20(172.25.254.20:3306)..Fri Aug 2 18:30:43 2024 - [info] Executed CHANGE MASTER.Fri Aug 2 18:30:44 2024 - [info] Slave started.Fri Aug 2 18:30:45 2024 - [info] End of log messages from 172.25.254.30 ...Fri Aug 2 18:30:45 2024 - [info]Fri Aug 2 18:30:45 2024 - [info] -- Slave switch on host172.25.254.30(172.25.254.30:3306) succeeded.Fri Aug 2 18:30:45 2024 - [info] Unlocking all tables on the orig master:Fri Aug 2 18:30:45 2024 - [info] Executing UNLOCK TABLES..Fri Aug 2 18:30:45 2024 - [info] ok.Fri Aug 2 18:30:45 2024 - [info] Starting orig master as a new slave..Fri Aug 2 18:30:45 2024 - [info] Resetting slave172.25.254.10(172.25.254.10:3306) and starting replication from the new master172.25.254.20(172.25.254.20:3306)..Fri Aug 2 18:30:45 2024 - [info] Executed CHANGE MASTER.Fri Aug 2 18:30:46 2024 - [info] Slave started.Fri Aug 2 18:30:46 2024 - [info] All new slave servers switched successfully.Fri Aug 2 18:30:46 2024 - [info]Fri Aug 2 18:30:46 2024 - [info] * Phase 5: New master cleanup phase..Fri Aug 2 18:30:46 2024 - [info]Fri Aug 2 18:30:46 2024 - [info] 172.25.254.20: Resetting slave info succeeded.Fri Aug 2 18:30:46 2024 - [info] Switching master to172.25.254.20(172.25.254.20:3306) completed successfully.[root@mysql-mha masterha]#
检测：
[root@mysql-mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnfFri Aug 2 18:33:46 2024 - [warning] Globalconfiguration file /etc/masterha_default.cnf not found. Skipping.Fri Aug 2 18:33:46 2024 - [info] Reading application default configuration from/etc/masterha/app1.cnf..Fri Aug 2 18:33:46 2024 - [info] Reading server configuration from/etc/masterha/app1.cnf..Fri Aug 2 18:33:46 2024 - [info] MHA::MasterMonitor version 0.58.Fri Aug 2 18:33:47 2024 - [info] GTID failover mode = 1Fri Aug 2 18:33:47 2024 - [info] Dead Servers:Fri Aug 2 18:33:47 2024 - [info] Alive Servers:Fri Aug 2 18:33:47 2024 - [info] 172.25.254.10(172.25.254.10:3306)Fri Aug 2 18:33:47 2024 - [info] 172.25.254.20(172.25.254.20:3306)Fri Aug 2 18:33:47 2024 - [info] 172.25.254.30(172.25.254.30:3306)Fri Aug 2 18:33:47 2024 - [info] Alive Slaves:Fri Aug 2 18:33:47 2024 - [info] 172.25.254.10(172.25.254.10:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 18:33:47 2024 - [info] GTID ON Fri Aug 2 18:33:47 2024 - [info] Replicating from172.25.254.20(172.25.254.20:3306)Fri Aug 2 18:33:47 2024 - [info] Primary candidate for the new Master(candidate_master is set)Fri Aug 2 18:33:47 2024 - [info] 172.25.254.30(172.25.254.30:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 18:33:47 2024 - [info] GTID ONFri Aug 2 18:33:47 2024 - [info] Replicating from172.25.254.20(172.25.254.20:3306)Fri Aug 2 18:33:47 2024 - [info] Not candidate for the new Master (no_master is set)Fri Aug 2 18:33:47 2024 - [info] Current Alive Master:172.25.254.20(172.25.254.20:3306)Fri Aug 2 18:33:47 2024 - [info] Checking slave configurations..Fri Aug 2 18:33:47 2024 - [info] read_only=1 is not set on slave172.25.254.30(172.25.254.30:3306).Fri Aug 2 18:33:47 2024 - [info] Checking replication filtering settings..Fri Aug 2 18:33:47 2024 - [info] binlog_do_db= , binlog_ignore_db=Fri Aug 2 18:33:47 2024 - [info] Replication filtering check ok.Fri Aug 2 18:33:47 2024 - [info] GTID (with auto-pos) is supported. Skipping allSSH and Node package checking.Fri Aug 2 18:33:47 2024 - [info] Checking SSH publickey authentication settingson the current master..Fri Aug 2 18:33:47 2024 - [info] HealthCheck: SSH to 172.25.254.20 is reachable.Fri Aug 2 18:33:47 2024 - [info]172.25.254.20(172.25.254.20:3306) (current master)+--172.25.254.10(172.25.254.10:3306)+--172.25.254.30(172.25.254.30:3306)Fri Aug 2 18:33:47 2024 - [info] Checking replication health on 172.25.254.10..Fri Aug 2 18:33:47 2024 - [info] ok.Fri Aug 2 18:33:47 2024 - [info] Checking replication health on 172.25.254.30..Fri Aug 2 18:33:47 2024 - [info] ok.Fri Aug 2 18:33:47 2024 - [warning] master_ip_failover_script is not defined.Fri Aug 2 18:33:47 2024 - [warning] shutdown_script is not defined.Fri Aug 2 18:33:47 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK
master故障手动切换
# 模拟 master 故障[root@mysql-node20 mysql]# /etc/init.d/mysqld stop# 在 MHA-master 中做故障切换[root@mysql-mha masterha]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.56.12 --dead_master_port=3306 --new_master_host=192.168.56.11 --new_master_port=3306 --ignore_last_failover--ignore_last_failover 表示忽略在 /etc/masterha/ 目录中在切换过程中生成的锁文件masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.20 --dead_master_port=3306 --new_master_host=172.25.254.10 --new_master_port=3306 --ignore_last_failover--dead_master_ip=<dead_master_ip> is not set. Using 172.25.254.20.Fri Aug 2 19:38:35 2024 - [warning] Global configuration file/etc/masterha_default.cnf not found. Skipping.Fri Aug 2 19:38:35 2024 - [info] Reading application default configuration from/etc/masterha/app1.cnf..Fri Aug 2 19:38:35 2024 - [info] Reading server configuration from/etc/masterha/app1.cnf..Fri Aug 2 19:38:35 2024 - [info] MHA::MasterFailover version 0.58.Fri Aug 2 19:38:35 2024 - [info] Starting master failover.Fri Aug 2 19:38:35 2024 - [info]Fri Aug 2 19:38:35 2024 - [info] * Phase 1: Configuration Check Phase..Fri Aug 2 19:38:35 2024 - [info]Fri Aug 2 19:38:36 2024 - [info] GTID failover mode = 1Fri Aug 2 19:38:36 2024 - [info] Dead Servers:Fri Aug 2 19:38:36 2024 - [info] 172.25.254.20(172.25.254.20:3306)Fri Aug 2 19:38:36 2024 - [info] Checking master reachability via MySQL(double check)...Fri Aug 2 19:38:36 2024 - [info] ok.Fri Aug 2 19:38:36 2024 - [info] Alive Servers:Fri Aug 2 19:38:36 2024 - [info] 172.25.254.10(172.25.254.10:3306)Fri Aug 2 19:38:36 2024 - [info] 172.25.254.30(172.25.254.30:3306)Fri Aug 2 19:38:36 2024 - [info] Alive Slaves:Fri Aug 2 19:38:36 2024 - [info] 172.25.254.10(172.25.254.10:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 19:38:36 2024 - [info] GTID ONFri Aug 2 19:38:36 2024 - [info] Replicating from172.25.254.20(172.25.254.20:3306)Fri Aug 2 19:38:36 2024 - [info] Primary candidate for the new Master (candidate_master is set)Fri Aug 2 19:38:36 2024 - [info] 172.25.254.30(172.25.254.30:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 19:38:36 2024 - [info] GTID ONFri Aug 2 19:38:36 2024 - [info] Replicating from172.25.254.20(172.25.254.20:3306)Fri Aug 2 19:38:36 2024 - [info] Not candidate for the new Master (no_master is set)Master 172.25.254.20(172.25.254.20:3306) is dead. Proceed? (yes/NO): yesFri Aug 2 19:38:39 2024 - [info] Starting GTID based failover.Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] ** Phase 1: Configuration Check Phase completed.Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] * Phase 2: Dead Master Shutdown Phase..Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] HealthCheck: SSH to 172.25.254.20 is reachable.Fri Aug 2 19:38:39 2024 - [info] Forcing shutdown so that applications neverconnect to the current master..Fri Aug 2 19:38:39 2024 - [warning] master_ip_failover_script is not set.Skipping invalidating dead master IP address.Fri Aug 2 19:38:39 2024 - [warning] shutdown_script is not set. Skippingexplicit shutting down of the dead master.Fri Aug 2 19:38:39 2024 - [info] * Phase 2: Dead Master Shutdown Phase completed.Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] * Phase 3: Master Recovery Phase..Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] * Phase 3.1: Getting Latest Slaves Phase..Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] The latest binary log file/position on all slaves is mysql-bin.000002:1519Fri Aug 2 19:38:39 2024 - [info] Retrieved Gtid Set: 1a02fc44-4d68-11ef-8dd9- 000c29d8cf7e:1Fri Aug 2 19:38:39 2024 - [info] Latest slaves (Slaves that received relay log files to the latest):Fri Aug 2 19:38:39 2024 - [info] 172.25.254.10(172.25.254.10:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 19:38:39 2024 - [info] GTID ONFri Aug 2 19:38:39 2024 - [info] Replicating from 172.25.254.20(172.25.254.20:3306)Fri Aug 2 19:38:39 2024 - [info] Primary candidate for the new Master (candidate_master is set)Fri Aug 2 19:38:39 2024 - [info] 172.25.254.30(172.25.254.30:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 19:38:39 2024 - [info] GTID ONFri Aug 2 19:38:39 2024 - [info] Replicating from 172.25.254.20(172.25.254.20:3306)Fri Aug 2 19:38:39 2024 - [info] Not candidate for the new Master (no_master is set)Fri Aug 2 19:38:39 2024 - [info] The oldest binary log file/position on all slaves is mysql-bin.000002:1519Fri Aug 2 19:38:39 2024 - [info] Retrieved Gtid Set: 1a02fc44-4d68-11ef-8dd9- 000c29d8cf7e:1Fri Aug 2 19:38:39 2024 - [info] Oldest slaves:Fri Aug 2 19:38:39 2024 - [info] 172.25.254.10(172.25.254.10:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 19:38:39 2024 - [info] GTID ONFri Aug 2 19:38:39 2024 - [info] Replicating from172.25.254.20(172.25.254.20:3306)Fri Aug 2 19:38:39 2024 - [info] Primary candidate for the new Master (candidate_master is set)Fri Aug 2 19:38:39 2024 - [info] 172.25.254.30(172.25.254.30:3306)Version=5.7.44-log (oldest major version between slaves) log-bin:enabledFri Aug 2 19:38:39 2024 - [info] GTID ONFri Aug 2 19:38:39 2024 - [info] Replicating from172.25.254.20(172.25.254.20:3306)Fri Aug 2 19:38:39 2024 - [info] Not candidate for the new Master (no_master is set)Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] * Phase 3.3: Determining New Master Phase..Fri Aug 2 19:38:39 2024 - [info]Fri Aug 2 19:38:39 2024 - [info] 172.25.254.10 can be new master.Fri Aug 2 19:38:39 2024 - [info] New master is 172.25.254.10(172.25.254.10:3306)Fri Aug 2 19:38:39 2024 - [info] Starting master failover..Fri Aug 2 19:38:39 2024 - [info]From:172.25.254.20(172.25.254.20:3306) (current master)+--172.25.254.10(172.25.254.10:3306)+--172.25.254.30(172.25.254.30:3306) To:172.25.254.10(172.25.254.10:3306) (new master)+--172.25.254.30(172.25.254.30:3306)Starting master switch from 172.25.254.20(172.25.254.20:3306) to172.25.254.10(172.25.254.10:3306)? (yes/NO): yesFri Aug 2 19:38:41 2024 - [info] New master decided manually is172.25.254.10(172.25.254.10:3306)Fri Aug 2 19:38:41 2024 - [info]Fri Aug 2 19:38:41 2024 - [info] * Phase 3.3: New Master Recovery Phase..Fri Aug 2 19:38:41 2024 - [info]Fri Aug 2 19:38:41 2024 - [info] Waiting all logs to be applied..Fri Aug 2 19:38:41 2024 - [info] done.Fri Aug 2 19:38:41 2024 - [info] Getting new master's binlog name and position..Fri Aug 2 19:38:41 2024 - [info] mysql-bin.000002:1519Fri Aug 2 19:38:41 2024 - [info] All other slaves should start replication fromhere. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.254.10',MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl',MASTER_PASSWORD='xxx';Fri Aug 2 19:38:41 2024 - [info] Master Recovery succeeded.File:Pos:Exec_Gtid_Set: mysql-bin.000002, 1519, 1a02fc44-4d68-11ef-8dd9-000c29d8cf7e:1,68f3a901-4deb-11ef-8055-000c29cb63ce:1-5Fri Aug 2 19:38:41 2024 - [warning] master_ip_failover_script is not set.Skipping taking over new master IP address.Fri Aug 2 19:38:41 2024 - [info] Setting read_only=0 on172.25.254.10(172.25.254.10:3306)..Fri Aug 2 19:38:41 2024 - [info] ok.Fri Aug 2 19:38:41 2024 - [info] ** Finished master recovery successfully.Fri Aug 2 19:38:41 2024 - [info] * Phase 3: Master Recovery Phase completed.Fri Aug 2 19:38:41 2024 - [info]Fri Aug 2 19:38:41 2024 - [info] * Phase 4: Slaves Recovery Phase..Fri Aug 2 19:38:41 2024 - [info]Fri Aug 2 19:38:41 2024 - [info]Fri Aug 2 19:38:41 2024 - [info] * Phase 4.1: Starting Slaves in parallel..Fri Aug 2 19:38:41 2024 - [info]Fri Aug 2 19:38:41 2024 - [info] -- Slave recovery on host172.25.254.30(172.25.254.30:3306) started, pid: 42023. Check tmp log/etc/masterha/172.25.254.30_3306_20240802193835.log if it takes time..Fri Aug 2 19:38:43 2024 - [info]Fri Aug 2 19:38:43 2024 - [info] Log messages from 172.25.254.30 ...Fri Aug 2 19:38:43 2024 - [info]Fri Aug 2 19:38:41 2024 - [info] Resetting slave172.25.254.30(172.25.254.30:3306) and starting replication from the new master172.25.254.10(172.25.254.10:3306)..Fri Aug 2 19:38:41 2024 - [info] Executed CHANGE MASTER.Fri Aug 2 19:38:42 2024 - [info] Slave started.Fri Aug 2 19:38:42 2024 - [info] gtid_wait(1a02fc44-4d68-11ef-8dd9- 000c29d8cf7e:1,68f3a901-4deb-11ef-8055-000c29cb63ce:1-5) completed on172.25.254.30(172.25.254.30:3306). Executed 0 events.Fri Aug 2 19:38:43 2024 - [info] End of log messages from 172.25.254.30.Fri Aug 2 19:38:43 2024 - [info] -- Slave on host172.25.254.30(172.25.254.30:3306) started.Fri Aug 2 19:38:43 2024 - [info] All new slave servers recovered successfully.Fri Aug 2 19:38:43 2024 - [info] Fri Aug 2 19:38:43 2024 - [info] * Phase 5: New master cleanup phase..Fri Aug 2 19:38:43 2024 - [info]Fri Aug 2 19:38:43 2024 - [info] Resetting slave info on the new master..Fri Aug 2 19:38:43 2024 - [info] 172.25.254.10: Resetting slave info succeeded.Fri Aug 2 19:38:43 2024 - [info] Master failover to172.25.254.10(172.25.254.10:3306) completed successfully.Fri Aug 2 19:38:43 2024 - [info]----- Failover Report -----app1: MySQL Master failover 172.25.254.20(172.25.254.20:3306) to172.25.254.10(172.25.254.10:3306) succeededMaster 172.25.254.20(172.25.254.20:3306) is down!Check MHA Manager logs at mysql-mha.timinglee.org for details.Started manual(interactive) failover.Selected 172.25.254.10(172.25.254.10:3306) as a new master.172.25.254.10(172.25.254.10:3306): OK: Applying all logs succeeded.172.25.254.30(172.25.254.30:3306): OK: Slave started, replicating from172.25.254.10(172.25.254.10:3306)172.25.254.10(172.25.254.10:3306): Resetting slave info succeeded.Master failover to 172.25.254.10(172.25.254.10:3306) completed successfully.
恢复故障mysql节点
[root@mysql-node20 tmp]# /etc/init.d/mysqld startStarting MySQL. SUCCESS![root@mysql-node20 tmp]# mysql -pmysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;mysql> show slave\G# 测试一主两从是否正常[root@mysql-mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnfFri Aug 2 20:15:29 2024 - [info] Checking replication health on 172.25.254.20..Fri Aug 2 20:15:29 2024 - [info] ok.Fri Aug 2 20:15:29 2024 - [info] Checking replication health on 172.25.254.30..Fri Aug 2 20:15:29 2024 - [info] ok.Fri Aug 2 20:15:29 2024 - [warning] master_ip_failover_script is not defined.Fri Aug 2 20:15:29 2024 - [warning] shutdown_script is not defined.Fri Aug 2 20:15:29 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.
自动切换
[root@mysql-mha masterha]# rm -fr app1.failover.complete # 删掉切换锁文件# 监控程序通过指定配置文件监控 master 状态，当 master 出问题后自动切换并退出避免重复做故障切换[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf[root@mysql-mha masterha]# cat /etc/masterha/manager.log
# 恢复故障节点[root@mysql-node20 mysql]# /etc/init.d/mysqld startmysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1
清除锁文件
[root@mysql-mha masterha]# rm -rf app1.failover.complete manager.log
2.5 为MHA添加VIP功能
#上传在群中发给大家的脚本
[root@mysql-mha ~]# ls
master_ip_failover master_ip_online_change MHA-7 MHA-7.zip
[root@mysql-mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@mysql-mha ~]# chmod +x /usr/local/bin/master_ip_*
#修改脚本在脚本中只需要修改下vip即可
[root@mysql-mha ~]# vim /usr/local/bin/master_ip_failover
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
[root@mysql-mha ~]# vim /usr/local/bin/master_ip_online_change
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;
[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf & 启动监控程序
[root@mysql-node10 tmp]# ip a a 172.25.254.100/24 dev eth0 #在master节点添加VIP模拟故障
[root@mysql-node10 ~]# /etc/init.d/mysqld stop # 关闭主节点服务[root@mysql-mha masterha]# cat manager.log
恢复故障主机
[root@mysql-node20 mysql]# /etc/init.d/mysqld startmysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1[root@mysql-mha masterha]# rm -rf app1.failover.complete manager.log
手动切换后查看vip变化
[root@mysql-mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf--master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000[root@mysql-node10 ~]# ip a1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group defaultqlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UPgroup default qlen 1000link/ether 00:0c:29:cb:63:ce brd ff:ff:ff:ff:ff:ffinet 172.25.254.10/24 brd 172.25.254.255 scope global noprefixroute eth0valid_lft forever preferred_lft foreverinet 172.25.254.100/24 scope global secondary eth0valid_lft forever preferred_lft forever
实验部分
1、源码编译安装MySQL
解压安装包
安装依赖
[root@mysql-node1 ~]# yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm rpcgen.x86_64 -y
源码编译安装
[root@mysql-node1 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 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/
[root@mysql-node1 mysql-5.7.44]# make -j2
[root@mysql-node1 mysql-5.7.44]# make install
删除缓存
[root@mysql-node1 mysql-5.7.44]# rm -fr CMakeCache.txt
部署mysql
[root@mysql-node1 mysql-5.7.44]# cd /usr/local/mysql/
[root@mysql-node1 mysql]# ls
bin include LICENSE mysql-test README-test support-files
docs lib man README share
[root@mysql-node1 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 mysql]# mkdir -p /data/mysql
[root@mysql-node1 mysql]# chown mysql.mysql -R /data/mysql
[root@mysql-node1 mysql]# pwd
/usr/local/mysql
#生成启动脚本
[root@mysql-node1 mysql]# ls
bin include LICENSE mysql-test README-test support-files
docs lib man README share
[root@mysql-node1 mysql]# cd support-files/
[root@mysql-node1 support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@mysql-node1 support-files]# cp mysql.server /etc/init.d/mysqld
#生成配置文件
[root@mysql-node1 support-files]# vim /etc/my.cnf
[root@mysql-node1 support-files]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[root@mysql-node1 support-files]# ls /data/mysql/
[root@mysql-node1 support-files]# ll /usr/local/mysql/bin/
总用量 1059020
#修改环境变量
#数据库初始化建立mysql基本数据
[root@mysql-node1 support-files]# vim passwd
[root@mysql-node1 support-files]# cat passwd
eyu,IfqF.2-a
[root@mysql-node1 support-files]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.timinglee.org.err'.
SUCCESS!
[root@mysql-node1 support-files]# yum install chkconfig -y
#数据库安全初始化
[root@mysql-node1 support-files]# mysql_secure_installation
测试
[root@mysql-node2 ~]# cd /usr/local/
[root@mysql-node2 local]# ls
bin etc games include lib lib64 libexec sbin share src
[root@mysql-node2 local]# useradd -s /sbin/nologin -M mysql
[root@mysql-node2 local]# mkdir -p /data/mysql
[root@mysql-node2 local]# chown mysql.mysql /data/mysql/
[root@mysql-node2 local]# cd /usr/local/
[root@mysql-node2 support-files]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node2.timinglee.org.err'.
SUCCESS!
[root@mysql-node2 support-files]# mysql_secure_installation
2、mysql的组从复制
配置mastesr
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 595
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
[root@mysql-node1 ~]# cd /data/mysql/
[root@mysql-node1 mysql]# mysqlbinlog mysql-bin.000001
配置salve
[root@mysql-node2 support-files]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
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> change master to master_host='172.25.254.10',master_user='repl',master_password='lee',master_log_file='mysql-bin.000001',master_log_pos=595;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 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: 595
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: 595
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: 10
Master_UUID: 50ebac2f-6030-11ef-bae9-000c2997e81d
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)
测试
当有数据时添加slave2
root@.25.254.30's password:
[root@mysql-node3 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node3 ~]# mkdir -p /data/mysql/
[root@mysql-node3 ~]# chown mysql.mysql /data/mysql/
[root@mysql-node3 ~]# vim /etc/my.cnf
[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
[root@mysql-node3 ~]# mysql_secure_installation
[root@mysql-node3 ~]# vim /etc/my.cnf
[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
[root@mysql-node3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@mysql-node1 ~]# mysqldump -uroot -p123 lee > lee.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-node1 ~]# scp lee.sql root@172.25.254.30:/mnt/
root@172.25.254.30's password:
lee.sql 100% 1943 2.0MB/s 00:00
[root@mysql-node3 ~]# cd /mnt/
[root@mysql-node3 mnt]# ls
lee.sql
[root@mysql-node3 ~]# mysql -uroot -p123 -e "create database lee;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql-node3 mnt]# mysql -uroot -p lee < lee.sql
Enter password:
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=1236;
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: 1236
Relay_Log_File: mysql-node3-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: 1236
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: 10
Master_UUID: 50ebac2f-6030-11ef-bae9-000c2997e81d
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-node1~]# mysql -uroot -p123
mysql> insert into lee.userlist values ('user2','123');
Query OK, 1 row affected (0.03 sec)
mysql> select * from lee.userlist;
+----------+------------------+
| username | password |
+----------+------------------+
| lee | 123 |
| user2 | 123 |
+----------+-------------------+
2 rows in set (0.01 sec)
[root@mysql-node3 ~]# mysql -uroot -p123
mysql> select * from lee.userlist;
+----------+-----------------+
| username | password |
+----------+------------------+
| lee | 123 |
| user2 | 123 |
+----------+------------------+
2 rows in set (0.00 sec)
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> select * from lee.userlist;
+----------+-----------------+
| username | password |
+----------+------------------+
| lee | 123 |
| user2 | 123 |
+----------+------------------+
2 rows in set (0.00 sec)
3、延迟复制
[root@mysql-node3 ~]# mysql -uroot -p123
mysql> stop slave sql_thread ;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_delay=60;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
[root@mysql-node1 ~]# mysql -uroot -p123
[root@mysql-node3 ~]# mysql -uroot -p123
60秒之后
4、慢查询
5、mysql的并行复制
开启MySQL的多线程回放可以解决上述问题
[root@mysql-node2 support-files]# vim /etc/my.cnf
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.... SUCCESS!
6、mysql的gitd日志模式
[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL............ SUCCESS!
Starting MySQL.... SUCCESS!
[root@mysql-node3 mnt]# vim /etc/my.cnf
[root@mysql-node3 mnt]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
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.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 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.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-node2-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
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: 10
Master_UUID: 50ebac2f-6030-11ef-bae9-000c2997e81d
Master_Info_File: mysql.slave_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_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)
7、半同步模式
[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# mysql -uroot -p
[root@mysql-node2 ~]# vim /etc/my.cnf
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
[root@mysql-node1 ~]# mysql -uroot -p
[root@mysql-node3~]# mysql -uroot -p
模拟故障
master
slave
master
slave
8、MySQL组复制
停掉所以MySQL服务 （为了避免出错，在所有节点中从新生成数据库数据）
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL........... SUCCESS!
[root@mysql-node1 ~]# rm -fr /data/mysql/
[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.44-log
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 '123';
Query OK, 0 rows affected (0.01 sec)
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.02 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.08 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
三台主机都要做解析
[root@mysql-node1 ~]# vim /etc/hosts
#在复制配置文件到myql-node20和mysql-node30
#修改mysql—node20和mysl-node30中的配置
[root@mysql-node2 ~]# vim /etc/my.cnf
[root@mysql-node3 ~]# vim /etc/my.cnf
测试
mysql> CREATE TABLE lee.userlist(
-> username VARCHAR(10) PRIMARY KEY NOT NULL,
-> password varchar(50) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into lee.userlist values('user1','123');
Query OK, 1 row affected (0.17 sec)
[root@mysql-node3 ~]# mysql -uroot -p
若一台掉线，应手动开启
9、mysql-router负载均衡
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@mysql-node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[root@mysql-node2 ~]# mysql -uroot -p
mysql> create user root@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# mysql -uroot -p
mysql> create user root@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
10、mysql高可用之MHA
[root@mysql-mha ~]# ls
anaconda-ks.cfg master_ip_failover master_ip_online_change MHA-7.zip
[root@mysql-mha ~]# yum -y install unzip zip
[root@mysql-mha ~]# vim /etc/hosts
[root@mysql-node3 ~]# vim /etc/my.cnf
[root@mysql-node2 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# /etc/init.d/mysqld stop
[root@mysql-node2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@mysql-node3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
恢复一组两从
[root@mysql-node1 ~]# rm -fr /data/mysql/*
[root@mysql-node2 ~]# rm -fr /data/mysql/*
[root@mysql-node3 ~]# rm -fr /data/mysql/*
slave配置
安装MHA所需要的软件
[root@mysql-node1 ~]# vim /etc/ssh/sshd_config
[root@mysql-node1 ~]# systemctl restart sshd
#在sql-node中
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.10:/root
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 15.9MB/s 00:00
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.20:/root
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 9.3MB/s 00:00
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.30:/root
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 14.8MB/s 00:00
[root@mysql-node1 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql-node2 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql-node3 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
#生成配置文件
[root@mysql-mha MHA-7]# mkdir /etc/masterha
[root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@mysql-mha MHA-7]# cd mha4mysql-manager-0.58/samples/conf/
[root@mysql-mha conf]# ls
app1.cnf masterha_default.cnf
[root@mysql-mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf
#编辑配置文件
[root@mysql-mha conf]# vim /etc/masterha/app1.cnf
允许远程连接
检测
ssh免密测试
检测数据主从复制情况
[root@mysql-node2 ~]# ip a a 172.25.254.11/24 dev eth0
[root@mysql-node2 ~]# mysql -uroot -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)
11、MySQL
[root@mysql-mha ~]# vim /etc/masterha/app1.cnf
[root@mysql-mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.20 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
2012
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
