mysql主从复制集群是一主多从结构，主数据库将数据操作的命令记录到binlog中，从数据库io线程同步主数据库的binlog日志存入从数据库的relay日志，从服务器的sql线程读取relay日志重新执行命令达到数据同步的效果。
一、需要的条件
1、master数据库开启binlog日志
2、master创建账号并授权replication slave 权限，使从服务器可以连接主服务器同步日志
3、slave数据库需要配置master数据库的相关配置
二、主从的优点
1、主数据库宕机可以切换到从数据库
2、数据备份，可以在从数据库备份数据，避免了在主数据库备份，锁表的风险。
3、实现读写分离，主数据库提供写入操作，从数据提供查询操作
三、缺点
1、无法实现故障自动切换，需要人为干预
2、需要同步binlog日志并且回放。数据同步存在延迟
四、docker搭建mysql主从复制集群
一、安装docker，docker-compose
二、本地创建两个目录，区分master和slave分别存放主从数据库的配置文件my.cnf
[root@ecs-213814 mysql]# ls
docker-compose.yml master slave
[root@ecs-213814 mysql]# pwd
/data/mysql
[root@ecs-213814 mysql]#
三、主数据库配置
[root@ecs-213814 mysql]# cat master/my.cnf
[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
server-id=1 #serverid 集群内唯一
log-bin=mysql-bin #开启log-bin日志
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL #新特性文件导入导出限制，NULL使无限制
从数据库配置
[root@ecs-213814 mysql]# cat slave/my.cnf
[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
server-id=2 #serverid 集群内唯一
log-bin=mysql-bin #可以不开启
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
四、docker-compose.yml文件
version: '3'
services:
master:
image: mysql
volumes:
- /data/mysql/master:/etc/mysql/ #将本地配置挂载到容器内
environment:
MYSQL_ROOT_PASSWORD: "123456" #root密码环境变量
networks: #master slave放入同一网络在容器内可通过名字互相访问
mysql_network:
aliases:
- master #master在网络中的名字
slave:
image: mysql
volumes:
- /data/mysql/slave:/etc/mysql/
environment:
MYSQL_ROOT_PASSWORD: "123456"
networks:
mysql_network:
aliases:
- slave
networks: #定义网络
mysql_network:
创建主从数据库容器
[root@ecs-213814 mysql]# docker-compose up -d
Creating network "mysql_mysql_network" with the default driver
Creating mysql_slave_1 ... done
Creating mysql_master_1 ... done
[root@ecs-213814 mysql]# docker-compose ps
Name Command State Ports
--------------------------------------------------------------------------
mysql_master_1 docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp
mysql_slave_1 docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp
五、在主数据创建同步账号
mysql8.0 通过以下命令创建账号和授权，传统方式会报错
登录主库容器内部执行命令
[root@ecs-213814 mysql]# docker exec -it mysql_master_1 /bin/bash
root@6d2d16bd8902:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> create user mysync identified by '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> grant replication slave on *.* to 'mysync'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges
-> ;
Query OK, 0 rows affected (0.00 sec)
查看主数据库状态，记录日志文件和位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 861 | | | |
+------------------+----------+--------------+------------------+-------------------+
七、配置从数据库
登录从库容器执行命令
[root@ecs-213814 mysql]# docker exec -it mysql_slave_1 /bin/bash
root@41ff8c4bfd6e:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> change master to
-> master_host="master", #主数据库地址
-> master_user="mysync",
-> master_password="123456",
-> master_log_file="mysql-bin.000003", #日志文件
-> master_log_pos=861; #位置
Query OK, 0 rows affected, 8 warnings (0.05 sec)
开启同步进程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看从数据库状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: master
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 861
Relay_Log_File: d7b21b7e9ac2-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000003
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: 861
Relay_Log_Space: 543
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: db38c12a-9f52-11ec-ba45-0242c0a8f002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
Slave_IO_Running，Slave_SQL_Running都为yes说明从数据库状态正常
八、测试
主库当前数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
从数据库当前数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
2、在主数据库创建test2数据库、创建t2表并且插入数据
mysql> create database test2;
Query OK, 1 row affected (0.01 sec)
mysql> use test2;
Database changed
mysql> create table t2(name char(2));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t2 value("a");
Query OK, 1 row affected (0.01 sec)
从库查询，数据库test2，表t2，都存在且内容已同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
| test2 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test2;
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 t2;
+------+
| name |
+------+
| a |
+------+
1 row in set (0.01 sec)
扫一扫
1255
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
