欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

docker之MySQL主从复制

程序员文章站 2022-05-17 16:14:05
MySQL主从复制 主服务器 配置文件目录 mkdir /var/lib/mysql/master/conf.d 数据存储目录 mkdir var/lib/mysql/master/data 配置my.cnf文件 [mysqld] pid file = /var/run/mysqld/mysqld. ......

mysql主从复制

主服务器

配置文件目录
mkdir /var/lib/mysql/master/conf.d
数据存储目录
mkdir var/lib/mysql/master/data

配置my.cnf文件
# cd /var/lib/mysql/master/conf.d
# vim my.cnf

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= null
symbolic-links=0
log-bin = mysql-bin
server-id = 1

启动master容器:

docker run -it -p 3306:3306 --name master -v /var/lib/mysql/master/conf.d:/etc/mysql/conf.d -v /var/lib/mysql/master/data:/var/lib/mysql -e mysql_root_password=123456 -d mysql:latest

重启master容器

docker stop master slave
docker start master slave

进入master容器,设置mysql主从复制用户reader

docker exec -it master /bin/bash
root@1e8e984a9a30:/# mysql -uroot –p123456(前面设置的密码mysql_root_password=123456)

mysql旧版赋权方法:

grant all privileges on . to 'root'@'%' identified by '123456' with grant option;
grant replication slave on . to 'reader'@'%' identified by 'readerpwd';

注意:因为新版的的mysql版本已经将创建账户和赋予权限的方式分开了,所以如果是最先版本的mysql镜像按下面的方法创建用户、赋权。

创建账户:create user '用户名'@'访问主机' identified by '密码';
赋予权限:grant 权限列表 on 数据库 to '用户名'@'访问主机' ;(修改权限时在后面加with grant option)
mysql> create user 'reader'@'%' identified by 'readerpwd';
query ok, 0 rows affected (4.57 sec)
mysql> grant replication slave on . to 'reader'@'%';
query ok, 0 rows affected (1.13 sec)
flush privileges;

查看二进制日志是否开启:
mysql> show variables like 'log_bin';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| log_bin | on |
+---------------+-------+
1 row in set (0.99 sec)
mysql> show master status;
docker之MySQL主从复制

从服务器

配置文件目录
mkdir /var/lib/mysql/slave/conf.d
数据存储目录
mkdir var/lib/mysql/slave/data

# cd /var/lib/mysql/slave/conf.d
# vim my.cnf

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= null
symbolic-links=0
server-id = 2
relay_log = mysql-relay-bin #打开mysql日志,日志格式为二进制
read_only = 1 #设置只读权限
log_bin = mysql-bin #开启从服务器二进制日志
log_slave_updates = 1 #使得更新的数据写进二进制日志中

启动slave容器:

 docker run -it -p 3307:3306 --name slave -v /var/lib/mysql/slave/conf.d:/etc/mysql/conf.d -v /var/lib/mysql/slave/data:/var/lib/mysql    -e mysql_root_password=123456 -d mysql:latest

进入slave容器,启动从服务器复制线程,让slave连接master,并开始重做master二进制日志中的事件

docker exec -it slave /bin/bash
root@6df92c02669a:/# mysql -uroot -p123456
mysql>change master to master_host='192.168.1.24',master_port=3306,master_user='reader',master_password='readerpwd',master_log_file='mysql-bin.000001',master_log_pos=155;
mysql> show slave status\g;

docker之MySQL主从复制

测试:

主服务器:

create database slavetest;

从服务器:观察是否创建了slavetest数据库,如果看到了,就说明实验成功了

show databases;