MYSQL 主从配置

下载数据库

docker pull mysql:5.6

安装数据库master 和 slave

# master 33023
docker run -p 33023:3306 --name master.mysql5.7 \
-v /hb/docker/mydata/mysql_master_33023/conf:/etc/mysql \
-v /hb/docker/mydata/mysql_master_33023/data:/var/lib/mysql \
-v /hb/docker/mydata/mysql_master_33023/log:/var/log/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7



# slave 32069
docker run -p 32069:3306 --name slave.mysql5.7 \
-v /hb/docker/mydata/mysql_slave_32069/conf:/etc/mysql \
-v /hb/docker/mydata/mysql_slave_32069/data:/var/lib/mysql \
-v /hb/docker/mydata/mysql_slave_32069/log:/var/log/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7

添加同步账号和权限

#  在 master 数据添加同步账号和权限
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';   
Query OK, 0 rows affected (0.00 sec)

查询masert 状态

# 查询id  show variables like 'server_id';
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)

# 查询masert 状态 ，记录下 File 和 Position 字段的值，后面会用到。show master status;
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000003 |      617 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

进入 slave 从库

# 获取docker 容器ip
[root@ecs-36c9 mydata]# docker inspect --format='{{.NetworkSettings.IPAddress}}' master.mysql5.7
172.17.0.2


# 进入容器数据库执行一下命令
change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000003', master_log_pos=617, master_connect_retry=30

命令解释

master_host: Master 的IP地址
master_user: 在 Master 中授权的用于数据同步的用户
master_password: 同步数据的用户的密码
master_port: Master 的数据库的端口号
master_log_file: 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos: 从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry: 当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒。

查看从库状态

# 查询id  show variables like 'server_id';

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.01 sec)

# 查询状态 show slave status \G;
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: edu-mysql-bin.000003
          Read_Master_Log_Pos: 617
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: edu-mysql-bin.000003
             Slave_IO_Running: Yes   / NO 未启动
            Slave_SQL_Running: Yes   / NO 未启动
              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: 617
              Relay_Log_Space: 535
              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: 1
                  Master_UUID: 625b4ddf-e613-11eb-b017-0242ac110002
             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)

启动主从同步

# 启动
start slave;
# 停止
stop slave;

验证配置

# 主库添加新数据库
mysql> create database test;

# 从库查询验证
mysql> show databases;

主库 my.cnf

# 主库 my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
server_id=1
binlog-ignore-db=mysql
log-bin=edu-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=10
slave_skip_errors=1062

lower_case_table_names=1
init_connect='SET collation_connection=utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

从库

# 从库
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-time_zone='+8:00' 

server_id=2
binlog-ignore-db=mysql
log-bin=edu-mysql-slave1-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=17
slave_skip_errors=1062
relay_log=edu-mysql-relay-bin
log_slave_updates=1
read_only=1

lower_case_table_names=1
init_connect='SET collation_connection=utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION