MySQL主从配置
1、准备工作
ps: centos7 install docker
# 准备两个mysql服务
# 使用docker拉取mysql:5.7镜像
$ docker pull mysql:5.7
2、配置master配置文件
# 将mysql1配置为mysql的master,在/opt/mysql/conf1添加一个mysqld.cnf配置文件
$ vi /opt/mysql/conf1/mysqld.cnf
# 配置文件内容如下:
[mysqld]
bind_address = 0.0.0.0
server-id = 1 #服务id
log_bin = /var/log/mysql/mysql-bin.log #开启binlog 日志
binlog_do_db = db #同步的数据库名称
binlog_ignore_db = mysql #忽略同步的数据库
3、配置slave配置文件
# 将mysql2配置为mysql的slave,在/opt/mysql/conf2添加一个mysqld.cnf配置文件
$ vi /opt/mysql/conf2/mysqld.cnf
# 配置文件内容如下
[mysqld]
bind_address = 0.0.0.0
server-id = 2 #服务id,要和master不同
replicate_do_db = db #同步的数据库名称
replicate_ignore_db = mysql #忽略同步的数据库
4、启动两个mysql容器
# -p 3316:3306 //两个容器分别绑定3316和3326端口
# -v /opt/mysql/conf1:/etc/mysql/conf.d // 将主机/opt/mysql/conf1目录挂载到容器的/etc/mysql/conf.d
$ docker run -p 3316:3306 --name mysql1 -v /opt/mysql/conf1:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
$ docker run -p 3326:3306 --name mysql2 -v /opt/mysql/conf2:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
5、在master上创建一个同步权限的用户用来同步数据
# 进入docker容器mysql1
$ docker exec -it mysql1 mysql -uroot -p123456
# 进入mysql后添加一个backup用户,启用远程连接
> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'%' IDENTIFIED BY '123456';
# 刷新MySQL的系统权限,让backup用户生效
> FLUSH PRIVILEGES;
# 查看master状态
> show master status;
#+------------------+----------+--------------+------------------+-------------------+
#| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
#+------------------+----------+--------------+------------------+-------------------+
#| mysql-bin.000001 | 607 | db | mysql | |
#+------------------+----------+--------------+------------------+-------------------+
6、配置slave,关联master
# 进入docker容器mysql2
$ docker exec -it mysql2 mysql -uroot -p123456
# 关联master
# master_host:master服务器的ip
# master_port:master服务器的端口
# master_log_file:上面master中show master status;显示的File的值
# master_log_pos:上面master中show master status;显示的Position的值
> change master to
master_host='172.31.199.85',
master_port=3316,
master_user='backup',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=607;
# 查看slave的状态,如果没有就使用:start slave;启动
> show slave status\G;
# 如果Slave_IO_Running和Slave_SQL_Running都是yes说明连接成功
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.199.85
Master_User: backup
Master_Port: 3326
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: ea0331f12db5-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 42
Master_UUID: c338daea-2b70-11ea-a73d-0242ac110003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
7、测试mysql主从
# 在master和slave创建db数据库
> create database db;
# 在master和slave创建user表
> create table user(id int(11) primary key auto_increment not null,name varchar(32) not null) default charset=utf8;
# 在master添加记录
> insert into user values (1, 'admin');
# 在slave查看记录
> select * from user;
参考:MySQL主从复制面试之作用和原理
参考:mysql主从配置详细教程最新