基于主主复制的mysql读写分离配置

基于主主复制的mysql读写分离配置

1. 环境准备

1.1准备两台全新的虚拟机（最好用centos7）

1.   192.168.0.55

2.   192.168.0.56

1.2安装jdk和mysql

1.安装jdk执行百度

2.安装mysql：

使用yum命令安装mysql

·       1、简单粗暴直接命令敲起来,安装服务端：mysql-server、mysql-devel；安装客户端：mysql:

        yum install mysql-server mysql-devel mysql

·       2、不仔细看你会觉得已经安装成功了，其实mysql-server没有安装成功！ 

·       3、我们需要手动下载一下,再单独安装mysql-server，此处用到wget命令（yum install wget）:

·       下载rpm包

     wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

-  安装rpm包

rpm -ivh mysql-community-release-el7-5.noarch.rpm

·       检查是否安装成功，如下图安装成功：

ls -1 /etc/yum.repos.d/mysql-community*

·       安装mysql-server,这次如下图就真的安装好了！！！

        yum install mysql-server

4、启动、配置、登录

·       启动mysql，/etc/init.d/下面需要手动极爱那个mysqld复制一份进去。

        service mysqld start

·       创建root管理员：

        mysqladmin -u root password 123456

·       登录mysql：

        mysql -uroot-p123456

·       如下图就表示成功了，我还查看了此时数据库中所有的表名：

        select table_name from information_schema.tables;

5、Navicat forMySQL连接

·       如下图配置好之后，点击测试连接会报错，是因为我们刚装的mysql还不支持远程连接： 

·       解决：网上有改表法和授权法的区别，我这边就直接使用该表法了：先切换到mysql自带数据库中，然后更新root用户的host字段

        use mysql;

        updateuserset host = '%'whereuser = 'root';

·       可能会报错，但是如果查询用户表，显示已经改为%了，那么直接执行刷新下权限就行了。

        select host, userfromuser;

        //发现用户表中存在用户名为空的情况，此时会导致登录时不填写用户名可以直接登录，正确填写账号密码反而不能登录，所以执行以下sql语句，删除用户名为空的用户。

        delete from user where user='';

        flush privileges; 

·       连接成功： 

到此为止就装好了jdk以及mysql;

 

可参考：http://blog.csdn.net/u010246789/article/details/52382632

 

2. 主主同步配置

2.1. 创建同步用户

启动MySQL，然后进入MySQL，执行以下命令创建用于同步的用户：

grant replication slave,file on *.* to 'user'@'peer' identified by 'password';

flush privileges;

注意：

其中user为用于同步的用户名，peer为另一主的IP地址，password为user的MySQL密码，这3个的值需根据实际进行修改。

注意，两主机器都需要创建同步用户。并确保peer为另一主的IP地址，其它user和password两主建议保持相同，示例（假设两主IP分别为192.168.0.55和192.168.0.56）：

Ø 192.168.0.55上执行：

grant replication slave,file on *.* to 'replication'@'192.168.0.55' identified by '123456';

flush privileges;

Ø 192.168.0.56上执行：

grant replication slave,file on *.* to 'replication'@'192.168.0.56' identified by '123456';

flush privileges;

2.2. my.cnf

修改/etc/my.cnf，实现主主配置。

如果不存在/etc/my.cnf，则复制support-files/my-default.cnf生成my.cnf，两台机器的my.cnf分别配置为（不难看到，只有server_id和auto_increment_increment两项不同）：

机器A

机器B

server-id=1

user=mysql

log-bin=mysql-bin

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=1

auto_increment_offset=1

server-id=2

user=mysql

log-bin=mysql-bin

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

2.3. 配置项说明

配置项

配置项说明

server-id

不能相同！唯一标识号，值位于1~2^32-1之间

user

这个可以不指定，则使用mysqld_safe指定的用户，或者mysqld_safe默认的用户mysql

log-bin

启用二进制日志文件

log-slave-updates

配置从库上的更新操作是否写二进制文件，需要和log-bin一起使用

slave-skip-errors

值为all表示让从库跳过所有错误（但不能跳过所有DDL所引起的主从错误），也可以只跳过指定的错误，如：--slave-skip-errors=1062,1053；也可以配置只跳过DDL错误，如：--slave-skip-errors=ddl_exist_errors，这等同于：

--slave-skip-errors=1007,1008,1050,1051,1054,1060,1061,1068,1094,1146

sync_binlog

值为1表示主机每次提交事务的时候把二进制日志的内容同步到磁盘上

auto_increment_increment

auto_increment_offset

和auto_increment_offset一起用于主主同步，用来错开自增，防止键值冲突，所以auto_increment_increment和auto_increment_offset两者，至少要有一项值不同。

 

上述配置会导致同步所有的数据库，借助下列配置项也可以选择性的同步或不同步：

配置项

配置项说明

binlog-do-db=test1

binlog-do-db=test2

表示只同步数据库test1和test2，如果还想同步test3，只需要新增一行：binlog-do-db=test3即可

binlog-ignore-db=db1

binlog-ignore-db=db2

表示不同步数据库db1和db2，如果还有db3不想同步，新增一行：binlog-ignore-db=db3即可

 

相关配置项（对于主从同步，只需要在从上配置）：replicate-do-db、replicate-ignore-db、replicate_wild_do_table和replicate_wild_ignore_table。

2.4. 设置同步关系

分别重启MySQL，进入MySQL Cli，执行命令“show master status\G”，记住“File”和“Position”的值，如：

mysql> show master status\G

*************************** 1. row ***************************

             File: mysql-bin.000001

         Position: 120

     Binlog_Do_DB:

 Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

 

设置同步关系（两个主都需要设置）：

stop slave;

change master to master_host='peer',master_user='user',master_password='password',master_log_file='mysql-bin.000004', master_log_pos=682;

 

如果不先执行“stop slave;”，则可能遇到如下错误：

ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

 

这里，peer、user和password三者的取值为“创建同步用户”时指定的值。设置示例：

change master to master_host='192.168.0.56',master_user='replication',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=120;

 

由于前面一步调用“stop slave;”，停止了复制。在完成后，需再启动复制：

start slave;

 

2.5. 验证

执行命令“show slave status\G;”查看复制状态，如果出现“Slave_IO_Running: Yes”和“Slave_SQL_Running: Yes”，则表示状态正常。

进一步，可以分别创建一个表，如：create table test1 (a int)和create table test2 (b int)。再分别使用show tables查看是否同步过去。

 

获取可以使用navicat创建数据库、创建表。此时只要在任意一遍新建，都会同步到另一个数据库。这就是所谓的主主复制（主主同步）。

 

到此为止，主主同步就复制好了

具体可参考：

http://blog.csdn.net/aquester/article/details/50674140

 

3. mycat实现读写分离

3.1 mycat安装及配置

只需要在192.168.0.55上安装

3．1下载解压mycat安装文件

tar -zxvf /geneplus/downloads/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

3.2 创建mycat用户，并设置环境变量

shell> groupadd mycat

shell> useradd -g mycat mycat

shell> passwd mycat

shell> chown -R mycat.mycat /usr/local/mycat

shell> vi /etc/profile 添加如下内容

export MYCAT_HOME=/usr/local/mycat

shell> source /etc/profile

3.3修改mycat配置

3.2 配置文件介绍

·       conf/rule.xml定义分片规则

·       conf/schema.xml定义逻辑库、表以及分片节点等内容

·       conf/server.xml定义用户授权及服务器参数相关配置

 

先修改schema.xml

<?xmlversion="1.0"?>

<!DOCTYPE mycat:schema SYSTEM"schema.dtd">

<mycat:schemaxmlns:mycat="http://io.mycat/">

  <schemaname="TESTDB" checkSQLschema="false"sqlMaxLimit="100" dataNode="dn1"></schema>

  <dataNode name="dn1"dataHost="localhost1" database="db1" />

  <dataHost name="localhost1"maxCon="1000" minCon="10" balance="1"writeType="0" dbType="mysql" dbDriver="native"switchType="2"  slaveThreshold="100">

  <heartbeat>show slavestatus</heartbeat>

        <writeHosthost="hostMaster" url="192.168.0.55:3306"user="root" password="123456" />

        <writeHosthost="hostSlave" url="192.168.0.56:3306"user="root" password="123456" />

  </dataHost>

</mycat:schema>

 

 

 

其中，balance指的负载均衡类型，目前的取值有4种：

 

1.balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

 

2.balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

 

3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。

 

4.balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

 

switchType指的是切换的模式，目前的取值也有4种：

 

1.switchType='-1' 表示不自动切换

 

2.switchType='1' 默认值，表示自动切换

 

3.switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status

 

4.     switchType='3'基于MySQLgalary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'。

5.      

6.  writeType="0":所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个 writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties。

 

 

因此，该配置文件中的balance="1"意味着作为stand by writeHost的hostS1和hostS2将参与select语句的负载均衡，这就实现了主从的读写分离，switchType='2'意味着基于Mysql主从同步的状态决定是否切换。