MySQL主从复制-读写分离

数据库 主从同步

一、为什么读写分离

当配置好主从同步之后，所有对数据库内容的更新都必须在主库上进行。那么为什么所有的更新都要在主库上进行呢？这是由于主从复制是单向的，只有在主库上更新，才能确保主从之间的数据一致性。综上所述，为了保证数据的一致性，我们需要进行读写分离：数据的写操作只能在主库上进行，而相应的，读操作只能在从库上进行，这样既维护了数据的一致性，又降低了单台服务器的工作负载。

我认为，主从复制与读写分离相辅相成，不可分割。

那么如何确保只能在主库上进行写操作，同时读操作只能在从库上进行呢？
首先想到的就是对数据库用户进行授权控制，但是有一个问题需要注意，那就是默认情况下主从之间的系统库也会进行同步复制，主库上有的用户账户也会同步到从库上去，这就使得从库上的数据可以通过具有写权限的用户进行更新成为了潜在的可能。
当然，解决这个问题的手段有不少：

• 例如在从库上对同步过来的用户作相应的权限回收，然后将处理后的同一套用用户账号提供给开发人员，但是一旦开发人员混淆主库与从库，是相当危险的；
• 在主库上创建相应的用户账号，一个用来写，一个只能够读，然后将这两个账号同时交给开发人员，进行对数据库得读写操作时分别使用对应的账号即可。但是这种方式仍旧存在潜在的风险（即用写账号来连接从库）；
• 主库的授权表不同步到从库（不同步MySQL库），但主从双方都创建一个具有相同用户名及密码的账号，这样，开发人员使用的是一套用户名及密码，但实质上在主从库上完全是两个不同的用户账号。

生产环境中最为适用的是上面的第三种方法，即采用忽略授权表的同步方式，然后在主从库上对同名同密码的用户分别进行授权。所以来说，不同步MySQL库，才能够保证在主从库上分别进行授权。
但是第三种方法也有缺陷，那就在在从库接替出故障的主库成为新的从库时，会出现用户权限问题。百度的做法是，专门找一台从库作为预备，以便将来可以顺利转换为主库，而该服务器是不面向开发人员的。

二、通过忽略授权表防止写从库

忽略同步mysql库和information_schema库的方法：

#编辑主库的配置文件：
[root@MySQL-B ~]# vim /etc/my.cnf
[mysqld]
#在模块中添加如下配置参数，忽略不同步的库。
binlog-ignore-db=mysql
binlog-ignore-db=information-schema
#对用的，也可以指定需要同步的库。
binlog-do-db=xxx
#修改配置文件后需要重启服务：
[root@MySQL-B ~]# /etc/init.d/mysqld restart

binlog-do-db=需要复制的数据库，如果复制多个数据库，重复设置这个选项即可。
binlog-ignore-db=不需要复制的数据库，如果忽略多个数据库，重复设置这个选项即可。

三、通过read-only参数防止写从库

除了上面所说的权限控制外，还可以在从库的启动指令中添加选项（使用--read-only选项），或者在配置文件my.cnf中添加read-only参数来确保从库只读（两种方法的效果是相同的）。当然，在从库上仅授权SELECT权限和设置只读参数二者双管齐下效果更理想，这也是生产环境中的常用方案。
只读参数read-only可以让从库只允许来自从库自身的线程，或者具有SUPER权限的用户进行写操作，这样就可以确保从库不接受来自普通用户的更新数据的操作。

1、在配置文件中添加只读参数

[root@MySQL-E ~]# vim /etc/my.cnf
[mysqld]
read-only    
#添加上面的只读参数，就可以防止写从库。
#注意：该参数不会限制具有Super及其以上权限（Super或ALL）的用户。
#当然，不要忘记重启服务以便配置生效。

当使用普通用户进行数据插入等操作时，会出现报错：

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement