mysql5.7主从配置

　　

主： 192.168.10.144
从：192.168.10.113

 

主：

mysql -u root -p

create user slave;
grant replication slave on *.* to 'slave'@'192.168.10.113' identified by 'mysql';

//注解： 192.168.10.113是从机的IP

修改/etc/mysql/mysql.conf.d/mysqld.cnf,如下信息：

#重启mysql

service mysql restart;

mysql -u root -p;

show master status;

 

 

从：

change master to master_host='192.168.10.144',master_user='slave',master_password='mysql',master_port=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=603;

//192.168.10.144 主mysql, mysql-bin.000001, 603, 对应的是主服务器上 show master status的结果。

show slave status\G

 

 

1. 如果开启远程访问：

在/etc/mysql/mysql.conf.d/mysqld.cnf文件中，注释掉如下：

mysql>use mysql;

mysql>update user set host='%' where user='root';

mysql>flush privileges; -- 刷新MySQL的系统权限相关表;

 

2. 去掉 slave所赋予的权限。

stop slave;

change master to master_host=' ';

start slave;

 

 

3. 从机出现如下错误时：

Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'server'@'%' WITH GRANT OPTION'

请在主机上使用
grant CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER on *.* to 'slave'@'192.168.0.9' identified by 'mysql';

4. 查看user的命令如下：

select host,user from mysql.user

 

参考：

 

http://rainbow702.iteye.com/blog/1558412

http://blog.yuansc.com/2016/02/17/mysql-5-7%E4%BA%92%E4%B8%BA%E4%B8%BB%E4%BB%8E%E5%90%8C%E6%AD%A5%E9%85%8D%E7%BD%AE/

http://www.cfei.net/archives/1066