mariadb数据库主从配置

1.0项目目的

数据就是企业的命脉，数据库的单点配置肯定不可取，至少应该考虑数据库的主从配置，实现数据的备份。

2.0数据库同步原理

主从同步的核心需求：二进制日志

实现主从同步（主从复制）：

主库将改变的数据记录在本地的二进制日志中（binary log），该过程称之为二进制日志事件；
从库将主库的二进制文件拷贝到本地的 relay log（中继日志）中；
slave 将中继日志数据写入到本地的数据库之中。

3.0项目实施步骤

准备 2 台 Centos7 虚拟机：

master：172.17.2.10
slave：172.17.2.11

4.0 配置过程概述

主从服务器节点设置不同的server-id；
master 启用二进制日志和slave启用relaylog（中继日志，从节点才需要开启）；
主节点创建一个拥有复制权限的用户账号；
查询主节点binlog信息；
设置从节点同步主节点；

master 节点配置

修改 Mariadb 配置文件

[root@db1 ~]# cd /etc/my.cnf.d/
[root@db1 my.cnf.d]# ll
total 16
-rw-r--r--. 1 root root  763 May 18 19:30 enable_encryption.preset
-rw-r--r--. 1 root root  232 May 18 19:30 mysql-clients.cnf
-rw-r--r--. 1 root root 1080 May 18 19:30 server.cnf
-rw-r--r--. 1 root root  120 May 18 19:30 spider.cnf
[root@db1 my.cnf.d]# vi server.cnf
[mysqld]
lower_case_table_names=1   #表名大小写不敏感；
[mariadb]
bind-address=172.17.2.10   #绑定服务IP
server_id=51    #用来标识Mariadb的身份，必须唯一（一般用IP地址的最后一组数字表示）；
log-bin=mysql-bin   #binlog二进制日志文件；
log-basename=master   #binlog 的命名规则， binlog 会以它为前缀生成日志，如 master-bin.000001；
binlog-format=mixed
max_binlog_size=200M  #生成的log最大值，到达最大值，会重新创建一个，如 master-bin.000002；
expire_logs_days=14   #binlog日志过期天数，过期则自动清理。

max_binlog_size=200M 生成的 log 最大值，到达最大值，会重新创建一个，如 master-bin.000002；

expire_logs_days binlog 日志过期天数，过期则自动清理。

重启服务生效

[root@db1 my.cnf.d]# systemctl restart mariadb
[root@db1 my.cnf.d]# ss -lntp |grep 3306
LISTEN     0      80           *:3306                     *:*                   users:(("mariadbd",pid=13354,fd=22))
LISTEN     0      80        [::]:3306                  [::]:*                   users:(("mariadbd",pid=13354,fd=23))

创建数据同步账号

[root@db1 my.cnf.d]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.8.3-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#创建账号slave，密码为：password
MariaDB [(none)]> CREATE USER 'slave'@'172.17.2.11' IDENTIFIED BY 'password';
#账号赋权
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.17.2.11';
Query OK, 0 rows affected (0.000 sec)

查看 master 数据库 binglog 日志文件信息

slave 数据库数据同步开始的位置

#添加读锁，会关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。也就是将所有的脏页都要刷新到磁盘，然后对所有的表加上了读锁，于是这时候直接拷贝数据文件也就是安全的。
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      648 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> exit
Bye
[root@db1 my.cnf.d]# cd
[root@db1 ~]#

备份数据库

2 个作用：

防止主从数据同步失败，造成数据库损坏；
导入 slave 数据库，减少数据同步时间。

#由于上述步骤中已对所有数据库表加锁，这里备份的时候不用加锁了。
[root@db1 ~]# mysqldump --all-databases -uroot -proot >master.sql
[root@db1 ~]# ll
total 1756
-rw-------. 1 root root    1401 Feb 21 09:08 anaconda-ks.cfg
-rw-r--r--. 1 root root 1792684 May 30 09:36 master.sql

[root@db1 ~]# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.8.3-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      648 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

#解除读锁
MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye

#传输备份文件到slave
[root@db1 ~]# scp master.sql root@172.17.2.11:/root/
The authenticity of host '172.17.2.11 (172.17.2.11)' can't be established.
ECDSA key fingerprint is SHA256:J8HkNDGiVe1356WZEh4iNUNgrjIhyKvT/X0HEOrYGyg.
ECDSA key fingerprint is MD5:40:0f:f3:d4:08:0b:7b:51:de:b8:a0:01:ad:97:bb:49.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.17.2.11' (ECDSA) to the list of known hosts.
root@172.17.2.11's password:
master.sql

slave 节点配置

slave 节点配置文件

[root@db2 ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
lower_case_table_names=1   #表名大小写不敏感；
[mariadb]
bind-address=172.17.2.11   #绑定服务IP
server_id=52    #用来标识Mariadb的身份，必须唯一（一般用IP地址的最后一组数字表示）；
#log-bin=mysql-bin   #binlog二进制日志文件；
#log-basename=master   #binlog 的命名规则， binlog 会以它为前缀生成日志，如 master-bin.000001；
#binlog-format=mixed
max_binlog_size=200M  #生成的log最大值，到达最大值，会重新创建一个，如 master-bin.000002；
expire_logs_days=14   #binlog日志过期天数，过期则自动清理。
#主数据库到从数据库是单向同步，从数据库设置只读，防止数据被写入从数据库，进而造成主从数据不一致问题，但是mysql的root仍旧可以写入
read_only=ON

注意：如果主从角色切换，需要取消：read_only 参数。

重启 Mariadb 服务

[root@db2 ~]# systemctl restart mariadb
[root@db2 ~]# ss -lntp |grep 3306
LISTEN     0      80     172.17.2.11:3306                     *:*                   users:(("mariadbd",pid=2448,fd=19))

slave 导入数据库

MariaDB [test]> source ~/master.sql;
Query OK, 0 rows affected (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
...
Database changed

设置 slave 主从配置

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='172.17.2.10',   #master IP
    -> MASTER_USER='slave',     #同步账号
    -> MASTER_PASSWORD='password',   #同步账号密码
    -> MASTER_LOG_FILE='master-bin.000001', #二进制文件名
    -> MASTER_LOG_POS=648,     #日志记录节点
    -> MASTER_CONNECT_RETRY=10;    #在主服务器宕机或连接丢失的情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数。默认值为60
Query OK, 0 rows affected (0.006 sec)
MariaDB [test]> START SLAVE;    #开始同步
Query OK, 0 rows affected (0.002 sec)

测试主从配置

master 节点

MariaDB [(none)]> create database weipeng;   #创建测试数据库weipeng
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]>

slave 节点

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| weipeng            |
+--------------------+
6 rows in set (0.000 sec)
MariaDB [(none)]> show slave status \G;  #查看数据同步是否异常

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.17.2.10
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: master-bin.000001
           Read_Master_Log_Pos: 91117
                Relay_Log_File: slave-relay-bin.000002
                 Relay_Log_Pos: 45829
         Relay_Master_Log_File: master-bin.000001
              Slave_IO_Running: Yes    #显示Yes正常
             Slave_SQL_Running: Yes    #显示Yes正常
             ...

mariadb 数据库主从结构设置成功。

数据库主从切换

正常切换

主备正常切换，此场景主要是针对在主备同步复制正常的情况下进行的主备切换，例如：灾备演练，主节点服务器异常警报等计划性的主备切换。

步骤：

主库锁表

切断主库数据写入，保证 master 库与 slave 库数据完全同步。

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.001 sec)

执行完成后生产环境必须等待所有语句执行完成，如果有个慢查询在执行，或者一个打开的事务，flush tables with read lock 就会被阻塞，直到所有的锁被释放。

查看主、从库事务是否处理完毕

master 节点执行

MariaDB [(none)]> show processlist;
+-----+------+-----------+------+---------+------+----------+------------------+----------+
| Id  | User | Host      | db   | Command | Time | State    | Info             | Progress |
+-----+------+-----------+------+---------+------+----------+------------------+----------+
|  89 | root | localhost | NULL | Sleep   | 1825 |          | NULL             |    0.000 |
| 106 | root | localhost | NULL | Query   |    0 | starting | show processlist |    0.000 |
+-----+------+-----------+------+---------+------+----------+------------------+----------+
2 rows in set (0.000 sec)

slave 节点执行

MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State    | Info             | Progress |
+----+------+-----------+------+---------+------+----------+------------------+----------+
| 16 | root | localhost | NULL | Query   |    0 | starting | show processlist |    0.000 |
+----+------+-----------+------+---------+------+----------+------------------+----------+
1 row in set (0.000 sec)

slave 节点关掉 slave 同步功能

#停掉slave数据同步
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.001 sec)

#清理slave配置信息
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.000 sec)

slave 转为 master

关闭 read-only

[root@slave my.cnf.d]# vim server.cnf
...
read_only=off
[root@slave my.cnf.d]# systemctl restart mariadb

设置状态为 maser

涉及命令：reset master;

[root@slave my.cnf.d]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.6.8-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      329 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

master 转为 slave

上述 slave 的步骤再来一遍

MariaDB [(none)]>CHANGE MASTER TO MASTER_HOST='172.17.2.11',MASTER_USER='slave',MASTER_PORT=3306,MASTER_PASSWORD='password',master_auto_position=329 ;
MariaDB [(none)]>start slave;
MariaDB [(none)]>show slave status\G

非正常切换

master 突然宕机，大都存在未同步的数据，需要详查。如果 master 的 binlog 还可以获取，则需要找到已被 slave 同步的数据，剩下的数据需要导出为 sql 语句导入到从库中。

posted @ 2023-08-05 07:40 浅~~ 阅读(726) 评论(0) 收藏举报

刷新页面返回顶部

qianqian2