实现在同一台linux主机上mysql主从复制与读写分离

来源：互联网发布：林珊珊淘宝店铺首页编辑：程序博客网时间：2024/05/21 17:53

环境情况：由于资源有限，仅在一台CentOS release 6.6上实现M-S主从复制与读写分离

一、mysql安装与配置

具体安装过程建议参考我的上篇一博客文章

二、mysql主从复制

主从服务器场景如下

主（m）：172.30.204.111:3307

从1（s1）：172.30.204.111:3308

从2（s2）：172.30.204.111:3309

[root@master dingmingyi]# netstat-anlpt|grep mysql

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 19411/mysqld

tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 19734/mysqld

tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 20202/mysqld

2.1主服务器操作：

[root@master dingmingyi]# cat /usr/local/mysql-m/etc/my.cnf

[mysqld]

basedir=/usr/local/mysql-m

datadir=/opt/database-m

socket=/var/run/mysql-m/mysql-m.sock

pid-file=/var/run/mysql-m/mysql-m.pid

port=3307

user=mysql

server-id=1

log-bin=mysql-bin

binlog_ignore_db=mysql

character_set_server=utf8

[mysqld_safe]

log-error=/var/log/mysql-m/mysql--error.log

[mysql]

socket = /var/run/mysql-m/mysql-m.sock

default-character-set=utf8

只要在my.cnf里面添加内容都要重新启动服务#service mysql-m restart

[root@master dingmingyi]#/usr/local/mysql-m/bin/mysqladmin -uroot password '321321' -S /var/run/mysql-m/mysql-m.sock #为M-mysql设置密码；

[root@master dingmingyi]# /usr/local/mysql-m/bin/mysql-uroot -p321321 -S /var/run/mysql-m/mysql-m.sock

授权给从服务器

mysql> grant replication slave on *.* tos1@localhost identified by '123123';

mysql> grant replication slave on *.* tos2@localhost identified by '123123';

mysql>flush privileges;

查询主数据库状态

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

记录file和position的值，在配置从服务器时需要用到

2.2从服务器操作

修改从服务器配置文件/usr/local/mysql-s1/etc/my.cnf

将server-id=10 ，确保此id与主服务器不同 #设置多个从服务器必须与M-S和S-S都不同

同样在[mysql]下增加default-character-set=utf8 ，在[mysqld]下增加character_set_server=utf8

重启服务

登陆从服务器

/usr/local/mysql-s1/bin/mysql -uroot -p -S/var/run/mysql-s1/mysql-s1.sock #由于没设密码，可以直接回车进入

执行同步语句

>change master to

master_host='localhost',

master_user='s1',

master_password='123123',

master_log_file='mysql-bin.000001',

master_log_pos=670,

master_port=3307; #一定要指定相应的端口号，不然在查看状态时会出错

>start slave;

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: s1

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 670

Relay_Log_File: mysql-s1-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes #以下两排都要为YES才表明状态正常

Slave_SQL_Running: Yes

Replicate_Do_DB:

…………………………..省略若干…………………………………

另一从服务器也做类似以上操作。

2.3 验证主从复制效果

主服务器上操作

mysql> create database ding;

Query OK, 1 row affected (0.00 sec)

mysql> create table ding.d_tb(id int(3),name char(16),age char(4));

Query OK, 0 rows affected (0.14 sec)

mysql> insert into ding.d_tb values(001,'xixi','23');

Query OK, 1 row affected (0.03 sec)

两台从服务器上都可以查看相同的内容

mysql> show databases;

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

| Database |

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

| information_schema |

| ding |

| mysql |

| performance_schema |

| test |

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

5 rows in set (0.00 sec)

mysql> use ding;

Database changed

mysql> show tables;

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

| Tables_in_d_test |

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

| d_tb |

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

1 row in set (0.00 sec)

mysql> select * from ding.d_tb;

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

| id | name | age |

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

| 1 | xixi | 23 |

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

1 row in set (0.00 sec)

同步成功！！！

三、Mysql-proxy读写分离

Mysql-proxy代理：将写操作分到mysql-master，读操作分到mysql-slave

Mysql-proxy的读写分离是通过rw-splitting.lua脚本实现，因此需要安装lua

3.1安装lua

[root@master dingmingyi]# yum -y install gcc gcc-c++ libedit libedit-devel libtermcap-devel ncurses-devel libevent-devel readline-devel

[root@master dingmingyi]# wget http://www.lua.org/ftp/lua-5.2.3.tar.gz

[root@master dingmingyi]# tar zxvf lua-5.2.3.tar.gz

[root@master dingmingyi]#cd lua-5.2.3

[root@master lua-5.2.3]#make linux

[root@master lua-5.2.3]#make install

3.2 下载mysql-proxy

[root@master dingmingyi]#wget http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz

[root@master dingmingyi]# tar zxvf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz

[root@master dingmingyi]# mv mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit /usr/local/mysql_proxy

[root@master dingmingyi]# cd /usr/local/mysql_proxy/

[root@master mysql_proxy]# mkdir scripts

[root@master mysql_proxy]# cp share/doc/mysql-proxy/rw-splitting.lua scripts/

[root@master mysql_proxy]# vim scripts/rw-splitting.lua

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 1, #默认4 改为1，表示最小链接数只有超过1时才会进行读写分离

max_idle_connections = 1, #默认8

is_debug = false

}

end

[root@master mysql_proxy]# vim proxy.conf #此为手动创建

1 [mysql-proxy]

2 admin-username=proxy

3 admin-password=123123

5 admin-lua-script=/usr/local/mysql_proxy/lib/mysql-proxy/lua/admin.lua #定义管理脚本路径

6 proxy-read-only-backend-addresses=172.30.204.111:3308,172.30.204.111:3309 #读服务器地址

7 proxy-backend-addresses=172.30.204.111:3307 #写服务器地址

8 proxy-lua-script=/usr/local/mysql_proxy/scripts/rw-splitting.lua #读写分离脚本路径

9 log-file=/var/log/mysql-proxy.log

10 log-level=debug

11 daemon=true

12 keepalive=true

[root@master mysql_proxy]# chmod 660 proxy.conf

[root@master mysql_proxy]# bin/mysql-proxy -P 172.30.204.111:3310 --defaults-file=/usr/local/mysql_proxy/proxy.conf

[root@master mysql_proxy]# lsof -i:3310

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysql-pro 1416 root 10u IPv4 1011376 0t0 TCP master.jay.com:dyna-access (LISTEN)

登陆主服务器上

mysql> grant all on *.* to proxy@'%'identified by '123123';

mysql>flush privileges;

由于配置了主从复制，可以在从数据库服务器上查看到同步的数据

从服务器上

mysql> select host,user,password from mysql.user;

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

| host | user | password |

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

| localhost | root | |

| master.jay.com | root | |

| 127.0.0.1 | root | |

| ::1 | root | |

| localhost | | |

| master.jay.com | | |

| % | proxy | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

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

7 rows in set (0.00 sec)

为了更显著看到读写分离效果，现将两台slave关闭

mysql> stop slave;

注：这里以ding.d_tb为测试数据库，由于前面进行主从复制，在从服务器中也存在ding.d_tb库。

为了看到单点效果，现做如下操作：

mysql> insert into ding.d_tbvalues(004,'master','4'); ----〉主服务器

mysql> insert into ding.d_tbvalues(005,'slave-1','5'); -----〉从服务器 s1

mysql> insert into ding.d_tbvalues(006,'slave-2','6'); ----->从服务器s2

测试机已安装mysql，现远程连接mysql-proxy

[root@ldap mysql-p]# bin/mysql -uproxy -p123123 -h172.30.204.111 -P3310 –e” insert into d_tb values(002,'dingding','25');” #插入数据

[root@ldap mysql-p]# bin/mysql -uproxy -p123123 -h172.30.204.111 -P3310

mysql> select * from ding.d_tb; #此时看到的是从服务器s1上的数据，上一步添加的数据没有

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

| id | name | age |

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

| 1 | xixi | 23 |

| 2 | haha | 24 |

| 2 | dingding | 25 |

| 5 | slave-1 | 5 |

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

4 rows in set (0.00 sec)

去主库中查询：

mysql> select * from ding.d_tb;

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

| id | name | age |

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

| 1 | xixi | 23 |

| 2 | haha | 24 |

| 2 | dingding | 25 |

| 4 | master | 4 |

| 2 | dingding | 25 |

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

数据已插入

最后检查从数据库

S1:

mysql> select * from ding.d_tb;

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

| id | name | age |

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

| 1 | xixi | 23 |

| 2 | haha | 24 |

| 2 | dingding | 25 |

| 5 | slave-1 | 5 |

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

4 rows in set (0.00 sec)

S2:

mysql> select * from ding.d_tb;

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

| id | name | age |

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

| 1 | xixi | 23 |

| 2 | haha | 24 |

| 2 | dingding | 25 |

| 6 | slave-2 | 6 |

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

4 rows in set (0.00 sec)

由于关闭了主从同步，也没有记录。

现停掉S1服务 #service mysql-s1 stop

再次登陆测试机

[root@ldap mysql-p]# bin/mysql -uproxy -p123123 -h172.30.204.111 -P3310

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.6.24-debug Source distribution

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> select * from ding.d_tb;

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

| id | name | age |

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

| 1 | xixi | 23 |

| 2 | haha | 24 |

| 2 | dingding | 25 |

| 6 | slave-2 | 6 |

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

4 rows in set (0.00 sec)

发现已自动切到S2上读取数据了

由此验证，已经实现mysql读写分离，目前写操作都在master主服务器上，用了避免数据不同步；读操作都分摊给其他各个slave从服务器，用来分担数据库压力。

===================================================================

配置过程出现的问题及相应的解决方法

1、mysql> showmaster status;

Empty set (0.00 sec)

解决：没开启log-bin导致。在主服务器my.cnf中[mysqld]添加log-bin=mysql-bin，重启

2、mysql> show slavestatus\G;

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

Slave_IO_State: Connecting tomaster

Master_Host: localhost

Master_User: s1

Master_Port: 3308

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File:mysql-s1-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

解决：

对照以下三条：网络不通、密码不对、pos不对

>stop slave;

>change master to

master_host='localhost',

master_user='s1',

master_password='123123',

master_log_file='mysql-bin.000001',

master_log_pos=120,

master_port=3307; #一定要指定端口号

>start slave;

3、mysql> show slavestatus\G；

……………….省略若干………………….

ERROR:

No query specified

解决：mysql> show slave status\G不要后面的；（==》分号）

4、# make linux

cd src && make linux

make[1]: Entering directory`/home/dingmy/lua-5.2.4/src'

make allSYSCFLAGS="-DLUA_USE_LINUX" SYSLIBS="-Wl,-E -ldl-lreadline"

make[2]: Entering directory `/home/dingmy/lua-5.2.4/src'

gcc -O2 -Wall -DLUA_COMPAT_ALL-DLUA_USE_LINUX -c -o lua.o lua.c

lua.c:67:31: error: readline/readline.h: Nosuch file or directory

lua.c:68:30: error: readline/history.h: Nosuch file or directory

lua.c: In function ?.ushline?.

lua.c:265: warning: implicit declaration offunction ?.eadline?

lua.c:265: warning: assignment makespointer from integer without a cast

lua.c: In function ?.oadline?.

lua.c:297: warning: implicit declaration offunction ?.dd_history?

make[2]: *** [lua.o] Error 1

make[2]: Leaving directory`/home/dingmy/lua-5.2.4/src'

make[1]: *** [linux] Error 2

make[1]: Leaving directory`/home/dingmy/lua-5.2.4/src'

make: *** [linux] Error 2

解决：yum install readline-devel

5、

[root@master dingmingyi]#/usr/local/mysql-m/bin/mysql -uroot -p321321 -S /var/run/mysql-m/mysql-m.sock

Warning: Using a password on the commandline interface can be insecure.

/usr/local/mysql-m/bin/mysql: Unknown OScharacter set 'GB18030'.

/usr/local/mysql-m/bin/mysql: Switching tothe default character set 'latin1'.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.24-debug-log Sourcedistribution

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarksof their respective

owners.

Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.

mysql>

解决：

# vim /usr/local/mysql-m/etc/my.cnf

在[mysql]下增加default-character-set=utf8

在[mysqld]下增加character_set_server=utf8

# service mysql-m restart

6、[root@ldap mysql-p]#bin/mysql -uproxy -p123123 -P3310 -h172.30.204.111

Warning: Using a password on the commandline interface can be insecure.

ERROR 1045 (28000): Access denied for user'proxy'@'master.jay.com' (using password: YES)

解决：进入主服务器中

mysql> select host,user,password frommysql.user;

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

| host | user | password |

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

| localhost | root | *4160291B4C8CC2573CC94951203FFBC858754907 |

| master.jay.com | root | |

| 127.0.0.1 |root | |

| ::1 | root | |

| localhost | | |

| master.jay.com | | |

| localhost | s1 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| localhost | s2 | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| % | proxy | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| % | proxy1 |*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

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

10 rows in set (0.00 sec)

mysql> delete from mysql.user wherehost='master.jay.com'; #删除host=master.jay.com

Query OK, 2 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

重新连接mysql-proxy成功！！

0 0