使用 docker 完成mysql 主从搭建

安装mysql

docker pull mysql:latest

启动mysql

docker run --name mysql-master -d -e MYSQL_ROOT_PASSWORD=123456  -p 8900:3306 mysql:latest

docker run --name mysql-slave -d -e MYSQL_ROOT_PASSWORD=123456  -p 8901:3306 mysql:latest

如果需要使用navicat连接数据库需要执行下面命令

docker exec -it mysql-master bash // 进入到容器内

mysql -u root -p // 执行该命令进入数据库

// 执行下面语句
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

1. 配置主节点
   以此执行下面命令

docker exec -it mysql-master bash // 进入到容器内
cd /etc/mysql	
vim my.cnf	// 编辑mysql的配置信息，并将下面信息添加到my.cnf文件中

server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-do-db=testdb
binlog-format=STATEMENT

配置完上述内容之后需要重启mysql

docker restart 容器ID

2. 配置从节点

docker exec -it mysql-slave bash // 进入到容器内
cd /etc/mysql	
vim my.cnf	// 编辑mysql的配置信息，并将下面信息添加到my.cnf文件中

server-id=21
relay-log=mysql-relay

配置完上述内容之后同样重启mysql
3. 配置主数据库信息
以此执行下面命令

docker exec -it mysql-master bash // 进入到容器内
mysql -u root -p
输入密码
---下面是在mysql里面执行，数据mysql的语句
create user 'slave'@'%' identified by '123456';
flush privileges;
grant all privileges on *.* to 'slave'@'%' with grant option;
flush privileges;
show master status; // 显示如下信息

+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 |     3203 | testdb       | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+

show master status;语句所显示的File和Position是我们重点关注的
File对应从库中的master_log_file
Position对应从库中的master_log_pos

4. 配置从数据库信息

docker exec -it mysql-master bash // 进入到容器内
mysql -u root -p
输入密码
---下面是在mysql里面执行，数据mysql的语句
change master to master_host='主节点的IP地址', 
master_port=3306,master_user='slave',master_password='123456',
master_log_file='mysql-bin.000001', master_log_pos=3203;

stop slave;
start slave;
show slave status\G;

如果show slave status\G;获得信息中
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
则表示我们本次的主从复制已经完成

异常报错

1、如果show slave status\G；出现下面报错

Slave_IO_Running: Connecting
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'slave@xxxxx:3306' - retry-time: 60 retries: 17 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

表示我们需要修改主库slave的plugin

ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

2、如果show slave status\G；出现下面报错

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1396
Last_SQL_Error: Error 'Operation ALTER USER failed for 'slave'@'%'' on query. Default database: ''. Query: 'ALTER USER 'slave'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9''

表示我们的从库没有slave用户，我们需要在从库新建该用户并给他权限，然后重启

create user 'slave'@'%' identified by '123456';
grant all privileges on *.* to 'slave'@'%' with grant option;
stop slave;
start slave;