双主数据库搭建

诺依阁

发布于 2025-02-28 19:07:08

7900

代码可运行

文章被收录于专栏：诺依阁的日记簿诺依阁的日记簿

运行总次数：0

代码可运行

首先先准备好两个相同的数据库

docke-compose写法

version: '2.2.2'
services:
  nuoyis-lnmp-mariadb:
    container_name: nuoyis-lnmp-mariadb
    image: mariadb:11.6.2
    environment:
      TIME_ZONE: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: "123456"
    volumes:
      - /nuoyis-web/mariadb/init/init.sql:/docker-entrypoint-initdb.d/init.sql
      - /nuoyis-web/mariadb/server:/var/lib/mysql
      - /nuoyis-web/mariadb/import:/nuoyis-web/mariadb/import
      - /nuoyis-web/mariadb/config/my.cnf:/etc/mysql/my.cnf
    ports:
      - 3306:3306
    restart: always

编辑主数据库

编辑/etc/mysql/my.cnf，添加如下内容

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW
slave_skip_errors=1062

编辑从数据库

编辑/etc/mysql/my.cnf添加如下内容

[mysqld]
server-id=2
relay_log=relay-bin
log_bin=mysql-bin
slave_skip_errors=1062

创建主从同步用户

CREATE USER 'nuoyis_master'@'从库IP地址' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'nuoyis_master'@'从库IP地址';
FLUSH PRIVILEGES;

获取日志

SHOW MASTER STATUS;

保存File和Position的值

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 | 12345    |              |                  |
+------------------+----------+--------------+------------------+

CHANGE MASTER TO
    MASTER_HOST='主库IP地址',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='your_password',
    MASTER_LOG_FILE='mysql-bin.000025',
    MASTER_LOG_POS=12345;