Mysql的主从配置

mysql的主从配置

修改my.cnf文件

1. ​
   !includedir /etc/mysql/conf.d/
   server-id=201
   log-bin=mysql-bin
   binlog_format=row
   
   [mysqld]
   pid-file = /var/run/mysqld/mysqld.pid
   socket = /var/run/mysqld/mysqld.sock
   datadir = /var/lib/mysql
   secure-file-priv= NULL
   default_authentication_plugin=mysql_native_password
   
   # Custom config should go here
   !includedir /etc/mysql/conf.d/
   server-id=200
   log-bin=mysql-bin
   binlog_format=row
   
   ​

master中创建slave账号操作从服务

进入容器显示bash-4.4的解决方案

进入容器后输入以下两个命令

cp /etc/skel/.bashrc /root/

cp /etc/skel/.bash_profile /root/

退出容器后再次进入

1. bash-4.4# cp /etc/skel/.bashrc /root/
   bash-4.4# cp /etc/skel/.bash_profile /root/
   bash-4.4# exit
   exit
   [root@localhost 3306]# docker exec -it mysql_3306 bash
   [root@ed5404afb218 /]#

进入mysql_master容器

1. [root@localhost conf]# docker exec -it mysql_master bash

登录mysql

1. root@6c7b6078f4ec:/# mysql -u root -p
2. Enter password:

创建主从账号”slave”

1. mysql> create user 'slave'@'%' identified by '123';
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';
5. Query OK, 0 rows affected (0.00 sec)
7. mysql> use mysql
8. mysql> ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';
11. mysql> flush privileges;
12. Query OK, 0 rows affected (0.00 sec)

1. create user 'slave'@'%' identified by '123';
2. mysql> grant replication slave, replication client on *.* to 'slave'@'%';
3. alter user 'slave'@'%' identified with mysql_native_password by '123';
4. flush privileges;

由于mysql8.x的密码加密方式改变，如果之后主从出现问题，需要修改密码加密方式,如果之前在mysql_3306中设置过的则可以跳过此处。

进入mysql的主服务器:

1. [root@localhost ~]# docker exec -it mysql_3306 bash
3. mysql> use mysql
4. Reading table information for completion of table and column names
5. You can turn off this feature to get a quicker startup with -A
6. Database changed
7. mysql> ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';

获取master容器的状态

显示mysql_3306的主机状态
show master status;

1. mysql> show master status;
   +------------------+----------+--------------+------------------+-------------------+
   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+-------------------+
   | mysql-bin.000001 | 157 | | | |
   +------------------+----------+--------------+------------------+-------------------+
   1 row in set (0.00 sec)

获取mysql_master的ip地址

docker inspect 容器名称 |grep IPA

1. [root@localhost conf]# docker inspect mysql_master |grep IPA
2. "SecondaryIPAddresses": null,
3. "IPAddress": "172.17.0.2",
4. "IPAMConfig": null,
5. "IPAddress": "172.17.0.2",

mysql主服务器参数(非固定的)

序列	名称	值
1	日志文件:file	mysql-bin.000001
2	日志文件位置:Position	157
3	主服务器的ip	172.17.0.2

---

mysql从服务器

修改从配置文件

进入mysql配置主从

1. change master to master_host='172.17.0.2',master_user='slave',master_password='123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;

开启主从

查看状态

1. mysql> show slave status \G;

主从配置顺序

1. 创建mysql master(3306) 容器
2. 进入master(3306) docker exec -it mysql_3306 bash
3. 显示master状态: show master status; —-记住 mysql-bin , position
4. 退出容器
5. 创建mysql_3310容器,注意挂载点的文件
6. 进入slave(3310) docker exec -it mysql_3310 bash
7. mysql -uroot -p123
8. stop slave;
9. reset slave;
10. change master to master_host=’172.17.0.2’,master_user=’slave’,master_password=’123’,MASTER_LOG_FILE=’mysql-bin.000001’,MASTER_LOG_POS=157;
11. start slave;