MySQL5.7数据库主从复制方案与配置实现.doc

MySQL5.7数据库主从复制方案与配置实现1 主从复制方案1.1 主从复制的好处MySQL的Replication（英文为复制）是一个多MySQL数据库做主从同步的方案，特点是异步复制，广泛用在各种对MySQL有更高性能、更高可靠性要求的场合。主从复制有以下四方面的好处：1、 数据备份(Data Backup)只是简单的对数据库进行备份，降低数据丢失的风险，有时也用于报表等对数据时效性要求不高的场合。2、 负载均衡(Load Balance)主要用在MySQL集群，解决单点故障或做故障切换；以降低单台服务器的负载和风险，如实现读写分离，可以使得服务器访问负荷比较均衡。3、 数据分发(Data Distribution)主要用于多数据中心或异地备份，实现数据分发与同步。4、 高可用和数据容错(High Availability and Failover)MySQL自带的健康监控和检测，根据配置的时间间隔，可以检测主库是否正常工作，一旦发现主库宕机或无法正常工作，就会选择到最好的一个备库上。1.2 主从复制类型MySQL主从复制有下面三种类型：1、 基于语句的复制（Statement-Based）： 在主服务器上执行的SQL语句，然后将语句写入二进制日志文件，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。2、 基于行的复制（Row-Based）：把改变的内容（数据本身）复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持。3、 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。1.3 主从复制常用拓扑结构1、一主一从这是最基础的复制结构，用来分担之前单台数据库服务器的压力，可以进行读写分离。2、一主多从一台 Slave 承受不住读请求压力时，可以添加多台，进行负载均衡，分散读压力。还可以对多台 Slave 进行分工，服务于不同的系统，例如一部分 Slave 负责网站前台的读请求，另一部分 Slave 负责后台统计系统的请求。因为不同系统的查询需求不同，对 Slave 分工后，可以创建不同的索引，使其更好的服务于目标系统。Mysql 的复制结构有很多种方式，复制的最大问题是数据延时，选择复制结构时需要根据自己的具体情况，并评估好目标结构的延时对系统的影响。2 主从复制实施配置实例2.1 配置环境准备这里以一主一从的拓扑结构为例来进行配置。一主多从拓扑结构的配置类似。【以下配置在MySQL5.5.x和MySQL5.7.x版本上配置测试通过，主从数据库版本必须一样】安装环境：1、 操作系统：Window7 x64（跟操作系统关系不大，只是命令和操作上有差别）；2、 数据库版本：MySQL5.5.16(Master主库)、MySQL5.5.29(Slave从库)；最好是同一大版本系列，小版本差别不大。3、 Master主库IP地址：0；4、 Slave从库IP地址：0；这里强调数据库版本，是因为不同MySQL版本的性能有差别；目前MySQL5.7.x版本系列的性能是最好的，实际系统使用时最好用此系列的数据库版本。【MySQL5.7号称性能是MySQL5.6的3倍，而MySQL5.6号称性能是MySQL5.5的2倍，你信吗？这个问题还是要辩证的理解，首先性能肯定是改进了，但为什么大部份人升级后也没有感觉？这就要从测试方法说起，大部份厂商发布新版本都会说性能提升了多少多少，因为这是最能吸引客户的数据。从官方的测试数据来看，首先是32个并发以上的简单查询，并且超过10万QPS才有区别，如果你的系统没有达到这个并发度那肯定没有体会的。另外是纯内存与CPU计算，如果你的系统瓶颈在磁盘或网络IO那也不会有明显的效率提升。当然，我认为MySQL5.6的ICP这种特性其实对于业务来说更有意义，说不定刚好你的慢SQL可以解决掉。】2.2 主从复制参数配置主从复制参数配置内容及步骤顺序如下：1、 在Master主库上创建复制用的账号，并授权：mysql GRANT REPLICATION SLAVE ON *.* TO copyer192.168% IDENTIFIED BY copyer password;mysql FLUSH PRIVILEGES;2、 对Master主库的my.ini或f配置文件做更改：#必须在mysqld节点上进行配置mysqld#设置服务器ID，必须保持唯一性server-id=10#设置需要写bin日志的数据库，多个数据库则要多行分别设置binlog-do-db=test#binlog-do-db=xxxx#设置不需要写bin日志的数据库，多个数据库则要多行分别设置binlog-ignore-db=mysqlbinlog-ignore-db=information_schema#注：binlog-do-db, binlog-ignore-db 为互斥关系，只需设置其中一项即可；#更要注意binlog-do-db库的SQL不能有操作binlog-ignore-db库的语句或动作，否则报错#日志模式 ROW|STATEMENT|MIXED，默认是语句模式，现设置为行模式binlog-format=row#二进制日志文件存放位置，可以设置路径；也可以不设路径，此时存放到datadir目录下。log-bin=E:/MySQL5/Data/test-binlog3、 对Slave从库的my.ini或f配置文件做更改：#必须在mysqld节点上进行配置mysqld#设置服务器ID，必须保持唯一性server-id=20#设置需要复制的数据库，多个数据库则要多行分别设置replicate-do-db=test#replicate-do-db=xxxx#设置不需要复制的数据库，多个数据库则要多行分别设置replicate-ignore-db=mysqlreplicate-ignore-db=information_schema4、 在完成Master主库和Slave从库的配置后，分别重启主从MySQL数据库，然后在Master主库里用root账号登录、执行以下命令，这些参数在Slave从库启动复制时需要用到。然后在Slave从库（记住是Slave从库）也用root账号登录、在MySQL命令行执行以下命令：mysql change master to - master_host=0, - master_user=copyer, - master_password=copyer password, - master_log_file=test-binlog.000005, - master_log_pos=107;Query OK, 0 rows affected (0.10 sec)注意：里面的host/user/password/log_file/log_pos要根据实际参数做更改。如果MySQL Server不是默认的3306端口，那么必须加上 master_port=xxxx 参数，设置具体端口数字。如果有经过防火墙的话，在防火墙上必须开放对应端口。然后在Slave从库里启动Slave复制，这样主从复制配置完成。mysql start slave;Query OK, 0 rows affected (0.00 sec)如果复制过程中有问题，可以先 mysql stop slave; 然后再 mysql start slave;2.3 主从复制状态监测在Master主库的MySQL命令行里，可以执行以下命令来监测主从复制执行情况：mysql show master status; #显示master复制状态mysql show master logs; #显示Master库bin log情况，跟 show binary logs;命令一样。mysql show processlist; #查看MySQL进程状态，包括复制进程，看state字段说明在Slave主库的MySQL命令行里，可以执行以下命令来监测主从复制执行情况：mysql show processlist; #查看MySQL进程状态，包括复制进程，看state字段说明mysql show slave status; #显示Slave复制状态，状态很多，具体要查看手册另外是直接查看主从复制的库和表里的数据，是否有及时正常复制过来。在Slave主库通过show slave status命令主要监测以下四个参数：1、 Slave_IO_State IO进程处理状态2、 Slave_IO_Running IO线程是否打开：YES/NO/NULL三种状态3、 Slav