您的位置: 首页  >  文章  >  N46第十四周作业

N46第十四周作业

分类: 文章 • 2024-08-25 20:11:40

1 MariaDB主从复制原理：

MariaDB主从复制采用的是异步复制方式，主服务器写数据时会将数据写到本地磁盘并新增
二进制文件中的内容，二进制文件写好后就会向客户端发送写成功的消息，从服务器是否同步了主
服务器的二进制文件并不关心，具体步骤：

1 主服务器收到写请求，将结果写入磁盘，生成二进制日志文件的内容，然后返回客户端
成功的消息
2 主服务器通知从服务器二进制文件内容新增的消息，从服务器收到通知后会由IO thread
向主服务器发起二进制文件同步请求
3 主服务器的dump thread读取主服务器上发生改变的二进制文件
4 主服务器会将发生改变的二进制文件内容发送给从服务器，从服务器会将内容保存到
自己的中继日志中
5 从服务器上的sql thread线程将中继日志读取并执行，执行的结果会被保存到磁盘文件中

2 MariaDB一主一从架构构建，准备2台纯新的centos7.6服务器，其中主机称为node1的服
务器地址为192.168.130.132，主机称为node2的服务器地址为192.168.130.133：

在主和从上配置：
[[email protected] ~]# vim /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1

[[email protected] ~]# yum install -y mariadb-server
[[email protected]ost ~]# mkdir -p /data/{mysql,log,pid}
[[email protected] ~]# chown -R mysql:mysql /data/{mysql,log,pid}
[[email protected] ~]# mysql_install_db --datadir=/data/mysql --user=mysql
[[email protected] ~]# vim /etc/my.cnf.d/server.cnf
...
[mysqld]
datadir=/data/mysql/
socket=/data/mysql/mysql.sock
pid-file=/data/pid/mysql.pid
log-error=/data/log/mysql.log
...
[[email protected] ~]# vim /etc/my.cnf.d/mysql-clients.cnf

[mysql]
socket=/data/mysql/mysql.sock

[[email protected] ~]# systemctl enable --now mariadb

主节点配置：
[[email protected] ~]# vim /etc/my.cnf.d/server.cnf

[mysqld]
datadir=/data/mysql/
socket=/data/mysql/mysql.sock
pid-file=/data/pid/mysql.pid
log-error=/data/log/mysql.log
log_bin
server-id=200
log-basename=master

[[email protected] ~]# systemctl restart mariadb
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       526 |
+-------------------+-----------+
1 row in set (0.00 sec)

从节点配置：
[[email protected] yum.repos.d]# vim /etc/my.cnf.d/server.cnf

[mysqld]
datadir=/data/mysql/
socket=/data/mysql/mysql.sock
pid-file=/data/pid/mysql.pid
log-error=/data/log/mysql.log
log-bin
server-id=201
read_only=ON
relay_log=relay-log
relay_log_index=relay-log.index

[[email protected] yum.repos.d]# systemctl restart mariadb
MariaDB [(none)]> change master to master_host='10.0.0.200',
    -> master_user='hyc',
    -> master_password='hyc940421'
    -> ,master_log_file='master-bin.000001',
    -> master_log_pos=526;
Query OK, 0 rows affected (0.03 sec)

启动从节点：
MariaDB [(none)]> start slave;

查看从节点状态：
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.200
                  Master_User: hyc
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 526
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 556
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

主节点导入测试数据：
[[email protected] ~]# mysql<hellodb_innodb.sql
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

查看从节点同步情况：
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

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

从节点的配置：
[[email protected] ~]# vim /etc/my.cnf.d/server.cnf

...
[mysqld]
datadir=/data/mysql/
socket=/data/mysql/mysql.sock
pid-file=/data/pid/mysql.pid
log-error=/data/log/mysql.log
log-bin
server-id=201
read_only=ON
relay_log=relay-log
relay_log_index=relay-log.index
log_slave-updates

[[email protected] ~]# vim /etc/my.cnf.d/mysql-clients.cnf

...
[mysqldump]
socket=/data/mysql/mysql.sock

[[email protected] ~]# systemctl restart mariadb
[[email protected] ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql
[[email protected] ~]# scp /data/all.sql [email protected]:/data

新增后备节点配置：
[[email protected] data]# vim /etc/my.cnf.d/server.cnf

...
[mysqld]
server-id=202
read-only
datadir=/data/mysql/
socket=/data/mysql/mysql.sock
pid-file=/data/pid/mysql.pid
log-error=/data/log/mysql.log

[[email protected] /]# vim /data/all.sql
...
-- Dump completed on 2020-09-11 21:01:32
CHANGE MASTER TO
MASTER_HOST='10.0.0.201',
MASTER_USER='hyc',
MASTER_PASSWORD='hyc940421',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=371;

[[email protected] /]# mysql < /data/all.sql
[[email protected] /]# mysql -e 'start slave;'

查看从节点状态：
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.200
                  Master_User: hyc
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 470
               Relay_Log_File: relay-log.000008
                Relay_Log_Pos: 635
        Relay_Master_Log_File: master-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: 470
              Relay_Log_Space: 1238
              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: 200
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.01 sec)

在主节点上创建hehe库：

N46第十四周作业

在从节点和备份节点查看：

N46第十四周作业

N46第十四周作业


4 MariaDB半同步复制

主节点配置：
[[email protected] ~]# vim /etc/my.cnf.d/server.cnf

...
[mysqld]
datadir=/data/mysql/
socket=/data/mysql/mysql.sock
pid-file=/data/pid/mysql.pid
log-error=/data/log/mysql.log
log_bin
server-id=200
log-basename=master
plugin-load-add=semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
...

重启主节点的mariadb：
[[email protected] ~]# systemctl restart mariadb


查看主节点的半同步变量是否正常打开：
N46第十四周作业

N46第十四周作业

从节点配置：
[[email protected] ~]# vim /etc/my.cnf.d/server.cnf

...
[mysqld]
datadir=/data/mysql/
socket=/data/mysql/mysql.sock
pid-file=/data/pid/mysql.pid
log-error=/data/log/mysql.log
log-bin
server-id=201
read_only=ON
relay_log=relay-log
relay_log_index=relay-log.index
log_slave-updates
plugin_load_add=semisync_slave
rpl_semi_sync_slave_enabled=ON
...

重启从节点的mariadb：
[[email protected] ~]# systemctl restart mariadb

查看从节点半同步变量是否正常打开：
N46第十四周作业

测试：
主节点server.cnf配置文件中，rpl_semi_sync_master_timeout被设置为3000：
N46第十四周作业

5 MariaDB高可用方案MHA，准备3台新安装的centos7.6服务器，并使用yum安装MariaDB10.2.23，
主机名分别为node1，node2，node3，IP地址分别为192.168.130.132-134

在3个节点配置安装mariadb：
[[email protected] bin]#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配置（master配置）：
[[email protected] bin]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=1
log-bin
skip_name_resolve=1
general_log

[[email protected] bin]#systemctl enable mariadb.service
[[email protected] bin]#systemctl start mariadb.service
[[email protected] bin]#mysql_secure_installation
[[email protected] bin]#mysql -uroot -p
MariaDB [(none)]> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000001 |      815 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to [email protected]'10.0.0.%' identified by 'replpass';
MariaDB [(none)]> grant all on *.* to [email protected]'10.0.0.%' identified by 'mhapass';
[[email protected] ~]#ifconfig ens33:1 10.0.0.100/24
[[email protected] ~]#yum -y install mha4mysql-manager-0.57-0.el7.noarch.rpm mha4mysql-node-0.57-0.el7.noarch.rpm
[[email protected] ~]#ssh-******
[[email protected] ~]#ssh-copy-id 10.0.0.201
[[email protected] ~]#rsync -a .ssh 10.0.0.202:/root/
[[email protected] ~]#rsync -a .ssh 10.0.0.203:/root/
[[email protected] ~]#mkdir -p /data/mha/app1
[[email protected] ~]#mkdir /etc/mha

[[email protected] ~]#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=10.0.0.201
[server2]
hostname=10.0.0.202
candidate_master=1
[server3]
hostname=10.0.0.203

node2配置：
[[email protected] ~]#yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm

[[email protected] ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=2
log-bin
read_only
skip_name_resolve=1
relay_log_purge=0

[[email protected] ~]#systemctl enable mariadb.service
[[email protected] ~]#systemctl start mariadb.service
[[email protected] ~]#mysql_secure_installation
[[email protected] ~]#mysql -uroot -p123456

配置node2与node1进行同步并启动slave：
MariaDB [(none)]> change master to
master_host='10.0.0.201',
master_user='repluser',
master_password='replpass',
master_port=3306,
master_log_file='localhost-bin.000001',
master_log_pos=815;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

node3配置
[[email protected] ~]#yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm

[[email protected] ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id=3
log-bin
read_only
skip_name_resolve=1
relay_log_purge=0

[[email protected] ~]#systemctl enable mariadb.service
[[email protected] ~]#systemctl start mariadb.service
[[email protected] ~]#mysql_secure_installation

配置node3与node1同步并启动slave：
[[email protected] ~]#mysql -uroot -p123456
MariaDB [(none)]> change master to
master_host='10.0.0.201',
master_user='repluser',
master_password='replpass',
master_port=3306,
master_log_file='localhost-bin.000001',
master_log_pos=815;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

node1配置（mha-master配置）：
[[email protected] ~]#masterha_check_ssh --conf=/etc/mha/app1.cnf

vim /usr/local/bin/master_ip_failover
[[email protected] bin]# cat master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.0.0.100';#设置Virtual IP
my $gateway = '10.0.0.254';#网关Gateway IP
my $interface = 'ens33';
...
(内容较多，只列出修改项)

安装并配置邮件服务：
[[email protected] bin]#yum install -y mailx

[[email protected] bin]# tail -7 /etc/mail.rc
...
# For Linux and BSD, this should be set.
set bsdcompat
set [email protected]
set smtp=smtp.qq.com
set [email protected]
set smtp-auth-password=*********************
(内容较多仅列出修改项)

[[email protected] bin]# cat sendmail.sh
echo "MySQL is down" | mail -s "MHA Warning" [email protected]

[[email protected] bin]#chmod +x /usr/local/bin/master_ip_failover
[[email protected] bin]#chmod +x /usr/local/bin/sendmail.sh
masterha_check_repl --conf=/etc/mha/app1.cnf


测试：
node1启动mha manager：
[[email protected] bin]# nohup masterha_manager --conf=/etc/mha/app1.cnf &
masterha_check_status --conf=/etc/mha/app1.cnf
[2] 15683
nohup: ignoring input and appending output to ‘nohup.out’
app1 (pid:8645) is running(0:PING_OK), master:10.0.0.201
[[email protected] bin]# systemctl stop mariadb.service

node1上的mariadb出现异常后，node1上的mha4mysql-manager切换主从和VIP后自动退出：
[[email protected] bin]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:e9:01:5d brd ff:ff:ff:ff:ff:ff
    inet 10.0.0.202/24 brd 10.0.0.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 10.0.0.100/8 brd 10.255.255.255 scope global ens33:1
       valid_lft forever preferred_lft forever
...
[[email protected] ~]# mysql -uroot -p123456
...

MariaDB [(none)]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| localhost-bin.000001 |      1079 |
+----------------------+-----------+
1 row in set (0.00 sec)

将node1的mariadb恢复运行并作为node2的从节点运行：
MariaDB [(none)]> change master to
 master_host='10.0.0.202',
master_user='repluser',
master_password='replpass',
master_port=3306,
master_log_file='localhost-bin.000001',
master_log_pos=1079;
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

 

 

 

相关推荐