mysql+docker 主从配置示例
系统环境
Ubuntu 20.04 LTS
Docker version 20.10.18
docker安装mysql
ubuntu下运行
sudo docker search mysql
拉取mysql镜像
sudo docker pull mysql
运行2台mysql(mysql1,mysql2)
sudo docker run -itd --name=mysql1 -e MYSQL_ROOT_PASSWORD=123456 mysql
sudo docker run -itd --name=mysql2 -e MYSQL_ROOT_PASSWORD=12345678 mysql
查看mysql1,mysql2的IP
sudo docker inspect mysql1
sudo docker inspect mysql2
mysql1IP:172.17.0.2 mysql2 IP:172.17.0.4 (后面需要用到!!!)
mysql1 为主服务器，mysql2为从服务器
主从服务器配置
从mysql1复制配置文件my.cnf到ubuntu
sudo docker cp mysql1:/etc/my.cnf /home/ubuntu/my.cnf
主服务器mysql1 配置
sudo vi /home/ubuntu/my.cnf
修改后如下
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql,information_schema,performance_schema
#binlog-do-db=school-info
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
把修改好的主服务器配置文件my.cnf复制回mysql1
sudo docker cp /home/ubuntu/my.cnf mysql1:/etc/my.cnf
从服务器 mysql2 配置
sudo vi /home/ubuntu/my.cnf
修改后如下
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-ignore-db=mysql,information_schema,performance_schema
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
把修改好的从服务器配置文件my.cnf复制回mysql2
sudo docker cp /home/ubuntu/my.cnf mysql2:/etc/my.cnf
重启mysql1 mysql2
sudo docker restart mysql1
sudo docker restart mysql2
主服务器mysql1配置
进入mysql1容器
sudo docker exec -it mysql1 /bin/bash
登录mysql
mysql -uroot -p123456
修改mysql1密码为mysql_native_password模式
alter user 'root'@'%' identified with mysql_native_password by '123456';
给root授权
grant replication slave,replication client on *.* to 'root'@'%';
刷新权限
flush privileges;
查看master状态
show master status
如下(file,position在slave服务器要用到!!!)
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从服务器mysql2配置
进入mysql2容器
sudo docker exec -it mysql2 /bin/bash
登录mysql
mysql -uroot -p12345678
修改mysql1密码为mysql_native_password模式 停止slave
stop slave;
从服务器连接主服务器(用到前面的mysql1IP,root账号密码，file,position的值)
change master to master_host='172.17.0.2',master_user='root',master_password='123456',master_log_file='binlog.000002',master_log_pos=157;
启动slave
start slave
查看slave状态
show slave status \G
如下(file,position在slave服务器要用到!!!)
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.17.0.2
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 1013
Relay_Log_File: eea723c11c11-relay-bin.000003
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 1013
Relay_Log_Space: 720
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: b5d4b549-3a15-11ed-9215-0242ac110002
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)
查看是否正常(如下是正常项)
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_SQL_Error:
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
测试(master添加数据库数据，slave查看是否同步)
master(mysql1)下执行
创建数据库
create database school_info;
创建表
CREATE TABLE `school_info`.`school` (
`id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(200) NULL COMMENT '学校名称',
`create_at` bigint(11) NULL,
`update_at` bigint(11) NULL,
`status` tinyint(4) NULL DEFAULT 0 COMMENT '状态',
PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT = '学校信息表';
表添加记录
insert into school values(1,"school1",1625568499,1625568499,1);
insert into school values(2,"school2",1625568499,1625568499,1);
slave(mysql2)下执行
查看数据库列表
show databases;
选择数据库
use school_info;
查看表记录
select * from school;
如果看见有school_info数据库,school表，记录跟master一致，则说明配置成功
