mysql主从复制配置

服务器环境 centos6.5, 主 mysql 5.6; 从: docker 1.7 , mysql 5.6

基本配置

配置master

1.修改配置文件my.cnf

$ vim /etc/my.cnf

在 [mysqld] 的配置中增加:

# copying - master
log_bin = mysql_bin
server_id = 10

重启mysql服务, 使新配置生效
进入mysql, 并确认配置已经生效

mysql> show variables like '%log_bin%';

mysql> show variables like 'server_id';

2.添加同步时所用用户

mysql> use mysql;
mysql> GRANT ALL PRIVILEGES ON *.* TO repl'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> flush privileges;

为了安全, 此处可以简单配置, 而不是像上面一样配置所有权限和可连接ip;
如果不配置所有权限, 必须要开的权限有 replication slave, replication client;

配置slave

1.docker安装mysql

$ docker search mysql

确认有mysql后,进行拉取操作

$ docker pull mysql:5.6

确认拉取成功

$ docker images |grep mysql

启动, 因为服务器上已开启的mysql占用了3306所以此处使用3307

$ docker run -p 3307:3307 --name mysql -e MYSQL_ROOT_PASSWORD=RootPassword -d mysql:5.6

进入docker, 确认mysql可用

docker$ docker exec -it mysql bash
docker$ mysql -uroot -pRootPassword

修改docker中mysql的配置文件

$ vim /var/lib/docker/devicemapper/mnt/2d32144b80f431e98c15c30077f48d5f59063413aa64b4a65dc85d1b327d6e81/rootfs/etc/mysql/mysql.conf.d/mysqld.cnf

在 [mysqld] 的配置中增加:

# 便于出错时查看日志
log-error=/var/lib/mysql/docker_mysql_error.log 

# copy - slave
log_bin = mysql_bin
server_id = 2
relay_log = /var/lib/mysql/mysql-relay-bin
log_slave_updates = 1
read_only = 1

重启mysql服务

$ docker restart mysql

2.docker配置salve

进入docker中的mysql, 添加用户

mysql> use mysql;
mysql> GRANT ALL PRIVILEGES ON *.* TO repl'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
mysql> flush privileges;

设置要同步的master

mysql> change master to master_host='MASTERHOST',master_user='repl',master_password='PASSWORD',master_log_file='mysql_bin.000001',master_log_pos=0;

开始同步

mysql> start slave;

查看运行状态

master

查看同步状态

mysql> show master status\G;

查看正在运行的线程

mysql> show processlist;

slave

查看同步状态

mysql> show slave status\G;

查看正在运行的线程

mysql> show processlist;