3台机器:master(192.168.1.190)、slave1(192.168.1.191)、slave2(192.168.1.192);master机器做insert、update数据,同时作为mysql-proxy服务器,实际项目上mysql-proxy最好安装在单独的机器上,slave1、slave2只作为查询。
操作系统:Red Hat Linux 6.2 64bit
3台机器都执行下面的安装:
[root@slave1 桌面]# cp /mnt/hgfs/F/mysql-advanced-5.6.20-linux-glibc2.5-x86_64.tar.gz /usr/local
[root@slave1 local]# cd /usr/local/
[root@slave1 local]# tar -zxvf mysql-advanced-5.6.20-linux-glibc2.5-x86_64.tar.gz
[root@slave1 local]# ln -s mysql-advanced-5.6.20-linux-glibc2.5-x86_64 mysql
[root@slave1 local]# groupadd mysql
[root@slave1 local]# useradd -g mysql mysql
[root@slave1 local]# cd mysql
//修改文件属组和属主,注意该命令后面有一个点,表示当前目录
[root@slave1 mysql]# chown root:mysql .
//把data目录的属主改为mysql
[root@slave1 mysql]# chown mysql:mysql data/
//拷贝mysql配置文件放在etc目录下并改名为my.cnf
[root@slave1 mysql]# cp support-files/my-default.cnf /etc/my.cnf
cp:是否覆盖"/etc/my.cnf"? y
//拷贝mysql的启动脚本放在/etc/init.d/目录下
[root@slave1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
//初始化创建数据库,默认没有创建数据库,将导致mysqld服务无法启动
[root@slave1 mysql]# ./scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
//启动服务
[root@slave1 mysql]# service mysqld start
Starting MySQL.. [确定]
//登录,测试是否正常启动,初始密码为空
[root@slave1 mysql]# ./bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
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> use test
Database changed
mysql>
配置master
[root@master mysql]# vi /etc/my.cnf
增加或修改以下3项:
server-id = 1 //默认是1,不是的话则改为1
thread_concurrency = 4 //线程并发数(CPU*2)
log-bin = mysql-bin //打开二进制日志功能
保存退出,重新启动mysql
[root@master mysql]# ./bin/mysql -uroot
mysql> grant replication slave on *.* to repl@'192.168.1.%' identified by '123456';
mysql>flush privileges;
mysql>show grants for 'repl'@'192.168.1.%'; //查看授权,有记录说明ok
mysql>show master status\G //查看master服务器状态,有二进制日志文件名和记录位置(position)
至此mysql配置完成。
配置Slave
[root@slave1 mysql]# vi /etc/my.cnf
新增加或修改以下4项:
server-id = 2 //设置与master和其他的slave不一样就行
thread_concurrency = 4 //线程并发数(CPU*2)
relay-log=mysql-log //打开中继日志
relay-log-index=mysql-log.index //设置relay-log-index文件名
保存退出,重新启动mysql。
[root@slave1 mysql]# ./bin/mysql –uroot
mysql>stop slave;
mysql>change master to
->master_host='192.168.1.190', //master服务器ip
->master_user='repl', //用户名
->master_password='123456', //密码
->master_log_file='mysql-bin.000001', //master服务器二进制日志名
->master_log_pos=107;
mysql>start slave;
mysql> SHOW SLAVE STATUS\G //查看slave状态
如果 Slave_IO_Running和Slave_SQL_Running显示为yes则配置OK。
在本次配置过程出现了Slave_IO_Running为NO的情况,而且数据没有从master同步到slave,查看mysq/data目录的日志发现报错:
[ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'mysql-bin.000001' at 107, the last event read from './mysql-bin.000001' at 107, the last byte read from './mysql-bin.000001' at 126. ( server_errno=1236)
意思是max_allowed_packet的值小了,在master增加其值:
mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.01 sec)
max_allowed_packet调整为10G的方法
在主库和备库分别执行
mysql>stop slave;//master上不执行
mysql>set global max_allowed_packet=10*1024*1024;
mysql>start slave;
然后发现错误依旧,数据仍然没有复制到slave,在slave执行
mysql>reset slave;
mysql>start slave;
错误解决,数据复制成功。
在master服务器上安装mysql-proxy
[root@master 桌面]# cd /usr/local
[root@master local]# tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@master local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
[root@master local]# groupadd mysql-proxy
[root@master local]# useradd -g mysql-proxy mysql-proxy
[root@master local]# cd mysql-proxy
[root@master mysql-proxy]# chown -R root:mysql-proxy .
// mysql-proxy.sh不存在,vi之后会自动创建这个文件,增加如下行
// export PATH=$PATH:/usr/local/mysql-proxy/bin
[root@master mysql-proxy]# vi /etc/profile.d/mysql-proxy.sh
[root@master mysql-proxy]# source /etc/profile
[root@master mysql-proxy]# mysql-proxy –help
[root@master mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.1.190:3306" --proxy-read-only-backend-addresses="192.168.1.191:3306" --proxy-read-only-backend-addresses="192.168.1.192:3306"
[root@master mysql-proxy]# netstat -ntulp |grep 4040
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 53245/mysql-proxy
[root@master mysql-proxy]#
//在master服务器中赋予root远程登入的权限
[root@master mysql]# ./bin/mysql –uroot
mysql> grant all on *.* to root@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
//在slave服务器上测试远程连接master的mysql
//这样就连上了Mysql-Proxy服务器了
[root@slave1 mysql]# ./bin/mysql -h 192.168.1.190 -u root -p --port=4040
Enter password:
……………………….
mysql>
//下面我们来实现读写分离,Mysql-Proxy本身不会实现读写分离,主要是依靠 Lua 脚本实现的
//杀掉mysql-proxy进程
[root@master mysql-proxy]# killall mysql-proxy
//查看进程是否停掉
[root@master mysql-proxy]# netstat -ntlup |grep 4040
[root@master mysql-proxy]# cd ./share/doc/mysql-proxy/
// //查看读写分离脚本,lua语言实现rw-splitting.lua
[root@master mysql-proxy]# ls |grep rw-splitting.lua
rw-splitting.lua
[root@master mysql-proxy]# mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.1.190:3306" --proxy-read-only-backend-addresses="192.168.1.191:3306" --proxy-read-only-backend-addresses="192.168.1.192:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
[root@master mysql-proxy]#
亲,沙发正空着,还不快来抢?
我要评论 / 展开表单