mysql主从复制

介绍

 注意：主库master一般只有一个，从库slave可以有多个

如何实现mysql主从复制？

1. 配置主库

GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by 'Root@123456'; 

mysql8版本以上，需要先创建用户再授权：

CREATE USER 'xiaoming'@'%' IDENTIFIED BY 'Root@123456';

GRANT REPLICATION SLAVE ON *.* TO 'xiaoming'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

注意：如果在mysql8中对xiaoming用户授权报错：ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation，表示当前用户（root）没有SYSTEM_USER权限，只需执行：grant system_user on *.* to 'root'; 在执行xiaoming用户的授权语句，即可操作成功。

 2. 配置从库

 

change master to master_host='主库ip',master_user='主库创建的用户名',master_password='主库创建的用户密码',master_log_file='binlog文件',master_log_pos=位置Position,get_master_public_key=1;

mysql> change master to 
master_host='192.168.153.131',
master_user='xiaoming',
master_password='root@123456',
master_log_file='binlog.000008',
master_log_pos=476,
get_master_public_key=1;

Query OK, 0 rows affected, 8 warnings (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)