MySql主从数据库同步读写分离架构设计完整版

一、设计要求

1）主机采用双主机实时互备模式（双Master），从机接其中一台主机完成单向同步复制，新增从机也接这台主机。

两台主机服务器：主机A，主机B。

N台从机服务器：从机01，从机02...从机N。

2）应用读数据从本地从机读取即localhost，从机只分配读权限以及触发器执行权限和视图执行权限。

3）应用写数据向远程主机写入，分配基本数据库操作权限。

二、权限分配

1）为从机本机读操作设置权限，加上触发器和视图的执行权限。下面的授权语句需要在从机执行。

grant select,trigger on appDB.* to root@localhost identified by 'appDbPwd';

2）为从机远程写操作设置权限，只分配基本数据库操作权限。下面的授权语句需要在主机执行。

grant select,insert,update,delete on appDB.* to root@[从机IP地址] identified by 'appDbPwd';

因为grant语句也会随着其他数据被同步到从机，所以这一操作会让所有从机都拥有当前数据库的基本操作权限。
这个问题可以通过防火墙设置把从机之间的数据库服务端口屏蔽掉（屏蔽外部机器访问从机的3306端口即可）

3）建立全局超级账号，用来给系统管理员进行维护工作时候使用。下面的授权语句需要在主机执行。

grant all on *.* to dba@localhost identified by 'superAdminPwd' with grant option;

这个超级账号的密码尽量设置的复杂些，并且要定期更换。
因为grant语句也会随着其他数据被同步到从机，这一操作会将所有数据库服务器的dba密码修改掉。

4）为从机同步复制分配权限。下面的授权语句需要在主机执行。

grant replication slave, replication client on *.* to syncUser@[从机IP地址] identified by 'syncPwd';

因为grant语句也会随着其他数据被同步到从机，所以这一操作会让所有从机都拥有当前数据库的同步复制操作权限。
这个问题可以通过防火墙设置把从机之间的数据库服务端口屏蔽掉（屏蔽外部机器访问从机的3306端口即可）

三、建立双Master相互热备的方法步骤

完成主机B复制主机A的数据并完成双向互为热备的目的

1）主机A：stop slave; change master to master_host = '1.2.3.4'; start slave;

2）主机A：锁表

3）主机A：导出position信息（show master status;）

4）主机A：导出完整数据

5）主机A：释放表

6）主机B：stop slave; change master to master_host = '1.2.3.4'; start slave;

7）主机B：删库

8）主机B：建库

9）主机B：导入完整数据

10）主机B：stop slave;

11）主机B：根据position信息修改同步参数（change master to ...）

12）主机B：start slave;

13）主机B：导出position信息（show master status;）

14）主机A：stop slave;

15）主机A：拿着主机B的position信息重新设置自己的position信息

16）主机A：start slave;

注意：这里面将master_host修改为1.2.3.4是为了防止操作时候将不应该同步的指令也同步到另一台服务器了，务必照着流程操作以确保万无一失！


四、新增从机需要进行的操作步骤

1）建立好标准MySql数据库服务环境

2）从备份机取最新完整备份到新从机上

3）将备份数据导入到新数据库当中

4）使用备份数据自带的position进行同步复制设置

5）启动同步复制进程


五、日常维护备份工作

数据库异地备份是不可或缺的，一个没有设置数据库异地备份的系统无异于单刀赴会，一旦出现意外则万事休矣~~

异地备份数据库可以使用MySql的同步复制功能，在远程服务器（不同机房甚至不同城市）上建立与主机的同步复制设置。
然后定期对这台备份服务器的数据库进行定期Dump备份即可。为了方便以后进行数据库准确的恢复建议Dump数据的时候将当时的position信息也导出来。

1）stop slave;

2）show master status;

3）mysqldump 导出完整数据库

4）start slave;

建议做15天的完整备份：即每天自动完整备份一次，保留最近15份备份数据

同时对主要数据表进行单独备份：即对主要数据表单独进行备份，也保留最近15份备份数据。主要数据表一般只用户信息表、订单表等。


六：数据分析工作

很多时候我们需要做一些数据的分析和统计工作，这个工作很多时候为了省事就直接在线上服务器操作了，这样其实是很危险的。
相信大家都经理过在线上服务器进行数据库操作时候一个不小心把整个表清空了，或者把整个表的部分字段更新成一样的了。

所以，我在这里郑重地倡导一下，大家一定要有这个本地从机数据库服务器，所有的分析统计操作都在这里完成！一定！一定！一定！