mysql主从配置代理_mysql-proxy代理加mysql主从实现读写分离

实验环境搭建：

mysql-proxy   192.168.1.163

mysql-master  192.168.1.164 (主)

mysql-slave   192.168.1.162 (从)

配置：mysql-proxy   192.168.1.163

#yum install lua -y

lua-5.1.4-4.1.el6.x86_64(大部分都是默认安装过的)

#yum install lrzsz -y

#rz (上传mysql-proxy)

#tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

#mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

#ls -l /usr/local/mysql-proxy

drwxr-xr-x. 2 7161 wheel 4096 Aug 19  2014 bin

drwxr-xr-x. 2 7161 wheel 4096 Aug 19  2014 include

drwxr-xr-x. 6 7161 wheel 4096 Aug 19  2014 lib

drwxr-xr-x. 2 7161 wheel 4096 Aug 19  2014 libexec

drwxr-xr-x. 7 7161 wheel 4096 Aug 19  2014 licenses

drwxr-xr-x. 3 7161 wheel 4096 Aug 19  2014 share

#ehho "PATH=$PATH:/usr/local/mysql-proxy/bin/">> /etc/bashrc

#export PATH=$PATH:/usr/local/mysql-proxy/bin/

#source /etc/bashrc 刷新

mysql-master  192.168.1.164 (主)

#yum install mysql mysql-server -y

#/etc/init.d/mysqld start

#mysql

mysql> create database db;

Query OK, 1 rowaffected (0.00 sec)

mysql> use db;

Database changed

mysql> show databases;

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

| Database           |

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

|information_schema |

| db                 |

| mysql              |

| test               |

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

4 rows in set(0.00 sec)

mysql>  create table test(id int);

Query OK, 0 rowsaffected (0.04 sec)

mysql>  insert into testvalues(6363);

Query OK, 1 rowaffected (0.00 sec)

mysql> grant all on db.* to user1@'%'  identified by '123456' ; 登录的用户及权限密码。

Query OK, 0 rowsaffected (0.00 sec)

mysql-slave   192.168.1.162 (从)(步骤同上和主服务器一样的配置)

#yum install mysql mysql-server -y

#/etc/init.d/mysqld start

#mysql

mysql> create database db;

Query OK, 1 rowaffected (0.00 sec)

mysql> use db;

Database changed

mysql> show databases;

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

| Database           |

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

|information_schema |

| db                 |

| mysql              |

| test               |

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

4 rows in set(0.00 sec)

mysql>  create table test(id int);

Query OK, 0 rowsaffected (0.04 sec)

mysql>  insert into testvalues(6363);

Query OK, 1 rowaffected (0.00 sec)

mysql> grant all on db.* to user1@'%'  identified by '123456' ; 登录的用户及权限密码。

Query OK, 0 rowsaffected (0.00 sec)

配置：mysql-proxy   192.168.1.163

#mysql-proxy -r 192.168.0.162:3306 -b 192.168.0.164:3306 -s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &

-r 指定读服务器

-b 指定些服务器

-s 指定使用那个lua(我这们这边做的读写分离所以选rw-splitting.lua)

执行命令后提示：

2016-03-18 00:32:38: (critical) plugin proxy 0.8.5 started

# netstat -antup|grep proxy

tcp   0   0   0.0.0.0:4040          0.0.0.0:*               LISTEN      3323/mysql-proxy

测试：

#mysql -u user1 -p123456 -P 4040 -h 192.168.0.163(拿mysql-proxy做pc机测试。)

mysql>use db ;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select *from test;

server default db:

client default db: db

syncronizing

+------+

| id   |

+------+

|   62 |

+------+

1 row in set (0.00 sec)

mysql> insert into test values(63);

Query OK, 1 row affected (0.00 sec)

mysql> select *from test;

+------+

| id   |

+------+

|   62 |

+------+

1 row in set (0.00 sec)

测试写数据没有问题，但是select *from test;查看不到，说明现在已经实现读写分离。

检测数据登录1.162从(只读服务器)

mysql> use db;

Database changed

mysql> select *from test;

+------+

| id   |

+------+

|   62 |

+------+

1 row in set (0.00 sec)

检测数据登录1.164从(写服务器)

mysql> use db;

Database changed

mysql> select *from test;

+------+

| id   |

+------+

|   64 |

|   63 |

+------+

2 rows in set (0.00 sec)

在1.163上面insert into test values(63);添加的63来到(1.64写服务器)。

读写分离成功。

那下面我们配置mysql主从:

mysql-master  192.168.1.164 (主)

#vim /etc/my.conf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-bin=mysqllog

server-id=1            添加这三行。

binlog-do-db=db

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

登录数据库：主服务器

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

Query OK, 0 rows affected (0.00 sec)

建个同步的账户.

mysql> drop table test; 删除test表格

#service mysqld restart

mysql-slave   192.168.1.162 (从)

#vim /etc/my.conf

[mysqld]

datadir=/var/lib/mysql

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

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

server-id=2

master-host=192.168.0.164     t添加这4行

master-user=user2

master-password=123456

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

登录数据库：从服务器

mysql> drop table test; 删除test表格

#service mysqld restart

登录：mysql-master  192.168.1.164 (主)

mysql> use db;

Database changed

mysql>  create table test (id int );

Query OK, 0 rows affected (0.03 sec)

mysql>  insert into test values(62);

Query OK, 1 row affected (0.00 sec)

mysql>  insert into test values(63);

Query OK, 1 row affected (0.00 sec)

建立test表。

登录：mysql-slave   192.168.1.162 (从)

mysql> use db ;

Database changed

mysql> select *from test;

+------+

| id   |

+------+

|   62 |

|   63 |

+------+

2 rows in set (0.00 sec)

数据已同步过来。

mysql> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.164

Master_User: user2

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqllog.000002

Read_Master_Log_Pos: 622

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 514

Relay_Master_Log_File: mysqllog.000002

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: 622

Relay_Log_Space: 670

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)

ERROR:

No query specified

mysql>