mysql native数据同步_mysql 主从同步-读写分离

主从同步与读写分离测试

一、  实验环境(主从同步)

Master                   centos 7.3              192.168.138.13

Slave                     centos 7.3               192.168.138.14

二、在master操作

安装并配置文件

[root@localhost ~]# yum install mariadb-server -y

[root@localhost ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=1                  //必须唯一

log-bin=mysql-bin          //开启binlog日志

character-set-server=utf8         //设置字符集

2.启动mysql

[root@localhost ~]# systemctl start mariadb

3. 授权root用户

MariaDB [(none)]> grant all on *.* to root@'localhost' identified by '123456';

MariaDB [(none)]> grant all on *.* to root@'%' identified by '123456';

4． 刷新权限表

MariaDB [(none)]> flush privileges

5.查看master状态

MariaDB [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 |    521 |                   |                  |

+------------------+----------+--------------+------------------+

注意：如果主库中已存在数据，则需要备份拷到从库保持数据一致性

1.锁定数据表，避免在备份过程中，表被更新

mysql>LOCK TABLES tbl_name READ;

为表增加一个写锁定：

mysql>LOCK TABLES tbl_name WRITE;

解锁 ： mysql>UNLOCK TABLES;

2.备份数据

方法一

Mysqldump  -uroot  -p –B db1 –T tb1  | gzip  > /mysqlbackup/tb1.sql.gz

方法二

直接备份datadir=/var/lib/mysql

三、       在slave上操作

安装并配置/etc/my,cnf

[root@localhost ~]# yum install mariadb-server -y

[root@localhost ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

server-id=1                  //必须唯一

character-set-server=utf8         //设置字符集

2. 启动mysql

[root@localhost ~]# systemctl start mariadb

3. 配置同步参数

MariaDB [(none)]> change master to

master_host='192.168.138.13',

master_user='root',

master_password='123456',

master_port=3306,

master_log_file='mysql-bin.000003',

master_log_pos=521;

4. 启动主从同步进程

MariaDB [(none)]> start slave;

5. 检查状态

MariaDB [(none)]> show slave status\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.138.13

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 521

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 529

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

看到两个yes说明配置成功

四、 测试

1.在slave上查看

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

2. 在master创建数据库

MariaDB [(none)]> create database testdb1;

Query OK, 1 row affected (0.00 sec)

3.  在slave查看

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| testdb1            |

+--------------------+

4 rows in set (0.00 sec)

--------------------------------------------------------------------------------------------------------------------------------------------

读写分离

一、环境准备(在主从同步基础上)

Master       centos7.3                   192.168.138.13

Slave         centos7.3                   192.168.138.14

Mycat         centos7.3                    192.168.138.15

管理端        centos7.3                    192.168.138.16

二、安装并配置mycat

1.部署jdk环境

MyCAT用Java开发，需要有JAVA运行环境，mycat依赖jdk1.7的环境

1)上传jdk

[root@localhost tools]# ll jdk-7u45-linux-x64.tar.gz

2)安装jdk

[root@localhost tools]# mkdir /usr/java

[root@localhost tools]# tar xf jdk-7u45-linux-x64.tar.gz -C /usr/java/

3)设置环境变量

[root@localhost tools]# vim /etc/profile.d/java.sh

内容如下：

export JAVA_HOME=/usr/java/jdk1.7.0_45/

export PATH=$JAVA_HOME/bin:$PATH

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

使环境变量当前终端生效

[root@localhost tools]# source /etc/profile.d/java.sh

4)测试

[root@localhost tools]# java -version

java version "1.7.0_45"

Java(TM) SE Runtime Environment (build 1.7.0_45-b18)

Java HotSpot(TM) 64-Bit Server VM (build 24.45-b08, mixed mode)

2.安装mycat

1)下载mycat源码包

2)解压

[root@localhost ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz  -C /usr/local

解压内容

[root@localhost ~]# ll /usr/local/mycat/

total 12

drwxr-xr-x 2 root root  190 Dec

3 16:17 bin

drwxrwxrwx 2 root root    6 Mar

1  2016 catlet

drwxrwxrwx 4 root root 4096 Dec  3 16:17 conf

drwxr-xr-x 2 root root 4096 Dec  3 16:17 lib

drwxrwxrwx 2 root root    6 Oct 28

2016 logs

-rwxrwxrwx 1 root root  217 Oct 28

2016 version.txt

3)添加环境变量

[root@localhost local]# cat  /etc/profile.d/mycat.sh

export

PATH=$PATH:/usr/local/mycat/bin

[root@localhost

tools]# source /etc/profile.d/mycat.sh

3.读写分离配置

[root@localhost local]# cd

/usr/local/mycat/

[root@localhost mycat]# ls

bin

catlet  conf  lib

logs  version.txt

[root@localhost conf]# cp

schema.xml{,.bak}

[root@localhost conf]# vim schema.xml

主要修改：

1)balance="1" writeType="0"

switchType="1"

balance

1、balance=0 不开启读写分离机制，所有读操作都发送到当前可用的writehostle

.

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

3、balance=2 所有读操作都随机的在readhost和writehost上分

writeType

负载均衡类型，目前的取值有3种：

1、writeType="0", 所有写操作发送到配置的第一个writeHost。

2、writeType="1"，所有写操作都随机的发送到配置的writeHost。

3、writeType="2"，不执行写操作。

switchType

1、switchType=-1 表示不自动切换

2、switchType=1 默认值，自动切换

3、switchType=2 基于MySQL 主从同步的状态决定是否切换

2)在

修改

修改

注：根据自己实际情况，其他无用的就可删除

配置好的文件如下：

>

sqlMaxLimit="100" dataNode="dn1">

database="db1" />

minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native"

switchType="1" slaveThreshold="100">

select user()

user="root"

password="123456">

user="root" password="123456" />

4、创建管理用户

1)主库上对mycat用户授权如下：

用户：mycat 密码：123456 端口：3306

权限：insert,delete,update,select

命令：grant insert,delete,update,select on TD_OA.* to

mycat@'192.168.138.%' identified by '123456';

flush privileges;

2)从库上mycat用户授权如下：

用户：mycat 密码：123456 端口：3306/3307

权限： select

grant select on TD_OA.* to mycat@'192.168.138.%' identified by '123456';

flush privileges;

测试环境可以直接使用root用户，授予所有权限：

mysql> grant all on *.* to root@'192.168.95.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@'localhost' identified by '123456';

修改mycat配置文件

默认配置即可

①这里配置的是可以连接主库的两个用户

用户：root 密码：123456 给予此用户TESTDB数据库增删改查的权限。

用户：user 密码：user 给予此用户TESTDB数据库读的权限。

②这里的TESTDB，不一定是你数据库上的真实库名，可以任意指定，只要接下来和schema.xml的配置文件的库名统一即可。

1. 在客户端连接mysql主库服务器：

1)先安装mysql

2)# mysql -uroot –p123456

-h192.168.138.15 -P8066 -DTESTDB

8.主从同步读写分离测试

1)管理端创建表

[root@localhost ~]# mysql -uroot –p123456 -h192.168.138.16

-P8066 -DTESTDB

CREATE TABLE test1 (id int(10),name varchar(10),address varchar(20) DEFAULT

NULL);

手动停止主从同步：(stop slave)

注意：工作中不能这么干

2)读功能测试：

分别在主从库插入数据:

master: insert into test1 values(1,'test1','master');

slave1: insert into test1 values(2,'test1','slave1');

3)管理端验证

负载均衡:

mysql> select * from test1;

+------+-------+---------+

| id | name | address |

+------+-------+---------+

| 2 | test1 | slave1 |

+------+-------+---------+

1 row in set (0.00 sec)

4)写功能:

管理端再次插入数据

mysql> insert into test1 values(3,'test1','write');

Query OK, 1 row affected (0.00 sec)

在master查看

MariaDB [db1]> select * from test1;

+------+------+---------+

| id

| name | address |

+------+------+---------+

|

1 | test | master  |

|

3 | test | write   |

+------+------+---------+

2 rows in set (0.00 sec)

在Slave查看

MariaDB [db1]> select * from test1;

+------+------+---------+

| id

| name | address |

+------+------+---------+

|

2 | test | slave   |

+------+------+---------+

3 rows in set (0.00 sec)

#注意：测试完毕启动主从同步功能。