MySQL数据库优化

时间:2022-09-19 15:21:17

MySQL主从复制:

工作原理图:

MySQL数据库优化

主从复制的原理:

分为同步复制和异步复制,实际复制架构中大部分为异步复制。 复制的基本过程如下:

1).Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

2).Master接收到来自Slave的IO进程的请求后,通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息,返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置;  

3).Slave的IO进程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”;  

4).Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在自身执行。

环境描述

操作系统:CentOS6.3_x64

主服务器master:192.168.0.202

从服务器slave:192.168.0.203

一、mysql主从复制

1、主从安装mysql,版本一致

我们装的是mysql-5.5.30.tar.gz这里省略...请参考http://going.blog.51cto.com/7876557/1290440

2、修改master,slave服务器

12345678910111213 master服务器配置:vi /usr/local/mysql/etc/my.cnf[mysqld]server-id=202     #设置服务器唯一的id,默认是1,我们设置ip最后一段,slave设置203log-bin=mysql-bin # 启用二进制日志#binlog-ignore-db = mysql,information_schema  #忽略写入binlog的库 slave服务器配置:vi /usr/local/mysql/etc/my.cnf[mysqld]server-id=203replicate-do-db = abc     #只同步abc库slave-skip-errors = all   #忽略因复制出现的所有错误

3、重启主从服务器mysql

1 /etc/init.d/mysqld restart

4、在主服务器上建立帐户并授权slave

12 mysql> mysql -u root -p123.commysql> GRANT REPLICATION SLAVE ON *.* to ‘sync’@‘192.168.1.2’ identified by ‘1234.com’; #replication:复制

5、查看主数据库状态

123456 mysql> show master status;+------------------+----------+--------------+------------------+| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 | 263 |  |   |+------------------+----------+--------------+------------------+

6、配置从数据库

1234567 mysql> change master to -> master_host='192.168.0.202', -> master_user='sync', -> master_password='1234.com', -> master_log_file='mysql-bin.000002', -> master_log_pos=263;#Log和pos是master上随机获取的。这段也可以写到my.cnf里面。

7、启动slave同步进程并查看状态

1 mysql> start slave;

MySQL数据库优化

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES,才表明状态正常。

8、验证主从同步

在主mysql创建数据库abc,再从mysql查看已经同步成功!

1234567891011 mysql> create database abc;mysql> show databases;+--------------------+| Database   |+--------------------+| information_schema || abc    || mysql    || performance_schema |test    |+--------------------+


在slave启动报错:

“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’”

解决:报错的原因主要是slave设置master的二进制文件名或pos值不对应!

先flush logs;清空日志,在查看下主数据库的状态 show master status;看下日志文件名字和position值;

再在slave中,执行:CHANGE MASTER TO MASTER_LOG_FILE=‘二进制日志名’,MASTER_LOG_POS=值;

最后启动同步进程:start slave;


MySQL-Proxy实现MySQL读写分离提高并发负载:


工作拓扑:

MySQL数据库优化

MySQL Proxy有一项强大功能是实现“读写分离”,基本原理是让主数据库处理写方面事务,让从库处理SELECT查询。

Amoeba for MySQL是一款优秀的中间件软件,同样可以实现读写分离,负载均衡等功能,并且稳定性也高于MySQL Proxy,有兴趣的可以测试一下。

环境描述:

操作系统:CentOS6.3_x64

主服务器Master:192.168.0.202

从服务器Slave:192.168.0.203

调度服务器MySQL-Proxy:192.168.0.204

一、mysql主从复制

这里就省略了,请参考http://going.blog.51cto.com/7876557/1290431

二、mysql-proxy实现读写分离

1、安装mysql-proxy

实现读写分离是有lua脚本实现的,现在mysql-proxy里面已经集成,无需再安装

下载:http://dev.mysql.com/downloads/mysql-proxy/

12 tar zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gzmv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

2、配置mysql-proxy,创建主配置文件

123456789101112131415161718192021 cd /usr/local/mysql-proxymkdir lua #创建脚本存放目录mkdir logs #创建日志目录cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本vi /etc/mysql-proxy.cnf   #创建配置文件[mysql-proxy]user=root #运行mysql-proxy用户admin-username=proxy #主从mysql共有的用户admin-password=123.com #用户的密码proxy-address=192.168.0.204:4000 #mysql-proxy运行ip和端口,不加端口,默认4040proxy-read-only-backend-addresses=192.168.0.203 #指定后端从slave读取数据proxy-backend-addresses=192.168.0.202 #指定后端主master写入数据proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置log-level=info #定义log日志级别,由高到低分别有(error|warning|info|message|debug)daemon=true    #以守护进程方式运行keepalive=true #mysql-proxy崩溃时,尝试重启保存退出!chmod 660 /etc/mysql-porxy.cnf

3、修改读写分离配置文件

12345678 vi /usr/local/mysql-proxy/lua/rw-splitting.luaif not proxy.global.config.rwsplit then proxy.global.config.rwsplit = {  min_idle_connections = 1, #默认超过4个连接数时,才开始读写分离,改为1  max_idle_connections = 1, #默认8,改为1  is_debug = false }end

4、启动mysql-proxy

1234 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnfnetstat -tupln | grep 4000 #已经启动tcp 0 0 192.168.0.204:4000 0.0.0.0:* LISTEN 1264/mysql-proxy关闭mysql-proxy使用:killall -9 mysql-proxy

5、测试读写分离

1>.在主服务器创建proxy用户用于mysql-proxy使用,从服务器也会同步这个操作

1 mysql> grant all on *.* to 'proxy'@'192.168.0.204' identified by '123.com';

2>.使用客户端连接mysql-proxy

1 mysql -u proxy -h 192.168.0.204 -P 4000 -p123.com

创建数据库和表,这时的数据只写入主mysql,然后再同步从slave,可以先把slave的关了,看能不能写入,这里我就不测试了,下面测试下读的数据!

123 mysql> create table user (number INT(10),name VARCHAR(255));mysql> insert into test values(01,'zhangsan');mysql> insert into user values(02,'lisi');

3>.登陆主从mysq查看新写入的数据如下,

123456789 mysql> use test;Database changedmysql> select * from user;+--------+----------+| number | name |+--------+----------+| 1 | zhangsan || 2 | lisi |+--------+----------+

4>.再登陆到mysql-proxy,查询数据,看出能正常查询

123456789 mysql -u proxy -h 192.168.0.204 -P 4000 -p123.commysql> use test;mysql> select * from user;+--------+----------+| number | name |+--------+----------+| 1 | zhangsan || 2 | lisi |+--------+----------+

5>.登陆从服务器关闭mysql同步进程,这时再登陆mysql-proxy肯定会查询不出数据

1 slave stop;

6>.登陆mysql-proxy查询数据,下面看来,能看到表,查询不出数据

12345678910 mysql> use test;Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| user |+----------------+mysql> select * from user;ERROR 1146 (42S02): Table 'test.user' doesn't exist


配置成功!真正实现了读写分离的效果!



MySQL高可用集群之MySQL-MMM:


一、环境简述

1、工作逻辑图

MySQL数据库优化

2、MySQL-MMM优缺点

优点:高可用性,扩展性好,出现故障自动切换,对于主主同步,在同一时间只提供一台数据库写操作,保证的数据的一致性。

缺点:Monitor节点是单点,可以结合Keepalived实现高可用。

3、MySQL-MMM工作原理

MMM(Master-Master replication managerfor MysqlMysql主主复制管理器)是一套灵活的脚本程序,基于perl实现,用来对mysql replication进行监控和故障迁移,并能管理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)

mmm_mond监控进程,负责所有的监控工作,决定和处理所有节点角色活动。此脚本需要在监管机上运行。

mmm_agentd运行在每个mysql服务器上的代理进程,完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行。

mmm_control一个简单的脚本,提供管理mmm_mond进程的命令。

mysql-mmm的监管端会提供多个虚拟IP(VIP),包括一个可写VIP,多个可读VIP,通过监管的管理,这些IP会绑定在可用mysql之上,当某一台mysql宕机时,监管会将VIP迁移至其他mysql。

在整个监管过程中,需要在mysql中添加相关授权用户,以便让mysql可以支持监理机的维护。授权的用户包括一个mmm_monitor用户和一个mmm_agent用户,如果想使用mmm的备份工具则还要添加一个mmm_tools用户。

4、需求描述

操作系统:CentOS 6.5_X64

数据库:MySQL 5.1

MMM:MySQL-MMM 2.2.1

数据库分配:

function

ip

hostname

server id

monitoring host

192.168.0.201

monitor

master 1

192.168.0.202

db1

1

master 2

192.168.0.203

db2

2

slave 1

192.168.0.204

db3

3

slave 2

192.168.0.205

db4

4

虚拟IP地址(VIP):

ip

role

192.168.0.211

writer

192.168.0.212

reader

192.168.0.213

reader

数据库同步需要的用户:

function

description

privileges

monitor user

mmm监控用于对mysql服务器进程健康检查

REPLICATION  CLIENT

agent user

mmm代理用来更改只读模式,复制的主服务器等

SUPER,  REPLICATION CLIENT, PROCESS

replication user

用于复制

REPLICATION SLAVE


二、db1,db2,db3和db4安装数据库并配置

123 [root@db1 ~]# yum install mysql-server mysql[root@db1 ~]# service mysqld start[root@db1 ~]# mysqladmin -u root password 123.com
12345678910111213 [root@db1 ~]# vi /etc/my.cnf   #添加如下[mysqld]binlog-do-db=test           #需要记录二进制日志的数据库,多个用逗号隔开binlog-ignore-db=mysql,information_schema  #不需要记录二进制日志的数据库,多个用逗号隔开auto_increment_increment=2  #字段一次递增多少auto_increment_offset=1     #自增字段的起始值,值设置不同replicate-do-db=test        #同步的数据库,多个写多行replicate-ignore-db = information_schema #不同步的数据库,多个写多行server_id = 1               #每台设置不同log_bin = mysql-binlog_slave_updates           #当一个主故障,另一个立即接管sync-binlog=1               #每条自动更新,安全性高,默认是0[root@db1 ~]# service mysqld restart

三、配置db1和db2主主同步

#先查看下log bin日志和pos值位置

MySQL数据库优化

db1配置如下:

12345678910 [root@db1 ~]# mysql -u root -p123.commysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication';mysql> flush privileges;mysql> change master to    -> master_host='192.168.0.203',    -> master_user='replication',    -> master_password='replication',    -> master_log_file='mysql-bin.000002',-> master_log_pos=106;  #对端状态显示的值mysql> start slave;     #启动同步

db2配置如下:

12345678910 [root@db2 ~]# mysql -u root -p123.commysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED BY 'replication';mysql> flush privileges;mysql> change master to    -> master_host='192.168.0.202',    -> master_user='replication',    -> master_password='replication',    -> master_log_file='mysql-bin.000002',    -> master_log_pos=106;mysql> start slave;  #启动同步

#主主同步配置完毕,查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功。

MySQL数据库优化

在db2插入数据测试下:

MySQL数据库优化

在db2查看是否同步成功:

MySQL数据库优化

可以看到已经成功同步过去,同样在db2插入到user表数据,也能同步过去。我们的双主就成功了,开始做主从复制。

四、配置slave1和slave2做为master1的从库

#先看下master1状态值

MySQL数据库优化

在slave1和slave2分别执行:

123456 mysql> change master to    -> master_host='192.168.0.202',    -> master_user='replication',    -> master_password='replication',    -> master_log_file='mysql-bin.000002',    -> master_log_pos=434;

在slave1和slave2查看如下说明主从复制成功。但是数据没过来,这是因为主从复制原理只同步配置完后的增删改记录,以后的数据是不能同步的,我们可以把主的数据库备份了,然后在送数据库还原。

MySQL数据库优化

12345 [root@db1 ~]# mysqldump -uroot -p123.com test > test.sql[root@db1 ~]# scp test.sql root@192.168.0.204:/root/[root@db1 ~]# scp test.sql root@192.168.0.205:/root/[root@db3 ~]# mysql -u root -p123.com test < test.sql[root@db4 ~]# mysql -u root -p123.com test < test.sql

五、MySQL-MMM安装配置

CentOS默认没有mysql-mmm软件包,官方推荐使用epel的网络源,五台都安装epel:

rpm -ivh http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm

1、monitor节点安装

[root@monitor ~]#  yum -y install mysql-mmm-monitor

2、四台db节点安装

[root@db1 ~]# yum -y install mysql-mmm-agent

3、在四台db节点授权monitor访问

123 [root@db ~]# mysql -u root -p123.commysql> GRANT REPLICATIONCLIENT ON *.* TO 'mmm_monitor'@'192.168.0.%' IDENTIFIED BY 'monitor';                   mysql> GRANT SUPER,REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'192.168.0.%' IDENTIFIED BY'agent';

4、修改mmm_common.conf文件(五台相同)

123456789101112131415161718192021222324252627282930313233343536373839 [root@monitor ~]# vi /etc/mysql-mmm/mmm_common.confactive_master_role      writer<host default>    cluster_interface       eth0    pid_path               /var/run/mysql-mmm/mmm_agentd.pid    bin_path                /usr/libexec/mysql-mmm/    replication_user        replication    replication_password    replication    agent_user              mmm_agent    agent_password          agent</host><host db1>    ip     192.168.0.202    mode   master    peer   db2</host><host db2>    ip     192.168.0.203    mode   master    peer   db1</host><host db3>    ip     192.168.0.204    mode   slave</host><host db4>    ip     192.168.0.205    mode   slave</host><role writer>    hosts  db1, db2    ips    192.168.0.211    mode   exclusive    #只有一个host可以writer,一般写操作是这个模式</role><role reader>    hosts  db3, db4    ips    192.168.0.212,192.168.0.213    mode   balanced     #多个host可以reader,一般读操作是这个模式</role>

#通过scp命令传送到其他四台:

scp /etc/mysql-mmm/mmm_common.conf root@192.168.0.202/203/204/205:/etc/mysql-mmm/

5、修改四台db代理端mmm_agent.conf文件

123 [root@db ~]# vi /etc/mysql-mmm/mmm_agent.confinclude mmm_common.confthis db1   #分别修改为本机的主机名,即db1、db2、db3和db4

6、修改管理端mmm_mon.conf文件

12345678910111213141516 [root@monitor ~]# vi /etc/mysql-mmm/mmm_mon.confinclude mmm_common.conf<monitor>    ip                  127.0.0.1    pid_path            /var/run/mysql-mmm/mmm_mond.pid    bin_path            /usr/libexec/mysql-mmm    status_path         /var/lib/mysql-mmm/mmm_mond.status    ping_ips            192.168.0.202,192.168.0.203,192.168.0.204,192.168.0.205#真实数据库IP,来检测网络是否正常    auto_set_online     10  #恢复后自动设置在线的时间</monitor><host default>    monitor_user        mmm_monitor    monitor_password    monitor</host>debug 0

六、启动MySQL-MMM

1、db代理端启动

[root@db1 ~]# /etc/init.d/mysql-mmm-agent start

[root@db1 ~]# chkconfigmysql-mmm-agent on

2、monitor管理端启动

[root@monitor ~]# /etc/init.d/mysql-mmm-monitor start

[root@monitor ~]# chkconfigmysql-mmm-monitor on

七、测试集群

1、查看集群状态

MySQL数据库优化

由此看来,主db1是对外一个写入的角色,但不真正提供只写,要想实现读写分离还需要结合amoeba。后面的虚拟IP是真正来访问Mysql数据库的。

2、故障转移切换

停掉主db1数据库,等待几秒后,可以看到数据库db1处于HARD_OFFLINE(离线状态),检测不到数据库的存在。

MySQL数据库优化

启动主db1数据库后,可以看到数据库db1处于AWAITING_RECOVER(恢复状态),几秒后将恢复在线状态。模拟Slave故障也是如此,DOWN掉一个,虚拟IP会全部在另一台正常数据库上。

至此,MySQL-MMM架构配置完毕。后续会写在此基础上实现读写分离、负载均衡机制。如图:

MySQL数据库优化



MySQL高可用性之Keepalived+Mysql(双主热备):


环境描述:

OS:CentOS6.5_X64

MASTER:192.168.0.202

BACKUP:192.168.0.203

VIP:192.168.0.204

1、配置两台Mysql主主同步

123456789101112 [root@master ~]# yum install mysql-server mysql -y[root@master ~]# service mysqld start[root@master ~]# mysqladmin -u root password 123.com[root@master ~]# vi /etc/my.cnf  #开启二进制日志,设置id[mysqld]server-id = 1                    #backup这台设置2log-bin = mysql-binbinlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库auto-increment-increment = 2             #字段变化增量值auto-increment-offset = 1              #初始字段ID为1slave-skip-errors = all                       #忽略所有复制产生的错误     [root@master ~]# service mysqld restart

#先查看下log bin日志和pos值位置

MySQL数据库优化

master配置如下:

12345678910 [root@ master ~]# mysql -u root -p123.commysql> GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED  BY 'replication';mysql> flush  privileges;mysql> change  master to    ->  master_host='192.168.0.203',    ->  master_user='replication',    ->  master_password='replication',    ->  master_log_file='mysql-bin.000002',    ->  master_log_pos=106;  #对端状态显示的值mysql> start  slave;         #启动同步

backup配置如下:

12345678910 [root@backup ~]#  mysql -u root -p123.commysql> GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED  BY 'replication';mysql> flush  privileges;mysql> change  master to    ->  master_host='192.168.0.202',    ->  master_user='replication',    ->  master_password='replication',    ->  master_log_file='mysql-bin.000002',    ->  master_log_pos=106;mysql> start  slave;

#主主同步配置完毕,查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功。

MySQL数据库优化

在master插入数据测试下:

MySQL数据库优化

在backup查看是否同步成功:

MySQL数据库优化

可以看到已经成功同步过去,同样在backup插入到user表数据,一样同步过去,双主就做成功了。

2、配置keepalived实现热备

[root@backup ~]# yum install -y pcre-devel openssl-devel popt-devel #安装依赖包

12345 [root@master ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz[root@master ~]# tar zxvf keepalived-1.2.7.tar.gz[root@master ~]# cd keepalived-1.2.7[root@master ~]#./configure --prefix=/usr/local/keepalivedmake && make install

#将keepalived配置成系统服务

12345 [root@master ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/[root@master ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/[root@master ~]# mkdir /etc/keepalived/[root@master ~]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/[root@master ~]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
1234567891011121314151617181920212223242526272829303132333435363738394041 [root@master ~]# vi /etc/keepalived/keepalived.conf! Configuration File forkeepalivedglobal_defs {notification_email {test@sina.com }notification_email_from  admin@test.comsmtp_server 127.0.0.1smtp_connect_timeout 30router_id MYSQL_HA      #标识,双主相同 }vrrp_instance VI_1 { state BACKUP           #两台都设置BACKUP interface eth0 virtual_router_id 51       #主备相同 priority 100           #优先级,backup设置90 advert_int 1 nopreempt             #不主动抢占资源,只在master这台优先级高的设置,backup不设置 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.0.204 }}virtual_server 192.168.0.204 3306 { delay_loop 2 #lb_algo rr              #LVS算法,用不到,我们就关闭了 #lb_kind DR              #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器 protocol TCP real_server 192.168.0.202 3306 {   #检测本地mysql,backup也要写检测本地mysql weight 3 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时,执行此脚本,杀死keepalived实现切换 TCP_CHECK { connect_timeout 3    #连接超时 nb_get_retry 3       #重试次数 delay_before_retry 3 #重试间隔时间  }}
12345 [root@master ~]# vi /usr/local/keepalived/mysql.sh#!/bin/bashpkill keepalived[root@master ~]# chmod +x /usr/local/keepalived/mysql.sh[root@master ~]# /etc/init.d/keepalived start


#backup服务器只修改priority为90、nopreempt不设置、real_server设置本地IP。


#授权两台Mysql服务器允许root远程登录,用于在其他服务器登陆测试!

mysql> grant all on *.* to'root'@'192.168.0.%' identified by '123.com';

mysql> flush privileges;

3、测试高可用性

1、通过Mysql客户端通过VIP连接,看是否连接成功。

2、停止master这台mysql服务,是否能正常切换过去,可通过ip addr命令来查看VIP在哪台服务器上。

MySQL数据库优化

3、可通过查看/var/log/messges日志,看出主备切换过程

4、master服务器故障恢复后,是否主动抢占资源,成为活动服务器。