Mysql 主从配置(待整理)

环境：
主：Windows 10 x64
Mysql 5.6
从：ubuntu 14.04.5 x86_64
Mysql 5.7

Ubuntu安装Mysql参考：

Ubuntu安装/卸载Mysql

遇到的错误：
ERROR 1045 (28000): Access denied for user’root’@’localhost’ (using password: YES)
错误原因是因为 plugin root 的字段是auth_socket 改掉就行
其解决方法如下：
#service mysql stop
#mysqld_safe –user=mysql –skip-grant-tables –skip-networking&
#mysql -u root mysql
mysql> update user set authentication_string =password(‘你的密码’),plugin=’msyql_native_password’ where user=’root’
mysql> FLUSH PRIVILEGES;
mysql> quit
#service mysql start

远程无法访问
修改配置文件
# By default we only accept connections from localhost
bind-address = 0.0.0.0

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000007 | 106 | robot | mysql |
+——————+———-+————–+——————+
1 row in set

mysql> show slave status;
1227 – Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

[c]
1. reload 是 administrative 级的权限，即 server administration；这类权限包括：
CREATE USER, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, SUPER
2. 这类权限的授权不是针对某个数据库的，因此须使用on *.* 来进行：
grant reload on *.* to ‘test’@’localhost’;
[/c]

主库：
mysql> grant replication slave, replication client on *.* to ‘robot’@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#主库配置：
server_id=21
binlog-do-db=mydb
log-bin=mysql-bin
binlog-ignore-db=mysql, information_schema, test
#从库配置：
log_bin=mysql-bin
server_id=34

使 slave 与 master 建立连接，从而同步：
mysql> change master to
-> master_host=’192.168.1.21′,
-> master_port=’3309′,
-> master_user=’mydb’,
-> master_password=’主库密码’,
-> master_log_file=’mysql-bin.000007′,
-> master_log_pos=106;