MySQL主从复制读写分离

引言：在数据管理中，如果对MySQL数据库的读写操作都在同一个数据库服务器中的进行，往往是无法满足实际需要的。因为，这样做不仅容易造成数据的丢失和损坏，而且因为同时承担读写操作，大大增加了数据库服务器的负载，造成服务器运行效率的降低。

在数据管理中，对MySQL数据库的读写操作都在同一个数据库服务器中进行的话，往往无法满足实际需要。

解决的方法：通过主从复制的方式同步数据，通过读写分离提高数据库的并发负载能力。所谓主从复制指的是同时配置多台MySQL数据库服务器，让某台服务器作为MySQL主服务器，其余的作为从服务器，让应用程序服务器只对MySQL主服务器进行写操作，对从服务器只进行读操作。

MySQL主从复制原理

MySQL主服务器和从服务器之间通过数据同步的方式，使其存储的数据彼此保持一致。因此说，主从复制是读写分离的前提条件。MySQL主从复制有三种实现方式，包括基于语句的复制，基于行的复制，基于类型的复制。对于基于语句的复制方式来说，当在MySQL主服务器上执行了修改数据库的T-SQL语句后，再在MySQL从服务器上重复执行相应的语句即可，这是主从复制默认的操作方式。其优点是执行效率高，但缺点是数据复制不是很精准。对于基于行的复制方式来说，只需将MySQL主服务器中发生改动的数据复制到MySQL从服务器中，其复制的不是具体的命令而是真实数据。优点是精准度高，缺点是效率很低。

对于混合类型的复制来说，在默认情况下，优先采用基于语句的复制，来提高复制效率。当发现使用该方法无法提高复制的精准度时，就会采用基于行的复制方法来处理剩余的数据。MySQL主从复制的原理是当在执行更新、插入、删除等语句时，首先对MySQL主服务器进行数据变更操作，将其写入自己的二进制日志中。MySQL从服务器利用I/O线程读取MySQL主服务器中的日志。当发现日志中出现的数据变动信息，就会让MySQL从服务器执行复制动作。

MySQL读写分离原理分析

读写分离的原理是只在MySQL主服务器上写数据，只在MySQL从服务器上读取数据，实现MySQL读写分离。一般来说有两种方式：其一,是基于程序代码来实现，因为主从服务器的名称和IP均不相同，因此在设计前端程序时，可以将所有的Select语句全部定位到MySQL从服务器上，而执行Insert，Update等语句时，全部定位到MySQL主服务器上。其二,是配置中间代理层，即在MySQL服务器和客户端之间设置代理服务器，客户端将读写操作请求全部提交给代理服务器，由代理服务器来决定如何将写操作转交给MySQL主服务器，将读操作转交给MySQL从服务器。其优点是可以优化维护操作，缺点是需要购买和配置代理服务器，让网络结构变得复杂。这里就后一种方式，通过实例来说明如何实现MySQL的主从复制和读写分离功能。

配置实验网络环境

在代理服务器上安装Amoeba这款软件，其IP为192.168.0.10。 一 台MySQL服务器作为主服务器，其IP为 192.168.0.90。另外两台作为从服务器，其IP分别为192.168.0.91和192.168.0.92。 应用客户端主机的IP为192.168.1.100，这些主机安装的都是CentOS6.5。为了便于管理，在MySQL主服务器上打开终端界面，执行“hostname mysqlzl”命令，对其进行更名操作。 执行“VIM /etc/sysconfig/networkscripts/ifcfg-eth0”，将其中的“ONBOOT”的值修改为“yes”，将“BOOTPROTO”的值修改为“static”。添加“IPADDR=192.168.0.90”和“NETMASK =255.255.255.0”语句，之后保存该文件。

执行“/etc/init.d/network resrart”命令，重启网卡。执行“bash”命令确认，执行“cd /etc/yum.repos.d”，“vim CentOSMedia.repo”命令，在该文件中只保留“baseurl=file:///media”，将无 关的 内 容清除，将“ebable”的值设置为“1”。之后插入CentOS 6.5光盘，执行“mount /dev/cdrom /media/”命令，配置好YUM源。之后在代理服务器和两台MySQL从服务器上分别执行修改名称（例如“proxysrv”，“mysqlc1”和“mysqlc2”），配 置 IP 和YUM源等操作。

安装MySQL和所需的组件

之后需要在MySQL主服务器和从服务器上安装MySQL数据库组件和Cmake。首先，必须保证这些服务器之间时间保持同步，例如在mysqlzl上执行“yun install ntp -y” 命令，在mysqlzl上执行“vim/etc/init.d/ntp.conf” 命令，在该配置文件后面添加“server 127.127.1.0”和“fudge 127.127.1.0 stratum 8”语句，然后执行“/etc/init.d/ntp”命令重启该服务。在mysqlc1和mysqlc2服务器上分别执行“yum install ntpdate -y”命令，安装时间同步工具，执行“/usr/sbin/ntpdate 192.168.0.90”命令，来同步mysqlzl服务器上的时间信息。

为了便于这些MySQL服务器之间顺利通讯，可以 执 行“iptables -F”命令，来清理防火墙规则。在MySQL主从服务器上分别 执 行“yum install gcc gcc-c++ ncurres-devel make -y”，“tar zxvf cmake-x.x.x.tar.gz”，“./configure ; gmake ;gmake install”，来安装对应版本的Cmake组件。执行“tar zxvf mysql-5.x.xx.tar.gz” 命 令，解压对应版本的MySQL安 装 包，执行“cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql&&make&&make install”命 令，对 MySQL进行编译安装，其中的“PREFIX=/usr/local/mysql”参数是指定其安装位置。在“mysqlc1”和“mysqlc2”服务器上分别执行“cd mysql-5.x.xx”命令，进入解压路径。

执行“cp supportfiles/my-medium.cnf /etc/my.cnf”，“cp supportfiles/mysql.server /etc/rc.d/init.d/mysqld”，复制所需的文件。执行“chmod +x /etc/rc.d/init.d/mysqld”命令，来设置所需的可执行权限。执行“chkconfig -add mysqld”命令，将MySQL添加到系统服务中。执行“echo"PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile”，“source /etc/profile”，添加所需环境变量并使其立即生效。执行“groupadd mysql”，“useradd -M -s /sbin/nologin mysql”命令，创建名为Mysql的组，在其中添加名为mysql的账户。

执行“chown -Rmysql:mysql /u s r/local/mysql/”命令，更改mysql用户对指定目录的权 限。 执 行“/usr/local/mysql/scripts/mysql_install_db --user=mysql--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data” 命 令，对MySQL数据库进行优化处理。执行“/etc/init.d/mysqld start”命 令，启动Mysql。执行“chkconfig mysql on”命令，使其自动启动。为了安全起见，执行“mysqladmin–u root password "p@ssw0rd"”，为SQL管理员设置密码，密码为“p@ssw0rd”，当然，您可以根据实际情况更改。这样，就完成了对MySQL从服务器的初始化配置。

配置MySQL主动复制参数

在mysqlzl上同样执行以上命令，来实现初始化操作。注意，主从服务器上的MySQL管理员密码需要设置为一致。执行“vim /etc/ld.so.conf”命令，对该文件中的“server-id = 1”修改为“server-id = 11”，如果保持默认值的话，该服务器就无法作为MySQL主服务器使用。将“logbin=mysql-bin”修 改 为“log-bin=master-bin”，添加一行内容“log-slaveupdates=true”，允许从服务器从本机更新。保存该文件，执 行“service mysqld restart”命令，重启 MySQL服 务。 执 行“mysql -u root -p”命令，输入密码后登录到MySQL。在控制台“mysql>”提示符下执行“GRANT REPLICATION SLAVE ON *.* TO 'cong user'@'192.168.0.%'IDENTI FIEDBY'qwe123'”，为账户名为“conguser”的用户设置“qwe123”的密码，允许其访问192.168.0网段的所有MySQL从服务器。

执行“FLUSHPRIVILEGES”命令，执行权限刷新操作。执行“show master status” 命 令，记下返回信息中的“File”和“Position”的值，这其实是主MySQL的日志文件名称和定位值。例如“masterbin.000001”和“397”。在两台MySQL从服务器上分别执行“vim /etc/my.cnf”命令，对配置文件进行修改，将其中的“server-id”的值修改为22。如果保持默认为1，则无法将其作为从服务器使用。添加“relaylog=relay-log-bin” 和“relay-log-index=slaverelay-bin.index”两行 语句，设置日志以及索引信息。执 行“/etc/init.d/mysqld restart”命令，重启 MySQL。

执 行“mysql -u root-p”命令，输入密码后登录到MySQL，在控制台提示符下执行“change master to master_host='192.168.0.90',master_user='conguser',master_password='qwe123',master_log_file='maste rbin.000001',master_log_pos=397;”命令，设置与其关联的MySQL主服务器信息，注意，这里的各项参数需要和主服务器上的配置相同。例如账户名、密码以及日志文件等。执行“start slave;”，“show slave statusG;”命令，在返回信息中的“Slave_IO_Runnin”栏和“Slave_SQL_Running”栏中必须显示“YES”，才表示配置成功。在MySQL主服务器上登录到MySQL控制台，执行“create database dbceshi;”命令，创建一个用于测试的数据库。

在某个MySQL从服务器中登录到MySQL控制台，执行“show databases;”命令，可以看到上述数据库，这就说明主从复制已经配置完毕。

代理服务器上配置AMO EBA

接下来在代理服务器上配置Amoeba，因为Amoeba是使用JDK开发的，所以需要安装JDK组件。注意，不需要使用最新的JDK。执行“chmod +x /usr/local/jdk-6u14-linux-x64.bin”命令，为其配置可执行权限。

执行“./jdk-6u14-linux-x64.bin”命 令，安装JDK组 件。 执 行“mv jdk1.6_14/ /usr/local/jdk1.6”命 令，将JDK文件复制到指定的位置。执行“vim /etc/profile” 命令，对其配置文件进行修改，在其中的“export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL”一 行 后面 添 加“export JAVA_HOME=/usr/local/jdk1.6”，“ export CLASSPATH=$CLASSPATH:$JAVA_HOME/l i b:$JAVA_HOME/jre/lib”，“export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin”，“export AMOEBA_HOME=/usr/local/amoeba/”，“export PATH=$PATH:$AMOEBA_HOME/bin”等内容，配置和JDK和AMOEBA相关的路径信息。

执行“source /etc/profile”语句，使上述配置生效。执行“java -version”命令，来验证其版本。执行“mkdir /usr/local/amoeba”，“tar zxvf /root/amoeba-mysql-binary-x.x.x.tar.gz -C /usr/local/amoeba”，“chmod-R 755 /usr/local/amoeba”，“/usr/local/amoeba/bin/amoeba”等语句，依次执行创建对应版本的AMOEBA运行路径，解压AMOEBA压缩包，为其设置可读可写可执行的权限，配置AMOEBA等操作。

注意，这里的AMOEBA解压路径需要和上述配置文件中的内容一致。在MySQL主从服务器上的“mysql>”控制台下分别执行“grant all on *.* to aduser@'192.168.0.%'identified by 'hello'”命令，创建名称为“aduser”，密码为“hello”的账户，使其拥有对192.168.0网段的访问权限。

在代理服务器上进入“/usr/local/amoeba/conf”目录，执行“vim amoeba.xml”命令。在编辑界面中找 到“”一行，在其下两行的内容修改为“amueba”和“qwe123”。

找到“”一行，将其下的“server1”修改为“master”， 将“server1”修改为“master”， 将“server1”修改为“slaves”，同时将其上方的注释标记删除，使上述两行生效。

保存该文件后，执行“vimdb Servers.xml”命令，在编辑界面中找到“root”行，将其修改为“aduser”，将其下的一行修 改 为“hello”，并将该行的注释标记删除。这两行启用了上述“aduser”账户，即让AMOEBA使用该账户来连接MySQL数据库。

将之后的“”修改为“”，在随后的“127.0.0.1”修改为“192.168.0.90”，即设置MySQL主服务器的名称和IP。

按照同样的方法进行同样的修改时，不同的是修改的内容为“”和“192.168.0.91”。 因为默认该文件中只提供了两个“”的语句段，所以需要复制并添加一个新的语句段。其完整内容包括“”，“”，“ < property name=ip Address>192.168.0.92 ”，“ ”，“”。这样就将两个从服务器信息添加进来。

将其后的“”修改为“”，这和上述主配置保持一致。并需要将其后面的“ Server1，server2”修改为“mysqlc1，mysqlc2”，与上述两个MySQL从服务器的名称保持一致。然后保存该文件，完成AMOEBA的相关配置操作。

执行“amoeba start &”命令，启动AMOEBA。执行“netstat anpt | gerp jave”命令，在端口列表中显示8066端口，说明AMOEBA配置没有问题。

在客户端执行测试操作

因为在AMOEBA配置文件中设置的用户名为“amoeba”，密码为“qwe123”，所以为了实现读写分离，在客户端程序中需要使用该账户和密码，来操作后台MySQL数据库。

例如，在客户端安装好MySQL，配置正确的IP后，执 行“mysql –u amoeba h 192.168.0.10 –P 8066-p”，使用预设的“amoeba”连接代理服务器，并指定连接端口，其中“-p”表示要输入密码。输入上述密码，连接成功后出现“mysql>”提示符，执行“use dbceshi”命令。

使用上述用于测试的数据库，再使用“create table”，“insert into”，“select”等指令，在该数据库创建表并插入数据和查看数据。当进入MySQL主服务器或者从服务器中，执行查看操作时，可看到上述表及数据已存在，说明主从复制和读写分离正常。