MariaDB/Mysql的主从复制部署(Docker)

旧城等待， 2022-03-11 08:20 346阅读 0赞

## MariaDB的主从复制部署 ##

*  **拉取mariadb镜像**
    
    > docker pull mariadb
 *  **创建好数据卷文件**
    
    > mkdir /home/lxj/hedisql/data/master
    > 
    > mkdir /home/lxj/hedisql/data/slave1
 *  **运行主容器**
    
    > docker run --name master -e MYSQL\_ROOT\_PASSWORD=123456 -v /home/lxj/heidisql/data/master:/var/lib/mysql -p 23306:3306 --restart always -d mariadb
 *  **进入主容器执行(添加备份的账号)**
    
    > docker exec -it master /bin/bash
    > 
    > mysql -uroot -p123456
    > 
    > MariaDB \[(none)\]> grant replication slave on *.* to ‘backup’@’%’ identified by ‘123456’;
    > 
    > MariaDB \[(none)\]> flush privileges;
 *  **从容器中拷贝一份mariadb配置文件**
    
    > sudo docker cp master:/etc/mysql/my.cnf
    > 
    > /home/lxj/hedisql/data/master\_my.cnf
 *  **修改master\_my.cnf，在 \[mysqld\] 节点下添加**
    
    > \[mysqld\]  
    > server-id=1
    > 
    > log\_bin=master-bin
    > 
    > binlog-ignore-db=mysql
    > 
    > binlog-ignore-db=information\_schema
    > 
    > binlog-ignore-db=performance\_schema
    > 
    > binlog-ignore-db=test
    > 
    > innodb\_flush\_log\_at\_trx\_commit=1
    > 
    > binlog\_format=mixed
 *  **复制master\_my.cnf并覆盖master中的my.cnf**
    
    > docker cp /home/lxj/heidisql/data/master\_my.cnf master:/etc/mysql/my.cnf
 *  **重启master容器**
    
    > docker restart master
 *  **进入master容器执行**
    
    > docker exec -it master /bin/bash
    > 
    > mysql -uroot -p123456
    > 
    > MariaDB \[(none)\]> SHOW MASTER status;
    
    **得到master的log文件及其位置**
    
    > 如File 为 master-bin.000002
    > 
    >  Position为632
 *  **运行从容器**
    
    > docker run --name slave1 -e MYSQL\_ROOT\_PASSWORD=123456 -v /home/lxj/heidisql/data/slave1:/var/lib/mysql -p 33306:3306 --restart always -d mariadb
 *  **拷贝一份MySQL配置文件**
    
    > sudo docker cp slave1:/etc/mysql/my.cnf /home/lxj/hedisql/data/slave1\_my.cnf
 *  **修改slave1\_my.cnf，在 \[mysqld\] 节点下添加**
    
    > \[mysqld\]
    > 
    > server-id=2
    > 
    > relay-log-index=slave-relay-bin.index
    > 
    > relay-log=slave-relay-bin
    > 
    > relay\_log\_recovery=1
 *  **复制slave1\_my.cnf并覆盖slave1中的my.cnf**
    
    > docker cp /home/lxj/heidisql/data/slave1\_my.cnf slave1:/etc/mysql/my.cnf
 *  **重启slave1容器**
    
    > docker restart slave1
 *  **进入从容器执行**
    
    > docker exec -it slave1 /bin/bash
    > 
    > mysql -uroot -p123456
    > 
    > MariaDB \[(none)\]> stop SLAVE;
    > 
    > MariaDB \[(none)\]> change master to
    > 
    >  MASTER\_HOST=‘10.18.139.141’,
    > 
    >  MASTER\_USER=‘root’,
    > 
    >  MASTER\_PASSWORD=‘123456’,
    > 
    >  MASTER\_PORT=23306,
    > 
    >  MASTER\_LOG\_FILE=‘master-bin.000002’,
    > 
    >  MASTER\_LOG\_POS=632;
    > 
    > MariaDB \[(none)\]> start SLAVE;
    > 
    > MariaDB \[(none)\]> show slave status;
    
    > 备注：  
    > MASTER\_HOST 连接主容器ip地址(宿主机的ip地址)  
    > MASTER\_PORT 主容器的端口  
    > MASTER\_USER 同步账号的用户名  
    > MASTER\_PASSWORD 同步账号的密码  
    > MASTER\_LOG\_FILE 主容器的日志地址(之前查到)  
    > MASTER\_LOG\_POS 主容器的日志位置(之前查到)
 *  **查看运行结果，当结果中的**
    
    > Slave\_IO\_State ：Waiting for master to send event
    > 
    > Slave\_IO\_Running ：Yes
    > 
    > Slave\_SQL\_Running ：Yes
    
    **时表示配置成功。**
 *  **添加更多从容器和以上方法一样。**
 *  **进行测试**
    
    > 在主容器运行 MariaDB \[(none)\]> create database edslxj;  
    > 在从容器能够查询到主从复制。
 *  **部署成功**

## 部署中的问题及解决方法 ##

*  **Mysql主从库不同步[1236][]错误：could not find first log file name in binary log index file错误是主从的一个日志问题**
    
    > [解决方法][Link 1]

[1236]: https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&tn=ubuntuu_cb&wd=mysql%E4%B8%BB%E4%BB%8E%E9%83%A8%E7%BD%B21236&oq=%25E4%25B8%25BB%25E4%25BB%258E%25E5%25A4%258D%25E5%2588%25B6%25E9%2583%25A8%25E7%25BD%25B2%25E6%2588%2590%25E5%258A%259F%25E4%25BD%2586%25E6%2598%25AF&rsv_pq=f5f960e90000d2bc&rsv_t=be20qexi%2BEIbmWeIGlIAXVQJVaNZsDY%2F9I1bUCVNmgPh0MiodXBzEyGZm9evOXnTsw&rqlang=cn&rsv_enter=1&inputT=16046&rsv_sug3=86&rsv_sug1=83&rsv_sug7=101&rsv_sug2=0&rsv_sug4=16609
[Link 1]: https://blog.csdn.net/edwzhang/article/details/46982661