mysql replication listener_set mysql master

本文主要介绍了如何设置mysql数据库的主从复制, 其中mysql服务使用docker container

master side

1. 先dump原有数据库, 安全操作

可以在本机上执行, 指定远程服务器的host即可1

2

3

4mysqldump -h [ip_address] -u [uname] -p db_name > db_backup.sql

例如:

mysqldump -h172.26.131.128 -uroot -p Pomlint > Pomlint_db_backup.sql

2. 在远程master主机上再起一个mysql docker container

这里将配置文件直接挂载进容器中, 也可以启动容器后再进入容器修改配置1docker run --restart=always --name pomlint-mysql-master -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /srv/mysql/master-my.cnf:/etc/mysql/my.cnf -d mysql

3. 恢复数据到新起的docker

先在master机器上的docker容器中创建数据库1create database Pomlint;

然后恢复数据到容器中(可在本地操作, 指定ip 和 port即可)1

2

3

4mysql -P [port] -h [ip_address] -u [uname] -p db_name < db_backup.sql

例如:

mysql -P 3307 -h172.26.131.128 -uroot -p Pomlint < Pomlint_db_backup.sql

4. 进入master 容器中, 修改/etc/mysql/my.cnf 文件(或者提前写好, 由外部挂载进来)

my.cnf是mysql启动时加载的配置文件1

2

3

4echo '[mysqld]' /etc/mysql/my.cnf

echo 'server-id=1' /etc/mysql/my.cnf

echo 'binlog_do_db=Pomlint' /etc/mysql/my.cnf

echo 'log-bin=mysql-bin' /etc/mysql/my.cnf

server-id:On the master and each slave, you must use the –server-id option to establish a unique replication ID in the range from 1 to 232 − 1. “Unique”, means that each ID must be different from every other ID in use by any other replication master or slave. For example, server-id=3.

binlog_do_db:指定要复制的数据库

log-binEnable binary logging.

完成后退出容器, 重启容器1docker restart pomlint-mysql-master

slave side

1. 同样的, 先dump数据1mysqldump -h192.168.133.19 -uroot -p Pomlint > Pomlint_db_backup_19.sql

2. 在远程slave主机上再起一个mysql docker container

配置文件可以提前写好并挂载进去, 或者在之后启动后再进行修改1docker run --restart=always --name pomlint-mysql-slave -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /srv/mysql/slave-my.cnf:/etc/mysql/my.cnf -d mysql

3. 恢复数据到新起的docker

先在slave的docker中创建数据库1create database Pomlint;

可在本地操作, 指定ip 和 port即可1

2

3mysql -P [port] -h [ip_address] -u [uname] -p db_name < db_backup.sql

例如:

mysql -P 3307 -h192.168.133.19 -uroot -p Pomlint < Pomlint_db_backup_19.sql

4. 进入slave 容器中, 修改/etc/mysql/my.cnf 文件1docker exec -it pomlint-mysql-slave bash1

2

3

4echo '[mysqld]' /etc/mysql/my.cnf

echo 'server-id=2' >>/etc/mysql/my.cnf

echo 'binlog_do_db=Pomlint' >>/etc/mysql/my.cnf

echo 'read_only=on' >>/etc/mysql/my.cnf

完成后退出容器, 重启容器1docker restart pomlint-mysql-slave

配置复制操作

1. 进入master容器, 再进入mysql终端1docker exec -it pomlint-mysql-master1mysql -uroot -p

执行以下SQL语句1

2

3# 创建一个用户名为repl密码为123456的用户, %表示省略用户名的主机部分

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';

1

2

3

4# SELECT : Enable use of SELECT. Levels: Global, database, table, column.

# REPLICATION SLAVE: Enable replication slaves to read binary log events from the master. Level: Global.

# 给 repl账户SELECT和读取binary log events(用于复制)的权限

GRANT SELECT,REPLICATION SLAVE ON *.* TO 'repl'@'%';1

2FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

记住表格中的两个值 FILE 和 POSITIONFilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Setmysql-bin.000001603Pomlint--

2. 进入slave容器, 再进入mysql终端

执行以下SQL语句1CHANGE MASTER TO MASTER_HOST='172.26.131.128',MASTER_PORT=3307, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=603;

注意 MASTER_LOG_FILE 和 MASTER_LOG_POS 来自之前的记录值if you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE=’’ and MASTER_LOG_POS=4 are silently appended to it.

3. 进入master MYSQL终端 unlock table1UNLOCK TABLES;

4. 进入slave MYSQL终端, 开启slave1

2START SLAVE;

SHOW SLAVE STATUSG