MySQL主从复制读写分离及奇怪的问题

摘要：当时是使用阿里云镜像，安装了两台，结果配置的时候出现了重复问题。以上操作过程，主从服务器配置完成。监控据说可以编写一脚本，用监控的两个及进程，如发现只有一个或零个，就表明主从有问题了。

联系邮箱：hi@jackcool.net

一直都没有写blog的习惯，以前总觉得自己的脑子就是最好的记忆容器，现在觉得我好像有个假脑子。

当时是使用阿里云镜像，安装了两台ECS，结果配置MySQL的时候出现了UUID重复问题。
先从配置主从开始吧，值得记录。

文中很多部分引用了网络上的零碎资料！

一般应用对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，有一个思路就是说采用数据库集群的方案，
基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中 的从数据库。 当然，主服务器也可以提供查询服务。使用读写分离最大的作用无非是环境服务器压力

1.MySQL复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务，因此不能光看性能，也就是说1主1从也是可以的。

2.多机器(集群)的处理能力

3.对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟。

4.主从只负责各自的写和读，极大程度的缓解X锁和S锁争用

5.从库可配置myisam引擎，提升查询性能以及节约系统开销

6.从库同步主库的数据和主库直接写还是有区别的，通过主库发送来的binlog恢复数据，但是，最重要区别在于主库向从库发送binlog是异步的，从库恢复数据也是异步的

7.读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。 对于写和读比例相近的应用，应该部署双主相互复制

8.可以在从库启动是增加一些参数来提高其读的性能，例如--skip-innodb、--skip-bdb、--low-priority-updates以及--delay-key-write=ALL。当然这些设置也是需要根据具体业务需求来定得，不一定能用上

9.分摊读取。假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1 分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不 承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白 了就是拿机器和带宽换性能。MySQL官方文档中有相关演算公式：官方文档 见6.9FAQ之“MySQL复制能够何时和多大程度提高系统性能”

MySQL主(称master)从(称slave)复制的原理：

master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件，binary log events)

slave将master的binary log events拷贝到它的中继日志(relay log)

slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

主DB server和从DB server数据库的版本一致

主DB server和从DB server数据库数据一致，这里就会可以把主的备份在从上还原，也可以直接将主的数据目录拷贝到从的相应数据目录

主DB server开启二进制日志,主DB server和从DB server的server_id及auto.cnf中的UUID都必须唯一

1、主从服务器分别作以下操作：

  1.1、版本一致
  1.2、初始化表，并在后台启动mysql
  1.3、修改root的密码

2、修改主服务器Master的MySQL配置文件

$ vi /etc/my.cnf（默认）
[mysqld]
log-bin=mysql-bin     //[必须]启用二进制日志
server-id=93      //[必须]服务器唯一ID，默认是1，一般取IP最后一段
# 指定同步的数据库， 如果 不指定则同步全部数据库
binlog-do-db= testdb

3、修改从服务器slave

$ vi /etc/my.cnf
[mysqld]
server-id=211      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4、重启两台服务器的MySQL

$ /etc/init.d/mysql restart
或
$ service mysqld restart

5、在主服务器上建立帐户并授权slave:

$ /usr/local/mysql/bin/mysql -uroot -p 或直接 mysql -u root -p
mysql>GRANT REPLICATION SLAVE ON *.* to "slave"@"%" identified by "123456"; 
mysql>flush privileges;
//一般不用root帐号，%表示所有客户端都可能连，只要帐号、密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。

6、登录主服务器的MySQL，查询Master的状态

   mysql>show master statusG;

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

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

7、配置从服务器Slave：

mysql>change master to master_host="192.168.145.222",master_user="slave",master_password="123456",
master_log_file="mysql-bin.000004",master_log_pos=308; 

//注意不要断开，308数字前后无单引号。
Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器(Slave)复制功能状态：

  mysql> show slave statusG

   *************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.2.222  //主服务器地址
              Master_User: slave   //授权帐户名，尽量避免使用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状态，否则都是错误的状态(如：其中一个NO均属错误)。

以上操作过程，主从服务器配置完成。

10、监控：

据说可以编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了。

配置mysql主从时，由于是阿里云镜像系统盘拷贝的MySQL目录，导致主从MySQL UUID相同， Slave_IO无法启动，报错信息如下：

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决办法:

mysql>show variables like "%log_error%";//查看配置的日志地址，查看日志

修改MySQL data 目录下auto.cnf 文件中uuid的值，使两台MySQL不同即可，

$ find / -name auto.cnf 或 find / -name "auto.cnf"

修改后重启MySQL服务。