首页 > 代码库 > 高可用mysql集群搭建
高可用mysql集群搭建
对web系统来说，瓶颈大多在数据库和磁盘IO上面，而不是服务器的计算能力。对于系统伸缩性我们一般有2种解决方案，scale-up（纵向扩展）和scale-out（横向扩展）。前者如扩内存，增加单机性能，更换ssd等，虽然看似指标不治本而且比较昂贵，但确实是非常有效的，大多数应用的数据规模不是很大，当内存足够缓存下所有数据的时候，磁盘就没有什么压力了；后者譬如各类分布式解决方案，冗余磁盘阵列等。
在我看来，mysql读写分离是一个scale-up和scale-out的结合体，通过多个机器服务来提升系统吞吐，但并没有增加存储总量。对于一些关键业务是比较适用的，毕竟关键业务的数据量不会达到单机的存储上限。非关键数据可以放到各类缓存系统就行。mysql官方提供了mysql-proxy来做负载均衡（Qihoo360团队的开源项目Atlas看着更为强大，优化了后段连接及lua部分的代码），方便我们统一的调度mysql资源，不用我们在业务层自己来做负载均衡。
使用Mysql-Proxy还有个好处，可以在proxy上实现统一的udf和memcached等缓存系统对接。此外，mysql-proxy自身可以用heartbeat做热备，因为proxy上无存储数据，所以还可以方便线性扩容。基于mysql主从复制和mysql-proxy我们可以搭建个高可用的数据库集群，构成图如下。
准备工作，三个服务器
192.168.1.107，安装mysql-proxy（依赖libevent）
192.168.1.108，安装mysql-server，作为slave，可多几个slave server，需要在mysql-proxy上添加配置
192.168.1.109，安装mysql-server，作为master
开启mysql-server的远程登录，
mysql> GRANT ALL PRIVILEGES ON *.* TO root@‘%‘ IDENTIFIED BY "rootpwd" WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;
1, 配置主mysql（192.168.1.109）的配置文件如下，然后重启
server-id = 1 #主数据库id最好为1log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_namebinlog_ignore_db = mysqlbinlog_ignore_db = information_schema
增加从数据库权限，多个从服务器需要多次配置（rsyncuser和rsyncowd为用作同步的用户，密码，最好不用root）
mysql> grant replication slave on *.* to rsyncuser@‘192.168.1.108‘ identified by ‘rsyncpwd‘;mysql> flush privileges;mysql> show master status;+------------------+----------+--------------+--------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+--------------------------+| mysql-bin.000001 | 333 | | mysql,information_schema |+------------------+----------+--------------+--------------------------+1 row in set (0.00 sec)mysql> show variables like ‘server_id‘
2, 配置从mysql（192.168.1.108）的配置文件如下（从数据库id必须比主数据库id大），然后重启
server-id = 2
指向主数据库，开启同步
mysql> change master to master_host=‘192.168.1.109‘, master_user=‘rsyncuser‘,master_password=‘rsyncpwd‘, master_port=3306, master_log_file=‘mysql-bin.000001‘, master_log_pos=333;mysql> show variables like ‘server_id‘mysql> start slavemysql> show slave status;
如果一切正常则主从复制已经搭建好了。（如果start slave不成功，看看server_id是不是生效）
3, 配置proxy（192.168.1.107）如下（多个从服务器需要添加多个--proxy-read-only-backend-addresses参数）：
#vi /etc/init.d/mysql-proxy#!/bin/bashexport LUA_PATH="/usr/share/mysql-proxy/?.lua"mode=$1if [ -z "$mode" ] ; then mode="start"ficase $mode in ‘start‘) mysql-proxy --admin-username=admin --admin-password=adminpwd --daemon --admin-address=:4401 --proxy-address=:3307 --proxy-backend-addresses=192.168.1.109:3306 --proxy-read-only-backend-addresses=192.168.1.108:3306 --admin-lua-script=/usr/share/mysql-proxy/rw-splitting.lua ;; ‘stop‘) killall mysql-proxy ;; ‘restart‘) if $0 stop ; then $0 start else echo "retart failed!!!" exit 1 fi ;;esac
启动proxy服务
service mysql-proxy start
用客户端连接mysql -uroot -p -h192.168.1.107 -P3307 （管理端mysql -uadmin -p -h192.168.1.107 -P4401）
附：
Mysql为了安全性，在默认情况下用户只允许在本地登录，有些情况需要进行远程连接，因此为了使其可以远程需要进行如下操作：
一、允许root用户在任何地方进行远程登录，并具有所有库任何操作权限，具体操作如下：
mysql -uroot -prootpwd mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘%‘ IDENTIFIED BY "rootpwd" WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;
二、允许root用户在一个特定的IP进行远程登录，并具有所有库任何操作权限，具体操作如下：
mysql -uroot -prootpwdmysql> GRANT ALL PRIVILEGES ON *.* TO root@"192.168.1.107" IDENTIFIED BY "rootpwd" WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;
三、允许root用户在一个特定的IP进行远程登录，并具有所有库特定操作权限，具体操作如下：
mysql -uroot -prootpwdmysql> GRANT select，insert，update，delete ON *.* TO root@"172.16.16.152" IDENTIFIED BY "rootpwd"; mysql> FLUSH PRIVILEGES;
四、删除用户授权，需要使用REVOKE命令
REVOKE privileges ON 数据库[.表名] FROM user-name; mysql -uroot -prootpwd 进行授权操作：GRANT select，insert，update，delete ON TEST-DB TO test-user@"192.168.1.107" IDENTIFIED BY "rootpwd"; #再进行删除授权操作： REVOKE all on TEST-DB from test-user; #该操作只是清除了用户对于TEST-DB的相关授权权限，但是这个“test-user”这个用户还是存在。 #最后从用户表内清除用户： DELETE FROM user WHERE user="test-user"; #重载授权表： FLUSH PRIVILEGES;#退出mysql数据库：
五、MYSQL权限详细分类：
全局管理权限： FILE: 在MySQL服务器上读写文件。 PROCESS: 显示或杀死属于其它用户的服务线程。 RELOAD: 重载访问控制表，刷新日志等。 SHUTDOWN: 关闭MySQL服务。数据库/数据表/数据列权限： ALTER: 修改已存在的数据表(例如增加/删除列)和索引。 CREATE: 建立新的数据库或数据表。 DELETE: 删除表的记录。 DROP: 删除数据表或数据库。 INDEX: 建立或删除索引。 INSERT: 增加表的记录。 SELECT: 显示/搜索表的记录。 UPDATE: 修改表中已存在的记录。 特别的权限： ALL: 允许做任何事(和root一样)。 USAGE: 只允许登录--其它什么也不允许做。