架构说明:
1, 数据库之间做主从复制,实现双主多备
2, 在数据库之间利用mysql-mmm套件实现HA,并用monitor进行监听
3, 利用mysql-proxy进行读写分离
4, 利用keepalived实现mysql-proxy的高可用
优势:
1, 数据库得到高可用的保护,当主库其中一个节点宕机,备用的主库进行切换,保证应用的正常进行。
2, 利用mysql-proxy进行读写分离,可以减轻主库的负载,并且能对从库进行负载均衡。
3, 对mysql-proxy采用keepalived的方法,实现对应用层的透明,并且保证mysql-proxy的高可用性。
4, 利用mysql-proxy可为日后的数据库改造,切分提供了有利的条件。
软件:
Mysql数据库 mysql-5.0.87.tar.gz
Mysql-mmm套件(做数据库的高可用) mysql-mmm-2.2.1.tar.gz
Keepalived套件(做mysqlproxy代理的高可用)
Mysql-proxy软件(实现读写分离)
IP与服务器hostname分配
192.168.21.5 monitor
192.168.21.11 master1
192.168.21.12 master2
192.168.21.13 slave1
192.168.21.14 slave2
192.168.21.15 mysqlproxy1
192.168.21.16 mysqlproxy2
数据库软件的安装:
#移动数据库软件包到/usr/src目录下
[root@test ~]# mv mysql-5.0.87.tar.gz /usr/src/
#解压数据库软件包
[root@test src]# tar –xzvf mysql-5.0.87.tar.gz
#建立目录
[root@test src]# mkdir /var/run/mysqld
#建立日志目录
[root@test src]# mkdir /var/log/mysqld
#添加mysql组
[root@test src]# groupadd mysql
#添加mysql用户
[root@test src]# useradd –g mysql mysql
[root@test src]# cd /usr/src/mysql-5.0.87
#配置mysql
--prefix=/usr/local/mysql #安装目录
--with-charset=gb2312 #设置字符集
--with-extra-charsets=gb2312,utf8,gbk #设置额外支持字符集
--with-server-suffix="-yzmyt" #添加数据库名称的后缀
--with-pthread 支持多线程
--with-unix-socket-path=/var/run/mysqld/mysql5.socket #设置unix socket路径
--with-tcp-port=3306 #设置通信端口
--with-mysqld-user=mysql #设置mysql用户
--with-big-tables #设置支持大表
--with-debug #设置为debug状态
--with-***-storage-engine #设置示例支持的存储引擎
[root@test mysql-5.0.87]# ./configure --prefix=/usr/local/mysql --enable-local-infile --with-charset=gb2312 --with-extra-charsets=gb2312,utf8,gbk --with-server-suffix="-yzmyt" --with-pthread --with-unix-socket-path=/var/run/mysqld/mysql5.socket --with-tcp-port=3306 --with-mysqld-user=mysql --with-zlib-dir=/usr --with-libwrap=/usr --with-low-memory --with-mysqlmanager --with-openssl --with-big-tables --with-debug --with-example-storage-engine --with-archive-storage-engine --with-csv-storage-engine --with-blackhole-storage-engine --with-ndbcluster --with-ndb-docs --with-ndb-test --with-federated-storage-engine
#编译
[root@test mysql-5.0.87]# make
#安装
[root@test mysql-5.0.87]# make install
#创建my.cnf所在的文件文件目录
[root@test mysql-5.0.87]# mkdir /usr/local/mysql/etc
#创建my.cnf
[root@test mysql-5.0.87]# vi /usr/local/mysql/etc/my.cnf
#添加:
[mysqld] #数据文件存储路径 datadir=/data-source #socket文件路径 socket=/var/run/mysqld/mysql5.socket #pid文件路径 pid-file=/var/run/mysqld/mysql5.pid #log日志文件路径 log=/var/log/mysqld/mysql5.log #错误日志文件路径 log-error=/var/log/mysqld/mysql5-error.log #端口 port=3306 #所属用户 user=mysql #默认存储引擎为innodb default-storage-engine=INNODB #初始化连接,设置为关闭自动提交 init_connect='set autocommit=0' #日志形式 log-bin=mysql-bin #服务器编号 server-id=1
|
#修改文件夹的所属组
[root@test mysql-5.0.87]# chown mysql:mysql /var/log/mysqld /var/run/mysqld /usr/src/local/mysql-5.0.87 -R
#创建数据文件所在文件夹
[root@test mysql-5.0.87]# mkdir /data-source
#修改数据文件所在文件夹的属组
[root@test mysql-5.0.87]# chown mysql:mysql /data-source -R
#安装mysql数据库
[root@test mysql-5.0.87]# /usr/local/mysql/bin/mysql_install_db --datadir=/data-source/ --user=mysql
#拷贝服务启动文件
[root@test mysql-5.0.87]# cp /usr/src/mysql-5.0.87/support-files/mysql.server /etc/rc.d/init.d/mysql5
#改变mysql5的权限
[root@test mysql-5.0.87]# chmod 755 /etc/rc.d/init.d/mysql5
#启动数据库
[root@test mysql-5.0.87]# service mysql5 start
Starting MySQL SUCCESS!
#进入数据库
[root@test mysql-5.0.87]# /usr/local/mysql/bin/mysql -uroot -p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 147 Server version: 5.0.87-yzmyt-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 6 rows in set (0.00 sec)
|
同理安装数据库到master2,slave1,slave2上
配置双主多从复制
先配置master1和master2之间的互相复制
在master1上执行:
[root@master1 ~]# /usr/local/mysql/bin/mysql -uroot –p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.87-terry-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
创建用户允许远程访问本库 mysql> grant replication slave,file on *.* to 'replication'@'192.168.1.12' identified by '123456'; #刷新权限 mysql> flush privileges;
|
在master2上执行:
[root@master2 ~]# /usr/local/mysql/bin/mysql -uroot –p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.0.87-terry-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave,file on *.* to 'replication'@'192.168.1.11' identified by '123456'; mysql> flush privileges;
|
然后修改master1和master2上的my.cnf
master1上添加
log-bin=mysql-bin server-id=1 log-slave-updates slave-skip-errors=all sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1
|
master2上添加
在master1上执行
[root@master1 ~]# /usr/local/mysql/bin/mysql -uroot –p
log-bin=mysql-bin
|
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.87-terry-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#刷新表为只读状态 mysql> flush tables with read lock; #查看主的状态 mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000008 Position: 98 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) #锁表 mysql> unlock tables; #关闭slave应用 mysql> stop slave; #修改主上的log应用 mysql> change master to master_host='192.168.1.12',master_user='replication',master_password='123456',master_log_file='mysql-bin.000008', master_log_pos=98; mysql> start slave;
|
在master1上执行
[root@master2 ~]# /usr/local/mysql/bin/mysql -uroot –p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.87-terry-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. #刷新表为只读模式 mysql> flush tables with read lock; #查看master状态 mysql> show master status\G *************************** 1. row *************************** File: mysql-bin.000008 Position: 98 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) #将表解锁 mysql> unlock tables; #停止slave应用 mysql> stop slave; #在master上进行应用 mysql> change master to master_host='192.168.1.11',master_user='replication',master_password='123456',master_log_file='mysql-bin.000008', master_log_pos=98; #启动日志应用 mysql> start slave;
|
查看slave状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
进行检测:
在master1中新建一个表,在master2中同时进行生成。
双主互为热备完成。
对两台slave主机进行单向复制
只要修改my.cnf
单向复制的资料网上很多,在此不再赘述
主备都完成的情况是:
在master1,master2上随便在一台上修改数据,在任何一台机器上都可以看到变化。
Mysql-mmm套件安装:
在所有的数据库服务器,包括监控机上安装mysql-mmm套件
#解压mysql-mmm套件
[root@master1 src]# tar –xzvf mysql-mmm-2.2.1.tar.gz
#进入mysql-mmm路径
[root@master1 src]# cd mysql-mmm-2.2.1
在所有的数据库服务器上都运行(master1,master2,slave1,slave2)
[root@master1 ~]# /usr/local/mysql/bin/mysql -uroot –p
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.0.87-terry-debug-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. #创建用户权限 mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.21.%' IDENTIFIED BY '123456'; #创建用户权限 mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.21.%' IDENTIFIED BY '123456'; #刷新权限 mysql> flush privileges;
|
配置所有数据库服务器包括监控机上的文件
[root@master1 mysql-mmm-2.2.1]# vi /etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default> cluster_interface eth0 #pid文件存储每次进程启动的pid pid_path /var/run/mmm_agentd.pid #路径位置 bin_path /usr/libexec/mysql-mmm/ #复制用户 replication_user replication #复制用户的明码 replication_password 123456 #Agent用户 agent_user mmm_agent #agent用户的密码 agent_password 123456 </host>
#服务器数据库1 <host db1> #数据库服务器真实IP地址 ip 192.168.21.11 #数据库服务器模式 mode master peer db2 </host> <host db2> #数据库服务器真实IP地址 Ip 192.168.21.12 #数据库服务器模式 mode master peer db1 </host> <host db3> #数据库服务器真实IP地址 ip 192.168.21.13 #数据库服务器模式 mode slave </host> <host db4> #数据库服务器真实IP地址 ip 192.168.21.14 #数据库服务器模式 mode slave </host> #写入角色 <role writer> #主机编号 hosts db1, db2 #虚拟IP ips 192.168.21.101 #模式-独占 mode exclusive </role> #只读角色 <role reader> #主机编号 hosts db1, db2, db3,db4 #虚拟IP ips 192.168.21.111,192.168.21.112,192.168.21.114,192.168.21.115 #模式 负载均衡模式 mode balanced </role>
|
|
修改监控机的mysql_mmm的配置
[root@monitor ~]# vi /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor> #监控机的IP ip 127.0.0.1 #pid文件路径 pid_path /var/run/mmm_mond.pid bin_path /usr/lib/mysql-mmm/ #状态文件路径 status_path /var/lib/misc/mmm_mond.status #可以ping的真实agent的IP ping_ips 192.168.21.11,192.168.21.12,192.168.21.13,192.168.21.14 #发现节点丢失则过6秒进行切换 auto_set_online 6 </monitor>
<host default> monitor_user mmm_monitor monitor_password 123456 </host>
debug 0
|
#启动数据库服务器上的agent
[root@master1 /]# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
[root@master2 /]# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
[root@slave1 /]# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
[root@slave2 /]# /etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: [ OK ]
#启动监控机上的monitor
[root@monitor ~]# /etc/init.d/mysql-mmm-monitor start
Starting MMM Monitor Daemon: [ OK ]
#在监控机上的查看各个agent的信息
[root@monitor ~]# mmm_control show
db1(192.168.21.11) master/ONLINE. Roles: reader(192.168.21.112), writer(192.168.21.101)
db2(192.168.21.12) master/ONLINE. Roles: reader(192.168.21.111)
db3(192.168.21.13) slave/ONLINE. Roles: reader(192.168.21.115)
db4(192.168.21.14) slave/ONLINE. Roles: reader(192.168.21.114)
数据库的HA完毕
实现读写分离机制
设置代理
在192.168.21.6上安装mysql-proxy
#解压lua,并且安装
tar zxf lua-5.1.2.tar.gz && cd lua-5.1.2 && make linux install
修改rw-splitting.lua这个脚本是用来控制读写分离的
#mysql-proxy中参数的含义
--proxy-read-only-backend-addresses为只读的机器
--proxy-backend-addresses为写入机器
--keepalive 支持keealived检测心跳
--proxy-lua-script=rw-splitting.lua运用rw-splitting.lua脚本进行读写分离
./mysql-proxy
--proxy-read-only-backend-addresses=192.168.21.102:3306
--proxy-read-only-backend-addresses=192.168.21.103:3306
--proxy-backend-addresses=192.168.21.101:3306
--keepalive
--proxy-lua-script=rw-splitting.lua
测试
mysql –uproxytest –pproxytest –P4040 h192.168.21.200
用keepalived实现mysql-proxy高可用
在proxy1和proxy2上安装
#下载keepalived
wget http://www.keepalived.org/software/keepalived-1.1.19.tar.gz
#解压keepalived
tar zxvf keepalived-1.1.19.tar.gz
cd keepalived-1.1.19
#配置初始化参数,安装在/usr/local/keepalived目录下
./configure --prefix=/usr/local/keepalived
#编译
make
#编译安装
make install
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
mkdir /etc/keepalived
cd /etc/keepalived/
配置keepalived,
[root@mysqlproxy1 /]# vi /etc/keepalived/keepalived.conf
bal_defs { #配置邮件通知 notification_email { [email]xulean@gmail.com[/email] } notification_email_from [email]xulean@gmail.com[/email] smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id test1 }
vrrp_instance VI_1 { state MASTER interface eth0 virtual_router_id 51 priority 100 advert_int 1 smtp_alert authentication { auth_type PASS auth_pass 123 } #配置虚拟IP virtual_ipaddress { 192.168.21.200/32 scope global eth0 } } |
#启动keepalived
[root@mysqlproxy1 /]# /etc/rc.d/init.d/keepalived start
Starting keepalived: [ OK ]
[root@mysqlproxy2 /]# /etc/rc.d/init.d/keepalived start
Starting keepalived: [ OK ]
#在两台mysql-proxy上看是否启动了虚拟IP
[root@mysqlproxy2 ~]# ip add|grep 192.168.21.200
inet 192.168.21.200/32 scope global eth0
虚拟IP绑定成功
对数据库进行调整(引擎)
为使数据库能支持事务,数据库主库采用innoDB引擎,slave库采用默认innodb引擎,在主库级别设置autocommit为0,备库autocommit为1,说明主库不能进行自动提交,而备库可以实现自动提交的功能,因为我们针对的是主的操作。