蓝桉云顶-如何在MySQL中配置LDAP主从复制？

MySQL主从复制配置涉及设置主服务器和至少一个从服务器，通过二进制日志文件实现数据同步。LDAP（轻量级目录访问协议）可用于身份验证。

MySQL互为主从配置及LDAP主从设置详解

一、数据库概念

1、什么是MySQL？：MySQL是一款开源的关系型数据库管理系统（RDBMS），在GPL（通用公共许可证）许可下发布，它使用结构化查询语言（SQL）进行数据库管理和操作，适用于各种平台和编程语言的连接。

2、什么是数据库？：数据库是长期存储在计算机内的、有组织的、可共享的数据集合，数据库中的数据按一定的数据模型组织和描述，具备较小的冗余度、较高的数据独立性和易扩展性，并可供各种用户共享。

3、什么是主从数据库？：主从复制是一种数据库架构，其中一个数据库（主库）负责处理写操作并将更改复制到一个或多个从库，从库主要负责读操作，这提高了数据库的性能和可用性。

4、什么是互为主从数据库？：互为主从架构是指两台数据库服务器互相作为对方的主库和从库，这种架构提供了高可用性和数据一致性，即使一台服务器出现故障，另一台服务器也能继续提供服务。

二、部署基础操作环境

1. 配置基础环境

（1）更改虚拟机的主机名：

主机名为master和slave，分别作为主数据库和从数据库。

[root@localhost ~]# hostnamectl set-hostname master

[root@localhost ~]# hostnamectl set-hostname slave

（2）分别在两台虚拟机配置本地yum源：

删除现有的repo包：[root@master ~]# rm -rf /etc/yum.repos.d/

创建新的yum源目录：[root@master ~]# mkdir /opt/centos

挂载光盘到目录：[root@master ~]# mount /dev/cdrom /opt/centos/

配置yum源文件：[root@master ~]# vi /etc/yum.repos.d/local.repo

内容如下：

    [centos]
    name=centos
    baseurl=file:///opt/centos
    enabled=1
    gpgcheck=0

清理缓存并获取yum源信息：[root@master ~]# yum clean all && yum repolist

（3）配置hosts文件解析：

master添加：192.168.200.10 master

slave添加：192.168.200.20 slave

（4）关闭防火墙：

临时关闭SELinux：[root@master ~]# setenforce 0

永久关闭SELinux：[root@master ~]# sed -i 's/SELINUX=enforcing/ SELINUX=disabled/g' /etc/selinux/config

临时关闭防火墙：[root@master ~]# systemctl stop firewalld

禁用防火墙开机自启动：[root@master ~]# systemctl disable firewalld

三、MySQL互为主从配置

1. 设置数据库配置文件

（1）库1（主库）的配置：

编辑MySQL配置文件：vim /etc/my.cnf

添加以下内容：

    [mysqld]
    server-id=1
    log_bin=master-bin
    auto_increment_increment=2
    auto_increment_offset=1
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    replicate-do-db=test

（2）库2（从库）的配置：

编辑MySQL配置文件：vim /etc/my.cnf

添加以下内容：

    [mysqld]
    server-id=2
    log_bin=master-bin
    auto_increment_increment=2
    auto_increment_offset=2
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    replicate-do-db=test

2. 创建复制账号

（1）库1（主库）：

进入MySQL命令行：mysql>

创建复制用户：

    CREATE USER 'repluser'@'%' IDENTIFIED BY 'Abc-1234';
    GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';
    flush privileges;
    SHOW MASTER STATUS\G;

（2）库2（从库）：

进入MySQL命令行：mysql>

创建复制用户：

    CREATE USER 'repluser'@'%' IDENTIFIED BY 'Abc-1234';
    GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';
    flush privileges;
    SHOW MASTER STATUS\G;

3. 配置从库

（1）库1（主库）：

无需额外配置。

（2）库2（从库）：

进入MySQL命令行：mysql>

配置从库：

    CHANGE MASTER TO
      MASTER_HOST='192.168.200.10',
      MASTER_USER='repluser',
      MASTER_PASSWORD='Abc-1234',
      MASTER_LOG_FILE='master-bin.000001',
      MASTER_LOG_POS=154;
    START SLAVE;
    SHOW SLAVE STATUS\G;

4. 配置主库

（1）库2（从库）：

无需额外配置。

（2）库1（主库）：

进入MySQL命令行：mysql>

配置主库：

    CHANGE MASTER TO
      MASTER_HOST='192.168.200.20',
      MASTER_USER='repluser',
      MASTER_PASSWORD='Abc-1234',
      MASTER_LOG_FILE='master-bin.000002',
      MASTER_LOG_POS=307;
    START SLAVE;
    SHOW SLAVE STATUS\G;

四、LDAP主从配置

1. 安装OpenLDAP服务器

（1）安装OpenLDAP软件包：

yum install openldap openldap-server openldap-clients -y

（2）配置LDAP主库：

编辑OpenLDAP配置文件：vim /etc/openldap/ldap.conf

添加以下内容：

    OLC_NONSTRICT_DIGEST_MD5 on

编辑slapd配置文件：vim /etc/openldap/slapd.conf

添加以下内容：

    dn: cn=config
    changetype: modify
    replace: olcSyncUseStartTLS
    olcSyncUseStartTLS: off

2. 配置LDAP从库

（1）复制LDAP数据文件：

将主库上的LDAP数据文件复制到从库上。

（2）编辑slapd从库配置文件：

编辑slapd从库配置文件：vim /etc/openldap/slapd-deb.conf

添加以下内容：

    moduleload syncprov.lazlo42.so
    modulepath /usr/lib64/openldap
    sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync-prov-nosync on sync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosync, nosysnc, nosysnc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosyc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc, nosysnc```（3）重启OpenLDAP服务：systemctl restart slapdsystemctl restart slapd
五、FAQs问答Q1：如何更改MySQL的主从复制模式为基于GTID的复制模式？
A1：要更改MySQL的主从复制模式为基于GTID的复制模式，需要在MySQL的配置文件中启用GTID模式，并确保两个MySQL实例都启用了该模式，具体步骤如下：
1、编辑MySQL配置文件（如/etc/my.cnf）：在[mysqld]部分添加以下内容：gtid_mode=ON enforce_gtid_consistency=true
2、重启MySQL服务以使配置生效：systemctl restart mysqld，通过以上步骤，即可启用MySQL的GTID复制模式。Q2：在配置MySQL主从复制时，如果从库无法连接到主库怎么办？
A2：如果从库无法连接到主库，可以按照以下步骤进行排查和解决：检查网络连接是否正常，确保主从库之间的网络畅通，检查主库的防火墙设置，确保3306端口已开放，检查主库的MySQL配置文件，确认绑定地址是否正确，例如是否绑定到0.0.0.0，检查从库的MySQL配置文件中的master_host、master_user和master_password是否正确，查看从库的错误日志，找出具体的连接错误信息，并根据错误信息进行相应的解决，通过以上步骤，通常可以解决从库无法连接到主库的问题。

一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

蓝桉云顶

Good Luck To You!

如何在MySQL中配置LDAP主从复制？2024-12-15 12:42:13

一、数据库概念

二、部署基础操作环境

三、MySQL互为主从配置

四、LDAP主从配置