企业网站只用静态页,绵阳市建设网招标公告,中国科技成就,汽车网站建设模板文章目录前言一、mysql组复制1.实验机配置2.测试二、mysql路由器三、mysql之MHA高可用1.MHA概念1.创建一主两从集群2.MHA部署3.故障切换前言 一、mysql组复制
1.实验机配置
server1配置
首先停止数据库
[rootserver1 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL..…
文章目录前言一、mysql组复制1.实验机配置2.测试二、mysql路由器三、mysql之MHA高可用1.MHA概念1.创建一主两从集群2.MHA部署3.故障切换前言 一、mysql组复制
1.实验机配置
server1配置
首先停止数据库
[rootserver1 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除数据
[rootserver1 ~]# cd /data/mysql
[rootserver1 mysql]# rm -fr *[rootserver1 mysql]# vim /etc/my.cnf ##根据实际情况修改主机名和网段
...
disabled_storage_enginesMyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORYserver_id1 ##
gtid_modeON
enforce_gtid_consistencyON
master_info_repositoryTABLE
relay_log_info_repositoryTABLE
binlog_checksumNONE
log_slave_updatesON
log_binbinlog
binlog_formatROWplugin_load_addgroup_replication.so
transaction_write_set_extractionXXHASH64
group_replication_group_nameaaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
group_replication_start_on_bootoff
group_replication_local_address server1:33061 ##主机名需对应
group_replication_group_seeds server1:33061,server2:33061,server3:33061
group_replication_bootstrap_groupoff
group_replication_ip_whitelist192.168.56.0/24,127.0.0.1/8 ##ip需对应
group_replication_single_primary_modeOFF
group_replication_enforce_update_everywhere_checksON
group_replication_allow_local_disjoint_gtids_join1初始化
[rootserver1 mysql]# mysqld --initialize --usermysql
启动服务
[rootserver1 mysql]# /etc/init.d/mysqld start[rootserver1 mysql]# mysql -p
#首先修改临时密码
mysql alter user rootlocalhost identified by westos;
Query OK, 0 rows affected (0.01 sec)mysql SET SQL_LOG_BIN0;
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 FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql SET SQL_LOG_BIN1;
Query OK, 0 rows affected (0.00 sec)mysql CHANGE MASTER TO MASTER_USERrpl_user, MASTER_PASSWORDwestos FOR CHANNEL group_replication_recovery;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql SET GLOBAL group_replication_bootstrap_groupON; #只在server1上执行
Query OK, 0 rows affected (0.00 sec)mysql START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql SET GLOBAL group_replication_bootstrap_groupOFF; #只在server1上执行
Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM performance_schema.replication_group_members;
---------------------------------------------------------------------------------------------------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
---------------------------------------------------------------------------------------------------------
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1 | 3306 | ONLINE |
---------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)server2配置
首先停止数据库
[rootserver2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除数据
[rootserver2 ~]# cd /data/mysql
[rootserver2 mysql]# rm -fr *[rootserver2 mysql]# vim /etc/my.cnf ##根据实际情况修改主机名和网段
...
disabled_storage_enginesMyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORYserver_id2 ##需对应
gtid_modeON
enforce_gtid_consistencyON
master_info_repositoryTABLE
relay_log_info_repositoryTABLE
binlog_checksumNONE
log_slave_updatesON
log_binbinlog
binlog_formatROWplugin_load_addgroup_replication.so
transaction_write_set_extractionXXHASH64
group_replication_group_nameaaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
group_replication_start_on_bootoff
group_replication_local_address server2:33061 ##需对应
group_replication_group_seeds server1:33061,server2:33061,server3:33061
group_replication_bootstrap_groupoff
group_replication_ip_whitelist192.168.56.0/24,127.0.0.1/8 ##需对应
group_replication_single_primary_modeOFF
group_replication_enforce_update_everywhere_checksON
group_replication_allow_local_disjoint_gtids_join1初始化
[rootserver2 mysql]# mysqld --initialize --usermysql启动服务
[rootserver2 mysql]# /etc/init.d/mysqld start[rootserver2 mysql]# mysql -p
#首先修改临时密码
mysql alter user rootlocalhost identified by westos;
Query OK, 0 rows affected (0.01 sec)mysql SET SQL_LOG_BIN0;
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 FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql SET SQL_LOG_BIN1;
Query OK, 0 rows affected (0.00 sec)mysql CHANGE MASTER TO MASTER_USERrpl_user, MASTER_PASSWORDwestos FOR CHANNEL group_replication_recovery;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql SELECT * FROM performance_schema.replication_group_members;
---------------------------------------------------------------------------------------------------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
---------------------------------------------------------------------------------------------------------
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1 | 3306 | ONLINE |
| group_replication_applier | e8be5058-b4e3-11ed-8788-000c29f15153 | server2 | 3306 | ONLINE |
---------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)server3与server一致
首先停止数据库
[rootserver3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除数据
[rootserver3 ~]# cd /data/mysql
[rootserver3 mysql]# rm -fr *[rootserver3 mysql]# vim /etc/my.cnf
...
disabled_storage_enginesMyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORYserver_id3
gtid_modeON
enforce_gtid_consistencyON
master_info_repositoryTABLE
relay_log_info_repositoryTABLE
binlog_checksumNONE
log_slave_updatesON
log_binbinlog
binlog_formatROWplugin_load_addgroup_replication.so
transaction_write_set_extractionXXHASH64
group_replication_group_nameaaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
group_replication_start_on_bootoff
group_replication_local_address server3:33061
group_replication_group_seeds server1:33061,server2:33061,server3:33061
group_replication_bootstrap_groupoff
group_replication_ip_whitelist192.168.56.0/24,127.0.0.1/8
group_replication_single_primary_modeOFF
group_replication_enforce_update_everywhere_checksON
group_replication_allow_local_disjoint_gtids_join1
#根据实际情况修改主机名和网段初始化
[rootserver3 mysql]# mysqld --initialize --usermysql启动服务
[rootserver3 mysql]# /etc/init.d/mysqld start[rootserver3 mysql]# mysql -p
#首先修改临时密码
mysql alter user rootlocalhost identified by westos;
Query OK, 0 rows affected (0.01 sec)mysql SET SQL_LOG_BIN0;
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 FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql SET SQL_LOG_BIN1;
Query OK, 0 rows affected (0.00 sec)mysql CHANGE MASTER TO MASTER_USERrpl_user, MASTER_PASSWORDwestos FOR CHANNEL group_replication_recovery;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql SELECT * FROM performance_schema.replication_group_members;
---------------------------------------------------------------------------------------------------------
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
---------------------------------------------------------------------------------------------------------
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1 | 3306 | ONLINE |
| group_replication_applier | 4c655909-b4e4-11ed-8dcc-000c29259218 | server3 | 3306 | ONLINE |
| group_replication_applier | e8be5058-b4e3-11ed-8788-000c29f15153 | server2 | 3306 | ONLINE |
---------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)2.测试
所有节点都可以读写数据
server1
mysql CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)mysql USE test;
Database changed
mysql CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.01 sec)mysql INSERT INTO t1 VALUES (1, Luis);
Query OK, 1 row affected (0.03 sec)mysql SELECT * FROM t1;
----------
| c1 | c2 |
----------
| 1 | Luis |
----------
1 row in set (0.00 sec)server2
mysql select * from test.t1;
----------
| c1 | c2 |
----------
| 1 | Luis |
----------
1 row in set (0.00 sec)mysql INSERT INTO test.t1 VALUES (2, hello);
Query OK, 1 row affected (0.00 sec)server3
mysql select * from test.t1;
----------
| c1 | c2 |
----------
| 1 | Luis |
| 2 | hello |
----------
2 rows in set (0.00 sec)mysql INSERT INTO test.t1 VALUES (3, westos);
Query OK, 1 row affected (0.01 sec)二、mysql路由器 安装软件 [rootserver4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm 配置服务 [rootserver4 ~]# cd /etc/mysqlrouter/ [rootserver4 mysqlrouter]# vim mysqlrouter.conf … [routing:ro] bind_address 0.0.0.0 bind_port 7001 destinations 192.168.56.11:3306,192.168.56.12:3306,192.168.56.13:3306 routing_strategy round-robin [routing:rw] bind_address 0.0.0.0 bind_port 7002 destinations 192.168.56.13:3306,192.168.56.12:3306,192.168.56.11:3306 routing_strategy first-available 启动服务 [rootserver4 mysqlrouter]# systemctl enable --now mysqlrouter.service 安装mysql客户端工具 [rootserver4 ~]# yum install -y mariadb 在mysql集群中创建远程测试用户 mysql grant all on test.* to ‘wxh’‘%’ identified by ‘westos’; 连接mysql路由器 [rootserver4 ~]# mysql -h 192.168.56.14 -P 7001 -u wxh -pwestos MySQL [(none)] select * from test.t1; ±—±------- | c1 | c2 | ±—±------- | 1 | Luis | | 2 | wxh | | 3 | westos | ±—±------- 3 rows in set (0.00 sec) 在server1上查看网络连接 [rootserver1 mysql]# yum install -y lsof [rootserver1 mysql]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 15184 mysql 22u IPv6 87530 0t0 TCP *:mysql (LISTEN) mysqld 15184 mysql 83u IPv6 93365 0t0 TCP server1:mysql-server4:55504 (ESTABLISHED) [rootserver4 ~]# mysql -h 192.168.56.14 -P 7002 -u wxh -pwestos [rootserver3 mysql]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 30811 mysql 22u IPv6 73360 0t0 TCP *:mysql (LISTEN) mysqld 30811 mysql 81u IPv6 78061 0t0 TCP server3:mysql-server4:38594 (ESTABLISHED) 三、mysql之MHA高可用
1.MHA概念 1.什么是MHA MHAMaster High Availability是一套优秀的MySQL高可用环境下故障切换和主从复制的软件 MHA的出现就是解决MySQL单点故障的问题 MySQL故障切换过程中MHA能做到0-30秒内自动完成故障切换操作 MHA能在故障切换的过程中最大程度上保证数据的一致性以达到真正意义上的高可用 2.MHA的组成 2.1MHA Node数据节点 MHA Node运行在每台MySQL服务器上 2.2MHA Manager管理节点 MHA Manager可以单独部署在一台独立的机器上管理多个Master-Slave集群也可以部署在一台Slave节点上 MHA Manager会定时探测集群中的master节点。当master出现故障时它可以自动将最新数据的Slave提升为新的Master然后将所有其他的Slave重新指向新的Master 整个故障迁移的过程对应用程序完全透明 3.MHA特点 自动故障切换的过程中MHA试图从宕机的主服务器上保存二进制日志最大程度的保证数据不丢失 使用半同步复制可以大大降低数据丢失的风险如果只有一个Slave已经收到了最新的二进制日志MHA可以将最新的二进制日志应用于其他所有的Slave服务器上因此可以保证所有节点的数据一致性 目前MHA支持一主多从架构最少三台服务即一主两从 MHA为了解决的是故障切换、数据尽可能地保存以及所有节点日志地一致性 4.MHA工作原理流程 MHA有3个部分 核心是主从 Manager 管理节点管理数据库集群信息定义、触发故障切换 Node 数据节点主要负责保存日志、比较中继日志选择主备 MHA会通过Node监控MySQL数据库服务地节点信息定期检测和返回Master角色地健康状态健康检查 MHA通过将VIP定义在Master节点上并且数据库的访问也从此VIP进入当Master异常时MHA会进行故障切换就是VIP漂移二进制日志保存 漂移到主备节点后会通过脚本命令来控制MySQL服务器角色的变更例如将主-备服务器变更为主服务器将其他的从服务器指向新的主服务器来继续运行 MHA工作原理总结如下 1.从宕机崩溃的master保存二进制日志时间binlog events 2.识别含有最新的更新的slave日志 3.应用差异的中继日志relay log到其他的slave 4.应用从master保存的二进制日志事件 5.提升一个slave为新的master 6.使其他的slave联机新的master进行复制 1.创建一主两从集群
master配置
清理数据
[rootserver1 ~]# cd /data/mysql
[rootserver1 mysql]# rm -fr *修改配置
[rootserver1 mysql]# vim /etc/my.cnf
server_id1
gtid_modeON
enforce_gtid_consistencyON
log_slave_updatesON
log_binbinlog初始化
[rootserver1 mysql]# mysqld --initialize --usermysql启动服务
[rootserver1 mysql]# /etc/init.d/mysqld start配置master
[rootserver1 mysql]# mysql -p
mysql alter user rootlocalhost identified by westos;
Query OK, 0 rows affected (0.01 sec)mysql grant replication slave on *.* to repl% identified by westos;
Query OK, 0 rows affected, 1 warning (0.01 sec)slave配置server3与server2配置方法一致
[rootserver2 ~]# cd /data/mysql/
[rootserver2 mysql]# rm -fr *[rootserver2 mysql]# vim /etc/my.cnf
server_id2
gtid_modeON
enforce_gtid_consistencyON
log_slave_updatesON
log_binbinlog[rootserver2 mysql]# mysqld --initialize --usermysql[rootserver2 mysql]# /etc/init.d/mysqld start[rootserver2 mysql]# mysql -p
mysql alter user rootlocalhost identified by westos;
Query OK, 0 rows affected (0.01 sec)mysql change master to master_host192.168.56.11, master_userrepl, master_passwordwestos, master_auto_position1;
Query OK, 0 rows affected, 2 warnings (0.02 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 eventMaster_Host: 192.168.56.11Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 681Relay_Log_File: server2-relay-bin.000002Relay_Log_Pos: 888Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: Yes测试
master写入数据
mysql create database westos;
Query OK, 1 row affected (0.00 sec)mysql use westos
Database changedmysql create table user_tb (- username varchar(25) not null,- password varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)mysql insert into user_tb values (user1,111);
Query OK, 1 row affected (0.03 sec)mysql insert into user_tb values (user2,222);
Query OK, 1 row affected (0.00 sec)slave:
mysql select * from westos.user_tb;
--------------------
| username | password |
--------------------
| user1 | 111 |
| user2 | 222 |
--------------------
2 rows in set (0.00 sec)2.MHA部署
server4管理端配置
server4上安装管理端软件
[rootserver4 ~]# cd MHA-7/
[rootserver4 MHA-7]# yum install -y *.rpm管理端配置ssh免密
[rootserver4 MHA-7]# ssh-keygen
[rootserver4 MHA-7]# ssh-copy-id server4把密钥复制到各节点
[rootserver4 ~]# scp -r .ssh/ server1:
[rootserver4 ~]# scp -r .ssh/ server2:
[rootserver4 ~]# scp -r .ssh/ server3:复制客户端软件
[rootserver4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
[rootserver4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:
[rootserver4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:server1、server2、server3上安装客户端软件
[rootserver1 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpmMHA配置
[rootserver4 ~]# mkdir /etc/masterha
[rootserver4 ~]# vim /etc/masterha/app1.cnf
[server default]
userroot #mysql管理员
passwordwestos #mysql管理员密码
ssh_userroot #ssh免密用户
repl_userrepl #mysql主从复制用户
repl_passwordwestos
master_binlog_dir/data/mysql #mysql数据库目录
remote_workdir/tmp
secondary_check_script masterha_secondary_check -s 192.168.56.12 -s 192.168.56.13
ping_interval3
# 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
manager_log/etc/masterha/manager.log
[server1]
hostname192.168.56.11
candidate_master1
check_repl_delay0[server2]
hostname192.168.56.12
candidate_master1
check_repl_delay0[server3]
hostname192.168.56.13
no_master1在master上设置mysql 管理员权限slave节点会自动同步
mysql grant all on *.* to root% identified by westos;
Query OK, 0 rows affected, 1 warning (0.00 sec)检测各节点ssh免密连接 [rootserver4 ~]# masterha_check_ssh --conf/etc/masterha/app1.cnf 检测主从复制集群状态 [rootserver4 ~]# masterha_check_repl --conf/etc/masterha/app1.cnf
3.故障切换
手动切换master正常
[rootserver4 ~]# masterha_master_switch --conf/etc/masterha/app1.cnf --master_statealive --new_master_host192.168.56.12 --new_master_port3306 --orig_master_is_new_slave --running_updates_limit10000手动切换master故障
手动停止master节点上的msql服务
[rootserver2 ~]# /etc/init.d/mysqld stop[rootserver4 masterha]# masterha_master_switch --master_statedead --conf/etc/masterha/app1.cnf --dead_master_host192.168.56.12 --dead_master_port3306 --new_master_host192.168.56.11 --new_master_port3306 --ignore_last_failover故障节点需要手动修复重新加入主从集群
[rootserver2 ~]# /etc/init.d/mysqld start
[rootserver2 ~]# mysql -pwestosmysql change master to master_host192.168.56.11, master_userrepl, master_passwordwestos, master_auto_position1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql start slave;
Query OK, 0 rows affected (0.00 sec)自动切换
故障切换后会生成lock文件需要手动删除
[rootserver4 masterha]# rm -f app1.failover.complete启动manger程序并打入后台运行完成切换任务后进程会自动退出
[rootserver4 masterha]# masterha_manager --conf/etc/masterha/app1.cnf 在进行自动切换之前必须保证主从集群正常一主两从加入故障切换脚本
[rootserver4 ~]# vim /etc/masterha/app1.cnf
[server default]
userroot
passwordwestos
ssh_userroot
repl_userrepl
repl_passwordwestos
master_binlog_dir/data/mysql
remote_workdir/tmp
secondary_check_script masterha_secondary_check -s 192.168.56.12 -s 192.168.56.13
ping_interval3
master_ip_failover_script /usr/local/bin/master_ip_failover
# shutdown_script /script/masterha/power_manager
# report_script /script/masterha/send_report
master_ip_online_change_script /usr/local/bin/master_ip_online_change
manager_workdir/etc/masterha
manager_log/etc/masterha/manager.log[server1]
hostname192.168.56.11
candidate_master1
check_repl_delay0[server2]
hostname192.168.56.12
candidate_master1
check_repl_delay0[server3]
hostname192.168.56.13
no_master1需要有可执行权限
[rootserver4 ~]# ll /usr/local/bin/
total 8
-rwxr-xr-x 1 root root 2158 Feb 26 11:20 master_ip_failover
-rwxr-xr-x 1 root root 3815 Feb 26 11:21 master_ip_online_change最后按需要修改VIP地址即可
