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服务器

master服务器配置：
vi /usr/local/mysql/etc/my.cnf
[mysqld]
server-id=202     #设置服务器唯一的id，默认是1，我们设置ip最后一段，slave设置203
log-bin=mysql-bin # 启用二进制日志
#binlog-ignore-db = mysql,information_schema  #忽略写入binlog的库

slave服务器配置：
vi /usr/local/mysql/etc/my.cnf
[mysqld]
server-id=203
replicate-do-db = abc     #只同步abc库
slave-skip-errors = all   #忽略因复制出现的所有错误

3、重启主从服务器mysql

/etc/init.d/mysqld restart

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

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

5、查看主数据库状态

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

6、配置从数据库

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同步进程并查看状态

mysql> start slave;

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

8、验证主从同步

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

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 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/

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

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

cd /usr/local/mysql-proxy
mkdir 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和端口，不加端口，默认4040
proxy-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、修改读写分离配置文件

vi /usr/local/mysql-proxy/lua/rw-splitting.lua
if 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

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
netstat -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使用，从服务器也会同步这个操作

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

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

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

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

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查看新写入的数据如下，

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

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

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

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

slave stop；

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

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

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

MySQL高可用集群之MySQL-MMM:

一、环境简述

1、工作逻辑图

2、MySQL-MMM优缺点

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

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

3、MySQL-MMM工作原理

MMM(Master-Master replication managerfor Mysql，Mysql主主复制管理器)是一套灵活的脚本程序，基于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安装数据库并配置

[root@db1 ~]# yum install mysql-server mysql
[root@db1 ~]# service mysqld start
[root@db1 ~]# mysqladmin -u root password 123.com

[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-bin
log_slave_updates           #当一个主故障，另一个立即接管
sync-binlog=1               #每条自动更新，安全性高，默认是0
[root@db1 ~]# service mysqld restart

三、配置db1和db2主主同步

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

db1配置如下：

[root@db1 ~]# mysql -u root -p123.com
mysql> 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配置如下：

[root@db2 ~]# mysql -u root -p123.com
mysql> 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说明主主同步成功。

在db2插入数据测试下：

在db2查看是否同步成功：

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

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

#先看下master1状态值

在slave1和slave2分别执行：

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

[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访问

[root@db ~]# mysql -u root -p123.com
mysql> 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文件（五台相同）

[root@monitor ~]# vi /etc/mysql-mmm/mmm_common.conf
active_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文件

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

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

[root@monitor ~]# vi /etc/mysql-mmm/mmm_mon.conf
include 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、查看集群状态

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

2、故障转移切换

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

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

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

MySQL高可用性之Keepalived+Mysql（双主热备）:

环境描述：

OS：CentOS6.5_X64

MASTER：192.168.0.202

BACKUP：192.168.0.203

VIP：192.168.0.204

1、配置两台Mysql主主同步

[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这台设置2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库
auto-increment-increment = 2             #字段变化增量值
auto-increment-offset = 1              #初始字段ID为1
slave-skip-errors = all                       #忽略所有复制产生的错误
[root@master ~]# service mysqld restart

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

master配置如下：

[root@ master ~]# mysql -u root -p123.com
mysql> 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配置如下：

[root@backup ~]#  mysql -u root -p123.com
mysql> 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说明主主同步成功。

在master插入数据测试下：

在backup查看是否同步成功：

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

2、配置keepalived实现热备

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

[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/keepalived
make && make install

#将keepalived配置成系统服务

[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/

[root@master ~]# vi /etc/keepalived/keepalived.conf
! Configuration File forkeepalived
global_defs {
notification_email {
[e-mail]
 }
notification_email_from  [e-mail]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_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 #重试间隔时间
  }
}

[root@master ~]# vi /usr/local/keepalived/mysql.sh
#!/bin/bash
pkill 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在哪台服务器上。

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

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