Mysql主从复制（docker）

目录：

1. Mysql主从原理
2. Mysql主从部署过程
3. 连接到数据库
4. 测试主从同步

Mysql主从原理

MYSQL 主从复制集群在中小企业、大型企业中被广泛使用，MYSQL 主从复制的目的是实现数据库冗余备份，将 Master 数据库数据定时同步至 Slave 库中，一旦 Master 数据库宕机，可以将 WEB 应用数据库配置快速切换至 Slave 数据库，确保 WEB 应用较高的可用

mysql主从配置的流程

1. master会将变动记录到二进制日志（binlog）里面
2. master有一个I/O线程将二进制日志发送到slave
3. slave有一个I/O线程把master发送的二进制写入到relay日志里面
4. slave有一个SQL线程，按照relay日志处理slave的数据

回到目录

Mysql主从部署过程

1. 使用docker 部署两台mysql容器用作测试

[root@linux-node2 ~]# docker pull mysql:5.7

# 略过创建mysql用户
[root@linux-node2 ~]# mkdir -p /home/mysql/{conf.d,data}
# 创建从库映射路径
[root@linux-node2 ~]# mkdir -p /home/mysql2/{conf.d,data}
[root@linux-node2 ~]# tree /home/mysql/
/home/mysql/
├── conf.d
└── data

2 directories, 0 files


[root@linux-node2 ~]# cat /home/mysql/my.cnf 
[mysqld]
user=mysql
character-set-server=utf8
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
# 从库配置server-id=2
server-id=1
log-bin=mysql-bin

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

启动容器

# 启动主库容器（挂载外部目录，端口映射成33307，密码设置为123456）
[root@linux-node2 ~]# docker run -id -v /home/mysql/data/:/var/lib/mysql -v /home/mysql/conf.d/:/etc/mysql/conf.d -v /home/mysql/my.cnf:/etc/mysql/my.cnf -p 33307:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
[root@linux-node2 ~]# docker run -id -v /home/mysql2/data/:/var/lib/mysql -v /home/mysql2/conf.d/:/etc/mysql/conf.d -v /home/mysql2/my.cnf:/etc/mysql/my.cnf -p 33306:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

注：报错docker: Error response from daemon: driver failed programming external connectivity on endpoint
解决方法：

[root@linux-node2 ~]# systemctl restart docker

回到目录

连接到数据库

1. 查看容器ip

[root@linux-node2 mysql2]# docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
fbf576d3c585        mysql:5.7           "docker-entrypoint..."   9 minutes ago       Up 9 minutes        33060/tcp, 0.0.0.0:33306->3306/tcp   mysql-slave
871c387dd2f8        mysql:5.7           "docker-entrypoint..."   16 minutes ago      Up 16 minutes       33060/tcp, 0.0.0.0:33307->3306/tcp   mysql-master

# 因为测试机使用了docker桥接网络使用pipework分配指定ip
[root@linux-node2 mysql2]# pipework br0 fbf576d3c585 172.31.0.188/22@172.31.0.1
[root@linux-node2 mysql2]# pipework br0 871c387dd2f8 172.31.0.189/22@172.31.0.1

2. 连接主库

[root@linux-node2 ~]# mysql -h 172.31.0.189 -u root -p
# 创建同步用户
mysql> create user 'tongbu'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
# 授权
mysql> grant replication slave on *.* to 'tongbu'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 查看binlog
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注：需要重启mysql服务时，需要重启容器，此时容器ip会发生改变，需要使用pipework重新指定ip

3. 连接从库

[root@linux-node2 mysql]# mysql -h 172.31.0.188 -u root -p123456

# 配置
mysql> change master to master_host='172.31.0.189',master_port=3306,master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.21 sec)

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.0.189
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: fbf576d3c585-relay-bin.000002
                Relay_Log_Pos: 320
        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: 154
              Relay_Log_Space: 534
              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: ff1dd0f8-0084-11ea-8bbe-0242ac1f0001
             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)

回到目录

测试主从同步

1. 主库

mysql> create database test1 default charset utf8;
Query OK, 1 row affected (0.03 sec)

mysql> use test1
Database changed

mysql> create table jeff (id int not null, name varchar(24) not null, age tinyint);
Query OK, 0 rows affected (0.28 sec)

mysql> insert jeff (id,name,age) values (1, 'xxx', 20),(2, 'yyy', 18),(3,'jeff', 18);
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

2. 从库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from jeff;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xxx  |   20 |
|  2 | yyy  |   18 |
|  3 | jeff |   18 |
+----+------+------+
3 rows in set (0.01 sec)

回到目录