MariaDB主从热备部分库部分表同步

lihongbao80

已于 2022-12-28 10:06:15 修改

阅读量840

点赞数

分类专栏： mysql 文章标签： mariadb mysql 数据库

于 2020-12-28 15:55:06 首次发布

本文链接：https://blog.csdn.net/lihongbao80/article/details/103875281

版权

mysql 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

1 安装前清理

1、清理原有的mysql数据库；
使用以下命令查找出安装的mysql软件包和依赖包：

rpm -qa | grep mysql

显示结果如下：

mysql80-community-release-el7-1.noarch
mysql-community-server-8.0.11-1.el7.x86_64
mysql-community-common-8.0.11-1.el7.x86_64
mysql-community-libs-8.0.11-1.el7.x86_64
mysql-community-client-8.0.11-1.el7.x86_64

使用以下命令依次删除上面的程序

yum remove mysql

删除mysql的配置文件，卸载不会自动删除配置文件，首先使用如下命令查找出所用的配置文件；

find / -name mysql

可能的显示结果如下：

/etc/logrotate.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/etc/selinux/targeted/tmp/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/mysql
/usr/local/mysql

根据需求使用以下命令依次对配置文件进行删除

rm -rf /var/lib/mysql

2、删除MariaDB的文件，
由于MySQL在CentOS7中收费了，所以已经不支持MySQL了，取而代之在CentOS7内部集成了mariadb，而安装MySQL的话会和MariaDB的文件冲突，所以需要先卸载掉MariaDB.
使用rpm 命令查找出要删除的mariadb文件；

rpm -qa | grep Maria

可能的显示结果如下：

mariadb-libs-5.5.56-2.el7.x86_64

删除上面的程序

rpm -e mariadb-libs-5.5.56-2.el7.x86_64

可能出现错误提示如下：
依赖检测失败：

libmysqlclient.so.18()(64bit) 被 (已安裝) postfix-2:2.10.1-6.el7.x86_64 需要
libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-6.el7.x86_64 需要
libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-6.el7.x86_64 需要

使用强制删除：

rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

至此就将原来有的mysql 和mariadb数据库删除了；

2 安装

2.1 添加 MariaDB yum 仓库

1、编辑创建mariadb.repo仓库文件

 vi /etc/yum.repos.d/MariaDB.repo

2、添加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

2.2 安装数据库

2.2.1 安装mariadb

yum install MariaDB-server MariaDB-client -y

2.2.2 启动mariadb数据库

systemctl start mariadb

2.2.3 初始化mariadb（可以不做，做了信息安全）

mysql_secure_installation

首先是设置密码，会提示先输入密码
Enter current password for root (enter for none):<–初次运行直接回车
设置密码
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
其他配置
Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,
Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车

2.2.4 配置myariadb远程登录

mysql -u root -p

输入密码并进入数据库

grant all privileges on *.* to root@'%' identified by '123456';

授予所有的权限在所有的库.所有的表用户名@“所有的地址” identified by “密码”;
最后别忘了

flush privileges;

2.2.5 关闭selinux

查看：

getenforce

临时关闭：

setenforce 0

彻底关闭：

vim /etc/selinux/config

2.2.6 CentOS 7 开放防火墙端口

如果还是不能远程连上数据库的话应该就是3306端口被防火墙拦截了，关闭防火墙：

systemctl stop firewalld.service            #停止firewall
systemctl disable firewalld.service        #禁止firewall开机启动

如果不关闭防火墙，需要开放防火墙端口，开启后要重启防火墙：

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

2.2.7 设置数据库字母大小写不敏感（需要重启数据库）

vim /etc/my.cnf
#在[mysqld]下加上】
lower_case_table_names=1

默认是等于0的,即大小写敏感。改成1就OK了。如果之前已经建了数据库要把之前建立的数据库删除，重建才生效。

2.2.8 设置MariaDB数据库默认编码（需要重启数据库）

MariaDB的默认编码是latin1，插入中文会乱码，因此需要将编码改为utf8。

1、登录数据库，使用以下命令查看当前使用的字符集

 SHOW VARIABLES LIKE 'character%';

2、修改的配置文件

[root@app10-paas ~]# vim /etc/my.cnf

#在[mysqld]字段里加入
character-set-server=utf8

2.2.9 优化数据库（需要重启数据库）

[root@app10-paas ~]# vim /etc/my.cnf

#在[mysqld]字段里加入：
max_connections=1000
innodb_buffer_pool_size = 6144M
wait_timeout=1800
interactive_timeout=1800
innodb_log_buffer_size=100M
query_cache_size=1024M

2.2.10 修改数据文件路径

1、若要修改文件目录，需要拷贝/var/lib/mysql中的文件覆盖到新的文件路径里，需要带着权限，请使用命令如下：

[root@test ~]# cp -a /var/lib/mysql/* /tpdata/mysql/

2、修改配置文件mariadb.service（mysql我没有做这一步）

vim /usr/lib/systemd/system/mariadb.service

把ProtectHome=true
改成ProtectHome=false
需要重新加载并给出了命令：

systemctl daemon-reload

3、修改配置文件

vim /etc/my.cnf

在[mysqld]字段里加入：

datadir=/home/mysql/data

4、重启主从服务

systemctl restart mysql;

2.3 主从配置

2.3.1 Master主数据库配置，并重启数据库

vim /etc/my.cnf

#在[mysqld]字段里加入：
log-bin=mysql-bin #启用二进制日志，默认存在/var/lib/mariadb 下面
server-id=14 #本机数据库ID唯一标示，一般是ip最后一位

replicate-do-db=test1,test2 #同步哪些库

2.3.2 Slave从数据库配置，并重启数据库

vim /etc/my.cnf.d/server.cnf

#在[mysqld]字段里加入：
```powershell
server-id=48

replicate-do-table=test1.a1  #同步哪些表，多个库多个表可以写多次
replicate-do-table=test1.a3

2.3.3 master主数据库操作

1、锁定数据库的表，防止数据写入

flush table with read lock;

2、主从同步，将从库与主库的数据，保持一致后，它俩都在同一个起跑线，然后解除锁表，一同写入数据，保证数据一致性
a）导出当前的数据，用于slave机器导入数据，保证在一个起始点

[root@test3 ~]# mysqldump -u root -p --all-databases > /data/db.dump

b）将此db.dump文件远程传输给 slave机器，用于导入

[root@test3 ~]# scp /data/db.dump  root@192.168.12.87:/tmp/

c）登录slave从库，导入主库的数据信息

[root@test3 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> source /tmp/db.dump

3、解锁表，开始主从同步

mysql> unlock tables;

2.2.3.4 slave从数据库操作

1、通过命令，开启主从同步
在Master上执行，查出master_log_file和master_log_pos的结果：

mysql> show master status;

在这里插入图片描述

在从数据库执行

change master to master_host='192.168.192.27',
master_user='root',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=321;

2、通过命令，开启主从同步

mysql> start slave;

3、查看从库同步情况

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.192.27
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 321
               Relay_Log_File: test3-relay-bin.000002
                Relay_Log_Pos: 510
        Relay_Master_Log_File: mysql-bin.000001
             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: 1211
              Relay_Log_Space: 717
              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: 27
                  Master_UUID: d6ec1d37-eea1-11eb-af3e-a85e45e7082e
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

2.4 彻底清除slave

现在由于业务的需要，不需要这台Slave同步复制Master，那么采用reset slave时，你会发现同步信息还存在，如：

mysql> stop slave; 
Query OK, 0 rows affected (0.19 sec)  3.  
mysql> reset slave; 
Query OK, 0 rows affected (0.17 sec)  6.  
mysql> show slave status\G;

执行reset slave，其实是把master.info和relay-log.info文件给删除，但里面的同步信息还在，那么可以用这个方法，让其清除的彻彻底底。

mysql> reset slave all;

注：此语句支持在MySQL5.5或更高版本。

3 部分库部分表同步设置说明

3.1 master端：

binlog-do-db 二进制日志记录的数据库(多数据库用逗号，隔开)

binlog-ignore-db 二进制日志中忽略数据库 (多数据库用逗号，隔开)

举例说明：

1)binlog-do-db=YYY 需要同步的数据库，不在内的不同步。(不添加这行表示同步所有)

这里主库只同步test1，test2库。

这里写图片描述

2)binlog-ignore-db = mysql 这是不记录binlog，来达到从库不同步mysql库，以确保各自权限

binlog-ignore-db = performance_schema

binlog-ignore-db = information_schema

这里向从库同步时忽略test1，test2库

这里写图片描述

3.2 slave端

replicate-do-db 设定需要复制的数据库(多数据库使用逗号，隔开)

replicate-ignore-db 设定需要忽略的复制数据库 (多数据库使用逗号，隔开)

replicate-do-table 设定需要复制的表

replicate-ignore-table 设定需要忽略的复制表

replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符

replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

与上述对比，这里的replicate就很好理解了，下面简单说几点。

例如：从库忽略某个库

从库忽略复制数据库test3，但是需要说明的是，其实从库的relaylog中是存在关于test3的相关日志，只是从库没有使用罢了。

在这里插入图片描述
例如：从库忽略某些表

例如：从库只同步某些表

例如：（没验证）增加通配符的两个配置

replicate-wild-do-table=db_name.%      #只复制哪个库的哪个表
replicate-wild-ignore-table=mysql.%    #忽略哪个库的哪个表

有可能存在的问题

网络上的教程就到这里，然后就让你去开启mariaDB的服务，如果你尝试一下，就会发现根本开启不了，系统会报错，告诉你在/home/report/data下新建一个临时文件新建不了，/etc/my.cnf.d/server.cnf/下的
aria_log_control文件不存在，aria_log.00000001文件无法打开等等…反正就是启动不了mariaDB服务，这是为什么呢？执行ls -Z查看新建目录与/var/lib/mysql两者的安全信息可以看到：
新目录：

/var/lib/mysql
两者的 security context信息并不一致，这就是导致mariaDB服务无法启动的原因，所以要使用semanage去修改新目录的 security context
1、修改新目录的 security context信息，执行：

semanage fcontext -a -t mysqld_db_t "/home/data(/.*)?"

2、恢复目录的 security context信息，执行：

 restorecon -R -v /home/data

3、启动MariaDB服务，执行：

systemctl restart mysql