Mysql 8.0.22 主从复制

1、docker-compose编排

version: '3.8'

services:

  mysql-master:

    image: mysql:8.0.22

    container_name: mysql-master

    restart: always

    command:

      --default-authentication-plugin=mysql_native_password

      --max_connections=1000

      --character-set-server=utf8mb4

      --collation-server=utf8mb4_general_ci

    environment:

      TZ: Asia/Shanghai

      MYSQL_ROOT_PASSWORD: 123456

    volumes:

      - ./mysql/master/data:/var/lib/mysql

      - ./mysql/master/cnf/my.cnf:/etc/my.cnf

      #- /etc/localtime:/etc/localtime:ro

    ports:

      - "3306:3306"

  mysql-slave:

    image: mysql:8.0.22

    container_name: mysql-slave

    restart: always

    command:

      --default-authentication-plugin=mysql_native_password

      --max_connections=1000

      --character-set-server=utf8mb4

      --collation-server=utf8mb4_general_ci

    environment:

      TZ: Asia/Shanghai

      MYSQL_ROOT_PASSWORD: 123456

    volumes:

      - ./mysql/slave/data:/var/lib/mysql

      - ./mysql/slave/cnf/my.cnf:/etc/my.cnf

      #- /etc/localtime:/etc/localtime:ro

    ports:

      - "3307:3306"

    links:

      - mysql-master

2、mysql-master查看(基于log日志，不推荐，每次docker重启日志会改变名称)

1、进入mysql container

docker exec  -it mysql-master bash  

2、登录mysql,并查看master 状态（注意file 与 position 值)

 mysql -uroot -p

 mysql> show master status

    -> ;

+---------------+----------+--------------+------------------+-------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000002 |      156 |              |                  |                   |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql>

3、mysql-slave 设置(基于log日志，不推荐，每次docker重启日志会改变名称)

docker exec -it mysql-slave bash

mysql -uroot -p

mysql> change master to master_host='mysql-master',master_user='root',master_password='123456',master_log_file='binlog.000002',master_log_pos=156;

Query OK, 0 rows affected, 2 warnings (0.44 sec)

mysql> start slave; (遇到错误 stop掉  再reset slave;

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show slave status\G

4、基于GTID(双主备份网络必须联通或者同一机房)

1、master my.cnf 测试不起效

[mysqld]

# [必须]服务器唯一ID

server-id=1

#开启 GTID

gtid-mode=on

enforce-gtid-consistency=true

# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）

binlog-ignore-db=mysql

# 主从复制的格式（mixed,statement,row，默认格式是statement。建议是设置为row，主从复制时数据更加能够统一）

binlog-format=row

# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。

# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave-skip-errors=1062

手动开启GTID:（无效)

mysql> set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

Query OK, 0 rows affected (0.09 sec)

mysql> show global variables like 'gtid_mode';

+---------------+----------------+

| Variable_name | Value          |

+---------------+----------------+

| gtid_mode     | OFF_PERMISSIVE |

+---------------+----------------+

1 row in set (0.00 sec)

2、docker-compose.yml

version: '3.8'

services:

  mysql-master:

    image: mysql:8.0.22

    container_name: mysql-master

    restart: always

    command:

      --default-authentication-plugin=mysql_native_password

      --max_connections=1000

      --character-set-server=utf8mb4

      --collation-server=utf8mb4_general_ci

      --server_id=1

      --gtid_mode=ON

      --enforce-gtid-consistency=ON

    environment:

      TZ: Asia/Shanghai

      MYSQL_ROOT_PASSWORD: 123456

    volumes:

      - ./mysql/master/data:/var/lib/mysql

      - ./mysql/master/cnf/conf.d:/etc/mysql/conf.d

      #- /etc/localtime:/etc/localtime:ro

    ports:

      - "3306:3306"

  mysql-slave:

    image: mysql:8.0.22

    container_name: mysql-slave

    restart: always

    command:

      --default-authentication-plugin=mysql_native_password

      --max_connections=1000

      --character-set-server=utf8mb4

      --collation-server=utf8mb4_general_ci

      --server_id=2

      --gtid_mode=ON

      --enforce-gtid-consistency=ON

    environment:

      TZ: Asia/Shanghai

      MYSQL_ROOT_PASSWORD: 123456

    volumes:

      - ./mysql/slave/data:/var/lib/mysql

      - ./mysql/slave/cnf/conf.d:/etc/mysql/conf.d

      #- /etc/localtime:/etc/localtime:ro

    ports:

      - "3307:3306"

    depends_on:

      - mysql-master

从库设置：

mysql> stop slave;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> reset slave;

Query OK, 0 rows affected, 1 warning (0.33 sec)

mysql> change master to MASTER_HOST='192.168.31.88',MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 2 warnings (0.34 sec)

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show slave status\G;

change master to MASTER_HOST='192.168.31.88',MASTER_PORT=3307,MASTER_USER='root',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1,GET_MASTER_PUBLIC_KEY=1

���mysql GTID.rar

<