mysql主从复制及mycat读写分离
登录#.#.190.163的服务器，在服务器上配置：
vim /etc/my.cnf
[mysqld]
在此添加如下配置，其中163为服务器的ip最后一位
server_id=163
需要复制的数据库名称
binlog-do-db=xxxy
log-bin=xxxy-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=100
slave_skip_errors=1062
[root@iZ112fail84Z ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.6.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave,replication client on *.* to 'root'@'120.77.##.##' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
在主数据库上模拟准备需要复制的表；
mysql> create database testpre;
Query OK, 1 row affected (0.00 sec)
mysql> use testpre;
Database changed
mysql> create table pre (id int,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into pre values (1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into pre values (2,'b');
Query OK, 1 row affected (0.00 sec)
mysql> select * from pre;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)为了保证master和slave的数据一致，采用主库备份，从库还原来实现初始数据一致。
先暂时把要复制的表锁定
主数据库上
mysql> insert into pre values (3,'c');
Query OK, 1 row affected (0.01 sec)
mysql> select * from pre;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into pre values (4,'d');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock进行数据的导出操作
mysql> exit;
Bye
[root@iZ112fail84Z ~]# mysqldump -uroot -p --add-drop-table testpre >/bak/testpre.sql;
Enter password:
[root@iZ112fail84Z bak]# cd /bak/
[root@iZ112fail84Z bak]# ls
bakmysql bakmysqlold shell testpre.sql
[root@iZ112fail84Z bak]# pwd拷贝主库的的testpre.sql文件到从数据库服务器
修改从slave的mysql配置文件/etc/my.cnf
vim /etc/my.cnf
在[mysqld]中增加
设置server_id,一般设置为ip的最后位
server_id=220
复制过滤：需要备份的数据库
binlog-do-db=xxxy
不需要备份的数据库，一般这2种中只设置一种
binlog-ignore-db=mysql
开启二进制日志，以备slave作为其它slave的master时使用
log-bin=xxxy-slave-bin
为每个session分配内存，
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=100
slave_skip_errors=1062
relay_log配置中继日志
relay_log=xxxy-relay-bin
log_slave_updates 表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
防止从节点的数据改变
read_only=1
service mysql restart
导入主库上的那个脚本到从库
mysqldump -uroot -p testpre < /bak/testpre.sql
或者使用navicat进行数据传输，保证主库和从库中的testpre库中的数据一致
在从库中执行
[root@izwz95v28cpic4beuk0rk0z ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 110
Server version: 5.6.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='#.#.190.163',master_user='root',master_password='123456',master_port=3306,master_log_file='xxxy-bin.000001',master_log_pos=30042,master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> 其中master_log_file和master_log_pos的值分别为在主库上的如下：
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| xxxy-bin.000001 | 30042 | xxxy | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)在从服务器上执行：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: #.#.190.163
Master_User: root
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: xxxy-bin.000001
Read_Master_Log_Pos: 30042
Relay_Log_File: xxxy-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: xxxy-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 30042
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
```来查询从的状态
开启主从复制：
<div class="se-preview-section-delimiter"></div>
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
***************** 1. row *****************
Slave_IO_State: Waiting for master to send event
Master_Host: #.#.190.163
Master_User: root
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: xxxy-bin.000001
Read_Master_Log_Pos: 30042
Relay_Log_File: xxxy-relay-bin.000002
Relay_Log_Pos: 282
Relay_Master_Log_File: xxxy-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 30042
Relay_Log_Space: 454
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 163
Master_UUID: ee963db0-323b-11e7-9fc2-00163e060905
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
如上：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
这2个表示主从同步正常，
查询线程状态：
在master上查看slave的i/o线程创建的连接
<div class="se-preview-section-delimiter"></div>
mysql> show processlist \G;
***************** 1. row *****************
Id: 8
User: root
Host: #.#.190.163:38009
db: xxxy
Command: Sleep
Time: 1159
State:
Info: NULL
测试主从复制是否成功：
之主库中的任意一个表中插入数据后，去从库查看，发现能同步成功了。
测试过程中，如果同步出错，可以在slave上重置主从复制的设置
<div class="se-preview-section-delimiter"></div>
mysql>reset slave;
mysql>change master to master_host=’#.#.190.163’,master_user=’root’,master_password=’123456’,master_port=3306,master_log_file=’xxxy-bin.000001’,master_log_pos=30042,master_connect_retry=30;
-------------------------------
<div class="se-preview-section-delimiter"></div>
##mycat主从读写分离
www.mycat.org.cn
mycat的读写分离是基于后端mysql集群的主从同步来实现。mycat的提供语句的分发功能。
<div class="se-preview-section-delimiter"></div>
[root@iZ112fail84Z ~]# useradd mycat
[root@iZ112fail84Z ~]# passwd mycat
Changing password for user mycat.
New password:
passwd: all authentication tokens updated successfully.
[root@iZ112fail84Z ~]# su mycat
[mycat@iZ112fail84Z root]
[mycat@iZ112fail84Z root]
/home/mycat
[mycat@iZ112fail84Z ~]$ su root
Password:
[root@iZ112fail84Z mycat]# vim /etc/profile
mycat env
export MYCAT_HOME=/usr/local/mycat
export PATH=
“/etc/profile” 86L, 2060C written
[root@iZ112fail84Z mycat]# source /etc/profile
[root@iZ112fail84Z mycat]# exit
exit
[mycat@iZ112fail84Z ~]
/home/mycat
wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
–2017-10-16 22:18:42– http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
Resolving dl.mycat.io… 210.51.26.184
Connecting to dl.mycat.io|210.51.26.184|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 15662280 (15M) [application/octet-stream]
Saving to: “Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz”
100%[============================================================>] 15,662,280 504K/s in 30s
2017-10-16 22:19:13 (502 KB/s) - “Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz” saved [15662280/15662280]
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
$ ls
mycat Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
su root
Password:
[root@iZ112fail84Z mycat]# ls
mycat Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@iZ112fail84Z mycat]# mv /home/mycat/mycat /usr/local/
[root@iZ112fail84Z mycat]# cd /usr/local/mycat/
[root@iZ112fail84Z mycat]# ls
bin catlet conf lib logs version.txt
[root@iZ112fail84Z mycat]# ll
total 24
drwxrwxr-x 2 mycat mycat 4096 Oct 16 22:21 bin
drwxrwxr-x 2 mycat mycat 4096 Mar 1 2016 catlet
drwxrwxr-x 4 mycat mycat 4096 Oct 16 22:21 conf
drwxrwxr-x 2 mycat mycat 4096 Oct 16 22:21 lib
drwxrwxr-x 2 mycat mycat 4096 Oct 28 2016 logs
-rwxrwxr-x 1 mycat mycat 217 Oct 28 2016 version.txt
mycat的配置说明：
如果主从复制需要复制自定义函数及存储过程，需要在/etc/my.cnf的[mysqld]中添加log_bin_trust_function_creates=true
默认linux版的mysql的大小写不敏感，需要在/etc/my.cnf的[mysqld]中添加lower_case_table_names=1
配置mycat的
[root@iZ112fail84Z conf]# pwd
/usr/local/mycat/conf
mycat的核心配置文件：
schema.xml 用于设置mycat的逻辑库，表，数据节点，datahost等内容
conf目录下主要以下三个需要熟悉。
server.xml是Mycat服务器参数调整和用户授权的配置文件
schema.xml是逻辑库定义和表以及分片定义的配置文件
rule.xml是分片规则的配置文件
vim server.xml
<div class="se-preview-section-delimiter"></div>
1
启动mycat失败,查看日志
[root@iZ112fail84Z bin]# ./mycat start
Starting Mycat-server...
[root@iZ112fail84Z bin]# tail -200 ../logs/wrapper.log
<div class="se-preview-section-delimiter"></div>
INFO | jvm 1 | 2017/11/01 19:27:35 | 2017-11-01 19:27:35,781 [ERROR][WrapperSimpleAppMain] 2017-11-01 19:27:35 startup error java.lang.NumberFormatException: Size must be specified as bytes (b), kibibytes (k), mebibytes (m), gibibytes (g), tebibytes (t), or pebibytes(p). E.g. 50b, 100k, or 250m.
INFO | jvm 1 | 2017/11/01 19:27:35 | Failed to parse byte string: -44040192B
vim server.xml添加如下:
<div class="se-preview-section-delimiter"></div>
0
详见:[错误处理 ](http://blog.csdn.net/gaojingsong/article/details/53244239)
在启动时还是启动不了,查看日志:
tail -200 ../logs/wrapper.log
提示内存不足
<div class="se-preview-section-delimiter"></div>
vim /usr/local/mycat/conf/wrapper.conf
wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G
wrapper.java.additional.10=-Xmx4G
wrapper.java.additional.11=-Xms1G
改为：
wrapper.java.additional.5=-XX:MaxDirectMemorySize=100M
wrapper.java.additional.10=-Xmx100M
wrapper.java.additional.11=-Xms100M
其中的2个配置文件内容:
<div class="se-preview-section-delimiter"></div>
2017-11-02 22:21:16.356 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341)) - release connection MySQLConnection [id=20, lastTime=1509632476341, user=root, schema=xxxy, old shema=xxxy, borrowed=true, fromSlaveDB=false, threadId=10487, charset=utf8, txIsolation=3, autocommit=true, attachment=rc_dn2{insert into cash (userid) values (345)}, respHandler=SingleNodeHandler [node=rc_dn2{insert into cash (userid) values (345)}, packetId=1], host=#.#.190.163, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]注意看：host=#.#.190.163 ，
在进行select，发现查询在另一个ip上
配置mycat状态检查服务
在mycat的服务主机上添加mycat服务的状态监测脚本，并开放相应的监测端口，以提供给HAProxy对mycat的服务状态进行检测判断。可以使用xinetd来实现，通过xinetd，HAProxy可以用httpchk来检测mycat的存活状态。
xinetd:网络守护进程服务程序
[root@izwz95v28cpic4beuk0rk0z etc]# yum install xinetd
[root@izwz95v28cpic4beuk0rk0z etc]# cat /etc/xinetd.conf
