一、 Mysql主服务器Master配置
1、修改主服务器配置:
#vi /etc/my.cnf
binlog-do-db=db1 #根据具体要复制的库进行添加
binlog-do-db=db2
binlog-do-db=db3
binlog-ignore-db = mysql #可以不写
log-bin=mysql-bin #启用二进制日志
server-id=1 #服务器唯一ID
2、重启MySQL
Service mysql restart
3、建立帐户并授权slave:
#/usr/local/mysql/bin/mysql -uroot -p123456 –h127.0.0.1
mysql>GRANT FILE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'slave'@'%' IDENTIFIED BY '123456';
#一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.137.20,加强安全。
刷新权限
mysql> FLUSH PRIVILEGES;
查看mysql现在有哪些用户
mysql>select user,host from mysql.user;
4、登录主服务器的mysql,查询master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 106 | db1,db2,db3 | mysql |
+------------------+----------+--------------+------------------+
Master 重启后会修改mysql-bin(序号加1)
二、 MySQL从服务器Slave 配置
1、修改从服务器配置:
#vi /etc/my.cnf
replicate-do-db=db1 #跟主服务器Master中的内容相同
replicate-do-db=db2
replicate-do-db=db3
replicate-ignore-db=mysql #可以不写
server-id=2 #每个Slave的server-id必须不同
#master-host=192.168.137.11 #如果Slave启动失败,注释掉该内容,从Slave的Mysql手动添加
#master-user=slave
#master-password=123456
#master-port=3306
#master-connect-retry=60
#log-slave-updates
skip-slave-start #防止复制随着mysql启动而自动启动。即slave端的mysql服务重启后需手动来启动主从复制(slave start),最好加上,slave端数据库服务重启后手动启动slave比较安全
2、重启MySQL
Service mysql restart
3、登录mysql并停止slave服务
mysql -uroot –p123456 –h127.0.0.1
mysql> stop slave;
设置与master服务器相关的配置参数
mysql>change master to master_host='192.168.137.11', master_user='slave', master_password='123456',MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=106;
注意:Master重启后slave 要修改MASTER_LOG_FILE,106无单引号。
启动从服务器复制功能
Mysql>start slave;
ERROR 1201 (HY000):Could not initialize master info structure的问题
解决方案是:运行命令 stop slave;
成功执行后继续运行 reset slave;
查看server_id:
Mysql> SHOW VARIABLES LIKE 'server_id';
4、检查从服务器复制功能状态
mysql> show slave status\G
以下两个参数必须为YES:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
返回如下:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.176.158
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000010
Read_Master_Log_Pos: 106
Relay_Log_File: bogon-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2,db3
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 406
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
三、 数据库中间件Mycat配置
跳过第1部分,看第二部分,配置Mycat的schema.xml文件
1、不使用Mycat托管MySQL主从服务器,简单使用如下配置
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM" url="192.168.11:3306" user="root"
password="123456">
</writeHost>
</dataHost>
2、Mycat 1.4 支持MySQL主从复制状态绑定的读写分离机制,让读更加安全可靠,配置如下:
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM" url"10.1.176.158:3306" user="root"
password="123456">
<readHost host="hostS" url="10.1.176.78:3306" user="root"
password="123456" />
</writeHost>s
</dataHost>
(1) 设置 balance="1"与writeType="0"
Balance参数设置:
1. balance=“0”, 所有读操作都发送到当前可用的writeHost上。
2. balance=“1”,所有读操作都随机的发送到readHost。
3. balance=“2”,所有读操作都随机的在writeHost、readhost上分发
WriteType参数设置:
1. writeType=“0”, 所有写操作都发送到可用的writeHost上。
2. writeType=“1”,所有写操作都随机的发送到readHost。
3. writeType=“2”,所有写操作都随机的在writeHost、readhost分上发。
“readHost是从属于writeHost的,即意味着它从那个writeHost获取同步数据,因此,当它所属的writeHost宕机了,则它也不会再参与到读写分离中来,即“不工作了”,这是因为此时,它的数据已经“不可靠”了。基于这个考虑,目前mycat 1.3和1.4版本中,若想支持MySQL一主一从的标准配置,并且在主节点宕机的情况下,从节点还能读取数据,则需要在Mycat里配置为两个writeHost并设置banlance=1。”
(2) 设置 switchType="2" 与slaveThreshold="100"
“Mycat心跳检查语句配置为 show slave status ,dataHost 上定义两个新属性: switchType="2" 与slaveThreshold="100",此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“
四、 测试读写分离与主从复制测试
主从复制测试:
通过mycat 创建表并插入字段
mysql> explain create table company(id int not null primary key,name varchar(100));
+-----------+---------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------------------+
| dn1 | create table company(id int not null primary key,name varchar(100)) |
| dn2 | create table company(id int not null primary key,name varchar(100)) |
| dn3 | create table company(id int not null primary key,name varchar(100)) |
+-----------+---------------------------------------------------------------------+
3 rows in set (0.32 sec)
mysql> create table company(id int not null primary key,name varchar(100));
Query OK, 0 rows affected (0.26 sec)
mysql> insert into company(id,name) values(1,'alibaba');
Query OK, 3 rows affected (0.15 sec)
在Master和Slave端查看数据是否同步。
读写分离测试:
设置mycat的log4j.xml文件,日志模式为debug,通过select查询观察日志中是否通过已配置的的读节点执行。
还可以在Master和Slave中插入不一样的数据,看mycat读取到的数据。