MySQLDocker 主从复制搭建

MySQLDocker 主从复制搭建

MySQLDocker 的搭建

docker search mysql

docker pull mysql/mysql-server:8.0.26

docker images

docker run -p 3307:3306 --name mysql8.0 -e MYSQL_ROOT_PASSWORD=root -d mysql/mysql-server:8.0.26

使用客户端无法连接的问题：

mysql -uroot -p

use mysql;

select host,user,plugin from user;

CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT USAGE ON *.* TO 'root'@'%';
grant all privileges on *.* to 'root'@'%';
UPDATE user SET plugin='mysql_native_password' WHERE User='root' and host='%';
flush privileges;

• 问题1 ：Mysql8的授权写法发生变更

  

  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
  -- 语法发生修改如下：
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
  

MySQL 主从同步搭建

1. 创建本地挂载的目录结构

 ├── master
 │   ├── conf
 │   ├── data
 │   └── logs
 └── slaver
     ├── conf
     ├── data
     └── logs

# master节点
mkdir -p ~/mydata/mysql/master/data
mkdir -p ~/mydata/mysql/master/conf
mkdir -p ~/mydata/mysql/master/logs
# slaver节点
mkdir -p ~/mydata/mysql/master/data
mkdir -p ~/mydata/mysql/master/conf
mkdir -p ~/mydata/mysql/master/logs

2. 创建临时节点，获取配置文件

   docker run --rm --name mysqltemp -it -v ~/mydata/mysql/master/conf/:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=root mysql/mysql-server:8.0.26
   
   docker cp mysqltemp:/etc/my.cnf ~/mydata/
   

3. 配置文件

   [mysqld]
   server-id=47
   #开启binlog
   log_bin=master-bin
   log_bin-index=master-bin.index
   skip-name-resolve
   # 设置连接端口
   port=3306
   # 设置mysql的安装目录
   basedir=/usr/local/mysql
   # 设置mysql数据库的数据的存放目录
   datadir=/usr/local/mysql/mysql-files
   # 允许最大连接数
   max_connections=200
   # 允许连接失败的次数。
   max_connect_errors=10
   # 服务端使用的字符集默认为UTF8
   character-set-server=utf8
   # 创建新表时将使用的默认存储引擎
   default-storage-engine=INNODB
   # 默认使用“mysql_native_password”插件认证
   #mysql_native_password
   default_authentication_plugin=mysql_native_password
   

4. 启动主

   docker run --name mysql-m -p 3336:3306 -v ~/mydata/mysql/master/conf/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=root -d mysql/mysql-server:8.0.26
   

   容器内容部执行MySQL命令:show master status;

   mysql> show master status;
   +-------------------+----------+--------------+------------------+-------------------+
   | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +-------------------+----------+--------------+------------------+-------------------+
   | master-bin.000003 |      156 |              |                  |                   |
   +-------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)
   

5. 配置从

   server-id=48
   #打开MySQL中继日志
   relay-log-index=slave-relay-bin.index
   relay-log=slave-relay-bin
   #打开从服务二进制日志
   log-bin=mysql-bin
   #使得更新的数据写进二进制日志中
   log-slave-updates=1
   
   #默认使用“mysql_native_password”插件认证
   #mysql_native_password
   default_authentication_plugin=mysql_native_password
   

6. 启动从

   docker run --name mysql-s -p 3337:3306 -v ~/mydata/mysql/slaver/conf/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=root -d mysql/mysql-server:8.0.26
   

7. 从连接主（容器内容的执行Mysql）

   登录从服务
   mysql -u root -p;
   
   #设置同步主节点：
   CHANGE MASTER TO
   MASTER_HOST='172.17.0.3',
   MASTER_PORT=3306,
   MASTER_USER='root',
   MASTER_PASSWORD='root',
   MASTER_LOG_FILE='master-bin.000003',
   MASTER_LOG_POS=156,
   GET_MASTER_PUBLIC_KEY=1;
   
   #开启slave
   start slave;
   #查看主从同步状态
   show slave status;
   或者用 show slave status G; 这样查看比较简洁
   

   注意

   1. MASTER_HOST 是 mysql-master 在docker的IP地址；使用docker inspect -- format 查看
   2. MASTER_PORT也是容器内部的端口；通常都是 3306

   如下可以确认成功运行：

   

8. 主从搭建测试