第十四周作业

原创

liyusheng530 2022-01-13 17:54:05 ©著作权

文章标签 linux mysql vim 服务器 it技术 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者liyusheng530的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、MariaDB主从复制原理
1.主节点的更新事件写入binlog
2.主节点创建一个binlog dump线程，把binlog的内容发送到从节点
3.从接点连接主节点以后，创建一个I/O线程，读取主节点传过来的binlog内容并写入到relay log
4.从节点创建一个SQL线程，读取relay log里面的更新事件并解析成SQL语句，然后执行这些SQL语句，将更新内容写入到从节点。

2、MariaDB一主一从架构构建
准备两台纯新的CentOS7.6服务器，其中主机称为node1的服务器地址为192.168.130.132，主机称为node2的服务器地址为192.168.130.133，配置mariadb yum源并安装mariadb

主从节点配置yum源
vim /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.5/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum -y install mariadb-server
主节点修改配置文件
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=1
log-bin=/data/mysql/mysql-bin
mkdir -p /data/mysql
chown -R mysql.mysql /data/mysql
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
主节点创建复制用户
mysql -uroot -p
MariaDB [(none)]> show master status;
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'replpasswd';
从节点修改配置文件
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=2
read-only
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
连接到主接点
mysql -uroot -p
MariaDB [(none)]> change master to master_host='192.168.130.132',master_user='repluser',master_password='replpasswd',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=1747;
MariaDB [(none)]> start slave;
注：master_log_file和master_log_pos的值为主节点创建复制用户前执行show master status;的查询结果
测试
在主节点导入数据，从节点查询是否有新的数据。

3、MariaDB级联复制
上一节中我们完成了一主一从的MariaDB的主从复制架构，但是有的时候，我们期望有一个后备的MariaDB的节点，用只备份存储数据，不需要对外提供服务。为了实现该功能，我们可以在之前的一主一从架构上进行调整，即将从节点的二进制文件打开，然后给他配置一个远程同步数据用户，接着使用一台新的服务器做为从服务器的从属，同步从服务器数据的即可，下面我们在原来的架构上添加一台新的CentOS7.6作为节点3，IP地址为192.168.130.134中间节点启用二进制日志（192.168.130.133做中间节点）

中间节点配置

vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=2
log-bin
read-only
log_slave_updates
systemctl restart mariadb.service
中间节点备份数据库
mysqldump -uroot -p -A -F --single-transaction --master-data=1 > /data/mysql/all.sql
scp /data/mysql/all.sql root@192.168.130.134:/root/
从节点配置
vim /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.5/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum -y install mariadb-server
vim /etc/my.cnf.d/server.cnf
server-id=3
read-only
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
从节点导入中间节点数据
vim all.sql
找到CHANGE MASTER TO所在的行，修改为CHANGE MASTER TO MASTER_HOST='192.168.130.133',MASTER_USER='repluser',MASTER_PASSWORD='replpasswd',MASTER_PORT=3306, MASTER_LOG_FILE='localhost-bin.000003', MASTER_LOG_POS=379;
注：后面的MASTER_LOG_FILE和MASTER_LOG_POS不用修改。
mysql -uroot -p < all.sql
mysql -uroot -p
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
检查Slave_IO_Running和Slave_SQL_Running状态是否为Yes
测试
在主节点导入数据，从节点查询是否通过中间节点产生新的数据。

4、MariaDB半同步复制
主从节点安装mariadb
vim /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.5/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum -y install mariadb-server
主节点配置
vim /etc/my.cnf.d/server.cnf
server-id=1
log-bin=/data/mysql/mysql-bin
plugin-load-add=semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
mkdir -p /data/mysql
chown -R mysql.mysql /data/mysql
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
mysql -uroot -p
MariaDB [(none)]> show master status;
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.1.%' identified by 'replpasswd';
MariaDB [(none)]> show global variables like '%semi%';
检查rpl_semi_sync_master_enabled是否为ON
MariaDB [(none)]> show global status like '%semi%';
从节点连上以后，会显示客户端数量

从节点配置
[mysqld]
server-id=2
log-bin
read-only
plugin-load-add=semisync_slave
rpl_semi_sync_slave_enabled=ON
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
mysql -uroot -p
MariaDB [(none)]> change master to master_host='192.168.130.132',master_user='repluser',master_password='replpasswd',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=332;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show global variables like '%semi%';
检查rpl_semi_sync_slave_enabled是否为ON
MariaDB [(none)]> show global status like '%semi%';
检查Rpl_semi_sync_slave_status是否为ON

5、MariaDB高可用方案MHA
准备三台新安装的CentOS7.6服务器，并使用yum安装MariaDB10.2.23，主机名分别为node1，node2，node3，IP地址分别为192.168.130.132-134
node1/2/3安装mariadb
vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name=MariaDB
baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
yum -y install mariadb-server

node1配置
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=1
log-bin
skip_name_resolve=1
general_log
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
mysql -uroot -p
MariaDB [(none)]> show master status;
记住File：mha-manager-bin.000001和Position：334，后面会用到
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'replpasswd';
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.130.%' identified by 'mhapasswd';
ifconfig ens33:1 192.168.130.100/24
yum -y install mha4mysql-manager-0.57-0.el7.noarch.rpm mha4mysql-node-0.57-0.el7.noarch.rpm
ssh-keygen
ssh-copy-id 192.168.130.132
rsync -a .ssh 192.168.130.133:/root/
rsync -a .ssh 192.168.130.134:/root/
scp mha4mysql-node-0.57-0.el7.noarch.rpm root@192.168.130.133:/root/
scp mha4mysql-node-0.57-0.el7.noarch.rpm root@192.168.130.134:/root/
mkdir -p /data/mha/app1
mkdir /etc/mha
vim /etc/mha/app1.cnf
[server default]
user=mhauser
password=mhapasswd
manager_workdir=/data/mha/app1/
manager_log=/data/mha/app1/manager.log
remote_workdir=/data/mha/app1/
ssh_user=root
repl_user=repluser
repl_password=replpasswd
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
#master_ip_online_change_script=/usr/local/bin/master_ip_online_change
#report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
[server1]
hostname=192.168.130.132
[server2]
hostname=192.168.130.133
candidate_master=1
[server3]
hostname=192.168.130.134

node2配置
yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm
node2修改mariadb配置文件
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=2
log-bin
read_only
skip_name_resolve=1
relay_log_purge=0
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
mysql -uroot -p
MariaDB [(none)]> change master to master_host='192.168.130.132',master_user='repluser',master_password='replpasswd',master_port=3306,master_log_file='mha-manager-bin.000001',master_log_pos=334;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

node3配置
yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm
node3修改mariadb配置文件
vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=3
log-bin
read_only
skip_name_resolve=1
relay_log_purge=0
systemctl enable mariadb.service
systemctl start mariadb.service
mysql_secure_installation
mysql -uroot -p
MariaDB [(none)]> change master to master_host='192.168.130.132',master_user='repluser',master_password='replpasswd',master_port=3306,master_log_file='mha-manager-bin.000001',master_log_pos=334;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

mha-manager配置
node1检查SSH连接
masterha_check_ssh --conf=/etc/mha/app1.cnf
node1检查复制
vim /usr/local/bin/master_ip_failover
my $vip设置mha-manager虚拟IP
my $gateway设置默认网关IP
my $interface设置网卡名称
chmod +x /usr/local/bin/master_ip_failover
chmod +x /usr/local/bin/sendmail.sh
masterha_check_repl --conf=/etc/mha/app1.cnf
node1启动mha manager
nohup masterha_manager --conf=/etc/mha/app1.cnf &
masterha_check_status --conf=/etc/mha/app1.cnf

测试master自动切换
node1执行systemctl stop mariadb.service
node1停止mariadb服务后，master自动切换到node2，虚拟IP也切换到node2上

注意事项：
master自动切换后masterha_manager会退出，node1重新启动mariadb服务后需要连接到新的主节点，操作过程如下：
新的master执行
MariaDB [(none)]> show master status;
记住File和Position的值，当前为slave1-bin.000001，1651
node1连接master节点
MariaDB [(none)]> change master to master_host='192.168.130.133',master_user='repluser',master_password='replpasswd',master_port=3306,master_log_file='slave1-bin.000001',master_log_pos=1651;
MariaDB [(none)]> start slave;
再次启动masterha_manager
rm -f /data/mha/app1/app1.failover.complete
masterha_check_repl --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf &
masterha_check_status --conf=/etc/mha/app1.cnf