基于mysql自带的master-slave 实现
环境
mysql5.7
master主机：172.16.XXX.1
slave主机：172.16.XXX.2
my.cny文件参考
https://blog.csdn.net/lee_yanyi/article/details/115132245
在master主机上
1、编辑配置文件
vi /etc/my.cnf
#server-id 要小于slave的id
server-id = 1
log_bin = master-bin
2、重启数据库
systemctl restart mysqld
3、登陆
mysql -uroot -proot
4、创建从库账号
grant all privileges on *.* to 'slave'@'172.16.XXX.2' identified by 'slave' with grant option;
flush privileges;
5、查看用户
use mysql;
select host, name from user;
6、查看master信息
show master status;
在slave主机上
1、编辑配置文件
vi /etc/my.cnf
server-id = 2
#log_bin = mysql-bin
2、重启数据库
systemctl restart mysqld
3、登陆
mysql -uroot -proot
4、停止slave功能
stop slave;
5、配置主库信息
需要修改的数据是依据Master信息修改的. ip是Master所在物理机IP. 用户名和密码是Master提供的Slave访问用户名和密码. 日志文件是在Master中查看的主库信息提供的.在Master中使用命令show master status查看日志文件名称.
change master to master_host='172.16.XXX.1', master_user='slave', master_password='slave', master_log_file='master-bin.000001';
6、启动slave
start slave;
7、查看slave配置
show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.XXX.1
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 427
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 591
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 427
Relay_Log_Space: 765
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 最后一次错误的IO请求编号
Last_IO_Error:
Last_SQL_Errno: 0 最后一次错误的执行SQL命令编号
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9ee988ac-8751-11e7-8a95-000c2953ac06
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 the slave I/O thread to update it
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
1 row in set (0.00 sec)
测试主从
1、主从：查看
show databases;
2、主：建立database、表、并插入数据
create database test charset utf8;
use test;
create table t_test(id int primary key , name varchar(32));
show tables;
desc t_test;
insert into t_test values(1,"lyy");
select * from t_test;
3、从库查询
use test;
show tables;
desc t_test;
select * from t_test;
4、主从模式下的逻辑图
mysql8版本
主：
mysql> CREATE USER 'repl'@'10.76.11.%' IDENTIFIED WITH mysql_native_password BY 'tj123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.76.11.%';
flush privileges;
SHOW MASTER STATUS;
备：
mysql> CHANGE MASTER TO MASTER_HOST='10.76.11.132', MASTER_USER='repl', MASTER_PASSWORD='tj123456',MASTER_LOG_FILE='binlog.000006',MASTER_LOG_POS=856;
start slave;
show slave status\G;
本文参考链接：https://liyanyi.blog.csdn.net/article/details/115177145
