docker容器部署mysql主从同步之log_pos方法

docker容器部署mysql主从同步之log_pos方法

环境

centos192.168.1.10 (master)192.168.1.11 (slave)

主（ master)

划分存储卷

lvcreate -L 200G -name lvdata vgdatamkfs.xfs /dev/mapper/vgdata-lvmysqlmkdir /mysqlvim /etc/fstab/dev/mapper/vgdata-lvmysql /mysql xfs defaults 0 0mount -a

初始化mysql主节点配置

$ docker pull local.harbor.io/library/mysql:5.7.22$ mkdir /mysql/data /mysql/log /mysql/config #创建日志、配置文件、数据持久存储目录$ docker run -tid --restart=always --net=host -e MYSQL_ROOT_PASSWORD=123456 --name mysql-test mysql:5.7.22 #启动测试容器将默认文件拷贝$ docker cp mysql-test:/var/log/mysql /mysql/log$ docker cp mysql-test:/var/lib/mysql /mysql/data$ docker cp mysql-test:/etc/mysql /mysql/config$ vim /mysql/config/mysql/my.cnf #设置配置文件参数[client]port = 3306socket = /var/lib/mysql/mysql.sock [mysqld]datadir=/var/lib/mysqlport = 3306socket = /var/lib/mysql/mysql.socksymbolic-links=0skip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Minnodb_buffer_pool_size = 2Glog-bin=mysql-binbinlog_format=mixedserver-id = 1 [mysqld_safe]log-error=/var/log/mysql/zbx3dbmaster.logpid-file=/var/lib/mysql/zbx3dbmaster.pid [mysqldump]quickmax_allowed_packet = 16M [mysql]no-auto-rehash [myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M [mysqlhotcopy]interactive-timeout

启动主节点容器

$ docker run -tid --restart=always --net=host -e MYSQL_ROOT_PASSWORD=123456 -v /mysql/data/mysql:/var/lib/mysql -v /mysql/log/mysql:/var/log/mysql -v /mysql/config/mysql:/etc/mysql --name mysql-master mysql:5.7.22

创建同步用户

docker exec -ti mysql-master mysql -uroot -p123456 -e "grant replication slave on *.* to 'db_sync'@'%' identified by 'abcdefg';"

查看主状态

docker exec -ti mysql-master mysql -uroot -p123456 -e "show master status\G;"*************************** 1. row *************************** File: mysql-bin.000001 Position: 154 Binlog_Do_DB: db Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

从 （slave）

划分存储卷

lvcreate -L 200G -name lvdata vgdatamkfs.xfs /dev/mapper/vgdata-lvmysqlmkdir /mysqlvim /etc/fstab/dev/mapper/vgdata-lvmysql /mysql xfs defaults 0 0mount -a

初始化从节点配置

```bash$ docker pull local.harbor.io/library/mysql:5.7.22$ mkdir /mysql/data /mysql/log /mysql/config #创建日志、配置文件、数据持久存储目录$ docker run -tid --restart=always --net=host -e MYSQL_ROOT_PASSWORD=123456 --name mysql-test mysql:5.7.22 #启动测试容器将默认文件拷贝$ docker cp mysql-test:/var/log/mysql /mysql/log$ docker cp mysql-test:/var/lib/mysql /mysql/data$ docker cp mysql-test:/etc/mysql /mysql/config$ vim /mysql/config/mysql/my.cnf #设置配置文件参数[client]port = 3306socket = /var/lib/mysql/mysql.sock [mysqld]datadir=/var/lib/mysqlport = 3306socket = /var/lib/mysql/mysql.socksymbolic-links=0skip-external-lockingkey_buffer_size = 16Mmax_allowed_packet = 1Mtable_open_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Minnodb_buffer_pool_size = 2Glog-bin=mysql-binbinlog_format=mixedserver-id = 2 [mysqld_safe]log-error=/var/log/mysql/zbx3dbslave.logpid-file=/var/lib/mysql/zbx3dbslave.pid [mysqldump]quickmax_allowed_packet = 16M [mysql]no-auto-rehash [myisamchk]key_buffer_size = 20Msort_buffer_size = 20Mread_buffer = 2Mwrite_buffer = 2M [mysqlhotcopy]interactive-timeout

$ docker run -tid --restart=always -p3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /mysql/data/mysql:/var/lib/mysql -v /mysql/log/mysql:/var/log/mysql -v /mysql/config/mysql:/etc/mysql --name mysql-slave mysql:5.7.22

与主同步

$ docker exec -ti mysql-slave mysql -uroot -p123456 -e "stop slave;change master to master_host='192.168.1.10',master_user='db_sync',master_password='abcdefg',master_log_file='mysql-bin.000001',master_log_pos=154;start slave;"$ docker exec -ti mysql-slave mysql -uroot -p123456 -e "show slave status\G;"

如果报错

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

原因：uuid值相等 解决方法：修改不一样即可 修改 ​​​/mysql/data/mysql/auto.cnf​​

[auto]server-uuid=a97c5316-4819-11eb-a312-000c29613c38

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。