mysql 主从配置

mysql 主从配置

master
1.cd /etc/my.cnf

#mysql服务ID，保证mysql集群环境中唯一
server-id=1

#mysql二进制日志文件存放路径和文件名称
log-bin=/var/lib/msyql/mysqlbin

#该节点读写状态，1-只读，0-读写
read-only=0

#忽略需要参与主从备份的库
binlog-ignore-db=mysql

service mysql restart

2.创建同步数据的账户，并且进行授权操作。在主库主机的用户,主要用于I/Othread

1.create user 'repl'@'192.168.136.130' IDENTIFIED BY '123456';
2.grant replication slave on *.* to 'repl'@'192.168.136.130';
或
grant replication slave on *.* to 'repl'@'192.168.136.130' identified by '123456';
flush privileges;
show master status;

//130ip地址为备库的ip地址，只允许这个ip地址可以登陆

从节点
1.server-id=2
2.log-bin=/var/lib/mysql/mysqlbin
3.service mysql restart;

stop slave;
change master to master_host='192.168.136.128', ,master_port=3306,master_user='repl',master_password='123456',master_log_file='mysqlbin.000001', master_log_pos=154;
start slave;
reset master;
show slave status;


执行show slave stauts
若报错：
1.Slave_IO_Running: Connecting
2.Last_IO_Error: error connecting to master 'repl@192.168.136.128:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
主库执行：grant ALL PRIVILEGES on *.* to 'repl'@'192.168.136.130';

若报错：
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
在主从库查看server-id或uuid是否一样
show variables like '%server%';
不一样，删除或修改auto.cnf中uuid值