docker mysql 主从安装

docker run -d -p 13306:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 mysql:latest


mysql -uroot -p

GRANT ALL ON *.* TO 'root'@'%';
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;




从机复制帐号密码设置



create user 'test'@'%' identified by '123456';
grant replication slave on *.* to 'test'@'%';
flush privileges;


从机
docker run -d -p 13306:3306 --name mysql_master -e MYSQL_ROOT_PASSWORD=123456 mysql:latest

docker run -d -p 23306:3306 --name mysql_salve -e MYSQL_ROOT_PASSWORD=123456 mysql:latest


然后查找mysql容器中使用到的my.cnf, 执行：
mysql --help | grep my.cnf ，

查找顺序：/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

docker run -d -p 33306:3306 -v /mnt/share/mysql/slave/my.cnf:/etc/mysql/mysql.d/mysql.cnf  --name mysql_salve2 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest

docker run -d -p 43306:3306 -v /mnt/share/mysql/master/my.cnf:/etc/mysql/mysql.d/mysql.cnf  --name mysql_master2 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest


docker run -d -p 53306:3306 -v /mnt/share/mysql/slave:/etc/mysql/mysql.d  --name mysql_salve3 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest

docker run -d -p 53306:3306 -v /home/test/mysql/slave/config/my.cnf:/etc/mysql/my.cnf  --name mysql_salve7 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest




apt-get update

apt-get install vim


show master status;


帐号登陆抛异常信息处理
GRANT ALL ON *.* TO 'test'@'%';
flush privileges;
ALTER USER 'test'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;



连接上数据库执行查关SQL
设置master相关信息
CHANGE MASTER TO
master_host='192.168.99.100',
master_user='test',
master_password='123456',
master_port=43306,
master_log_file='binlog.000002',
master_log_pos=3030;

启动同步
start slave;

查看master状态
show slave status;


show GLOBAL VARIABLES LIKE 'server%'


show GLOBAL VARIABLES LIKE 'binlog%'

#修改主库的配置
SET SESSION binlog_format = 'MIXED';



不重启修改方法（只对该登录会话有效,重新登录无效）
mysql> SET SESSION binlog_format = 'MIXED';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

重启MySQL服务修改方法:
编辑/etc/my.cnf文件，加入以下设置：
binlog_format=mixed 
重启服务后永久生效。


mysql> stop slave;

mysql> reset slave;

mysql> start slave;





show GLOBAL VARIABLES LIKE 'server_id'
set global server_id=2; #此处的数值和my.cnf里设置的一样就行



错误处理，my.cnf不能全有写权限，设置成644就可以了，chmod 644 /home/test/mysql/slave/config/my.cnf
Warning: World-writable config file '/etc/mysql/my.cnf' is ignored