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值