热门标签
热门文章
- 1每天搞懂一个小漏洞——jwt_jwt越权
- 2【重磅】2024国内主流AI 大模型架构及应用场景深度分析_ai大模型框架
- 3开发者实战 | 如何应用Anomalib在数据集不平衡的情况下检测缺陷？-上篇
- 4ChatGLM3在Docker环境部署_docker部署安装chatglm3
- 5IDEA的pom.xml文件上面有蜘蛛标志_idea中xml意外的标记
- 6学习 JSON.parseObject 和 JSON.toJSONString 一篇文章就够了
- 7免费，Python蓝桥杯等级考试真题--第7级（含答案解析和代码）_python 七级
- 8acm专题学习之动态规划（一）状压dp+记忆化搜索+uva 10651 - Pebble Solitaire_acm 旅行商问题 记忆化搜索
- 9pip升级报错，或者pip install pywin32报错_pip 安装pywin32报错
- 10超详细教程 | Hands-On 基于 Flagchip FC4150 MCAL-PWM 驱动 RGB灯
当前位置: article > 正文
mysql-多主复制、负载均衡、MHA高可用及主从切换_mysql 多主
作者：从前慢现在也慢 | 2024-08-20 23:18:45
赞
踩
mysql 多主
1、mysql多主模式配置
准备三台虚拟机： server1 、server2、server3
组复制必须保证三台数据库完全一致，否则会失败
点击搭建多主模式官方文档查看
server1配置:
[root@server1 data]# /etc/init.d/mysqld stop 停掉数据库
Shutting down MySQL............ SUCCESS!
[root@server1 ~]# cd /usr/local/mysql/data 进入mysql数据目录
[root@server1 data]# rm -fr * 删除原始数据
[root@server1 data]# mysqld --initialize --user=mysql 初始化,不用执行 mysql_secure_installation ，需要三台完全一致
- 1
- 2
- 3
- 4
- 5
[root@server1 data]# vim /etc/my.cnf 编辑配置文件 [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock symbolic-links=0 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 表示创建数据库等等时这些引擎此时不允许调用 server_id=1 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.50.1:33061" 主机名和端口 group_replication_group_seeds= "172.25.50.1:33061,172.25.50.2:33061,172.25.50.3:33061" 组员 group_replication_bootstrap_group=off group_replication_ip_whitelist="172.25.50.0/24,127.0.0.1/8" 默认只能是本地主机ip地址加到mgr里面，加上172.25.50.0/24 表示允许50网段也加到mgr里面 group_replication_single_primary_mode=OFF 默认为单组模式，后面=OFF就是多组模式 group_replication_enforce_update_everywhere_checks=ON 打开更新和检测
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
[root@server1 data]# /etc/init.d/mysqld start 开启mysql Starting MySQL.Logging to '/usr/local/mysql/data/server1.err'. SUCCESS! [root@server1 data]# mysql -p 登陆服务器 Enter password: 输入生成的密码 mysql> show databases; 但是是临时密码登陆，数据库不能用 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> alter user root@localhost identified by 'westos'; 需要修改本机数据库密码 Query OK, 0 rows affected (0.10 sec) mysql> show databases; 修改密码后就可以使用了 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> SET SQL_LOG_BIN=0; ##关闭日志同步 Query OK, 0 rows affected (0.00 sec) [root@server1 data]# /etc/init.d/mysqld stop 停掉mysql Shutting down MySQL.. SUCCESS!
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos'; 创建用于复制的用户 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='westos' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.40 sec) mysql> SET GLOBAL group_replication_bootstrap_group=ON; 设置组，让server1作为组的初始节点 ，只有server1需要设置此选项 mysql> START GROUP_REPLICATION; 启动组加入 Query OK, 0 rows affected (2.28 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; 查看mgr集群中节点状态，发现server1已经是集群mgr中的节点了
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
server2配置：
[root@server2 ~]# /etc/init.d/mysqld stop 停止数据库
Shutting down MySQL... SUCCESS!
[root@server2 ~]# cd /usr/local/mysql/data/ 进入数据目录
[root@server2 data]# rm -fr * 删除数据
[root@server1 data]# mysqld --initialize --user=mysql 初始化
- 1
- 2
- 3
- 4
- 5
[root@server2 data]# vim /etc/my.cnf 编辑配置文件 [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock symbolic-links=0 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 表示创建数据库等等时这些引擎此时不允许调用 server_id=2 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.50.2:33061" group_replication_group_seeds= "172.25.50.1:33061,172.25.50.2:33061,172.25.50.3:33061" group_replication_bootstrap_group=off group_replication_ip_whitelist="172.25.50.0/24,127.0.0.1/8" group_replication_single_primary_mode=OFF group_replication_enforce_update_everywhere_checks=ON group_replication_allow_local_disjoint_gtids_join=ON
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
[root@server2 data]# /etc/init.d/mysqld start 启动mysql Starting MySQL.Logging to '/usr/local/mysql/data/server2.err'. . SUCCESS! [root@server2 data]# mysql -p 登陆数据库 Enter password: 输入生成的临时密码 mysql> alter user root@localhost identified by 'westos'; 修改本地数据库密码 Query OK, 0 rows affected (0.06 sec) mysql> show databases; 可以查看数据库了 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
mysql> SET SQL_LOG_BIN=0; 关闭二进制日志
Query OK, 0 rows affected (0.00 sec)
mysql>CREATE USER rpl_user@'%' IDENTIFIED BY 'westos'; 创建复制的用户
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' ; 授予复制权限
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='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.50 sec)
mysql> START GROUP_REPLICATION; 启动，如果报错，可以查看 日志cat server2.err
Query OK, 0 rows affected, 1 warning (5.16 sec) 启动成功
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
server3配置：
[root@server3 ~]# cd /usr/local/mysql/data/ 进入数据目录
[root@server3 data]# rm -fr * 删除数据
[root@server3 data]# vim /etc/my.cnf 编辑配置文件，将多余的参数删除，只保留mysql.sock路经
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
[root@server3 data]# mysqld --initialize --user=mysql 初始化
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
[root@server3 data]# vim /etc/my.cnf 编辑配置文件添加参数 [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock symbolic-links=0 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" server_id=3 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.50.3:33061" group_replication_group_seeds= "172.25.50.1:33061,172.25.50.2:33061,172.25.50.3:33061" group_replication_bootstrap_group=off group_replication_ip_whitelist="172.25.50.0/24,127.0.0.1/8" group_replication_single_primary_mode=OFF group_replication_enforce_update_everywhere_checks=ON group_replication_allow_local_disjoint_gtids_join=ON
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
[root@server3 data]# /etc/init.d/mysqld start 启动mysql Starting MySQL.Logging to '/usr/local/mysql/data/server3.err'. . SUCCESS! [root@server3 data]# mysql -p 登陆数据库 Enter password: 输入生成的临时密码 mysql> alter user root@localhost identified by 'westos'; 修改本地数据库密码 Query OK, 0 rows affected (0.02 sec) mysql> SET SQL_LOG_BIN=0; 关闭二进制日志 Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'westos'; 创建复制用户 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> SET SQL_LOG_BIN=1; 开启二进制日志 Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.57 sec) mysql> START GROUP_REPLICATION; 启动，如果报错，可以查看 日志cat server2.err Query OK, 0 rows affected, 1 warning (5.16 sec) 启动成功
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
[root@server1 data]# mysql -p 登陆数据库,发现集群搭建成功，server1、server2、server3 都已经加入到组里，状态都是ONLINE
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 59b3d190-67e2-11ec-a91f-52540001ce24 | server1 | 33061 | ONLINE |
| group_replication_applier | 6f7713ae-67e3-11ec-b55e-525400eb7606 | server2 | 33061 | ONLINE |
| group_replication_applier | dae888ef-67da-11ec-a992-5254005fd43e | server3 | 33061 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
测试： [root@server1 data]# mysql -pwestos 登陆数据库 mysql> CREATE DATABASE test; Query OK, 1 row affected (0.11 sec) mysql> USE test; 进入数据库 mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); 创建表，表必须要有主建约束 Query OK, 0 rows affected (0.61 sec) mysql> INSERT INTO t1 VALUES (1, 'Luis'); 插入数据 Query OK, 1 row affected (0.16 sec) server1上创建数局库创建表插入数据，server2、server3都同步了 [root@server2 data]# mysql -pwestos mysql> use test; mysql> select * from t1; server2 上已经存在数据了 +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec) [root@server2 data]# mysql -pwestos 登陆数据库，同样server3上也有数据了 mysql> select * from test.t1; +----+------+ | c1 | c2 | +----+------+ | 1 | Luis | +----+------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
2、mysql 添加代理做负载均衡
[root@foundation50 mysql]# scp mysql-router-community-8.0.21-1.el7.x86_64.rpm server4:/mnt 将下载的mysql代理插件拷贝到server4的mnt里 [root@server4 mnt]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm 安装mysql代理插件 [root@server4 mysqlrouter]# vim mysqlrouter.conf 添加，编辑配置文件 [routing:ro] ###只读 bind_address = 0.0.0.0 bind_port = 7001 ##端口为7001 destinations = 172.25.50.1:33061,172.25.50.2:33061,172.25.50.3:33061 routing_strategy = round-robin ##轮询 [routing:rw] ##读写 bind_address = 0.0.0.0 bind_port = 7002 ##端口为7002 destinations = 172.25.50.1:33061,172.25.50.2:33061,172.25.50.3:33061 routing_strategy = first-available ###看第一个 [root@server4 mysqlrouter]# systemctl start mysqlrouter.service 启动mysqlrouter [root@foundation50 ~]# mysql -h 172.25.50.4 -P 7001 -u root -p 远程连接server4数据库连接不上，只能连接本地的 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'server4' (using password: YES) [root@server1 data]# mysql -p 登陆数据库 ，在任何一台主机server1、server2、server3都可以，每个节点都可以读和写 Enter password: mysql> grant select on *.* to user1@'%' identified by 'westos'; 授权user1可以远程登陆查看权限 Query OK, 0 rows affected, 1 warning (0.16 sec) mysql> grant insert,update on test.* to user2@'%' identified by 'westos'; 授权user2可以远程登陆插入和更新权限 Query OK, 0 rows affected, 1 warning (0.14 sec) mysql> flush privileges; 刷新用户授权表 Query OK, 0 rows affected (0.13 sec) 测试： server1、server2、server3上安装 yum install lsof -y 方便查看谁连接谁 [root@foundation50 ~]# mysql -h 172.25.50.4 -P 7001 -u root -p 登陆 [root@server1 data]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 6881 mysql 21u IPv6 44825 0t0 TCP *:mysql (LISTEN) mysqld 6881 mysql 49u IPv6 91597 0t0 TCP server1:mysql->server4:36742 (ESTABLISHED) 连的是server1 [root@foundation50 ~]# mysql -h 172.25.50.4 -P 7001 -u user1 -p 重新连接 [root@server2 data]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 8658 mysql 32u IPv6 43317 0t0 TCP *:mysql (LISTEN) mysqld 8658 mysql 55u IPv6 47237 0t0 TCP server2:mysql->server4:47340 (ESTABLISHED) 连的是server2 [root@server3 data]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 9022 mysql 31u IPv6 37737 0t0 TCP *:mysql (LISTEN) mysqld 9022 mysql 54u IPv6 43017 0t0 TCP server3:mysql->server4:49306 (ESTABLISHED) 连的是server3 说明server4在做负载均衡 换个用户和列表 [root@foundation50 ~]# mysql -h 172.25.50.4 -P 7002 -u user2 -pwestos 连接 MySQL [(none)]> INSERT INTO test.t1 VALUES (2,'wxh'); 插入数据 Query OK, 1 row affected (0.171 sec) [root@server1 data]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 6881 mysql 21u IPv6 44825 0t0 TCP *:mysql (LISTEN) mysqld 6881 mysql 49u IPv6 91967 0t0 TCP server1:mysql->server4:36802 (ESTABLISHED) 还是连接的server1，算法不一样，第一个好着一直连接第一个 [root@server1 data]# /etc/init.d/mysqld stop 将server1数据库关闭 Shutting down MySQL............. SUCCESS! [root@server2 data]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 8658 mysql 32u IPv6 43317 0t0 TCP *:mysql (LISTEN) mysqld 8658 mysql 55u IPv6 47308 0t0 TCP server2:mysql->server4:47422 (ESTABLISHED) 客户端从连，server1不可用，连接server2 MySQL [(none)]> INSERT INTO test.t1 VALUES (3,'westos'); 继续插入数据 Query OK, 1 row affected (0.214 sec) [root@server3 data]# mysql -pwestos server3上登陆 mysql> select * from test.t1; 可以发现刚才插入的数据也已经同步到server3上了，server1停掉集群也是好的 +----+--------+ | c1 | c2 | +----+--------+ | 1 | Luis | | 2 | wxh | | 3 | westos | +----+--------+ 3 rows in set (0.00 sec) [root@server1 data]# /etc/init.d/mysqld start 开启server1数据库 Starting MySQL. SUCCESS! root@server1 data]# mysql -pwestos 登陆server1 mysql> START GROUP_REPLICATION; 启动组复制 Query OK, 0 rows affected (3.46 sec) 在server3上数据库： mysql> SELECT * FROM performance_schema.replication_group_members; 查看， server1又加进来集群里了
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
这里的MEMBER_PORT应该是33061
3、mysql-MHA高可用
将之前搭建的mgr集群拆掉，还原一主两从架构
server1：
[root@server1 ~]# /etc/init.d/mysqld stop 停掉数据库 [root@server1 ~]# cd /usr/local/mysql/data/ 进入数据目录 [root@server1 data]# rm -fr * 删除 [root@server1 data]# vim /etc/my.cnf [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock symbolic-links=0 [root@server1 data]# vim /etc/my.cnf 编辑配置文件 server_id=1 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
[root@server1 data]# mysqld --initialize --user=mysql 初始化，生成临时密码 [root@server1 data]# /etc/init.d/mysqld start 启动数据库 Starting MySQL.Logging to '/usr/local/mysql/data/server1.err'. SUCCESS! [root@server1 data]# mysql -p 登陆数据库 Enter password: 输入临时密码 mysql> alter user root@localhost identified by 'westos'; 修改本机登陆密码 Query OK, 0 rows affected (0.05 sec) mysql> show master status; 查看master状态 +---------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+----------------------------------------+ | binlog.000002 | 386 | | | f36f1c44-693b-11ec-bad5-52540001ce24:1 | +---------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> grant replication slave on *.* to repl@'%' identified by 'westos'; 授予repl用户复制权限 Query OK, 0 rows affected, 1 warning (0.11 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
server2：
[root@server2 ~]# /etc/init.d/mysqld stop [root@server2 ~]# cd /usr/local/mysql/data/ [root@server2 data]# rm -fr * [root@server2 data]# vim /etc/my.cnf 编辑配置文件 [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock symbolic-links=0 server_id=2 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
[root@server2 data]# mysqld --initialize --user=mysql 初始化
[root@server2 data]# /etc/init.d/mysqld start 启动数据库
Starting MySQL.Logging to '/usr/local/mysql/data/server2.err'.
SUCCESS!
[root@server2 data]# mysql -p 登陆
Enter password: 输入临时密码
mysql> alter user root@localhost identified by 'westos'; 修改本机登陆的密码
Query OK, 0 rows affected (0.14 sec)
mysql> change master to master_host='172.25.50.1', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.57 sec)
mysql> start slave ; 开启slave
mysql> show slave status\G; 查看slave状态
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
server3：
[root@server3 ~]# /etc/init.d/mysqld stop 停掉mysql [root@server3 ~]# cd /usr/local/mysql/data/ 进入数据库目录 [root@server3 data]# rm -fr * 删除数据 [root@server3 data]# vim /etc/my.cnf 编辑配置文件 [mysqld] datadir=/usr/local/mysql/data socket=/usr/local/mysql/data/mysql.sock symbolic-links=0 server_id=3 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
[root@server3 data]# mysqld --initialize --user=mysql 重新初始化，生成临时密码
[root@server3 data]# /etc/init.d/mysqld start 启动数据库
[root@server3 data]# mysql -p 登陆数据库
Enter password: 输入临时密码
mysql> alter user root@localhost identified by 'westos'; 修改本机登陆密码
mysql> change master to master_host='172.25.50.1', master_user='repl', master_password='westos', master_auto_position=1;
mysql> start slave ; 开启slave
mysql> show slave status\G; 查看slave状态
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
[root@foundation50 mysql]# scp -r MHA-7/ server4:/mnt 将下载的MHA拷贝到server4的mntli [root@server4 ~]# cd /mnt/MHA-7/ [root@server4 MHA-7]# yum install -y *.rpm 安装MHA-7文件里所有包 为了方便分别给server1、server2做免密 [root@server4 MHA-7]# ssh-keygen [root@server4 MHA-7]# ssh-copy-id sercer1 [root@server4 MHA-7]# ssh-copy-id sercer2 [root@server4 MHA-7]# ssh-copy-id server3 [root@server4 .ssh]# ssh-copy-id server4 将server4上 mha4mysql-node包全部拷贝到server1、server2、server3上 [root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:/mnt mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 35.5MB/s 00:00 [root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:/mnt mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 26.9MB/s 00:00 [root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:/mnt mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 29.7MB/s 00:00 在server1、server2、server3上分别安装mha4mysql-node(此包为操作二进制日志的工具） [root@server1 mnt]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y [root@server2 mnt]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y [root@server3 mnt]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y [root@server4 MHA-7]# rpm -ql mha4mysql-manager 查看软件包安装了那些文件 [root@server1 mnt]# rpm -ql mha4mysql-node 查看软件包安装了那些文件
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
[root@server4 MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz 解压包 [root@server4 MHA-7]# cd mha4mysql-manager-0.58/ [root@server4 mha4mysql-manager-0.58]# cd samples/ [root@server4 samples]# cd conf/ [root@server4 conf]# ls app1.cnf masterha_default.cnf [root@server4 conf]# masterha_manager --help 查看masterha_manager帮助 Usage: 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. [root@server4 conf]# mkdir /etc/masterha 创建目录 [root@server4 conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app.cnf 本次实验将将两个配置文件合成一个，放入到/etc/masterha/app.cnf 里 [root@server4 conf]# vim /etc/masterha/app.cnf 变建配置文件 [server default] user=root 数据库管理员用户名 password=westos 数据库管理员密码 ssh_user=root 需要通过免密 repl_user=repl 数据库复制用户 repl_password=westos 数据库复制用户密码 master_binlog_dir= /usr/local/mysql/data mysql数据目录 remote_workdir=/tmp 远程工作目录 secondary_check_script= masterha_secondary_check -s 172.25.50.3 -s 172.25.50.250 此处写master之外的ip地址 主要是为了检测master网络是不是好着 ping_interval=3 每隔三秒 # master_ip_failover_script= /script/masterha/master_ip_failover # shutdown_script= /script/masterha/power_manager # report_script= /script/masterha/send_report # master_ip_online_change_script= /script/masterha/master_ip_online_change manager_workdir=/etc/masterha/app1 工作目录 manager_log=/etc/masterha/app1/manager.log [server1] hostname=172.25.50.1 candidate_master=1 server1也可以作为master [server2] hostname=172.25.50.2 candidate_master=1 server2可以作为master [server3] hostname=172.25.50.3 no_master=1 server3不可以作为master 将密钥分别发给1、2、3 [root@server4 ~]# scp -r .ssh/ server1: id_rsa 100% 1675 2.1MB/s 00:00 id_rsa.pub 100% 394 580.9KB/s 00:00 known_hosts 100% 543 782.9KB/s 00:00 [root@server4 ~]# scp -r .ssh/ server2: id_rsa 100% 1675 1.7MB/s 00:00 id_rsa.pub 100% 394 797.0KB/s 00:00 known_hosts 100% 543 912.7KB/s 00:00 [root@server4 ~]# scp -r .ssh/ server3: id_rsa 100% 1675 2.3MB/s 00:00 id_rsa.pub 100% 394 721.3KB/s 00:00 known_hosts 100% 543 1.1MB/s 00:00 注意：要求管理节点免密所有的数据库节点，数据库节点也要彼此免密 [root@server4 conf]# masterha_check_ssh --conf=/etc/masterha/app.cnf 校验各个节点的免密连接
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
[root@server4 masterha]# masterha_check_repl --conf=/etc/masterha/app.cnf 检测主从
- 1
mysql> grant all on *.* to root@'%' identified by 'westos'; 授予root用户远端所有主机登陆
Query OK, 0 rows affected, 1 warning (0.98 sec)
mysql> flush privileges; 刷新
Query OK, 0 rows affected (0.06 sec)
[root@server4 masterha]# masterha_check_repl --conf=/etc/masterha/app.cnf 再次测试主从
- 1
- 2
- 3
- 4
- 5
4、 MHA主从切换–手动切换和自动切换（mysql高可用）
手动切换，从server1切换到server2 (server1上的master是正常的）
[root@server4 masterha]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.50.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
--orig_master_is_new_slave 此参数在masetr是活着的才可以添加 ；running_updates_limit=10000 超时时间
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.50.1(172.25.50.1:3306)? (YES/no): yes 是否刷新
From:
172.25.50.1(172.25.50.1:3306) (current master)
+--172.25.50.2(172.25.50.2:3306)
+--172.25.50.3(172.25.50.3:3306)
To:
172.25.50.2(172.25.50.2:3306) (new master)
+--172.25.50.3(172.25.50.3:3306)
+--172.25.50.1(172.25.50.1:3306)
Starting master switch from 172.25.50.1(172.25.50.1:3306) to 172.25.50.2(172.25.50.2:3306)? (yes/NO): YES
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
检测：
[root@server1 ~]# mysql -pwestos 登陆server1数据库
mysql> show slave status\G; 查看slvae状态，server1已经变成slave了
- 1
- 2
- 3
[root@server3~]# mysql -pwestos 登陆server3数据库
mysql> show slave status\G; 查看slave状态
- 1
- 2
手动切换:server2切换到server1 (server2上的master是不正常的）
[root@server2 ~]# /etc/init.d/mysqld stop 将server2数据库停掉
Shutting down MySQL............ SUCCESS!
[root@server4 masterha]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app.cnf --dead_master_host=172.25.50.2 --dead_master_port=3306 --new_master_host=172.25.50.1 --new_master_port=3306 --ignore_last_failover
Master 172.25.50.2(172.25.50.2:3306) is dead. Proceed? (yes/NO): yes
From:
172.25.50.2(172.25.50.2:3306) (current master)
+--172.25.50.1(172.25.50.1:3306)
+--172.25.50.3(172.25.50.3:3306)
To:
172.25.50.1(172.25.50.1:3306) (new master)
+--172.25.50.3(172.25.50.3:3306)
Starting master switch from 172.25.50.2(172.25.50.2:3306) to 172.25.50.1(172.25.50.1:3306)? (yes/NO): yes
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
切换成功后，进入app1目录
[root@server4 masterha]# cd app1/
[root@server4 app1]# ls
app.failover.complete 出故障切换文件，就会生成这个文件，为了避免服务的抖动，导致频繁切换
- 1
- 2
- 3
测试：
mysql> show slave status\G; 查看slave状态
- 1
[root@server2 ~]# /etc/init.d/mysqld start 从新开启server2数据库
Starting MySQL. SUCCESS!
[root@server2 ~]# mysql -pwestos 登陆数据库
mysql> change master to master_host='172.25.50.1', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.86 sec)
mysql> start slave; 启动slave
Query OK, 0 rows affected (0.05 sec)
[root@server4 app1]# masterha_check_repl --conf=/etc/masterha/app.cnf 检测主从
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
自动切换
server1（master）自动切换到server2
[root@server4 app1]# ls
app.failover.complete
[root@server4 app1]# rm -fr app.failover.complete 先删除此文件，不然不能切换
[root@server4 app1]# masterha_manager --conf=/etc/masterha/app.cnf & 打入后台 ，管控数据库示例，可以监控多个数据库示例,切换后进程自动消失
[root@server1 ~]# /etc/init.d/mysqld stop 停掉数据库
Shutting down MySQL............ SUCCESS!
[root@server4 app1]# ps ax 查看进程发现进程还没消失，并没有自动切换
- 1
- 2
- 3
- 4
- 5
- 6
- 7
[root@server4 app1]# cat manager.log 查看日志，免密不能到达250
- 1
[root@server4 ~]# kill -9 10526 杀掉之前的进程
[root@server1 ~]# /etc/init.d/mysqld start 启动数据库
[root@server4 ~]# masterha_manager --conf=/etc/masterha/app.cnf & 再次运行
[root@server1 ~]# /etc/init.d/mysqld stop 停止数据库
检测：
[root@server3 .ssh]# mysql -p server3上登陆数据库
mysql> show slave status\G; 查看slave状态
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
恢复server1
server1由原先的master变成slave
[root@server1 ~]# /etc/init.d/mysqld start 启动数据库
Starting MySQL. SUCCESS!
mysql> change master to master_host='172.25.50.2', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.82 sec)
mysql> start slave; 开启slave
Query OK, 0 rows affected (0.13 sec)
mysql> show slave status\G; 查看slave状态,成功
- 1
- 2
- 3
- 4
- 5
- 6
- 7
[root@server4 app1]# masterha_check_repl --conf=/etc/masterha/app.cnf 检测主从，正常
- 1
带有vip的手动切换
[root@foundation50 isos]# cd /mnt/pub/docs/mysql/
[root@foundation50 mysql]# scp master_ip_failover master_ip_online_change server4:/etc/masterha 将写好的脚本拷贝到server4的/etc/masterha 目录里
root@server4's password:
master_ip_failover （手动切换 vip脚本） 100% 2156 2.4KB/s 00:00
master_ip_online_change （自动切换vip脚本） 100% 3813 87.3KB/s 00:00
[root@server4 app1]# cd /etc/masterha/
[root@server4 masterha]# ls
app1 app.cnf master_ip_failover master_ip_online_change
[root@server4 masterha]# chmod +x master_ip_* 给脚本加上执行权限
注：上面两个脚本是在此目录里的两个模板改的
[root@server4 ] cd /mnt/MHA-7/mha4mysql-manager-0.58/samples/scripts
[root@server4 scripts]# ls
master_ip_failover master_ip_online_change power_manager send_report
[root@server4 masterha]# vim master_ip_failover 编辑脚本
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
[root@server4 masterha]# vim master_ip_online_change 编辑配置文件
- 1
[root@server4 masterha]# masterha_check_repl --conf=/etc/masterha/app.cnf 检测主从状态，状态ok
- 1
[root@server4 masterha]# vim app.cnf 编辑配置文件，添加参数
- 1
[root@server4 masterha]# masterha_check_repl --conf=/etc/masterha/app.cnf 再次检测主从
- 1
[root@server2 ~]# ip addr add 172.25.50.100/24 dev eth0 在server2的master添加vip [root@foundation50 ~]# mysql -h 172.25.50.100 -uroot -pwestos 客户端远程登陆数据库，登陆成功 MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.001 sec) [root@server4 masterha]# masterha_master_switch --conf=/etc/masterha/app.cnf --master_state=alive --new_master_host=172.25.50.1 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000 切换master到server1 It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.50.2(172.25.50.2:3306)? (YES/no): yes 输入yes From: 172.25.50.2(172.25.50.2:3306) (current master) +--172.25.50.1(172.25.50.1:3306) +--172.25.50.3(172.25.50.3:3306) To: 172.25.50.1(172.25.50.1:3306) (new master) +--172.25.50.3(172.25.50.3:3306) +--172.25.50.2(172.25.50.2:3306) Starting master switch from 172.25.50.2(172.25.50.2:3306) to 172.25.50.1(172.25.50.1:3306)? (yes/NO): yes 输入yes *************************************************************** Enabling the VIP - 172.25.50.100/24 on new master: 172.25.50.1 vip切换到server1 *************************************************************** [root@server1 ~]# ip addr show 查看ip ，vip已经切换到server1上了
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
带有vip自动切换
server1切换到server2
[root@server4 masterha]# cd app1/
[root@server4 app1]# rm -fr app.failover.complete 删除防抖动文件
[root@server4 app1]# masterha_manager --conf=/etc/masterha/app.cnf &
[root@server1 ~]# /etc/init.d/mysqld stop 停止数据库（目前server1是master）
[root@server4 app1]# cat manager.log 查看生成的日志，可以发现切换成功
- 1
- 2
- 3
- 4
- 5
[root@server2 ~]# ip addr 查看server2ip，发现vip已经切换到server2上了
- 1
恢复server1
[root@server1 ~]# /etc/init.d/mysqld start 启动数据库
Starting MySQL. SUCCESS!
[root@server1 ~]# mysql -pwestos 登陆数据库
mysql> change master to master_host='172.25.50.2', master_user='repl', master_password='westos', master_auto_position=1;
mysql> start slave; 启动slave
mysql> show slave status\G;
- 1
- 2
- 3
- 4
- 5
- 6
原作者：小莫细说linux
原文地址：https://blog.csdn.net/qq_43114229/article/details/122181826
感谢~
声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签
