Mysql 5.6.30主从数据库配置

参考了
mysql配置主从数据库

首先主从两边确保版本一致，然后做下面操作

1. 主master:
   修改配置文件
   #vi /etc/my.cnf
   不知道为何，我的文件在/usr/my.cnf，查了一下，my.cnf文件是顺序找的，一个地方找不到，再顺序第二个地方，第三个地方，所以把my.cnf还是挪到了/etc下以免时间长了，忘记导致出错

[root@host-192-168-15-249 mysql]# mysql --help|grep 'my.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

进入my.cnf进行配置

[mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=1

#不同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

#要同步的数据库
binlog-do-db = cluster

2. 从slave:
   修改配置文件
   #vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin   //[不必须]启用二进制日志
server-id=2

#不同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

#要同步的数据库
binlog-do-db = cluster

3. 登录主master的mysql授权
   mysql -uroot -p

mysql>GRANT REPLICATION SLAVE ON *.* to 'username'@'%' identified by 'password'; //授权同步的账号
mysql>flush privileges;

4. 查看授权情况
   记住头两列信息，后面要用

   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

5. 登录到slave的mysql内
   执行语句

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.15.249',master_user='repl',master_password='slave',master_log_file='mysql-bin.000004', master_log_pos=308;
Query OK, 0 rows affected, 2 warnings (0.19 sec)

mysql> start slave;

注意一定要start slave;否则不起作用

6. 检查从服务器复制功能状态

mysql> show slave status\G

   *************************** 1. row ***************************

              Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服务器地址
              Master_User: mysync   //授权帐户名，尽量避免使用root
              Master_Port: 3306    //数据库端口，部分版本没有此行
              Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos
              Relay_Log_File: ddte-relay-bin.000003
              Relay_Log_Pos: 251
              Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态

7. 主从服务器测试：

到主服务器上修改数据，到从服务器上看数据是否同步过来了。

8. 完成：
   编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧

---

完成后update数据发现部分同步不过来！！！？？？新增删除能做，update报错
show slave status\G查看发现Slave_SQL_Running: No，下面还有具体的Error，发现是两边数据不一致，所以重新把主库数据，导出，放入从库，再把从库清理一下，做同步。

参见：Mysql主从重新同步
我没有做文档那么多步骤，基本就是如下语句：

1.从库停止主从复制

stop slave;

2.确保主库没人操作，导出再导入从库

3.重置主库日志

reset master；

4.show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
注意file变成1了

5.重置从库日志，清理slave 同步信息

reset slave;

6.开启主从复制

start slave;

7.查看主从复制状态

show slave status;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

同步文件也变为：mysql-bin.000001

我以为好了，删除一张表，又不同步，发现我主库做的忽略大小写，从库没有设置忽略大小写，造成的，所以去从库里设置lower_case_table_names=1

---

9. 设置从数据库只读

mysql> set global read_only=1;    #1是只读，0是读写
mysql> show global variables like "%read_only%";

设置 read_only=1 状态有两个需要注意的地方：
1)read_only=1只读模式，不会影响slave同步复制的功能，所以在MySQL slave库中设定了read_only=1后，通过 show slave status\G ，命令查看salve状态，可以看到salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；

2)read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作；在MySQL中设置read_only=1后，普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时，都会报出数据库处于只读模式不能发生数据变化的错误，但具有super权限的用户，例如在本地或远程通过root用户登录到数据库，还是可以进行数据变化的DML操作；

设定了read_only=1后，所有的select查询操作都是可以正常进行的。

最好还是写在配置文件里生效：

vi /etc/my.cnf
[mysqld]
read-only=1

重启

10. 经验
    要想顺利，从软件版本，配置，到数据一定要一模一样。