mysql应用实践 – 乘风破浪

mysql多实例

mysql多实例介绍

mysql多实例，简单的说，就是在一台服务器上开启多个不同的mysql服务端口（如3306，3307），运行多个mysql服务进程。这些服务进程通过不同的socket监听不同的服务端口，来提供各自的服务。

这些mysql实例共用一套mysql安装程序，使用不同的my.cnf配置文件（也可以相同）、启动程序（也可以相同）、数据文件。在提供服务时，mysql多实例在逻辑上看来是各自独立的，各个实例之间根据配置文件的设定值，来取得服务器的相关硬件资源。

其实，很多服务都可以配置多实例的，在门户网站用的更广泛，例如Nginx、Apache、haproxy、redis、memcache等。

mysql多实例的特点

有效的利用服务器资源:当单个服务器资源有剩余时，可以充分利用剩余的服务器资源来提供更多的服务。
节约服务器资源:当公司资金紧张，但是数据库需要各自提供独立服务，而且需要主从同步等技术时，使用多实例就最好了。
出现资源互相抢占问题:当某个实例服务并发很高或者有慢查询时，会消耗服务器更多的内存、CPU、磁盘IO等资源，这时就会导致服务器上的其它实例提供访问的质量下降，出现服务器资源互相抢占的现象。

mysql多实例应用场景

资金紧张型公司的选择

当公司业务访问量不太大，又舍不得花钱，但同时又希望不同业务的数据库服务各自独立，而且需要主从同步进行等技术提供备份或读写分离服务时，使用多实例是最好不过的。

并发访问不是特别大的业务

当公司业务访问量不太大，服务器资源基本闲置的比较多，这是就很适合多实例的应用。如果对SQL语句优化的好，多实例是一个很值得使用的技术。即使并发很大，只要合理分配好系统资源，也不会有太大问题。

门户网站应用mysql多实例场景

百度搜索引擎的数据库就是多实例，一般是从库，例如某部门例子，IBM48核CPU，内存96G，跑3-4个实例；sina网也是用的多实例，内存48G左右。门户网站使用多实例的目的是配硬件好的服务器，节省IDC机柜空间，同时，跑多实例让硬件资源不浪费。

问1:你们的数据库是多实例，跑几个实例？CPU、内存、磁盘类型、RAID类型分别如何配置的？

sina dba领导回答：一般在1-4个实例之间居多。1-2个最多，因为大业务占用的机器比较多。机器是R510居多，CPU是E5210，48G内存，磁盘12*300G SAS，做RAID10。

问2：sina是编译安装DB？还是二进制的多？还是什么方式？

sina dba领导回答：编译安装后，做成RPM包，统一使用

mysql-5.5.48多实例部署

5多实例部署方法

mysql-5.5多实例部署方法一个是通过多个配置文件启动多个不同进程的方法，第二个是使用官方自带的mysqld_multi来实现，单一配置文件、单一启动程序多实例部署方案。在同一个配置文件中，利用[mysqld1]、[mysqld2]、[mysqld3]、[mysqld4]标签实现不同实例的差异化配（不推荐）。

第二个方案耦合度太高，一个配置文件，不好管理。工作开发和运维的统一原则：降低耦合度

准备环境

安装依赖

yum install ncurses-devel libaio-devel -y

cmake官方网站：https://cmake.org/files/

cd /server/tools/

wget https://cmake.org/files/v2.8/cmake-2.8.8.tar.gz

tar zxf cmake-2.8.8.tar.gz

cd cmake-2.8.8

./configure

gmake

gmake install

创建用户和组

useradd mysql -s /sbin/nologin -M

安装mysql

cd /server/tools/

wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.48.tar.gz

tar zxvf mysql-5.5.48.tar.gz

cd mysql-5.5.48

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.48 \

-DMYSQL_DATADIR=/application/mysql-5.5.48/data \

-DMYSQL_UNIX_ADDR=/application/mysql-5.5.48/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

-DENABLED_LOCAL_INFILE=ON \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \

-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \

-DWITH_FAST_MUTEXES=1 \

-DWITH_ZLIB=bundled \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_READLINE=1 \

-DWITH_EMBEDDED_SERVER=1 \

-DWITH_DEBUG=0

配置结束提示

— Configuring done

— Generating done

— Build files have been written to: /server/tools/mysql-5.5.48

提示，编译时可配置的选项很多，具体可参考结尾附录或官方文档：

make

#[100%] Built target my_safe_process

make install

ln -s /application/mysql-5.5.48/ /application/mysql

如果上述操作未出现错误，则MySQL5.5.48软件cmake方式的安装就算成功

公司生产环境编译参数

cmake -DCMAKE_INSTALL_PREFIX=/home/mysql/mysql \

-DMYSQL_UNIX_ADDR=/home/mysql/mysql/tmp/mysql.sock \

-DDEFAULT_CHARSET=utf8mb4 \

-DDEFAULT_COLLATION=utf8mb4_unicode_ci \

-DWITH_EXTRA_CHARSETS:STRING=binary,utf8mb4,gbk,gb2312,utf8,latin1 \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_MEMORY_STORAGE_ENGINE=1 \

-DWITH_READLINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_DATADIR=/home/mysql/mysql/var \

-DMYSQL_USER=mysql \

-DMYSQL_TCP_PORT=5858 \

-DSYSCONFDIR=/home/mysql/mysql/etc \

-DINSTALL_SHAREDIR=share \

-DMYSQL_USER=mysql \

-DWITH_DEBUG=0

单实例数据库初始化（多实例可以不用操作此步）

cd /server/tools/mysql-5.5.48/support-files/

/bin/cp my-small.cnf /etc/my.cnf

chown -R mysql.mysql /application/mysql

/application/mysql/scripts/mysql_install_db –basedir=/application/mysql –datadir=/application/mysql/data –user=mysql

cd /server/tools/mysql-5.5.48/support-files/

cp mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

chkconfig –add mysqld

chkconfig mysqld on

/etc/init.d/mysqld start

mkdir -p /data/{3306,3307}/data

tree -L 2 /data/

创建mysql多实例的配置文件

mysql数据库默认为用户提供了多个配置文件模板，用户可以根据服务器的大小来选择

cnf配置文件

cd /data/3306/

[root@mysql 3306]# cat my.cnf

[client]

port = 3306

socket = /data/3306/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user = mysql

port = 3306

socket = /data/3306/mysql.sock

basedir = /application/mysql

datadir = /data/3306/data

open_files_limit = 1024

back_log = 600

max_connections = 800

max_connect_errors = 3000

table_cache = 614

external-locking = FALSE

max_allowed_packet =8M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size = 100

thread_concurrency = 2

query_cache_size = 2M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

#default_table_type = InnoDB

thread_stack = 192K

#transaction_isolation = READ-COMMITTED

tmp_table_size = 2M

max_heap_table_size = 2M

long_query_time = 1

#log_long_format

#log-error = /data/3306/error.log

#log-slow-queries = /data/3306/slow.log

pid-file = /data/3306/mysql.pid

log-bin = /data/3306/mysql-bin

relay-log = /data/3306/relay-bin

relay-log-info-file = /data/3306/relay-log.info

binlog_cache_size = 1M

max_binlog_cache_size = 1M

max_binlog_size = 2M

expire_logs_days = 7

key_buffer_size = 16M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

bulk_insert_buffer_size = 1M

#myisam_sort_buffer_size = 1M

#myisam_max_sort_file_size = 10G

#myisam_max_extra_sort_file_size = 10G

#myisam_repair_threads = 1

#myisam_recover

lower_case_table_names = 1

skip-name-resolve

slave-skip-errors = 1032,1062

replicate-ignore-db=mysql

server-id = 1

innodb_additional_mem_pool_size = 4M

innodb_buffer_pool_size = 32M

innodb_data_file_path = ibdata1:128M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 2M

innodb_log_file_size = 4M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[mysqldump]

quick

max_allowed_packet = 2M

[mysqld_safe]

log-error=/data/3306/mysql_oldboy3306.err

pid-file=/data/3306/mysqld.pid

mysql多实例启动脚本

[root@mysql mysql]# cat /data/3306/mysql

#!/bin/sh

################################################

#this scripts is created by oldboy at 2007-06-09

#oldboy QQ:31333741

#site:http://www.etiantian.org

#blog:http://oldboy.blog.51cto.com

#oldboy trainning QQ group: 208160987 226199307 44246017

################################################

#init

port=3306

mysql_user=”root”

mysql_pwd=”123456″

CmdPath=”/application/mysql/bin”

mysql_sock=”/data/${port}/mysql.sock”

#startup function

function_start_mysql()

{

if [ ! `netstat -lntup|grep “$port”|wc -l` -eq 1 ];then

printf “Starting MySQL…\n”

/bin/sh ${CmdPath}/mysqld_safe –defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

else

printf “MySQL is running…\n”

exit

}

#stop function

function_stop_mysql()

{

if [ ! -e “$mysql_sock” ];then

printf “MySQL is stopped…\n”

exit

else

printf “Stoping MySQL…\n”

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

printf “Restarting MySQL…\n”

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

printf “Usage: /data/${port}/mysql {start|stop|restart}\n”

esac

实例3307的配置文件cnf及mysql启动文件我们直接复制实例3306的my.cnf文件及mysql启动文件，然后通过sed命令把该文件中的3306修改为3307即可。

sed -e ‘s/3306/3307/g’ /data/3306/my.cnf >/data/3307/my.cnf

sed -e ‘s/3306/3307/g’ /data/3306/mysql >/data/3307/mysql

sed -i ‘s#server-id = 1#server-id = 7#g’ /data/3307/my.cnf

特别说明:
本文多实例启动文件的启动mysql服务实质命令

./bin/mysqld_safe –defaults-file=/data/3306/my.cnf &>/dev/null &

./bin/mysqld_safe –defaults-file=/data/3307/my.cnf &>/dev/null &

本文多实例启动文件的停止mysql服务实质命令

./bin/mysqladmin -uroot -p123456 -S /data/3306/mysql.sock shutdown

./bin/mysqladmin -uroot -p123456 -S /data/3307/mysql.sock shutdown

修改mysql实例的数据库目录及启动文件权限

chown -R mysql:mysql /data

find /data -type f -name “mysql”|xargs chmod 700

find /data -type f -name “mysql”|xargs ls -l

添加环境变量

法一ln -s /application/mysql/bin/* /usr/local/sbin/

法二echo ‘export PATH=”/application/mysql/bin/:$PATH”‘ >>/etc/profile

source /etc/profile生效

特别强调：务必把mysql命令路径放在$PATH前面，否则有可能使用yum安装的mysql命令而导致错误

初始化mysql数据库

cd /application/mysql/scripts/

./mysql_install_db –basedir=/application/mysql –datadir=/data/3306/data –user=mysql

./mysql_install_db –basedir=/application/mysql –datadir=/data/3307/data –user=mysql

实质是生成内置管理的系统的mysql及performance_schema库

启动mysql

[root@mysql ~]# /data/3306/mysql start

Starting MySQL…

方法二：mysqld_safe –defaults-file=/data/3306/my.cnf &

[root@mysql ~]# /data/3307/mysql start

Starting MySQL…

[root@mysql ~]# netstat -lnt|grep 330

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN

启动错误：

[root@mysql 3306]# /data/3306/mysql start

Starting MySQL…

[root@mysql 3306]# 160330 11:04:11 mysqld_safe Logging to ‘/data/3306/mysql_oldboy3306.err’.

160330 11:04:11 mysqld_safe Starting mysqld daemon with databases from /data/3306/data

160330 11:04:11 mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended

解决：chown -R mysql:mysql /data

设置mysql密码（命令行界面）

[root@mysql ~]# mysqladmin -u root password ‘123456’ -S /data/3306/mysql.sock

[root@mysql ~]# /data/3306/mysql stop

Stoping MySQL…

登录mysql

mysql -uroot –p123456 -S /data/3306/mysql.sock

如何再增加一个mysql实例

mkdir -p /data/3308/data

cp /data/3306/my.cnf /data/3308/

cp /data/3306/mysql /data/3308/

sed -i ‘s#3306#3308#g’ /data/3308/my.cnf

sed -i ‘s#server-id = 1#server-id = 8#g’ /data/3308/my.cnf

sed -i ‘s#3306#3308#g’ /data/3308/mysql

chown -R mysql:mysql /data/3308

chmod 700 /data/3308/mysql

/application/mysql/scripts/mysql_install_db –basedir=/application/mysql –datadir=/data/3308/data –user=mysql

/data/3308/mysql start

netstat -lnt|grep 3308

mysqladmin -u root password ‘12345678’ -S /data/3308/mysql.sock

sed -i ’13 s#123456#12345678#g’ /data/3308/mysql

sed -n ’13p’ /data/3308/mysql

/data/3308/mysql stop

登录mysql多实例

本地登录：

多实例本地登录一般通过socket文件来指定具体登录到哪个实例，此文件具体位置是在mysql编译过程或者my.cnf文件中指定的。登录多实例数据库时，我们需要加入该实例的socket文件，才能正常登录。例如：

mysql -uroot -p12345678 -S /data/3308/mysql.sock

mysql -uroot -p -S /data/3308/mysql.sock

远程登录

远程登录mysql多实例其中的一个实例，通过TCP端口来指定所要登录的mysql实例，此端口的配置是在mysql配置文件my.cnf指定的。例如：

grant all on *.* to root@’192.168.10.%’ identified by ‘123456’;

mysql -uroot -p -h 192.168.10.106 -P 3308

注意：必须提前授权,并且客户端必须安装mysql命令。

mysql主从复制

文件级别的异机同步方案

scp/sftp/nc命令可以实现远程数据同步
搭建ftp/http/svn/nfs服务器，然后在客户端上也可以把数据同步到服务器
搭建Samba文件共享服务器，然后在客户端上也可以把数据同步到服务器
利用rsync/csync2/unnion等均可实现多机同步

以上方式如果结合定时任务或者inotify，sersync等功能，可以实现定时以及实时的数据同步

扩展思想：文件级别也可以利用mysql，mongodb等软件作为容器实现
扩展思想：程序同时向两个服务器同时写数据，双写就是一个同步机制

特点:简单，方便，效率和文件系统级别要差一些，但是被同步的节点可以提供访问

软件的自身同步机制（mysql，oracle，mongdb……）文件放到数据库，同步到从库，再把文件拿出来
DRDB

mysql主从同步介绍

mysql主从同步复制方案和scp/rsync等文件级别同步是类似的，都是数据的传输。只不过mysql无需借助第三方工具，而是其自带的复制功能，有一点不同，mysql的主从复制并不是数据库磁盘上的文件直接拷贝复制，而是通过逻辑的binlog日志复制到同步数据本地然后读取里面的SQL语句应用到数据路的过程。

mysql数据库支持单向、双向、链式级联等不同场景的复制，在复制过程中一台服务器充当主服务器，而一个或其他的服务器充当从服务器（slave）。

复制可以是单向：M==>S,也可以是双向M<==>M,当然也可以多M环状同步等。如果设置了链式级联复制，那么，从（slave）服务器本身除了充当服务器外，也会同时充当下面服务器的主服务器。链式级联复制类似A–>B–>C–>D的复制形式。

MYSQL主从同步架构图：

mysql主从复制原理介绍

mysql主从复制是一个异步的、逻辑的复制过程（虽然一般情况下感觉是实时同步的），数据库数据从一个mysql数据库（master）复制到另一个mysql数据库（slave）。在master与slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和IO线程）在slave端，另一个IO线程在master端。

要实现mysql的主从复制首先必须打开master端的binlog（MySQL-bin.xxxxx）功能，否则就无法实现主从复制。因为整个复制过程实际上就是slave从master端获取binlog日志，然后再在slave自身上以相同顺序执行获取的binlog日志中所记载的各种操作

MySQL 复制的基本过程如下：

Slave上面的IO线程连接上 Master上面的IO线程，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
Master 接收到来自 Slave的IO线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 Binary Log 文件的名称以及在 Binary Log 中的位置;
Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
Slave的SQL线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。

实际上，在老版本中，MySQL 的复制实现在 Slave 端并不是由 SQL 线程和 IO 线程这两个线程共同协作而完成的，而是由单独的一个线程来完成所有的工作。但是 MySQL 的工程师们很快发现，这样做存在很大的风险和性能问题，主要如下：

首先，如果通过一个单一的线程来独立实现这个工作的话，就使复制 Master 端的，Binary Log日志，以及解析这些日志，然后再在自身执行的这个过程成为一个串行的过程，性能自然会受到较大的限制，这种架构下的 Replication 的延迟自然就比较长了。

其次，Slave 端的这个复制线程从 Master 端获取 Binary Log 过来之后，需要接着解析这些内容，还原成 Master 端所执行的原始 Query，然后在自身执行。在这个过程中，Master端很可能又已经产生了大量的变化并生成了大量的 Binary Log 信息。如果在这个阶段 Master 端的存储系统出现了无法修复的故障，那么在这个阶段所产生的所有变更都将永远的丢失，无法再找回来。这种潜在风险在Slave 端压力比较大的时候尤其突出，因为如果 Slave 压力比较大，解析日志以及应用这些日志所花费的时间自然就会更长一些，可能丢失的数据也就会更多。

所以，在后期的改造中，新版本的 MySQL 为了尽量减小这个风险，并提高复制的性能，将 Slave 端的复制改为两个线程来完成，也就是前面所提到的 SQL 线程和 IO 线程。最早提出这个改进方案的是Yahoo!的一位工程师“Jeremy Zawodny”。通过这样的改造，这样既在很大程度上解决了性能问题，缩短了异步的延时时间，同时也减少了潜在的数据丢失量。

当然，即使是换成了现在这样两个线程来协作处理之后，同样也还是存在 Slave 数据延时以及数据丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事务中，这些问题都是存在的。

如果要完全避免这些问题，就只能用 MySQL 的 Cluster 来解决了。不过 MySQL的 Cluster 知道笔者写这部分内容的时候，仍然还是一个内存数据库的解决方案，也就是需要将所有数据包括索引全部都 Load 到内存中，这样就对内存的要求就非常大的大，对于一般的大众化应用来说可实施性并不是太大。当然，在之前与 MySQL 的 CTO David 交流的时候得知，MySQL 现在正在不断改进其 Cluster 的实现，其中非常大的一个改动就是允许数据不用全部 Load 到内存中，而仅仅只是索引全部 Load 到内存中，我想信在完成该项改造之后的 MySQL Cluster 将会更加受人欢迎，可实施性也会更大。

mysql主从复制实践

mysql主从同步配置步骤

准备两台数据库环境，或单台单实例环境，确认能正常启动
配置cnf文件，主库配置log-bin和server-id参数，从库配置server-id（不能和主库相同），一般不开启log-bin功能。重启生效
建立用于主从复制的账号，授权replication slave权限
对主数据库锁表只读，不能关闭窗口
新开窗口，mysqldump备份数据库
解锁主库
使用cp、scp、rsync等命令将备份移动到从库
还原从库
从库执行change master to语句
从库开启同步开关：slave start;
从库show slave status\G检查同步状态并测试

主从复制实践准备

定义主从复制需要的角色

主库及从库IP及端口：

主库：168.10.106:3306
从库：168.10.106:3307

提示：一般常规做主从复制在不同的机器上，并且监听的端口均为默认的3306，实现方法大致相同。

实现mysql主从复制必备条件

sed -i ‘s#log-bin#\#log-bin#g’ /data/3307/my.cnf

[root@mysql ~]# egrep “log-bin|server-id” /data/{3306,3307}/my.cnf

/data/3306/my.cnf:log-bin = /data/3306/mysql-bin

/data/3306/my.cnf:server-id = 1

/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin

/data/3307/my.cnf:server-id = 3

show variables; #查看mysql配置文件的参数

show variables like “log_bin”; #查看bin-log状态

show variables like “server_id”; #查看server_id

建立用于主从复制的账号

在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中

mysql -uroot -p123456 -S /data/3306/mysql.sock

grant replication slave on *.* to ‘rep’@’192.168.80.%’ identified by ‘123456’;

flush privileges;

select user,host from mysql.user;

show grants for rep@’192.168.80.%’;

注意：如果使用rep登录mysql

mysql -urep -p123456 –P3306 -h 192.168.80.106

注：使用mysql -urep -p123456 -h 192.168.80.105命令授权后，两种登陆方式

mysql -urep -p123456 -h 192.168.80.105 #会成功登陆

[root@mysql ~]# mysql -urep -p123456 -S/data/3306/mysql.sock #登陆失败

ERROR 1045 (28000): Access denied for user ‘rep’@‘localhost’ (using password: YES)

对主数据库锁表只读

flush table with read lock; #注意：锁表后不能退出，另开窗口备份，否则锁表失效；也不能使用-e参数在命令行锁表。生产环境不建议使用此方法

mysql -uroot -p123456 -S /data/3306/mysql.sock -e “show master status;”

+——————+———-+————–+——————+

+——————+———-+————–+——————+

| mysql-bin.000007 | 334 | | |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

备份数据库

mysqldump -uroot -p123456 -A -B –events -S /data/3306/mysql.sock|gzip >/opt/bak_$(date +%F).sql.gz

mysql> show master status; #查看binlog没有变化

+——————+———-+————–+——————+

+——————+———-+————–+——————+

| mysql-bin.000007 | 334 | | |

+——————+———-+————–+——————+

1 row in set (0.00 sec)

注意：备份后必须检查

备份后检查备份的sql数据内容：过滤无用信息

egrep -v “#|\/|^$|–” /opt/bak_2016-03-31.sql

解锁表unlock tables;
查看mysql-bin.xxxxxx文件

由于mysql-bin.xxxxxx文件是二进制文件，不能使用cat查看，可以使用mysqlbinlog查看，是将log翻译成SQL语句的工具。

mysqlbinlog /data/3306/mysql-bin.000006

还原从库

gzip -d /opt/bak_2016-03-31.sql.gz

导入主机数据库

mysql -uroot -p1234567 -S /data/3307/mysql.sock </opt/bak_2016-03-31.sql

重启mysql数据库并设置相关参数

mysql -uroot -p1234567 -S /data/3307/mysql.sock<<EOF

change master to

master_host = ‘192.168.10.106’,

master_port =3306,

master_user = ‘rep’,

master_password = ‘123456’,

master_log_file = ‘mysql-bin.000007’,

master_log_pos = 334;

start slave;

EOF

可以在脚本中这样执行，也可以登录3307 mysql直接执行黑体部分

检查数据库查看相关参数

mysql -uroot -p123456 -S /data/3307/mysql.sock -e “show slave status\G;”

show slave status\G

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

……..

查看mysql线程同步状态

mysql>SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 2103

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

……..

注意：有关mysql主从复制线程状态更多信息，请参考官方参考手册

无需锁表备份的方案

在生产环境时，一般会每天备份一份完整数据，在备份时锁表备份并且记录下备份时的binlog对应的文件以及位置点，这样在实现主从复制的时候，就无需现锁表备份了，直接用夜里的全备即可完成主从同步的配置，另外如果没有全备，那么最好等到夜里进行全备，也可能需要申请停机时间。因为锁表期间，会影响业务。小规模就不用了直接夜里mysqldump锁表备份即可，当然了务必要记录binlog位置或者增加–master-data=1

无需锁表的mysqldump备份命令
myisam引擎企业生产备份命令

mysqldump -uroot -p123456 -A -B -F –master-data=2 -x –events|gzip >/opt/all.sql.gz

innodb引擎企业生产备份命令

mysqldump -uroot -p123456 -A -B -F –master-data=2 –events –single-transaction|gzip >/opt/all.sql.gz

–master-data=2的作用：

使用–master-data=2进行备份文件会增加如下内容：适合普通备份增量恢复

— CHANGE MASTER TO MASTER_LOG_FILE= ‘mysql-bin.000005’,MASTER_LOG_POS=119; #注释

使用–master-data=1进行备份文件会取消注释

mysql所有复制相关参数

server-id #用于唯一标志服务器，master与slave都需要，取值范围0-4294967295.如果设置server-id为0，那么master会拒绝所有来自slave的连接，slave也会拒绝连接master，在mysql 5.5中，如果server-id被设置为0，则server会默认把该值设置为1.

master相关参数：

auto_increment_increment及auto_increment_offset：用于多主复制的时候，控制auto_increment列的insert值，默认为1，如果设置为0也会被设置为1.auto_increment_increment控制每次增量，所有master都应该相同，auto_increment_offset控制增量之后的偏移量，所有master都应该不一样。

slave相关参数：

abort-slave-event-count #当slave start之后，执行指定数目的event之后，不再从relay log读取event，但IO与SQL还是处于双YES。

disconnect-slave-event-count #当slave start 之后，执行指定数目的event之后，断开与master的连接。

log-slave-update #控制从relay读取的执行记录是否写入从库的master日志，true的时候是写入，false的时候不写入。主要用于A->B->C结构时的B数据库。对于多主结构并且主库后面都有对应从库的情况下，所有主库的该值都应该是true。

log-slow-slave-statements #是否允许慢日志记录slave sql线程执行的sql语句。

log-warning #这个值主要用于标记是否输出服务器的一些警告信息，对于slave来说，主要包括在网络错误后重新成功连接到主库以及每个slave线程的启动。该值默认为1，可以设置为0来关闭它，当设置为大于1的时候，包括因为权限问题连接失败的情况也会被写入。

master-info-file #记录master相关信息的文件，默认为master.info。

master-retry-count #slave试图连接master的总次数，每次重连的时间间隔取决于change master的时候设置的master_connect_retry（默认60），默认为86400.

slave-max-allowd-packet #设置slave 的 io及sql线程每处理的最大包大小，这样的话一个大的行格式update复制不会导致复制失败。这个值总是1024的倍数。

max-relay-log-size:relay日志的最大大小。

read-only #设置slave拒绝除了super用户之外所有的更新，这个参数主要用于让更新只在master上执行。

relay-log #设置relay log的名称。

relay-log-index #设置relaylog索引的名称，可以重写relay-log指定的索引名称。

relay-log-info-file #slave记录relay位置信息的文件。

relay-log-purge #是否自动删除已经无用的relay日志文件，默认开启。

relay-log-recovery #该选项开启之后，当服务器启动之后，会删除所有已有的relay日志，然后从主库重新接收relay日志。

relay-log-space-limit #限制really log的最大占用磁盘空间，0是无限制，当到达限制后，io线程会停止读取master，直到sql线程执行完成后删除relay日志空出空间。但是当单个relay日志文件已经达到限制的话，这个限制会被无视。

replicate-do-db #对于行格式和语句格式的复制这个选项意义不同。行格式：执行所有非跨库的对dbname的更新。语句格式:只执行当前用户使用use 语法指定当前数据库为dbname后的复制更新（包括包含dbname的跨库更新）。

replicate-ignore-db #语句格式：不执行任何使用use dbname指定数据库的语句。行格式：不执行任何发生在dbname里面的表上的复制。

replicate-do-table #执行发生在指定表的复制，包括跨库更新及默认库更新。

replicate-ignore-table #不执行对指定表的更新，包括包含该表的多表更新。

replicate-rewrite-db:from->to,slave把use from替换为 use to，只有针对表操作的语句才有效（不包括create database，drop database，alter database），并且from必须是master的默认数据库（use 指定）。该选项不会重写语句。不支持跨库更新。

replicate-same-server-id：用于slave服务器，io线程会把server id与自己相同的event写入日志，与log-slave-updates选项冲突。

replicate-wild-do-table:类似数据库like方式指定多个匹配的表，行为类似replicate-do-table。

replicate-wild-ignore-table:类似数据库like方式指定多个匹配的表，行为类似replicate-ignore-table。

report-host:复制期间告知master slave的ip地址用于show slave hosts的输出，由于NAT及其他一些路由方式的干扰，master未必能从tcp包里面获取到正确的IP地址。

report-password:用于show slave hosts的输出，当master指定了show-slave-auth-info选项之后，会输出复制用户的认证信息。

report-port:指定报告给master的slave端口，默认为3306,5.5.23之后，数据库启动后，会自动修正为数据库使用的端口。

report-user:类似report-password，master启用show-slave-auth-info选项之后，会输出复制用户信息。

show-slave-auth-info：开启后，会在show slave hosts里面显示report-user与report-password的信息。

skip-salve-start:当服务器启动的时候，不要启动复制。

slave-compressed-protocol:设置为1的时候，如果master与slave都支持压缩，复制通讯将会以压缩的形式进行。

slave-load-tmpdir:slave创建临时文件的位置，当sql线程执行load data infile语句的时候，它会把数据文件从relaylog提取到临时文件里面，然后把数据加载到表里面。

slave-net-time-out:当多少时间没有收到master答复会被认为是网络超时。

slave-skip-errors:当遇到指定的错误码的时候跳过错误继续执行复制。

init-slave:指定一条每次sql线程初始化后就执行的语句。

relay-log：relaylog的名称

relay-log-index：relaylog的索引名称

relay-log-info-file:记载relay位置信息的文件。

relay-log-recovery:允许在服务器启动后自动删除已有relay日志，之后从服务器重传执行日志。用于slave crash后以防relay日志损坏。

slave-compressed_protocol:在支持的前提下，是否在slave或者master的协议中使用压缩。

slave-exec-mode:STRICT，IDEMPOTENT，设置为IDEMPOTENT后，会直接跳过1023（记录未找到）及1062（主键重复）错误。STRICT为默认值。

slave-transaction-retries:当slave sql线程在执行sql的时候遇到死锁导致执行失败的时候，sql线程在报告错误前执行指定次数来尝试修复问题。

slave-type-conversions:用于限制在行格式复制的时候，slave是否自动转化数据类型，默认不允许。

sql-slave-skip-counter:slave需要跳过的错误数。

sync-master-info:该值高于0的时候，会把master的信息同步到磁盘上，0的时候，依靠操作系统的io操作去同步。

sync-relay-log:每指定动作之后，slave都会把relay日志同步到磁盘上。

sync_relay_log_info:每执行指定次数的时候，修改relay-log.info文件。

binlog相关参数

binlog-row-event-max-event-size:指定行格式复制日志event的最大大小，单位bytes，每一行数据会被切分到多个小于该限制的event包中，必须是256的倍数，默认1024.

log-bin:启用二进制日志，并指定日志名称。

log-bin-index:指定日志索引名称，会重写log-bin的设定。

log-bin-trust-function-creators:指定mysql如何处理函数及存储过程的创建，取决于用户是否认为自己的存储过程及函数是否安全（确定的或者不修改数据），默认对于不安全的存储过程及函数不执行茶创建。

log-bin-use-v1-row-events:该参数会强制数据库使用旧的日志格式，主要用于对mysql－cluster的支持，mysql5.5默认不支持。

log-short-format:只有在Statement下有效的选项，用于减小binlog记录的大小。

binlog-do-db：类似replicate-do-db，其效果与statement模式及row模式有关，但是需要注意的是，在row模式下，也有以statement模式执行的语句，例如，ddl语句就是以statement模式记录的。

statement日志：只有默认db（use 指定）是指定值的语句才会被记录，需要注意的是，跨库执行（在指定库执行，但use指定另外一个数据库）的语句不会被记录，但是use指定指定库，在另一个数据库执行的语句会被记录。

row日志：只有实际在指定数据库上执行的语句才会被记录，与use无关。对于同时｀更新多个数据库的语句，只有指定数据库上的变更会被记录。

binlog-ignore-db：类似replicate-ignore-db,在statement与row模式下表现不同。同时也需要注意在row模式下也有部分比如ddl语句会以statement模式记录。statement模式：数据库不会记录所有使用use指定指定数据库的雨具。row模式：不会记录任何在指定数据库上执行的变更，与use无关。

binlog-cache-size #row模式下，在一个事务中，可以缓存的binlog日志大小。

binlog-stmt-cache-size #statement模式下，可以缓存的binlog大小。

binlog-direct-non-transactional-updates #只可以用于statement模式，事务模式下，非事务表的更新会被直接写入binglog。

binlog-format #复制的传输格式，statement，row，mixed，mixed默认使用statement，只有当statement不安全（比如系统函数调用）的时候，才会使用row模式。

log-bin #是否开启binlog。

log-binpuse-v1-row-event #是否使用从mysql NDB 7.2.1 开始的Version 2的日志格式，设置为1会使用之前的v1版本的日志格式，这个值的设置必须重启mysql 实例。

log-slave-updates #是否记录从主库接收的binlog记录

max-binlog-cache-size #row模式下，如果一个事务使用了多于指定大小的空间，会导致Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage错误。如果进行了修改，只有新连接的值才会被修正。

max-binlog-stmt-cache-sie #statement模式下，如果一个事务使用了多于指定大小的空间，会导致Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage错误。

max-binlog-size #如果一个event的写入会导致日志文件大于指定值，则会新建一个文件写入，但是大事务可能会导致日志文件大小超出指定大小。

sync-binlog #设置为大于0的数字的话，每sync-binlog次写日志活动都会被强制同步到磁盘。

主从复制部署配置问题汇总

主库show master status;没结果，主库binlog功能开关没开或没生效
change master时多了空格错误（各种奇怪错误）

master_log_file = ‘mysql-bin.000007’ #内容的两端不能有空格

服务无法启动故障

[root@nfs ~]# /data/3306/mysql start

MySQL is running…

解决：删掉mysql.sock或*.pid文件

rm -rf /data/3306/mysql.sock /data/3306/mysqld.pid

快速部署mysql主从复制

主库上：

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B -F –master-data=1 -x –events|gzip >/opt/all.sql.gz

从库上：

gzip -d /opt/all.sql.gz

mysql -uroot -p12345678 -S /data/3308/mysql.sock </opt/all.sql

cat /data/3308/data/master.info #可以不用

mysql -uroot -p12345678 -S /data/3308/mysql.sock <<EOF

change master to

master_host = ‘192.168.10.106’,

master_port =3306,

master_user = ‘rep’,

master_password = ‘123456’;

slave start;

EOF

登陆查看

mysql -uroot -p123456 -S /data/3308/mysql.sock

show slave status\G

show processlist\G

工作中MySQL从库停止复制故障案例

模拟错误：模拟重现故障的能力是运维人员最重要的能力。下面就来次模拟操作。先在从库创建一个库，然后去主库创建同名的库来模拟数据冲突。

从库：mysql>create database test;

主库：mysql> create database test;

从库：show slave status\G #报错如下

Slave_IO_Running: No

Seconds_Behind_Master: NULL

Last_SQL_Errno: 1007

Last_SQL_Error: Error ‘Can’t create database ‘test1′; database exists’ on query. Default database: ‘test1’. Query: ‘create database test1’

对于该冲突，解决方法1

stop slave; #临时停止同步开关

set global sql_slave_skip_counter =1; #将同步指针向下移动一个，可以移动多个

start slave; #开启同步开关

方法二

[root@nfs ~]# grep slave-skip /data/3306/my.cnf

slave-skip-errors = 1032,1062,1007

提示：类似入库重复导致的失败可以忽略

服务器错误代码和消息可以参考mysql手册

其他可能引起复制故障的问题：
mysql自身的原因及人为重复插入数据。
不同的数据库版本会引起不同步，低版本到高版本可以，但是高版本不能往低版本同步。
mysql的错误
binlog记录模式，例如：rowlevel模式就比默认的语句模式要好。

让MySQL从库记录binlog日志方法

从库需要记录binlog的应用场景说明：
当前的从库还要作为其他从库的主库，例如：级联复制或者双主互为主从场景。
从库记录binlog日志方法
在从库的cnf中加入如下参数，然后，重启服务生效即可。

log-slave-updates #必须要有这个参数

log-bin = /data/3306/mysql-bin

expire_logs_days = 7 #相当于find /data/3308/ -type f -name “mysql-bin.000*” -mtime +7|xargs rm –f

双主的实现方案
让表的记自增，让后主1写1,3,5二主2写2,4,6…小
不让表钓ID自增，然后通过wEB端程序去5eq服务器取ID，写入双主
双主工作场景

高并发写的场景，慎用！

Mysql主主同步生产库标准同步操作实施流程细节

本实验以3306<==>3307作为主主同步架构图，其中3306==>3307已经完成

编辑cnf配置文件

vi /data/3306/my.cnf

auto_increment_increment =2

auto_increment_offset =1

log-slave-updates

log-bin = /data/3306/mysql-bin #红色部分可以不写，会自动生成

expire_logs_days = 7

vi /data/3307/my.cnf

auto_increment_increment =2

auto_increment_offset =2

log-slave-updates

log-bin = /data/3307/mysql-bin #红色部分可以不写，会自动生成

expire_logs_days = 7

重启3306与3307实例

解决主建自增长变量冲突参数讲解：

Masterl：

auto_increment_increment =2 #自增ID的间隔如1、3、5间隔为2

auto_increment_offset =1 #ID的初始位置

（将形成1,3,5,7…序列）

Master2:

auto_increment_increment =2 #自增ID的间隔如2、4、6间隔为2

auto_increment_offset =2 #ID的初始位置

（将形成2,4,6,8…序列）

3307==>3306同步

mysqldump -uroot -p123456 -S /data/3307/mysql.sock -A -B -F –master-data=1 -x –events|gzip >/opt/3307_all.sql.gz

gzip -d /opt/3307_all.sql.gz

head /opt/3307_all.sql

mysql -uroot -p123456 -S /data/3306/mysql.sock </opt/3307_all.sql

cat /data/3306/data/master.info #可以不用

mysql -uroot -p123456 -S /data/3306/mysql.sock <<EOF

change master to

master_host = ‘192.168.10.106’,

master_port =3307,

master_user = ‘rep’,

master_password = ‘123456’;

slave start;

EOF

测试双主同步

至此，双主同步完成。如果在数据库中新建一张表，其中id为自增主键。则如果3306插入1、3、5；3307会从6、8、10开始，3306会接着10，从11开始类似。其他创建的数据库等则会同时同步过去

主从复制的企业应用场景

MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写成为可能，同时有效的保护物理服务器宕机场景的数据备份。

应用场景1：从服务器作为主服务器的实时数据备份

主从服务器架构的设置，可以大大的加强数据库架构的健壮性。例如：当主服务器出现问题时，我们可以人工或自动切换到从服务器继续提供服务，此时从服务器的数据和宕机时的主数据库几乎是一致的。

这类似我们前面课程中的NFS存储数据通过inotify+rsync同步到备份服务器非常类似，只不过MySQL的复制方案，是其自带的工具。

利用MySQL的复制功能做数据备份，在硬件宕机，服务故障的场景数据备份是有效的，但对于人为的执行drop，delete等语句删除数据的情况，从库的备份功能就没用了，因为从服务器也会执行删除的语句。

应用场景2：主从服务器实现读写分离，从服务器实现负载均衡

主从服务器架构可通过程序（php、java）或代理软件（mysql-proxy、amoeba）实现对用户（客户端）的请求读写分离，即让从服务器仅仅处理用户的select查询请求，降低用户查询响应时间及读写同时在主服务器带来的压力。对于更新的数据（update,insert,delete)仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

百度，淘宝，新浪等绝大多数的网站都是用户浏览页面多于用户发布内容，因此通过在从服务器只接收读请求，就可以很好的减轻主库的读压力，且从服务器可以很容易的扩展到多台，且可以加上LVS做负载均衡，效果就非常棒了，这就是传说中的数据库读写分离架构。上述架构的逻辑图如下：

应用场景3：把多个从服务器根据业务重要性进行拆分访问

可以把几个不同的从服务器，根据公司的业务进行拆分。例如：有为外部用户提供查询服务的从服务器，有内部DBA用来数据备份的从服务器，还有为公司内部人员提供访问的后台、脚本，日志分析及开发人员使用的从服务器。这样的拆分除了减轻主服务器的压力外。使得数据库对外部用户浏览、对内部用户业务处理，及DBA人员备份等互不影响。具体可以用下面的简单架构来说明：

实现MySQL主从读写分离的方案

通过程序实现读写分离（性能，效率最佳，推荐）

php和java程序都可以通过设置多个连接文件轻松的实现对数据库的读写分离，即当select时，就去连接读库的连接文件，当update,insert,delete时就连接写库的连接文件。通过程序实现读写分离的缺点就是需要开发对程序改造，对下层不透明，但这种方式更容易开发和实现，适合互联网场景。

通过开源的软件实现读写分离

MySQL-proxy,Amoeba等代理软件也可以实现读写分离功能，这些软件的稳定性和功能一般，不建议生产使用。普通公司常用的还是通过客户端程序实现读写分离。

大型门户独立开发DAL层综合软件

百度，阿里等大型门户有牛人，会花大力气开发适合自己业务的读写分离、负载均衡、监控报警、自动扩容、自动收缩等一系列功能的DAL层软件，此部分可以参考老男孩架构师分布式数据库的课程内容。

MySQL主从复制延迟问题原因及解决方案

一个主库的从库太多会导致复制延迟

建议从库数量3-5个为宜，要复制的从节点数量过多，会导致复制延迟。

从库硬件比主库差会导致复制延迟

查看master和slave的系统配置，由于机器配置的问题，包括磁盘I/O,CPU，内存等各方面因素，也会造成复制的延迟，一般发生在高并发大数据量写入场景。

慢SQL语句过多

假如一条SQL语句，执行时间是20秒，那么从执行完毕，到从库上能查到数据也至少是20秒，这样就延迟20秒了。

SQL语句的优化一般要作为常规工作不断的监控和优化，如果是单个SQL写入时间长的，可以修改分多次写入,通过看慢查询日志或show full proeesslist命令找出执行时间长的查询语句或者大的事务

主从复制的设计问题

例如主从复制单线程的，如果主库写并发太大，来不及传送到从库就会导致延迟。更高版本的MySQL可以支持多线程复制，或者门户网站自己开发多线程同步功能。

主从库之间的网络延迟。

主从库的网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟；另外，跨公网主从复制很容易导致主从复制延迟。

主库读写压力大的时候会导致复制延迟

主库硬件要搞好一点，架构的前端要加buffer以及缓存层。

mysql主从复制读写分离用户权限

专业的运维人员提供给开发人员读写分离账户设置方法如下

访问主库和从库时使用一套用户密码，例如：用户为web，密码为oldboy123
即使访问IP不同，端口也尽量相同，尽量未开发人员提供方便

授权web连接用户访问的方案：mysql主从复制读写分离集群

方法一：主从使用不同的用户，授予不同的权限
主库上对web_w用户的授权如下

用户：web_w 密码：oldboy123 端口：3306 主库VIP：192.168.10.106

权限：select,insert,update,delete

命令：grant select,insert,update,delete on web.* to ‘web_w’@’192.168.10.%’ identified by ‘oldboy123’;

从库上对web_r用户的授权如下

用户：web_r 密码：oldboy123 端口：3306 主库VIP：192.168.10.107

权限：select

命令：grant select on web.* to ‘web_r’@’192.168.10.%’ identified by ‘oldboy123’;

提示：此方法不够专业，但可以满足要求

方法二：主从使用相同的用户，但授予不同的权限
主库上对web用户的授权如下

用户：web 密码：oldboy123 端口：3306 主库VIP：192.168.10.106

权限：select,insert,update,delete

命令：grant select,insert,update,delete on web.* to ‘web_w’@’192.168.10.%’ identified by ‘oldboy123’;

从库上对web用户的授权如下

用户：web 密码：oldboy123 端口：3306 主库VIP：192.168.10.107

权限：select

提示：由于主库和从库是同步复制的所以从库上的web用户会自动和主库保持一致，即无法实现只读select的授权

要实现方案二中的授权方案，有如下两个方法

一是在主库上创建用户和授权后，从从库上收回对应的更新权限（insert,update,delete）。

命令为：revoke insert,update,delete on web.* from ‘web’@’192.168.10.%’;

二是忽略授权表的授权，主库的配置参数如下；

replicate-ignore-db = mysql

binlog-ignore-db = mysql

提示：以上两参数两边必须有空格

方法三：在从库上设置read-only参数，让从库只读
主库和从库使用相同的用户，授予相同的权限（非all权限）

用户：web 密码：oldboy123 端口：3306

权限：select,insert,update,delete

命令：grant select,insert,update,delete on web.* to ‘web’@’192.168.10.%’ identified by ‘oldboy123’;

由于从库设置了read-only，非super权限是无法写入的，因此通过read-only参数可以很好地控制用户，使其不能非法将数据写入从库

MysQL –read-only参数功能与使用实践

测试read–only参数用于从slave中的情况

–read-only参数功能描述

–read-only参数选项可以让从服务器只允许来自从服务器线程或具有SUPER权限的用户的更新。可以确保从服务器不接受来自用户端的更新。

–read-only参数更新的条件

a．具有SUPER权限的用户可以更新（root）

b．来自从服务器线程可以更新

read-only参数配置方法如下：

可以在从库slave中使用read-only参数，确保从库数据不被非法更新。

方法一：启动数据库时直接带–read-only参数启动或重起;

mysqladmin -uroot -p123456 -S /data/3306/mysql.sock shutdown

mysqld_safe –default-file=/data/3306/my.cnf –read-only &

方法二：在cnf里[mysqld］模块下加read-only参数重起

[mysqld］

read-only

MySQL主从复制指定不同库表同步参数说明

控制复制的启动选项

–replicate-do-db=db_name#告诉从服务器限制默认数据库(由USE所选择)为db_name的语句的复制
–replicate-do-table=db_name.tbl_name#从服务器线程限制对指定表的复制
–replicate-ignore-table=db_name.tbl_name#告诉从服务器线程不要复制更新指定表的任何语句
–replicate-wild-do-table=db_name.tbl_name#告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。模式可以包含‘%’和‘_’通配符

结论1：只在〔从〕库上配置replicate-ignore-db=mysql并不能做到从库不同步mysql库。

结论2：只在〔主〕库上配置replicate-ignore-db=mysql并不能做到从库不同步mysql库。

结论3：只有在〔主从〕库上分别设置replicate-ignore-db=mysql才可以做到从库不同步mysql库。

结论4：在主库上设置binlo-ignore-db=mysql不记录binlog，来达到从库不同步mysql库

彻底解除主从复制关系

1). stop slave;

2). reset slave; 或直接删除master.info和relay-log.info这两个文件；

3). 修改my.cnf删除主从相关配置参数。

让slave不随MySQL自动启动

修改my.cnf 在[mysqld]中增加 skip-slave-start 选项

mysql主从复制常见问题

错误一：

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’

或者：

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000016’ at 106, the last event read from ‘/data/mysql/mysql-bin.000016’ at 106, the last byte read from ‘/data/mysql/mysql-bin.000016′ at 107.’

解决：

在master那边，执行：

flush logs;

show master status;

记下File, Position。

在slave端，执行：

stop slave;

CHANGE MASTER TO MASTER_LOG_FILE=’testdbbinlog.000008′,MASTER_LOG_POS=107;

slave start;

show slave status \G

MySQL半同步配置

异步、同步、半同步复制的概念

同步复制：master提交事务，直到事务在所有的Slave都已提交，此时才会返回客户端，事务执行完毕。缺点：完成一个事务可能会有很大的延迟。
异步复制：MySQL本身支持单向的、异步的复制。异步复制指把数据从一台机器拷贝到另一台机器时有一个延时，最重要的是这意味着当应用系统的事务提交已经确认时数据并不能在同一时刻同步到从服务器上；这个延时是由网络带宽、资源可用性和系统负载决定的。不能保证一些事件都能够被所有的Slave所接收。
半同步复制：半同步复制工作的机制处于同步和异步之间，master的事务提交阻塞，只要一个Slave已收到该事务的事件且已记录。它不会等待所有的slave都告知已收到，且它只是接收，并不用等其完全执行且提交。

默认情况下我们的主从复制是采用异步的方式进行同步的，导致我们的从服务器数据有可能会落后于主服务器，在一定程度上是不安全的，如果我们主服务器瞬间挂掉，从服务器将来不及复制数据，从而就产生了半同步

开启这个功能后，主服务器只等待多个从服务器中的指定的一台从服务器复制成功，然后才进行其他写操作，使这台从服务器和主服务器上的数据完全同步，而并不管其他的从服务器，当然主服务器不会一直等待从服务器复制成功的：因为万一一从服务器挂了，那么主服务器将一直处于等待状态而不提供写服务，这就需要我们定义一个超时时间，防止等待从服务器时间太长，如果超过定义的时间，从服务器还没有响应，则把指定的从服务器自动降级到异步模式。

实现半同步复制的前提条件

安装Plugin插件

mysql-5.5版本支持半同步复制功能．但还不是原生的支持，是通过pingin来支待的．并且默认是没有安装这个插件的。不论是二进制发布的，还是自己源代码编译的，都会默认生成这个插件，一个是针对master的一个是针时slave的，在使用之前需要先安装这两个plugin

ls /application/mysql/lib/plugin/ -l

-rwxr-xr-x 1 root root 175767 3月 30 08:59 semisync_master.so

-rwxr-xr-x 1 root root 94002 3月 30 08:59 semisync_slave.so

安装需要分别在主从端开启该服务

5半同步配置

确认master和slave上是否开启have_dynamic_loading

mysql> show variables like ‘have_dynamic_loading’;

+———————-+——-+

| Variable_name | Value |

+———————-+——-+

| have_dynamic_loading | YES |

+———————-+——-+

1 row in set (0.00 sec)

（从库同样命令）

安装半同步插件
使用root在master上安装半同步插件

install plugin rpl_semi_sync_master soname ‘semisync_master.so’;

使用root在slave上安装半同步插件

install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’; #安装插件

show plugins; #查看

配置master和slave的my.cnf

master添加如下内容

rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000

rpl_semi_sync_master_trace_level=32

rpl_semi_sync_master_wait_no_slave=on

slave添加如下内容

rpl_semi_sync_slave_enabled=1

重启服务查看

show global status like ‘rpl%’;

mysql灾难恢复方案

主库master 宕机，接班人如何选择

1主5从：S1、S2、S3、S4、S5

是否事先指定接班人，太子，接班人如何选择：

半同步从库（谷歌半同步插件）

一一Sl作为太子：主库插入数据后，同时写入到Sl，成功返回。

优点：两台库同时写入数据。

缺点：写入会慢；网络不稳定，主库持续等待

解决措施：

连不上S1的时候自动转为异步。
设置10秒超时，超过10秒转为异步
Sl网络，硬件要好，不提供服务，干等接管。
S1啥也不干只做同步的从库。百度
皇帝驾崩现选（耽误事，容易被篡位）

确定主之后：角色切换S1提升为新主：

主库宕机有两种情况：

主库可以SSH连接

如果主库可以SSH连接，BINLOG数据没丢，要把主库的binlog补全到所有从库。如果为半同步，binlog补全应该不需要了，不存在binlog差异

提升Sl为M1的操作：

确保所有relay log全部更新完毕

在每个从库上执行stop slave io_thread;show processlist;

从库提升为主

mysql -uroot -p123456 -S /data/3307/mysql.sock

stop slave;

reset master;

quit

进入到数据库目录，删除info relay-log*

cd /data/3307/data

rm -f master.info relay-log*

调配置read-only。授权用户select，变成增删改查，和原来的主库一样，开启binlog。如果做了双主同步，就直接切即可，啥都不用做。
重启数据库，提升Sl为M1完毕。

所有从库：CHANGE MASTER TO（仅修改改变的部分，其他可以不改）

如果主库连不上
半同步从库提升主库，半同步数据，补全到所有从库（通过中继日志）

半同步从库提升主库操作同第一种情况提升Sl为M1的操作。所有从库同上第一种情况所有从库：操作

S1啥也不干只做同步方法

提升啥也不干的从库为主库，提升过程同上

主库宕机没有事先指定从库为主库

确保I/0和SQL都读取了自己的LOG，并应用到了数据库。

登录所有从库:show processlist\G查看两个线程的状态

State: Slave has read all relay log; waiting for the slave I/O thread to update it

State: Waiting for master to send event

如果显示有这两个线程状态，则从库同步完成

登录所有从库分别查看:谁和主库更新最接近

cat /data/3307/data/master.info

确保更新完毕，看看4个从库哪个最快，经过测试没有延迟的情况POS差距很小，甚至是一致的

相同文件选择POS最大的作为主库，补全（通过中继日志）所有其他从库

提升选择的从库为主库，提升过程同上，从库操作同上

高可用工具

MHA高可用工具就是利用我讲的原理实现的

MYSQL+HA+DRBD高可用场景

MYSQL-MMM（20期王欣）

MYSQL MHA（日本人开发）：所有服务器之间做了一个SSH免密码登录

PXC

MYSQL CLUSTER

mysql数据库服务日志

错误日志介绍与调整

MysQL的错误日志（error log）记录MySQL服务进程mysqld在启动／关闭或运行过程中遇到的错误信息；

在配置文件中调整方法：

[mysqld_safe]

log-error=/data/3306/mysql_oldboy3306.err

也可以在启动时加入启动参数

mysqld_safe –defaults-file=/data/3306/my.cnf –log-error=/data/3306/mysql_error.log &

查看是否生效

show variables like “error_log “;

普通查询日志介绍与调整

普通查询日志（general query log）：记录客户端连接信息和执行的SQL语句信息

show variables like “general_log%”; #查看是否启用等

高并发场景企业中普通查询日志一般是关闭的（因为IO性能问题）。默认关闭，可以如下命令开启

set global general_log = on ;

set global general_log_file = “/data/3306/data/MYSQL_log”; #设置配置文件

慢查询日志介绍

慢查询日志（slow query log）：记录执行时间超出指定值（long_query_time）的SQL语句及不使用索引的日志

long_query_time = 1

log-slow-queries = /data/3306/slow.log

log_queries_not_using_indexes

二进制日志介绍与调整

二进制日志（binary log)：介绍数据被修改的相关信息

show variables like “%log_bin%”; #查看bin-log信息

set session sql_log_bin = OFF; #设置当前会话不记录入bin-log

bin-log作用：

记录更改的SQL语句。
主从复制。
增量数据备份

删除二进制日志

法一：登录数据库reset master;

法二：登录数据库purge master logs to ‘mysql-bin.000005′;

删除mysql-bin.000005之前所有日志不包括mysql-bin.000005本身

法三：在my.cnf配置文件中添加expire_logs_days=7;自动删除7天前的日志

法四：purge master logs before’2016-04-12 16:03:14’;

删除2016-04-12 16:03:14之前产生的所有日志。

mysql数据库备份

MySQL binlog三种模式及设置方法

Row Level

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。

优点：在Row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update product set owner_member_id=‘b’ where owner_member_id=’a’，执行之后，日志中记录的不是这条update语句所对应的事件（MySQL以事件的形式来记录bin-log日志），而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多个事件。自然，bin-fog日志的量就会很大。尤其是当执行alter table之类的语句的时候，产生的日志量是惊人的。因为MySQL对于alter table之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中。

Statement Level(默认)

每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

优点：Statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。因为他只需要记录在Master上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点：由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于MySQL现在发展比较快，很多的新功熊不断的加入，使MySQL得复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前己经发现的就有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行来记录的变化，所以不会出现类似的问题。

Mixed

实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待袍录的日志形式，也就是在statement和Row之间选择一种。新版本中的Statment level还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

设置MySQL binlog的模式

在配置文件中参数如下：

log_bin=mysql-bin

#binloq_format=”STATEMENT”

#binlog_format=”ROW”

binlog_format=”MIXED”

运行时在线修改

show variables like “binlog_format%”; #查看当前使用的模式

set global binlog_format = row; #命令行修改当前使用的模式

检验ROW模式下binlog日志记录效果

mysqlbinlog –base64-output=’decode-rows’ –verbose mysql-bin.000001

全量备份与增量备份

全量备份：全量数据就是数据库中所有的数据，全量备份就是把数据库中所有的数据进行备份。

增量备份：增量数据是从上次全量备份之后，更新的新数据。对于MySQL来说，binlog日志就是MySQL的增量数据．

按天全量备份

只有当天的binlog日志为增量备份数据

优点：恢复时间短，维护成本：低
缺点：占用空间多，占用系统资源多，经常锁表影响用户体验。
按周全备情况

只有当周的binlog日志为增量备份数据

优点：占用空间小，占用系统资源少，无需锁表或锁表次数少，用户体验好一些。
缺点：维护成本高，恢复麻烦，时间长
企业场景全量和增量的频率

中小公司，全量一般是每天一次，业务流量低谷执行全备，备份时会锁表。

增量备：

定时推binlog增量。例如每分钟推一次增量。
再在其他远程实时读binlog
大公司周备，每周六00点一次全量，周日一下周六00点前都是增量。

优点节省备份时峋，减小备份压力。缺点：增量的binlog文件副本太多，还原麻烦。

一主多从环境，主从复制本身就是实时远程备份，可以解决服务器物理故障。
4）一主多从环境，可采取一个从库服务器上专门用mysqldump，cp,tar，xtrabackup备份，延迟同步。
mysql的mysqldump备份场景
迁移或者升级数据库时。
增加从库的时候。
因为硬件或特殊异常情况，主库或从库宕机，主从可以可以互相切换，无需备份
人为的DDL,DML语句，主从库没办法了，所有库都会执行。此时需要备份
跨机房灾备，需要备份到异地。

mysql备份常用方法

MySQL备份的常用方式有逻辑备份和物理备份（oracle也是如此）

逻辑备份介绍

MySQL的逻辑备份其实就是使用MySQL自带的mysqldump命令或者其他工具把MySQL数据备份成sql语句存储，在恢复的时候通过执行mysql恢复命令（或source等）将存储的sql语句还原到mysql数据库中。

补充：增量备份备份binog日志文件即可，如何增量恢复binlog日志呢，mysqlbinlog二具可以把binlog日志转换成SQL语句，然后通过mysql恢复命令（或source等）将SQl语句还原到MySQL数据库中。

常用工具为MysQL自带的mysqldump命令。

物理备份介绍

MySQL的物理备份方法之一是使用cp，rsync,tar，scp等复制工具把MysQL数据文件复制成多份，由于在备份期间数据仍然在写入数据库，所以，直接复制的方式备份会引起数据丢失。另外在恢复数据库时，对新数据库的路径、配置也有要求，一般要和原库配置保持一致（版本，路径，配置尽可能一样）。

为了确保备份期间的数据一致性，可以选择人工停库或者锁库后在进行物理复制，在生产环境中一般是不允许的，除非是可以申请停机或锁表时间。

基本可用性和数据一致性的选择问题！

一般在进行大规模数据库迁移时，停库然后物理迁移，是最有效率的方案。

除了在linux命令行通过命令直接复制MySQL数据文件外，还有一些其它第三方开源或商业备份工具。如：xtrabackup对于oracle数据库来说，可以通过rman工具来物理全备及增量备份。

如何选择MySQL备份方式？

主从复制集群架构的数据备份策略

有主从复制了，还需要做定时全量加增量备份么？答案是肯定的！

因为，如果主库有语句级误操作，例如：(drop database oldboy;),从库也会执行drop database oldboy;这样MySQL主从库就都删除了该数据。

把从库作为数据库备份服务器时,备份策略如下：

高并发业务场景备份时,可以选择在一台从库上备份，把从库作为数据库备份服务器时需要在从库开启binlog功能

步骤如下：

选择一个不对外服务服务的从库，确保和主库更新最接近，专门做数据备份用。
开启从库的binlog功能。

备份时可以选择只停止SQL线程，停止应用SQL语句到数据库，I/O线程保留工作状态，执行命令为stop slave sql_thread;，备份方式可以采取mysqldump逻辑备份或者直接物理备份，例如：cp、tar（/data）根据总的备份数据量的多少选择，把全备和binlog发送到备份服务器上留存。

mysqldump备份参数

意义：第一个是保护公司的熬据，第二个是网站7*24小时提供服务。

mysqldump备份介绍

mysql数据库自带了一个很好用的备份命令，就是mysqldump，使用mysq1dump是把数据库的数据导出通过sql语句的形式存储。这种备份方式称之为逻辑备份，效率不是很高，一般50G以内的数据。其他备份方式，物理备份：cp,tar（停库）,xtrabackup物理备份。它的基本使用如下：

语法：mysqldump -u用户名 -p数据库名＞备份的文件名

mysqldump -uroot -p123456 -S /data/3306/mysql.sock student >/backup/student_$(date +%F).sql

egrep -v “#|\*|–^$” /backup/student_$(date +%F).sql #检查备份情况

-B（推荐使用）

参数说明：

该参数用于导出若干个数据库，在备份结果中会加入USE db_nalne和CREATE DATABASE db_name；-B后的参数都将被作为数据库名。该参数比较常用。当-B后的数据库列全时同-A参数。请看-A的说明。

–compact参数说明（不推荐）

–compact Give less verbose output (useful for debugging). Disables

structure comments and header/footer constructs. Enables

options –skip-add-drop-table –skip-add-locks

–skip-comments –skip-disable-keys –skip-set-charset.

精简备份输出（过滤掉没有用的一些注释）

-F, –flush-logs Flush logs file in server before starting dump. Note that

#重新开始一个binlog

-R, –routines Dump stored routines (functions and procedures). #存储过程和函数

–events 由于mysql在全量导出时不导出event事件表,故需要在全量导出时忽略事件表

多库分别备份命令

也可以将上面命令写成脚本备份

单多表的备份

企业需求：一个库里有大表有小表，有时可能需要只恢复某一个小表，上述的库备份文件很难拆开，就会像没有分库那样导致恢复某一个小表很麻烦。

mysqldump -uroot -p123456 -S /data/3306/mysql.sock student score >/backup/student_score.sql #备份student库score表，在不使用-B的情况下，库名后加表名（可以接多个表，以空格隔开）

备份表结构

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -d student score >/backup/student_score.sql #加-d参数

备份表数据

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -t student score >/backup/student_score.sql #加-t参数

刷新binlog参数

-F刷新binlog日志，生成新文件，将来增量恢复从这个文件开始。相当于切割binlog

–single-transaction(单事务)

适合innodb序务数据库备份

InnoDB表在备份时，通常启用选项 –single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ以确保本次(dump）时，不会看到其他会话已经提交了的数据。与-x的却是还可以提交事务，而-x则不能写入

生产场景不同引擎mysqldump备份命令

myisam引擎企业生产备份命令

（适合所有引擎或混合引擎）

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B -F -R –master-data=2 -x –events|gzip >/opt/all.sql.gz

提示：也可以不用-F,与–master-data有些重复

innodb引擎企业生产备份命令：推荐使用的

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B -F -R –master-data=2 –events –single-transaction|gzip >/opt/all.sql.gz

提示：也可以不用-F,与–master-data有些重复

如果数据库有函数或存储过程还需要加-R参数

–master-data作用：

使用–master-data=2进行备份文件会增加如下内容：适合普通备份增量恢复

–CHANGE MASTER TO MASTER_LOG_FILE=’MYSQL-bin.000020′,MASTER_LOG_POS=1191;

使用–master-data=1进行备份文件会增加如下内容：更适合主从复制

CHANGE MASTER TO MASTER_LOG_FILE=’MYSQL-bin.000020′,MASTER_LOG_POS=1191;

额外补充：
mysqldump逻辑备份，效率不是特别高，简单、方便、可靠。
适用与数据量不是特别大的场景，50G以内数据。
xtrabackup物理备份工具：全备和增量。
物理备份方案：从库停止SQL线程，打包,cp。
mysql数据库恢复实践

数据库恢复事项

提示：数据恢复和字符集关联很大，如果字符集不正确会导致恢复的数据乱码，执行mysql命令以及source命令恢复数据库的原理就是把文件的SQL语句，在数据库执行行的过程。

利用source命令恢复数据库

mysql -uroot -p123456 -S /data/3308/mysql.sock

source /backup/student.sql #恢复库，等价于

mysql -uroot -p123456 -S /data/3306/mysql.sock </backup/student.sql

use oldboy_gbk;

source /backup/student_score.sql #恢复表，等价于

mysql -uroot -p123456 -S /data/3306/mysql.sock -e “use oldboy_gbk; source /backup/student_score.sql;”

mysqlbinlog工具解析binlog日志实践

mysqlbinlog工具的作用是解析mysql的二进制binlog日志内容，把二进制内容解析成可以在MySQL数据库里执行的SQL语句。

mysql的binlog日志作用是用来记录mysql内部增删改等对mysql数据库的记录（对数据库的改动），对数据库查询的语句如show,sdect开头的语句没有记录日志。

解析指定库的binlog
利用mysqlbinlog -d参数解析指定库的binlog日志

mysqlbinlog -d student /data/3306/mysql-bin.000045|egrep -v “#|–|^$|/\*”

注意：如果要使用-d参数解析指定库的binlog日志，那么在修改数据库时应该首先use db_name;然后在修改数据库。

insert into student.score values(3,’李文斌’,’数据结构’,90); #此修改不能分离出来。

按照位置截取，精确

mysqlbinlog /data/3306/mysql-bin.000045 –start-position=183 –stop-position=304

注意：起始位置必须精确，停止位置可以不精确

如果没有指定起始点默认从日志最开始开始，如果没有指定结束点则默认到日志最后。

按照时间截取：模糊、不准

mysqlbinlog /data/3306/mysql-bin.000045 –start-datetime=’2016-04-12 10:53:29′ –stop-datetime=’2016-04-12 11:06:42′

解析ROW模式binlog日志方法

mysqlbinlog –base64-output=”decode-rows” –verbose /data/3306/mysql-bin.000045 -r /log/bin-log.sql #-r参数，生成指定文件

mysqlbinlog命令总结
解析binlog日志为sql语句
-d参数指定库拆分binlog
位置参数–start-position、–stop-position，精确定位
时间参数–start-datetime、–stop-datetime，模糊定位
-r生成文件
解析ROW模式binlog日志

增量恢复场景

我们在生产工作中一般常用一主多从的数据库架构，常见的备份方案是在从服务器上开启binlog，然后实施定时全备份和实时增量备份。

什么是增量恢复？

利用二进制日志和全备进行的恢复过程，被称为增量恢复。

什么情况下需要增量恢复？

1）主或者从库宕机（硬件损坏）足否需要增量恢复？

答：不需要增量恢复，主库宕机，只需要把其中一个同步最快的从库切换为主库

从库宕机，直接不用就好了（一般都会配LVS负载均衡）。

2）人为操作数据库SQL语句破坏主库足否需要增量恢复？

在数据库主库内部命令行误操作，会导致所有的数据库（包括主从库）

在主库执行了drop database test;这样的删除语句，这时所有的从库也会执行drop database test;语句，从而导致所有数据库上的test库数据丢失。这样才需要增量恢复的

3）只有一个主库是否需要增量恢复？

如果公司里只有一个主库的情况，首先应该做定时全量备份，然后做增量备份（每隔1-10分钟对binlog日志做切割然后备份到其他的服务器，或者写到网络文件系统（备份服务器）里。如果不允许数据丢失，最好的办法就是做从库。

正常情况：

主从同步：除了分担读写分离压力外，还可以防止物理设备损坏数据丢失的情况

从库备份：在从库进行全量和增量方式的备份，可以防止人为对主库的误操作但必须确保备份的从库实时和主库是同步状态。

小结：一般由人为（或程序）逻辑的方式在数据库执行的SQL语句等误操作，才需要增量恢复，因为此时所有的从库也执行了误操作语句。物理故障，直接切换到从库。

MySQL增量恢复必备条件

存在一份全备加上全备之后的时刻到出问题时刻的所有增量binlog文件

开启MySQL log-bin日志功能

提示：主库和备份的从库都要开启binlog记录功能。

企业生产案例

MySQL Sleep线程过多如何解决？

睡眠连接过多，会对mysql服务器造成什么影响？

严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

造成睡眠连接过多的原因？
使用了太多持久连接（个人觉得，在高并发系统中，不适合使用持久连接）
程序中，没有及时关闭mysql连接
数据库查询不够优化，过度耗时。

当然，更根本的方法，还是从以上三点排查之

程序中，不使用持久链接，即使用mysql_connect而不是pconnect。
程序执行完毕，应该显式调用mysql_close
只能逐步分析系统的SQL查询，找到查询过慢的SQL,优化之

如果你没有修改过MySQL的配置，缺省情况下，wait_timeout的初始值是28800。

wait_timeout 过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题，通常来说，我觉得把wait_timeout设置为10是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：

# vi /etc/my.cnf

[mysqld]

interactive_timeout=120

wait_timeout=120

wait_timeout=120 #即设置mysql连接睡眠时间为120秒，任何sleep连接睡眠时间若超过120秒，将会被mysql服务自然终止。也可以直接在命令行

set global wait_timeout=120;

set global interactive_timeout=120;

如果查询时使用的是show variables的话，会发现设置好像并没有生效，这是因为单纯使用show variables的话就等同于使用的是show session variables，查询的是会话变量，只有使用show global variables，查询的才是全局变量。

生产场景常用重要命令小结

show processlist\G #查看正在执行的SQL语句（线程），看不全

show full processlist\G #查看完整的正在执行的SQL语句（线程），完整显示

show variables; #查看mysql变量(mysql配置参数)，是系统参数是静态

show global status; #查看mysql性能状态，是系统状态是动态，很重要（监控）

kill ID #杀掉线程的命令，ID为线程号

show engine innodb status; #查看innodb引擎的详细状态

mysql -uroot -p123456 -S /data/3306/mysql.sock -e “show full processlist;”|grep -v Sleep

#过滤当前执行的语句的完整内容，后面还可以加iconv等对中文转码

mysqlreport软件：详细的展示MYsQL状态等的情况

计算一天之内：数据库有多个insert,delete
定时任务每天0点，show global status；按天取出对比。

show global status like “%select%”; #取出结果，可以使用rrdtool工具出图

按天分析binlog日志，获取数据库不同语句的频率

mysql数据库连接慢的原因

skip-name-resolve #此参数的意思是不使用域名解析, 只能使用机器的外部IP地址访问，

企业案例：主库误操作不停主库恢复

l、停止一个从库，然后再主库刷新binlog，把mysql-bin.000014恢复成bin.sql（去掉drop语句）

2．把全备bak_2014-11-06.sql及增量bin.sql恢复到从库。

3、切换到从库提供服务。

数据丢多少？10:10分刷新binlog以后的数据．mysql-bin.000015

4、把mysql-bin.000015解析为sql，恢复到从库。

以上方案还是不会主键冲突问题。

MySQL引擎概述

什么是存储引擎？

在讲清楚什么是存储引擎之前，我们先来个比喻，我们都知道录制一个视频文件，以转成不同的格式如mp4,avi,wmv等，而存在我们电脑的磁盘上也会存在于不同类型的文件系统中如windows里常见的ntfs,fat32，存在于hnux里常见的ext3,ext4,xfs，但是，给我们或者用户看到实际视频内容都是是一样的。直观区别是，占用系统的空间大小与清晰程度可能不一样。那么，数据库表里的数据存储在数据库里及磁盘上和上述的视频格式及存储磁盘文件系统格式特征类似，也有很多种存储方式。但是，对于用户和应用程序来说同样一张表的数据，无论用什么引擎来存储，用户看到的数据都是一样的。不同的引擎存取，引擎功能，占用的空间大小，读取性能等可能有区别。

MySQL最常用存储引擎为：MyISAM和innoDB。全文索引：目前5.5版本，myisam和innodb都己经支持

MySQL存储引擎的架构

MySQL的存储引擎是MySQL数据库的重要组成部分，MySQL常用的表的引擎为MyISAM和innoDB两种。MySQL的每种存储引擎在MySQL里是通过插件的方式使用的，MySQL可以同时支持多种存储引擎。下面是MySQL存储引擎体系结构简图：

MySQL存储引擎体系结构简图

MyISAM引擎介绍

什么是MyISAM引擎？

MyISAM引擎是MySQL关系数据库管理系统的默认储存引擎（MySQL5.5.5以前）。这种MysQL表存储结构从旧的ISAM代码扩展出许多有用的功能。在新版本的MySQL中，innoDB引擎由于其对事务参照完整性，以及更高的并发性等优点开始逐步的取代MyISAM引擎•InnoDB is the default storage engine as of MysQLS-5.5。

MylSAM:The MySQL storage engine that is used the most in web,data warehousing,and other application environments.MylSAM is supported in all MySQL configurations,and is the default storage engine prior to MySQL5.5.5。

每一个MylSAM表都对应于硬盘上的三个文件。这三个文件有一样的文件名，但是有不同的扩展名指示其类型用途：.frm文件保存表的定义，这个文件并不是MylSAM引擎的一部分，而是服务器的一部分；.MYD保存表的数据；.MYI是表的索引文件。．MYD和．MYI是MylSAM的关键点。

MySQL系统的表多数都使用了MyISAM引擎（包括5.5.5以上）。

n MylSAM引擎特点

①不支持事务：（事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。）

②表级锁定：数据更新时锁定整个表：其锁定机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。

③读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。

④只会缓存索引：MyISAM可以通过key_buffer_size缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。

⑤读取速度较快。占用资源相对少。

⑥不支持外键约束，但支持全文索引。

⑦MyISAM引擎是MySQLS.5.5前缺省的存储引擎（is the default storage engine prior to MySQL5.5.5.)

n MylSAM引擎适用的生产业务场景

l、不需要事务支持的业务（例如转账就不行）

2、一般为读数据比较多的应用，读写都频繁场景不适合，读多或者写多的都适合。

3、读写并发访问相对较低的业务（纯读纯写高并发也可以）（锁定机制问题）

4、数据修改相对较少的业务（阻塞问题）

5、以读为主的业务，例如：数据库系统表、www、blog，图片信息数据库，用户数据库、商品库等业务。

6、对数据一致性要求不是非常高的业务（不支持事务）

7、硬件资源比较差的机器可以用MyISAM（占用资源少）。

8、使用读写分离的MySQL从库可以使用MyISAM。

小结：单一对数据库的操作都可以使用MyISAM，所谓单一就是尽量纯读，或纯写(insert,update,delete）等。
n MylSAM引擎调优精要

设置合适的索引（缓存机制）。

调整读写优先级，根据实际需求确保重要操作更优先执行。
启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）。
尽量顺序操作让insert数据都写入到尾部，减少阻塞。
分解大的时间长的SQL操作，降低单个操作的阻塞时间。
降低并发数（减少对MySQL访问），某些高并发场景通过应用进行排队队列机制Q队列。
对于相对静态（更改不频繁）的数据库数据，充分利用QueryCache或memcached缓存服务可以极大的提高访问效率，网站动态内容静态化，减少对数据库的访问。
缓存相关的参数

[root@nfs ~]# grep query /data/3306/my.cnf

query_cache_size = 2M

query_cache_limit = 1M

query_cache_min_res_unit = 2k

InnoDB引擎

什么是Inn0OB引擎？

InnoDB引擎是MySQL数据库的另一个重要的存储引擎，正成为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其它的存储引擎相比，Inn0DB引擎的优点是支持兼容ACID的事务（类似于PostgreSQL)，以及参数完整性（即对外键的支持）。Oracle公司2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其它想将InnoDB结合到商业软件的团体获得授权。

InnoDB引擎特点

1、支持事务：支持4个事务隔离级别，支持多版本读。

2、行级锁定（更新时一般是锁定当前行）：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。

3、读写阻塞与事务隔离级别相关。

4、具有非常高效的缓存特性：能缓存索引，也能缓存数据。

5、整个表和主键以Cluster方式存储，组成一颗平衡树。

6、所有secondary index都会保存主键信息。

7、支持分区，表空间，类似oracle数据库。

8、支持外键约束，5.5以前不支持全文索引，以后支持了。

9、和MyISAM引擎比，InnoDB对硬件资源要求比较高。

小结：Support transactions,row-level locking,and foreign keys

InnoDB引擎适用的生产业务场景

1、需要事务支持的业务（具有较好的事务特性）。

2、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。

3、数据读写及更新都较为频繁的场景，如：BBS,SNS，微博，微信等。

4、数据一致性要求较高的业务，例如：充值转账，银行卡转账。

5、硬件设备内存较大，可以利用Illn0DB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO。

共享表空间对应物理数据文件（默认）

[root@nfs ~]# ll /data/3306/data/ibdata1

-rw-rw—- 1 mysql mysql 134217728 4月 13 13:36 /data/3306/data/ibdata1

独立表空间对应物理数据文件(自己设定)

innodb_file_per_table

innodb_data_home_dir=/data/xxx

InnoDB引擎调优精要

l、主键尽可能小，避免给secondary index带来过大的空间负担

2、建立有效索引避免全表扫描，因为会使用表锁。

3、尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗。

4、在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交。有开关可以控制提交方式；

5、合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。如果innodb_flush_log_at_trx_commit的值为O,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。

6、避免主键更新，因为这会带来大量的数据移动。

生产环境中如何批量更改MySQL引擎

一般来说这样的需求不多见，但偶尔也会有，在这里我们推荐使用Sed对备份内容进行引擎转换的方式，当然了，不要忘记了修改my.cnf使之支持并能高效的使用对应的引擎。

法1)MySQL命令语句修改

创建后引擎的更改，5.0以上：

show create table score;

alter table score engine = myisam;

法2）使用sed对备份内容进行引擎转换

sed -e ‘s#MyISAM#InnoDB#g’ /opt/all.sql >/opt/all_2.sql

法3）mysql_convert_table_format命令修改

yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes #需要安装依赖包

[root@nfs ~]# mysql_convert_table_format –user=root -password=123456 –socket=/data/3306/mysql.sock –engine=MyISAM test liwen #能将innodb引擎改为myisam。

[root@nfs ~]# mysql_convert_table_format –user=root -password=123456 –socket=/data/3306/mysql.sock –engine=InnoDB test liwen #不能将myisam引擎改为innodb

liwen already uses the ‘MYISAM’ engine; Ignored

有关MySQL引擎常见企业面试题

MySQL有哪些存储引擎，各自有什么特点和区别？
生产环境中应如何选用MySQL的引擎？
不同的引擎如何备份？混合引擎如何备份
事务介绍

数据库事务介绍

简单地说，事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么全成功要么全失败。

事务的四大特性（ACID）

l、原子性（Atomicity)

事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生。

2、一致性（Consistency)

事务发生前和发生后，数据的完整性必须保持一致。

3、隔离性（Isolation)

当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其它的会话是不可见的，多个并发事务之间的数据是相互隔离的。还记得备份的参数么？–single-transaction

4、持久性（Durability)

一个事务一旦被提交，它对数据库中的数据改变就是永久性的。如果出了错误，事务也不允许撤消，只能通过”补偿性事务”

事务的开启（innodb）

数据库默认事务是自动提交的，也就是发一条sql它就执行一条。如果想多条sql放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql会自动回滚事务。或者我们使用rollback命令手动回滚事务。

数据库开启事务命令：

set global autocommit=OFF禁止自动提交，对当前会话无效，退出重新登录有效，重启失效

set autocommit=OFF 禁止自动提交，只对当前会话有效

set global autocommit=ON开启自动提交，对当前会话无效，退出重新登录有效，重启失效

set autocommit=ON 开启自动提交，只对当前会话有效

rollback 回滚事务

commit 提交事务

vi /data/3306/my.cnf 编辑配置文件，在[mysqld]模块下添加如下

autocommit=OFF #重启永久生效

提示：事务引擎基于表的，所以要在表上插入、更新测试事务的特性。

Mysql数据库安全权限控制管理思想

制度与流程控制

项目开发制度流程：

办公开发环境–>办公测试环境–>IDC测试环境–>IDC正式环境。通过这种较完善的项目开发制度及流程控制，尽可能的防止潜在的问题隐患发生。

数据库更新流程

开发人员提交需求–>开发主管审核–>部门领导审核–>DBA（运维）审核–>DBA（运维）执行项目开发制度及流程控制的数据库更新步骤（每个步骤都要测试），最后在工DC正式环境执行。

需要说明的是，在开发人员一开始提交需求时，就可以同时抄给以上的领导及审核人员，然后，审核人员依次审核。对于特殊紧急需求，可以根据紧急程度特殊处理，这里可以制定个紧急需求处理流程，比如：开发人员提交需求–>DBA（运维）审核，然后操作完在汇报给其它审核人员。

通过完善的数据库更新流程控制，可以防止很多潜在的数据丢失、破坏等问题发生。

DBA参与项目数据库设计

在项目开发环节上，DBA或资深运维人员最好参与数据库设计与审核工作，这样可以从源头上减少降低不良的数据库设计及不良SQL语句的发生，还可以做所有语句的审核工作，包括Select，但这个需要评估工作量是否允许，一般的互联网公司实施全审核比较困难。

各种操作申请流程

1）开发等人员权限申请流程。

2）数据库更新执行流程。

3）烂SQL语句计入KPI考核。

定期对内部人员培训

定期给开发及相关人员培训，目的还是从源头上降低不良数据库设计及不良SQL语句的发生，并通过培训让大家知晓大家数据库性能的重要性，让他大家提升开发时照顾数据库性能的意识。

1）数据库设计规范及制度。

2) SQL语句执行优化，性能优化技巧等。

3）数据库架构设计等内容。

账户权限控制

内部开发等人员权限分配

1）权限申请流程要设置规范、合理，让需求不明确者知难而退（比如老男孩老师的曾经公司开发就有上百人）。

2）办公开发和测试环境可以放开权限，idc测试和正式环境要严格控制数据库写权限，并且读权限和对外业务服务分离。

3）开发人员正式环境数据库权限分配规则：给单独的不对外服务的正式从库只读权限，不能分配线上正式主库写权限。

4）特殊人员（如领导），需要权限时，我们要问清楚他做什么，发邮件回复，注明用户名、密码、权限范围，多提醒操作注意事项，如果有可能由DBA人员代替其操作

5）特权帐号（all pritrliges)，由DBA控制，禁止在任何客户端上执行特权账号操作（如只能localhost或其他策略）。

web账户权限分配制度

1）写库账号默认权限为select,insert,update,delete。不要给建表改表（create,alter,drop）等的权限，更不能all权限。

2）读库帐号默认权限为select（配合mysql read-only参数用）。确保从库对所有非super权限是只读的。

3）最好专库专账号，不要一个账号管理多个库。碎库特别多的小公司根据情况特殊对待处理。

4）如果是lamp,Inmp一体在一台服务器的环境，db权限主机要设置为localhost，避免用root用户作为web连接用。

5)web和数据库分离的服务器的授权可以根据web服务数量多少按IP或网段来授权。

6）安全性和管理方便正总是互相矛盾的，需要达到一个较好平衡状态，如何平衡就要根据具体公司和业务来衡量了。

web账户授权实战案例

a．生产环境主库用户的账号授权：

grant select,insert,update,delete on blog.* to ‘blog’@’192.168.10.%’ identified by ‘oldboy’;

b．生产环境从库用户的授权：

grant select on blog.* to ‘blog’@’192.168.10.%’ identified by ‘oldboy’;

当然从库除了做SELECT的授权外，还可以加read-only等只读参数。

生产环境读写分离账户设置

给开发人员的读写分离用户设置，除了IP必须要不同外，我们尽量为开发人员的使用提供方便。因此，读写分离的地址，除了IP不同外，账号、密码、端口等看起来都是一样的。这才是人性化的设计。体现了运维或DBA人员的专业。

主库（尽量提供写服务）: blog oldboy456 ip:10.0.0.179 port:3306

从库（仅提供读服务）: blog oldboy456 ip:10.0.0.180 port:3306

提示：两个账号的权限是不一样的。

提示：在程序架构设计上，开发人员应该设计浏览数据优先连接读库，如果读从库数据超时后跳转到主库读数据，这样从程序设计上来保证用户的浏览体验。当然也要根据主库的繁忙程度来综合考虑。具体情况都是根据业务项目需求来抉择。

开发、运维、DBA要通力配合，才能达到一个相对完美的境界。

数据库客户端访问控制

更改默认mysql c11ent端口，如phpmyadmin管理端口为9999，其它客户端也是一样。

数据库web client端统一部署在1-2台不对外服务的web上，限制ip及9999端口只能从办公室内网访问。

不做公网域名解析，用host实现访问（限制任何IP直接访问）或者用内部IP访问

phpmyadmin站点目录独立于所有其它站点根目录外，只能由指定的域名或1P地址访问。

限制使用web连接的帐号管理数据库，根据开发人员用户角色分配指定帐号访问

按开发及相关人员根据职位角色分配适合的管理帐号。

设置指定帐号访问权限层次，web层使用apache/nginx账户验证，数据库层使用mysql用户登陆验证。

8、统一所有数据库帐号登陆入口地址。禁止所有开发人员私自上传phpadmin等数据库管理的程序。
9、开通VPN，跳板机，只能通过局域网内部IP管理数据库。

系统层控制

限制或禁止开发人员SSH ROOT管理，通过SUDO细化权限，使用日志审计。

对phpadmin端config等配置文件进行读写权限控制。

取消非指定服务器的所有phpadmin WEB连接端。

禁止非管理人员管理有数据南web c11ent端的服务器的权限。

读库分业务读写分离

细则补充：对数据库的Select等大量测试、统计、备份等操作，要在不对外提供select的单独从库执行

可以把几个不同的从服务器，根据公司的业务进行拆分。比如有为外部用户提供查询服务的从服务器，有用来备份的从服务器，还有提供公司内部后台、脚本，日志分析及开发人员服务的从服务器。这样的拆分除了减轻主服务器的压力外。使得对外用户浏览、对内处理公司内部用户业务，及DBA备份业务互不影响。具体可以用下面的简单架构来说明：

主从架构生产环境从服务器分业务拆分使用案例：

M–>S1==>对外部用户提供服务（浏览帖子、浏览博客、浏览文章）

–>S2==>对外部用户提供服务（浏览帖子、浏览博客、浏览文章）

–>S3==>对外部用户提供服务（浏览帖子、浏览博客、浏览文章）

–>S4==>对内部用户提供服务（后台访问、脚本任务、数据分析、开发人员浏览）

–>S5==>数据库备份服务（开启从服务器binlog功能，可实现增量备份及恢复）

数据库运维管理思想核心

未雨绸缪,不要停留在制度上，而是，实际做出来

亡羊补牢,举一反三，切记，不能好了伤疤忘了疼

完备的架构设计及备份、恢复策略。

定期思考、并实战模拟以上策略演练。

未雨绸缪永远比出了问题在处理要好的多，出了问题补救是没办法不得已的事，最差的是很多公司，没有亡羊补牢，而是好了伤疤忘了疼，没过多久问题又发生了。

因此，在工作中要尽量做到未雨绸缪，从源头上减少故障的发生。其次，要做到亡羊补牢、举一反三，事情出现一次就不能在出现第二次。当然，完善的备份和恢复策略也是需要做的。只有把这些结合起来，才能把我们运维的工作做的更好。

mysql数据库优化

MySQL主从同步高可用方案

MySQL主从同步高可用方案（一）

1、高可用软件：keepalived,heartbeat，只负责VIP白彻换即可。

2、此高可用架构部署简单，容易维护。

4、Master故障后业务可自动切换到Slave。

5、读写都依赖主库，压力大。死锁，锁等待。

6、S1ave可以提供读查询服务，但是依赖程序。

MySQL主从同步高可用方案（二）

1、高可用软件：keepalived,heartbeat，只负责VIP的切换即可。

2、Master故障后主库业务自动切换到Slavel。

3、支持读写分离，写Master，读Slavel/2，但必须通过程序代码实现

问题：切换后，Slave2可能无法和接管主库的Slave1自动同步了。

解决：利用半同步实时同步机制。

MySQL主从同步高可用方案（三）

1、高可用软件：keepalived+LVS,MMM。

2、双主同步后，可以最两个Master做LVS负载均衡了。挂掉一个业务不受影响。

3、问题：双写会带来严重的问题，增加数据不一致问题的几率。

4、双写对性能的提升不大，属于复杂化但又没有太多好处的架构方案，不推荐。

MySQL主从同步高可用方案（四）

MySQL主从同步高可用方案（五）

问题：A．从库为级联同步，可能同步延迟．B.Master2宕机，Slave同步中断。

MySQL第三方基于DRBD高可用方案（一）

问题：Passive Server作为备用节点时不能提供访问

MySQL第三方基于DRBD高可用方案（二）

1、高可用软件heartbeat,HA不但负责VIP的切换，还要负责数据库，DRBD服务的管理。

2、主库故障后业务自动切换到Backup,Slavel/2依然可以和新的主库同步。

3、Slavel/2支持读写分离，但是必须在程序上实现。

4、此方案也支持实时和异步写入。

MySQL第三方基于SAN共享存储高可用方案（一）

1、高可用软件：RedHat Cluster Site

2、BackuP无法正常开启。

3、Slavel/2支持读写分离．

4、业务依赖SAN存储。

MySQL第三方基于DNS解析高可用方案（一）

MySQL第三方基于DBProxy高可用方案

MySQL第三方分布式数据库高可用方案

MySQL第三方分布式数据库集群百度二期案例

MySQL第三方基于Galera高可用方案

架构方案：Ga1era是一套在MySQL InnoDB上面实现Multi-master且Synchronousreplication的集群系统。

MySQL官方cluster高可用方案

MySQL高可用架构方案的选择依据

发送评论 编辑评论

发送评论编辑评论