MySQL部署方式
源码安装
安装配置
主从复制
一主两从
延迟复制
慢查询日志
并行复制
半同步模式
MySQL高可用之组复制(MGR)
实现mysql组复制
MySQL route部署方式
MySQL高可用之MHA
故障切换
为MHA添加VIP功能
MySQL部署方式
源码安装
两个linux7主机,mysql-node1-10、mysql-node2-20yum install cmake -y#当cmake出错后如果想重新检测,删除 mysql-5.7.44 中 CMakeCache.txt重新检测即可#下载安装依赖性[root@mysql mysql-5.7.44]# 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 -y[root@mysql 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 mysql-5.7.44]# make -j4[root@mysql mysql-5.7.44]# make install#完成
安装配置
[root@mysql ~]# cd /usr/local/mysql/[root@mysql mysql]# useradd -s /sbin/nologin -M mysql[root@mysql mysql]# mkdir /data/mysql -p[root@mysql mysql]# chown mysql.mysql -R /data/mysql[root@mysql mysql]# cd support-files/[root@mysql support-files]# cp mysql.server /etc/init.d/mysqld[root@mysql support-files]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0[root@mysql support-files]# vim ~/.bash_profile[root@mysql support-files]# source ~/.bash_profilePATH=$PATH:$HOME/bin:/usr/local/mysql/bin[root@mysql ~]# mysqld --user mysql --initialize#默认密码#启动[root@mysql ~]# /etc/init.d/mysqld startStarting MySQL.Logging to '/data/mysql/mysql.node1.err'.SUCCESS!#设置开机启动[root@mysql ~]# chkconfig mysqld on安全初始化[root@mysql ~]# mysql_secure_installation#如果初始化有问题,删掉重新初始化#复制默认密码n-->n-->y-->y-->一直y#登录
[root@mysql ~]# mysql -uroot -p
主从复制
[root@mysql ~]# vim /etc/my.cnf[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0log-bin=mysql-binserver-id=10[root@mysql ~]# /etc/init.d/mysqld restart[root@mysql ~]# mysql -uroot -p -e "select @@server_id"mysql> create user repl@'%' identified by '123';mysql> grant replication slave on *.* to repl@'%';查看mysql> show master status;mysql> change master to master_host='192.168.86.10',master_user='repl',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=595;mysql> start slave;
一主两从
拉数据
锁库锁表
从master节点备份数据。
[root@mysql-node1 ~]# mysqldump -uroot -p tyw > tyw.sql
Enter password:
把 sql 语句拷贝到30主机。[root@mysql-node1 ~]# scp tyw.sql root@172.25.254.30:/mnt/
root@172.25.254.30's password:
scp: /mnt//tyw.sql: Read-only file system
30中建立 tyw 这个数据库。
[root@mysql-node3 opt]# mysql -uroot -pmysql -e "CREATE DATABASE tyw;"
mysql: [Warning] Using a password on the command line interface can be insecure.
把数据导入库。[root@mysql-node3 opt]# mysql -uroot -pmysql tyw < tyw.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
延迟复制
目的:防止误操作
从服务器
30关闭 sql 线程,设置延迟同步时间,再开启线程。
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)mysql> change master to master_delay=60;
Query OK, 0 rows affected (0.00 sec)mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
慢查询日志
主服务器
mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)#开启慢查询mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /data/mysql/mysql-node1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)
并行复制
开启多线程回放实现
编辑主配置文件。打开GTID。保证ID强一致性。
[root@mysql-node2 ~]# vim /etc/my.cnf[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
super_read_only=on
gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK #基于组提交
slave-parallel-workers=16 #开启线程数量
master_info_repository=TABLE #master信息在表中记录,默认记录在/data/mysql//master.info
relay_log_info_repository=TABLE #回放日志信息在表中记录,默认记录在/data/mysql/relay-log.info
relay_log_recovery=ON #日志回放恢复功能开启#重启数据库
[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
半同步模式
主服务器
#启用半同步#编辑10配置文件[root@mysql-node1 ~]# vim /etc/my.cnf[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10
slow_query_log=on
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL............ SUCCESS!
Starting MySQL. SUCCESS!#安装半同步插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)#查看是否安装成功
mysql> select * from information_schema.PLUGINS where PLUGIN_NAME LIKE '%semi%'\G
*************************** 1. row ***************************PLUGIN_NAME: rpl_semi_sync_masterPLUGIN_VERSION: 1.0PLUGIN_STATUS: ACTIVEPLUGIN_TYPE: REPLICATIONPLUGIN_TYPE_VERSION: 4.0PLUGIN_LIBRARY: semisync_master.so
PLUGIN_LIBRARY_VERSION: 1.7PLUGIN_AUTHOR: He ZhenxingPLUGIN_DESCRIPTION: Semi-synchronous replication masterPLUGIN_LICENSE: GPLLOAD_OPTION: ON
1 row in set (0.00 sec)#打开半同步功能
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)#查看插件状态
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
从服务器上
[root@mysql-node2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
[root@mysql-node2 ~]# vim /etc/my.cnf[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
#super_read_only=on
gtid_mode=ON
enforce-gtid-consistency=ON
#rpl_semi_sync_master_enable=1[root@mysql-node2 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! #安装插件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 VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
MySQL高可用之组复制(MGR)
支持单主和多主
多主用的多
实现mysql组复制
停服务,删数据[root@mysql ~]# /etc/init.d/mysqld stop[root@mysql ~]# ps aux | grep mysqlkill -9 号[root@mysql ~]# rm -rf /data/mysql/*初始化[root@mysql ~]# mysqld --user=mysql --initialize进入后更改密码mysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
编辑my.cnf文件[mysqld]datadir=/data/mysqlsocket=/data/mysql/mysql.socksymbolic-links=0log-bin=mysql-binserver-id=10disabled_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="192.168.86.10:33061"group_replication_group_seeds="192.168.86.10:33061,192.168.86.20:33061,192.168.86.30:33061"group_replication_ip_whitelist="192.168.86.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
配置mysql
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.01 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;mysql> START GROUP_REPLICATION;mysql> SET GLOBAL group_replication_bootstrap_group=OFF;mysql> select * from performance_schema.replication_group_members;#3台主机做本地解析,提高数据同步速度。[root@mysql-node1 ~]# vim /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.86.10 mysql-node1.timinglee.org
192.168.86.20 mysql-node2.timinglee.org
192.168.86.30 mysql-node3.timinglee.org#复制配置到20、30上[root@mysql-node1 ~]# scp /etc/my.cnf root@192.168.86.20:/etc/my.cnf
root@192.168.86.20's password:
my.cnf 100% 963 1.1MB/s 00:00
[root@mysql-node1 ~]# scp /etc/my.cnf root@192.168.86.30:/etc/my.cnf
root@192.168.86.30's password:
my.cnf 100% 963 1.5MB/s 00:00
20主机上
修改idserver-id=20[root@mysql-node2 ~]# rm -rf /data/mysql/*
[root@mysql-node2 ~]# mysqld --user=mysql --initialize[root@mysql-node2 ~]# /etc/init.d/mysqld startmysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.00 sec)
后续同10
最后10上查看。
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
| group_replication_applier | 03200011-63c5-11ef-ac95-000c2937a656 | mysql-node1.timinglee.org | 3306 | ONLINE |
| group_replication_applier | f749ef58-63c9-11ef-8f23-000c2957fcc4 | mysql-node2.timinglee.org | 3306 | RECOVERING |
+---------------------------+--------------------------------------+---------------------------+-------------+--------------+
2 rows in set (0.00 sec)
MySQL route部署方式
并不能限制数据库的读写
#先安装mysql-router[root@mysql_router ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm
[root@mysql_router ~]# vim /etc/mysqlrouter/mysqlrouter.conf[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306
routing_strategy = first-available[root@mysql_router ~]# systemctl start mysqlrouter.servicemysql> CREATE USER lee@‘%’ IDENTIFIED BY ‘lee’;mysql> GRANT ALL ON lee.* TO lee@‘%’;
MySQL高可用之MHA
mha-50
解压进入目录下[root@mysql_mha ~]# unzip MHA-7.zip[root@mysql_mha ~]# cd MHA-7/免密[root@mha ~]# ssh-keygen
10、20、30上只需要
[root@mysql ~]# vim /etc/my.cnf
在10中加
log_bin=mysql-bin
[root@mysql ~]# /etc/init.d/mysqld stop[root@mysql ~]# rm -rf /data/mysql/*[root@mysql ~]# mysqld --user=mysql --initialize[root@mysql ~]# /etc/init.d/mysqld startmysql> alter user root@localhost identified by '123';Query OK, 0 rows affected (0.00 sec)
10中
mysql> create user 'repl'@'%' identified by 'lee';Query OK, 0 rows affected (0.00 sec)mysql> grant replication slave on *.* to repl@'%';Query OK, 0 rows affected (0.00 sec)mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';Query OK, 0 rows affected (0.01 sec)set global rpl_semi_sync_master_enabled = 1;#查看
mysql> show variables like 'rpl_semi_sync%';
20、30中
mysql> change master to master_host='192.168.86.10', master_user='repl',master_password='lee', master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.02 sec)INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';SET GLOBAL rpl_semi_sync_slave_enabled =1;mysql> stop slave io_thread;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> start slave io_thread;Query OK, 0 rows affected (0.00 sec)
部署mha
[root@mysql_mha MHA-7]# yum install *.rpm -y10、20、30中[root@mysql_mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@192.168.86.10:/mnt[root@mysql1 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -ymysql> create user root@'%' identified by 'lee';Query OK, 0 rows affected (0.00 sec)
50上
[root@mysql_mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf
[root@mysql_mha conf]# vim /etc/masterha/app1.cnf[server default]
user=root
password=lee
ssh_user=root
repl_user=repl
repl_password=lee
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 192.168.86.10 -s 192.168.86.20
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
[server default]
manager_workdir=/etc/masterha
manager_log=/var/etc/masterha/manager.log[server1]
hostname=192.168.86.10
candidate_master=1
check_repl_delay=0[server2]
hostname=192.168.86.20
candidate_master=1
check_repl_delay=0[server3]
hostname=192.168.86.30
no_master=1
#检测网络及ssh免密[root@mysql_mha conf]# masterha_check_ssh --conf=/etc/masterha/app1.cnfmysql1[root@mysql_mha conf]# mysql -p123456mysql> GRANT ALL ON . TO root@‘%’ identified by ‘123456’;mha#执行检测[root@mysql_mha conf]# masterha_check_repl --conf=/etc/masterha/app1.cnf
故障切换
未发生故障手动切换
MHA
前提:主mysql还在正常运行
#在master数据节点还在正常工作情况下[root@mysql-mha ~]# masterha_master_switch \--conf=/etc/masterha/app1.cnf \ #指定配置文件--master_state=alive \ #指定master节点状态--new_master_host=192.168.86.20 \ #指定新master节点--new_master_port=3306 \ #执行新master节点端口--orig_master_is_new_slave \ #原始master会变成新的slave--running_updates_limit=10000 #切换的超时时间yes-->yes-->yes#测试
#模拟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#恢复故障mysql节点
[root@mysql-node20 tmp]# /etc/init.d/mysqld startStarting MySQL. SUCCESS![root@mysql-node20 tmp]# mysql -pmysql> CHANGE MASTER TO MASTER_HOST='192.168.86.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.cnf[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='192.168.86.10', MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1[root@mysql-mha masterha]# rm -rf app1.failover.complete manager.log
为MHA添加VIP功能
mha
[root@mysql_mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/[root@mysql_mha ~]# chmod +x /usr/local/bin/master_ip_*[root@mysql_mha ~]# vim /usr/local/bin/master_ip_failovermy $vip = '192.168.86.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_changemy $vip = '192.168.86.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]# rm -rf app1.failover.complete manager.log