• 设备
    • 今日
    • 9

    MySQL集群负载均衡

     环境:

    3台机器:master192.168.1.190)、slave1192.168.1.191)、slave2192.168.1.192);master机器做insertupdate数据,同时作为mysql-proxy服务器,实际项目上mysql-proxy最好安装在单独的机器上,slave1slave2只作为查询。

    操作系统: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_RunningSlave_SQL_Running显示为yes则配置OK

    在本次配置过程出现了Slave_IO_RunningNO的情况,而且数据没有从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服务器上测试远程连接mastermysql

    //这样就连上了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]#

    来自:PC 广东省佛山市
    上一篇: 防火墙-iptables
    您可能还喜欢这些:

    亲,沙发正空着,还不快来抢?

    评论审核已开启 记住我的个人信息 回复后邮件通知我