目录
四、添加复制master数据的用户reader,供从服务器使用
一、创建主服务器所需目录
mkdir -p /usr/local/mysqlData/master/cnf
mkdir -p /usr/local/mysqlData/master/data
二、主服务器配置文件
vim /usr/local/mysqlData/master/cnf/mysql.cnf
[mysqld]
default-character-set=utf8mb4
## 设置server_id,注意要唯一
server-id=1
## 开启binlog
log-bin=mysql-bin
## binlog缓存
binlog_cache_size=1M
## binlog格式(mixed、statement、row,默认格式是statement)
binlog_format=mixed
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
default-time-zone = '+08:00'
auto_increment_increment=2
auto_increment_offset=1
log-slave-updates
sync_binlog=1
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
三、创建并启动mysql主服务
docker run -itd --net=host -p 3306:3306 --name master -v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0.25
四、添加复制master数据的用户reader,供从服务器使用
docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8f401cb66b1b mysql:8.0.25 "docker-entrypoint..." 5 seconds ago Up 4 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp master
docker exec -it mysql env LANG=C.UTF-8 /bin/sh
mysql -u root -p123456
mysql> GRANT REPLICATION SLAVE ON *.* to 'reader'@'%' identified by 'reader';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
五、创建从服务器所需目录,编辑配置文件
mkdir /usr/local/mysqlData/slave/cnf -p
mkdir /usr/local/mysqlData/slave/cnf -p
vim /usr/local/mysqlData/slave/cnf/mysql.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id=2
## 开启binlog,以备Slave作为其它Slave的Master时使用
log-bin=mysql-bin
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
## 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
## binlog缓存
binlog_cache_size=1M
## binlog格式(mixed、statement、row,默认格式是statement)
binlog_format=mixed
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
[mysqld]
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
default-time-zone = '+08:00'
auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
六、创建并运行mysql从服务器
docker run -itd --net=host -p 3307:3306 --name slaver -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0.25
七、在从服务器上配置连接主服务器的信息
首先主服务器上查看master_log_file、master_log_pos两个参数,然后切换到从服务器上进行主服务器的连接信息的设置
主服务上执行:
mysql -u root -p123456
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 591 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
docker查看主服务器容器的ip地址
docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' master
172.17.0.2
从服务器上执行:
docker exec -it slaver /bin/bash
mysql -u root -p123456
mysql> change master to master_host='172.17.0.2',master_user='reader',master_password='reader',master_log_file='mysql-bin.000003',master_log_pos=591;
从服务器启动I/O 线程和SQL线程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: reader
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 591
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running: Yes,Slave_SQL_Running: Yes 即表示启动成功