Mysql主从模式

本案例 使用docker建立主从数据库

安装

这里我使用的是自己的服务器unraid里docker管理工具有mysql模版 我直接使用模版创建了

使用命令

# 拉取镜像并运行
docker run -d --name='mysql-master' --net='bridge' -e TZ="Asia/Shanghai" -e HOST_OS="Unraid" -e 'MYSQL_ROOT_PASSWORD'='1q2w3e1122!' -e 'MYSQL_DATABASE'='m_test' -e 'MYSQL_USER'='m_test' -e 'MYSQL_PASSWORD'='m_test' -p '3307:3306/tcp' -v '/mnt/user/appdata/mysql-master':'/var/lib/mysql':'rw' 'mysql'

修改配置

为了以后更容易的修改配置我把配置文件映射到了虚拟机上

# 查看运行中的容器
docker ps

# 进入容器
docker exec -it e0f16400a8af /bin/bash
# 复制原来的配置到本地文件目录
cp -rf mysql/ /var/lib/mysql/conf/
# 退出容器
exit

重新运行容器

编辑修改docker容器

docker命令

docker run -d --name='mysql-master' --net='bridge' -e TZ="Asia/Shanghai" -e HOST_OS="Unraid" -e 'MYSQL_ROOT_PASSWORD'='1q2w3e1122!' -e 'MYSQL_DATABASE'='m_test' -e 'MYSQL_USER'='m_test' -e 'MYSQL_PASSWORD'='m_test' -p '3307:3306/tcp' -v '/mnt/user/appdata/mysql-master':'/var/lib/mysql':'rw' -v '/mnt/user/appdata/mysql-master/conf/mysql/':'/etc/mysql':'rw' 'mysql'

修改配置

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id

master节点设置

# 进入节点 docker ps 查看id
docker exec -it xxxxxx /bin/bash
# 进入mysql
mysql -uroot -p1q2w3e1122!

# 设置用户号权限
GRANT REPLICATION SLAVE ON *.* to 'yzy'@'%' IDENTIFIED BY 'woaini';
# 报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'woaini'' at line 1

# 修改为3句命令
# 创建账户
CREATE USER 'yzy'@'%' IDENTIFIED BY 'woaini';

# 赋予权限
GRANT REPLICATION SLAVE ON *.* TO  'yzy'@'%';

# 刷新权限
flush privileges;

查看日志位置

SHOW MASTER STATUS;

记住以下2个
mysql-bin.000001 
854

slave节点设置

slave命令

docker run -d --name='mysql-slave' --net='bridge' -e TZ="Asia/Shanghai" -e HOST_OS="Unraid" -e 'MYSQL_ROOT_PASSWORD'='1q2w3e1122!' -e 'MYSQL_DATABASE'='m_test' -e 'MYSQL_USER'='m_test' -e 'MYSQL_PASSWORD'='m_test' -p '3308:3306/tcp' -v '/mnt/user/appdata/mysql-slave':'/var/lib/mysql':'rw' -v '/mnt/user/appdata/mysql-slave/conf/mysql/':'/etc/mysql':'rw' 'mysql'

mysqld设置

server-id=2 #设置server-id

# 设置哪些同步
# 不同步哪些数据库  
binlog-ignore-db = mysql  
binlog-ignore-db = sys  
binlog-ignore-db = information_schema  
binlog-ignore-db = performance_schema  
  
# 只同步哪些数据库，除此之外，其他不同步  
binlog-do-db = m_test

CHANGE MASTER TO MASTER_HOST='10.10.10.228', MASTER_USER='m_test', MASTER_PASSWORD='m_test', MASTER_LOG_FILE='mysql-bin.000001',MASTER_PORT=3307, MASTER_LOG_POS=2348;

启动从节点

start slave;

未完待续….