Linux 6 64bit下MySQL集群负载均衡的安装配置(亲自安装).docx

环境：3台机器：master（90）、slave1（91）、slave2（92）；master机器做insert、update数据，同时作为mysql-proxy服务器，实际项目上mysql-proxy最好安装在单独的机器上，slave1、slave2只作为查询。操作系统：Red Hat Linux 6.2 64bit3台机器都执行下面的安装：rootslave1 桌面# cp /mnt/hgfs/F/mysql-advanced-5.6.20-linux-glibc2.5-x86_64.tar.gz /usr/localrootslave1 local# cd /usr/local/rootslave1 local# tar -zxvf mysql-advanced-5.6.20-linux-glibc2.5-x86_64.tar.gzrootslave1 local# ln -s mysql-advanced-5.6.20-linux-glibc2.5-x86_64 mysqlrootslave1 local# groupadd mysqlrootslave1 local# useradd -g mysql mysqlrootslave1 local# cd mysql/修改文件属组和属主，注意该命令后面有一个点，表示当前目录rootslave1 mysql# chown root:mysql ./把data目录的属主改为mysqlrootslave1 mysql# chown mysql:mysql data/拷贝mysql配置文件放在etc目录下并改名为frootslave1 mysql# cp support-files/f /etc/f cp：是否覆盖/etc/f？ y/拷贝mysql的启动脚本放在/etc/init.d/目录下rootslave1 mysql# cp support-files/mysql.server /etc/init.d/mysqld/初始化创建数据库，默认没有创建数据库，将导致mysqld服务无法启动rootslave1 mysql# ./scripts/mysql_install_db -basedir=/usr/local/mysql -datadir=/usr/local/mysql/data -user=mysql/启动服务rootslave1 mysql# service mysqld startStarting MySQL. 确定/登录，测试是否正常启动，初始密码为空rootslave1 mysql# ./bin/mysql -urootWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or h for help. Type c to clear the current input statement.mysql use testDatabase changedmysql配置masterrootmaster mysql# vi /etc/f增加或修改以下3项：server-id = 1 /默认是1，不是的话则改为1thread_concurrency = 4 /线程并发数(CPU*2)log-bin = mysql-bin /打开二进制日志功能保存退出，重新启动mysqlrootmaster mysql# ./bin/mysql -urootmysql grant replication slave on *.* to repl192.168.1.% identified by 123456;mysqlflush privileges;mysqlshow grants for repl192.168.1.%;/查看授权，有记录说明okmysqlshow master statusG/查看master服务器状态，有二进制日志文件名和记录位置（position）至此mysql配置完成。配置Slaverootslave1 mysql# vi /etc/f新增加或修改以下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。rootslave1 mysql# ./bin/mysql urootmysqlstop slave;mysqlchange master to -master_host=90,/master服务器ip -master_user=repl,/用户名 -master_password=123456,/密码 -master_log_file=mysql-bin.000001,/master服务器二进制日志名 -master_log_pos=107;mysqlstart slave;mysql SHOW SLAVE STATUSG/查看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的方法在主库和备库分别执行mysqlstop slave;/master上不执行mysqlset global max_allowed_packet=10*1024*1024;mysqlstart slave;然后发现错误依旧，数据仍然没有复制到slave，在slave执行mysqlreset slave;mysqlstart slave;错误解决，数据复制成功。在master服务器上安装mysql-proxyrootmaster 桌面# cd /usr/localrootmaster local# tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gzrootmaster local# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxyrootmaster local# groupadd mysql-proxyrootmaster local# useradd -g mysql-proxy mysql-proxyrootmaster local# cd mysql-proxyrootmaster mysql-proxy# chown -R root:mysql-proxy ./ mysql-proxy.sh不存在，vi之后会自动创建这个文件，增加如下行/ export PATH=$PATH:/usr/local/mysql-proxy/binrootmaster mysql-proxy# vi /etc/profile.d/mysql-proxy.shrootmaster mysql-proxy# source /etc/profilerootmaster mysql-proxy# mysql-proxy helprootmaster mysql-proxy# mysql-proxy -daemon -log-level=debug -log-file=/var/log/mysql-proxy.log -plugins=proxy -proxy-backend-addresses=90:3306 -proxy-read-only-backend-addresses=91:3306 -proxy-read-only-backend-addresses=92:3306rootmaster mysql-proxy# netstat -ntulp |grep 4040tcp 0 0 :4040 :* LISTEN 53245/mysql-proxy rootmaster mysql-proxy#/在master服务器中赋予root远程登入的权限rootmaster mysql# ./bin/mysql urootmysql grant all on *.* to root192.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服务器了rootslave1 mysql# ./bin/mysql -h 90 -u root -p -port=4040Enter password: .mysql/下面我们来实现读写分离，Mysql-Proxy本身不会实现读写分离，主要是依靠 Lua 脚本实现的/杀掉mysql-proxy进程rootmaster mysql-proxy# killall mysql-proxy/查看进程是否停掉rootmaster mysql-proxy# netstat -ntlup |grep 4040rootmaster mysql-proxy# cd ./share/doc/mysql-proxy/ /查看读写分离脚本，lua语言实现rw-splitting.luarootmaster mysql-proxy# ls |grep rw-splitting.lua rw-splitting.luarootmaster mysql-proxy# mysql-proxy -daemon -log-level=debug -log-file=/var/log/mysql-proxy.lo