1. Mysql安装配置文档

1.1 软件版本

采用Mysql5.6.26(Mysql5.6版本中最后版本)

1.2 软件安装

1.2.1 安装

采用源码编译安装，具体如下

cd /usr/local/src/
wget&nbsp;http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.26.tar.gz
yum install -y gcc gcc-c++ make cmake ncurses-devel libtool zilib-devel bison bison-devel
groupadd -g 27 mysql&nbsp; #linux默认使用27作为mysql用户的gid和uid，建议所有服务器统一。
useradd -g 27 -u 27 -r -s /sbin/nologin -M mysql
mkdir -p /data/mysql3306/{data,log,tmp}
chown mysql.mysql /data/mysql3306/
tar xf mysql-5.6.26.tar.gz
cd mysql-5.6.26
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.6.26 \
-DMYSQL_DATADIR=/data/mysql3306/data/ \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DDEFAULT_CHARSET=utf8 \
-DMYSQL_UNIX_ADDR=/data/mysql3306/tmp/mysql.sock \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install
ln -s /usr/local/mysql-5.6.26/ /usr/local/mysql

1.2.2 配置Mysql环境变量

export PATH=/usr/local/mysql/bin:$PATH
export MYSQL_PS1="(\u@\h:\p) [\d]>"
#MYSQL_PS1: mysql客户端提示符(user@host:port/sock)  [databasename]

1.2.3 配置MysqlServer管理

Mysql管理方式分命令行与系统管理脚本两种：推荐使用系统管理脚本

第一，命令行方式：

#/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3306/my.cnf &

第二，系统管理脚本：默认配置文件路径为/etc/my.cnf

cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start|stop|restart|reload|force-reload|status
chkconfig mysqld on

1.3 数据库初始化

安装完毕，接下来需要配置my.cnf并对数据库进行初始化。

1.3.1 配置文件my.cnf

拷贝如下内容到/etc/my.cnf文件中，并根据提示以及实际情况如内存，并发访问连接数等对部分参数进行调整。

注意：my.cnf有一个参数覆盖原则：

#mysql --help |grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/mysql/my.cnf  /etc/my.cnf  ~/.my.cnf

如果采用/etc/init.d/mysqld方式来管理Mysql Server，则Mysql Server会按照这个顺序来寻找my.cnf文件，后面的my.cnf文件中定义的参数值会覆盖前面的my.cnf中

相同参数的配置。所以，请先执行如下命令，确定当前环境中mysql配置文件情况：

#find / -name my.cnf ;ls ~/.my.cnf

确认是否存在上面列举路径中的my.cnf，如有则删除或者改名，以免影响预计配置参数。

-----------------------------------------my.cnf-------------------------------------------
[client]
port              =     3306
socket          =     /data/mysql3306/tmp/mysql.sock
default_character_set       =     utf8

[mysqld]
port              =     3306
bind_address      =     0.0.0.0
basedir         =     /usr/local/mysql-5.6.26/
datadir         =     /data/mysql3306/data
user              =     mysql
tmpdir          =     /data/mysql3306/tmp
pid-file  =     /data/mysql3306/tmp/mysql.pid
socket          =     /data/mysql3306/tmp/mysql.sock

skip_external_locking
skip_name_resolve
character_set_server =     utf8
lower_case_table_names =     1
default_storage_engine   =     INNODB
#wait_timeout           =     100
#interactive_timeout =     100

back_log             =     1024  #limited by /proc/sys/net/core/somaxconn(default 128),所以设置再大也没有意义,要先调somaxconn.
myisam_recover_options
myisam_repair_threads    =     1
event_scheduler        =     ON
explicit_defaults_for_timestamp   = true #http://www.williamsang.com/archives/818.html

###########slow log###########
slow_query_log         =     1
slow_query_log_file  =     /data/mysql3306/log/slow.log
long_query_time              =     1
log_queries_not_using_indexes =       1
log_throttle_queries_not_using_indexes = 10

########error log#############
log_error             =     /data/mysql3306/log/error.log

###########bin log############
log_bin                =     /data/mysql3306/log/mysql-bin
log_bin_index            =     /data/mysql3306/log/mysql-bin.index
binlog_format           =     row
sync_binlog        =     1   #为避免mster宕机导致binlog丢失事务,master建议设置为1,mysql5.5中有bug此参数=1配合innodb_flush_log_at_trx_commit=1会导致组提交失效,导致性能急剧下降,需要将innodb_flush_log_at_trx_commit设为2来避免此问题.
binlog_cache_size     =     2M  #这个可以在后续根据情况进行调整
max_binlog_cache_size   =     4M
expire_logs_days       =     10

#########replication##########
server_id      =     102223306    #为4字节整数:0-2的32次方-1(0-4294967295),命名:ip3,4段+端口号,若三段为0,则前面加1.
#read_only          =     1
#slave_net_timeout   =     10
#relay_log           =     /data/mysql3306/log/relay-bin
#relay_log_index       =     /data/mysql3306/log/relay-bin.index
#master_info_file      =     /data/mysql3306/log/master.info
#relay_log_info_file   =     /data/mysql3306/log/relay.info
#sync_master_info    =     1 #这两个sync值默认为10000,是缓存写的,意味着如果slave宕机重启,这俩文件缓存的信息会清空,relay_log_recovery会让slave重新去master上获取relay_log,从而导致主从不一致.是否开启要看slave磁盘IOPS是否是主从延时的原因.
#sync_relay_log_info =     1
#relay_log_recovery  =     1 #slave宕机则清除本地relay_log并重新从master获取relay_log
#skip_slave_start
#log_slave_updates
#replicate_wild_ignore_table=mysql.%
#replicate_wild_ignore_table=test.%
#replicate_wild_do_table=xxxx.%  #使用该参数显式指定了某个库要同步,则别的需要同步的库都需要显式指定,否则,不会同步

#######per_thread_buffer#######
max_connections             =     1024
max_connect_errors  =     1000000
max_user_connections    =     1024
open_files_limit  =     65535
key_buffer_size          =     64M
max_allowed_packet =     128M
table_open_cache     =     2048  #http://www.cnblogs.com/suredandan/p/4010931.html
table_definition_cache    =     600
read_buffer_size =     32M  #这里要注意,这4个参数是per_thread的,需要根据内存和并发数的实际情况来调整.我这里是32-24=8G
read_rnd_buffer_size =     32M
join_buffer_size  =     32M
sort_buffer_size  =     64M
tmp_table_size          =     256M  #这两个也是per_thread的,需要根据情况来定.如果超过他们限制,就会写myisam表到硬盘/tmp
max_heap_table_size       =     256M
#query_cache_limit   =     32M       #缓存select结果集,write多,数据经常变化,不建议打开;以读为主,写操作少的场景,建议打开.
#query_cache_min_res_unit =      3k
#query_cache_size    =     256M
query_cache_type     =     0
query_cache_size      =     0
bulk_insert_buffer_size    =     8M
thread_cache_size     =     128
#thread_concurrency       =     12  #setting for only Solaris 8 and earlier.这个参数要谨慎设置,官方推荐设置为cpu的2倍,但是在5.7版本已经将此参数移除,这里建议保留默认值.不做更改
thread_stack              =     256k  #默认值就是256k

###########innodb############
innodb_data_file_path            =     ibdata1:2G:autoextend
innodb_log_file_size        =     1024M
innodb_log_files_in_group     =     3
innodb_log_buffer_size          =     256M
innodb_buffer_pool_size        =     24G  #官网推荐内存的75%-80%
innodb_buffer_pool_instances     =     8  #缓冲池数量.
innodb_flush_log_at_trx_commit  =     1 #主库上建议1, 从库建议2.Mysql5.5注意设为2.
innodb_support_xa          =     1
innodb_flush_method            =     'O_DIRECT'
innodb_lock_wait_timeout     =     100 #默认值是50,根据业务场景来调整.
innodb_max_dirty_pages_pct =     90
log_bin_trust_function_creators =       1
innodb_file_per_table             =     1
innodb_file_format          =     barracuda
innodb_file_format_max         =     barracuda
innodb_strict_mode         =     1
#innodb_thread_concurrency       =     16 #默认0,不限制
innodb_read_io_threads         =     4 #根据读写密集度来调整
innodb_write_io_threads        =     8
innodb_io_capacity          =     400 #根据硬盘IOPS情况来设定，不宜过大
innodb_purge_threads           =     1
transaction_isolation              =     READ-COMMITTED

[mysqldump]
quick
max_allowed_packet = 256M

---------------------------------------my.cnf---------------------------------------------

1.3.2 数据库初始化

cd /usr/local/mysql
scripts/mysql_install_db --defaults-file=/data/mysql3306/my.cnf
chown mysql.mysql /data/mysql3306/ -R

1.4 数据库授权

1.4.1 授权准则

删除所有用户名为空的用户

不允许密码为空的用户存在

管理员用户可以有所有库权限，并有授权权限

开发应用只需给相应库的权限

1.4.2 清理所有空用户和空密码用户

mysql>select user,host,password from mysql.user;
mysql>drop user ' '@'localhost';
mysql>drop user ' '@'your hostname';
mysql>drop user 'root'@'localhost';
mysql>drop user 'root'@'127.0.0.1';
mysql>drop user 'root'@'::1';
mysql>drop user 'root'@'your hostname';

1.4.3 给root以及应用用户授权

mysql>grant all privileges on *.* to 'root'@'localhost' identified by 'your password' with grant option;
mysql>grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'you password' with grant option;
mysql>flush privileges;
mysql>grant select on *.* to 'test1'@'your ipaddress' identified by 'your password';
mysql>grant select,insert,update,delete,index,create routine,alter routine,execute,trigger on test.* to 'test'@'%' identified by 'your password';
mysql>flush privileges;

1.4.3.1 微票测试数据库添加授权：后续新增的数据库需要单独授权

MariaDB [gjdyzjb]> grant all privileges on *.* to 'root'@'%' identified by 'your password' with grant option;
MariaDB [gjdyzjb]> grant select on *.* to 'wepiao_reader'@'%' identified by 'your password';
MariaDB [gjdyzjb]> flush privileges;

针对后续添加的数据库，wepiao_reader账号也只有select权限，如果需要添加其他权限，可使用如下语句：

mysql>grant all on your_database_name.* to 'wepiao_reader'@'%' identified by 'your password';

1.4.4 修改用户密码

mysql>update mysql.user set password=password('123456') where user='root';
mysql>flush privileges;

1.4.5 查看用户权限

mysql>show grants for 'root'@'localhost';
mysql>show grants for 'test'@'%';
mysql>show grants for 'test1'@'your ipaddress';

1.4.6 权限的回收以及用户的删除

回收权限使用revoke，删除用户使用drop，不推荐使用delete命令直接对mysql.user表进行操作。

回收权限：注意，revoke只回收权限，并不删除用户

mysql>revoke select on *.* from 'test1'@'your ipaddress';
mysql>revoke all on test.* from 'test'@'%';

删除用户：

mysql>drop user 'test'@'%';
mysql>drop user 'test1'@'your password';

1.5 数据导入导出：mysqldump

1.5.1 基于主从需求的导出导入

导出

/usr/local/mysql/bin/mysqldump -urepldata -p -h10.251.80.80 --single-transaction (--add-drop-database) --master-data=2 --databases results_professional wepiao_professional_v2 gjdyzjb wepiao_base_info wepiao_movie > professional_db.`date +%F`.sql

解析：

–single-transaction: 创建一致性快照（只适用于Innodb存储引擎），避免dump过程中锁表。同时，dump过程中不能存在如下操作：ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE。

–add-drop-database: 在dump出来的sql中添加create database语句（如果dump的对象只有一个数据库，该语句无效）。但是根据实际使用情况来看，新增的create database语句不会指定字符集。所以，这个选项不建议使用。建议导入前手动建立数据库。

–master-data: 会在dump出来的sql中增加master的binlog File和Position信息。设定为1，此新增的信息不会被注释；设定为2，此新增的信息会被注释。如果未启用GTID来实现复制的话，建议设定为2。

–databases: 只dump指定的数据库

导入

在新mysql实例上新建所有被导出的数据库，再导入，具体参考如下命令：

CREATE DATABASE IF NOT EXISTS my_testDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
…
mysql –uroot –p < dumpfilename.sql

1.5.2 导出数据库、函数、存储过程以及触发器

mysqldump --single-transcation --master-data=2 -A --triggers -R >all.sql

1.5.3 只导出数据结构

mysqldump –single-transaction --master-data=2 -A --no-data >all.sql   #导出全库表结构
mysqldump –single-transaction --master-data=2 --databases database_name --no-data

>database_name.sql #导出指定库表结构

1.5.4 导出并压缩

导出并压缩：mysqldump –single-transaction --master-data=2 |gzip > all.sql.gz
解压并导入：gunzip -f < all.sql.gz | mysql -u root -p

1.5.5 导出单表并压缩

/usr/local/mysql/bin/mysqldump -uroot -p -h10.104.9.129 --single-transaction wepiao_movie movie_schedule_seat_minutes |gzip > /data/backup/10.104.17.197_6_tables_20150925/wepiao_movie.movie_schedule_seat_minutes.sql.gz
gunzip < wepiao_movie.movie_schedule_seat_minutes.sql.gz |/usr/local/mysql/bin/mysql -uroot -p -h10.104.9.129 wxmovie_basic_backup

1.6 Mysql多实例配置

1.6.1 新实例初始化

关闭监听3306端口的实例，然后基于我们前面已经创建了一个3306端口的实例，拷贝初始化配置文件并根据实际情况做相应修改，初始化新数据库实例。

mkdir -p /data/mysql3307/{data,log,tmp}
chown mysql.mysql mysql3307/ -R
cp –a /etc/my.cnf /data/mysql3307/
sed -i 's/3306/3307/g' /data/mysql3307/my.cnf
scripts/mysql_install_db --defaults-file=/data/mysql3307/my.cnf
chown mysql.mysql mysql3307/ -R

单独启动此实例，修改此实例root用户密码信息（注意：同一数据库服务器上的所有数据库实例的密码需一致。）：

mysql -uroot -p -P3307 -h127.0.0.1
(root@127.0.0.1:3307) [(none)]>drop user 'root'@'localhost';
(root@127.0.0.1:3307) [(none)]>drop user 'root'@'127.0.0.1';
(root@127.0.0.1:3307) [(none)]>drop user 'root'@'::1';
(root@127.0.0.1:3307) [(none)]>drop user 'root'@'your hostname';
(root@127.0.0.1:3307) [(none)]>drop user ' '@'localhost';
(root@127.0.0.1:3307) [(none)]>drop user ' '@'your hostname';
(root@127.0.0.1:3307) [(none)]>grant all on *.* to 'root'@'localhost' identified by 'your password' with grant option;
(root@127.0.0.1:3307) [(none)]>grant all on *.* to 'root'@'127.0.0.1' identified by 'your password' with grant option;
(root@127.0.0.1:3307) [(none)]>flush privileges;

然后关闭3307端口数据库：

mysqladmin -uroot -p -h127.0.0.1 -P3307 shutdown

1.6.2 修改/etc/my.cnf

多实例支持如下配置块:

———————————mysqld-multi简介————————————-

[mysql] #client配置块

[mysqld_multi] #多实例控制配置块

[mysqld] #多实例相同参数配置块

[mysqld-5.5] #mysql5.5特有参数配置块

[mysqld-5.6] #mysql5.6特有参数配置块

[mysqld-5.7] #mysql5.7特有参数配置块

[mysqld1] #实例1特别配置块

[mysqld2] #实例2 特别配置块

[mysqld3] #实例3 特别配置块

。。。。。。

[mysqldump] #mysqldump参数配置块

。。。。。。

———————————mysqld-multi简介————————————–

mysqld-multi示例配置文件： /etc/my.cnf

————————————-mysqld-multi—————————————

[client]

user              =     root

password     =     123456

#port            =     3306

#socket        =     /data/mysql3306/tmp/mysql.sock

default_character_set       =     utf8



[mysqld_multi]

mysqld         =     /usr/local/mysql/bin/mysqld_safe

mysqladmin       =     /usr/local/mysql/bin/mysqladmin

log         =     /data/mysql3306/log/mysqld_multi.log



[mysqld]

#port            =     3306

#bind_address   =     0.0.0.0

#basedir             =     /usr/local/mysql-5.6.26/

#datadir              =     /data/mysql3306/data

#user            =     mysql

#tmpdir        =     /data/mysql3306/tmp

#pid-file       =     /data/mysql3306/tmp/mysql.pid

#socket        =     /data/mysql3306/tmp/mysql.sock

。。。。。。



[mysqld1]

port              =     3306

bind_address      =     0.0.0.0

basedir         =     /usr/local/mysql-5.6.26/

datadir         =     /data/mysql3306/data

user              =     mysql

tmpdir          =     /data/mysql3306/tmp

pid-file  =     /data/mysql3306/tmp/mysql.pid

socket          =     /data/mysql3306/tmp/mysql.sock



slow_query_log_file  =     /data/mysql3306/log/slow.log

log_error             =     /data/mysql3306/log/error.log

log_bin                =     /data/mysql3306/log/mysql-bin

log_bin_index            =     /data/mysql3306/log/mysql-bin.index

server_id      =     102223306    #为4字节整数:0-2的32次方-1(0-4294967295),命名:ip3,4段+端口号,若三段为0,则前面加1.



[mysqld2]

port              =     3307

bind_address      =     0.0.0.0

basedir         =     /usr/local/mysql-5.6.26/

datadir         =     /data/mysql3307/data

user              =     mysql

tmpdir          =     /data/mysql3307/tmp

pid-file  =     /data/mysql3307/tmp/mysql.pid

socket          =     /data/mysql3307/tmp/mysql.sock



slow_query_log_file  =     /data/mysql3307/log/slow.log

log_error             =     /data/mysql3307/log/error.log

log_bin                =     /data/mysql3307/log/mysql-bin

log_bin_index            =     /data/mysql3307/log/mysql-bin.index

server_id      =     102223307    #为4字节整数:0-2的32次方-1(0-4294967295),命名:ip3,4段+端口号,若三段为0,则前面加1.



[mysqldump]

quick

max_allowed_packet = 256M

————————————-mysqld-multi—————————————

1.6.3 启动mysqld_multi
#mv /etc/init.d/mysqld /etc/init.d/mysqld.server

#chmod –x /etc/init.d/mysqld.server

#cp -a /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi



#/etc/init.d/mysqld_multi使用方法：

#/etc/init.d/mysqld_multi start  #启动所有实例

#/etc/init.d/mysqld_multi stop         #停止所有实例

#/etc/init.d/mysqld_multi start 1  #启动编号为1的实例

#/etc/init.d/mysqld_multi start 2    #启动编号为2的实例

#/etc/init.d/mysqld_multi stop 1  #停止编号为1的实例

#/etc/init.d/mysqld_multi stop 2  #停止编号为2的实例

#/etc/init.d/mysqld_multi restart 1 #重启编号为1的实例

#/etc/init.d/mysqld_multi restart 2 #重启编号为2的实例

#/etc/init.d/mysqld_multi report  #查看所有实例运行状态

#/etc/init.d/mysqld_multi report 1 #查看编号为1的实例的运行状态

#/etc/init.d/mysqld_multi report 2 #查看编号为2的实例的运行状态

1.7 备份与恢复

备份与恢复这部分目前可选工具有2个，分别为Mysqldump与Xtrabackup：目前暂时用Mysqldump工具来实施数据库的备份方案。

1.7.1 Mysqldump：详情请见数据库导入和导出。

1.7.2 Xtrabackup

1.7.2.1 安装

#yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

#yum install percona-xtrabackup

1.7.2.2 备份脚本

此脚本基于前一个备份(可能是全备，也可能是增备)来判断接下来该执行哪一个备份，7天一轮(一个全备，6个增备)，数据事实上保留14天

—————–xtrabackup.sh—————————

cat /opt/xtrabackup/xtrabackup.sh
#!/bin/bash
BEGINTIME=`date +"%Y-%m-%d %H:%M:%S"` #begining time
format_time=`date +"%Y-%m-%d_%H:%M:%S"`
#oldDate=$(date -d "-7 days" "+%Y-%m-%d_%H:%M:%S")
oldDate=$(date -d "-7 days" "+%Y-%m-%d_%H")
week=`date +%w` #week number
backdir=/data/Xtrabackup #backup folder
#file_cnf=/etc/my.cnf
file_cnf=/data/mysql3306/my.cnf
user_name=root
password=123456
out_log=$backdir/xtrabackup_log_$format_time
time_cost=$backdir/xtrabackup_time.txt
if [ -d "$backdir/incr6" ];then
rm -rf $backdir-${oldDate}*
mv $backdir $backdir-$format_time
mkdir $backdir
else
mkdir -p $backdir
fi

###### Start backup now, Monday full backup other days is incremental backup ######

if [ ! -d "$backdir/full" ];then
echo "#####start monday full backup at $BEGINTIME to directory full" >>$time_cost
innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password $backdir/full 1> $out_log 2>&1
echo "#####finash monday full backup at `date +"%Y-%m-%d %H:%M:%S"` to directory full" >>$time_cost
exit 1
fi
if [ ! -d "$backdir/incr1" ];then
echo "#####start incremental 1 backup at $BEGINTIME to directory incr1" >>$time_cost
innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental-basedir=$backdir/full --no-timestamp --incremental $backdir/incr1 1> $out_log 2>&1
exit 1
fi
if [ ! -d "$backdir/incr2" ];then
echo "#####start incremental 2 backup at $BEGINTIME to directory incr2" >>$time_cost
innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir/incr1 $backdir/incr2 1> $out_log 2>&1
exit 1
fi
if [ ! -d "$backdir/incr3" ];then
echo "#####start incremental 3 backup at $BEGINTIME to directory incr3" >>$time_cost
innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir/incr2 $backdir/incr3 1> $out_log 2>&1
exit 1
fi
if [ ! -d "$backdir/incr4" ];then
echo "#####start incremental 4 backup at $BEGINTIME to directory incr4" >>$time_cost
innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir/incr3 $backdir/incr4 1> $out_log 2>&1
exit 1
fi
if [ ! -d "$backdir/incr5" ];then
echo "#####start incremental 5 backup at $BEGINTIME to directory incr5" >>$time_cost
innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir/incr4 $backdir/incr5 1> $out_log 2>&1
exit 1
fi
if [ ! -d "$backdir/incr6" ];then
echo "#####start incremental 6 backup at $BEGINTIME to directory incr6" >>$time_cost
innobackupex --defaults-file=$file_cnf --no-timestamp --user=$user_name --password=$password --incremental --incremental-basedir=$backdir/incr5 $backdir/incr6 1> $out_log 2>&1
exit 1
fi### END weekly backup ###

—————–xtrabackup.sh—————————

1.7.2.3 恢复脚本

Xtrabackup备份出来的文件，在进行恢复时，请注意以下几点：

1). 有几个备份文件，就使用哪几条恢复语句，顺序执行，多余的语句注释掉

2). 如果只有一个全备，无增量备份，在恢复时可将全备恢复命令中的–redo-only配置项去掉，以在恢复的时候即生成ib_logfile

3). 如果存在增备，在恢复时完所有增量备份后，可选择对恢复后的全量再执行一次apply log，以生成ib_logfile。

4).如果不按第2和第3条方式执行，恢复后的full目录中是没有ib_logfile文件的，会在启动数据库的时候根据指定的配置文件来重新生成。

—————–xtrarecover.sh—————————

#!/bin/bash

#backdir=/databackup/db_backup/Xtrabackup
backdir=/data/Xtrabackup

innobackupex --apply-log --redo-only --use-memory=4G $backdir/full/ 1>$backdir/huifu.log 2>&1
innobackupex --apply-log --redo-only --use-memory=4G $backdir/full/ --incremental-dir=$backdir/incr1/ 1>$backdir/huifu.log1 2>&1
innobackupex --apply-log --redo-only --use-memory=4G $backdir/full/ --incremental-dir=$backdir/incr2/ 1>$backdir/huifu.log2 2>&1
innobackupex --apply-log --redo-only --use-memory=4G $backdir/full/ --incremental-dir=$backdir/incr3/ 1>$backdir/huifu.log3 2>&1
innobackupex --apply-log --redo-only --use-memory=4G $backdir/full/ --incremental-dir=$backdir/incr4/ 1>$backdir/huifu.log4 2>&1
innobackupex --apply-log --redo-only --use-memory=4G $backdir/full/ --incremental-dir=$backdir/incr5/ 1>$backdir/huifu.log 2>&1
innobackupex --apply-log --use-memory=4G $backdir/full/ --incremental-dir=$backdir/incr6/ 1>$backdir/huifu.log 2>&1

innobackupex --apply-log --redo-only --use-memory=4G $backdir/full/ 1>$backdir/huifu.log 2>&1  #可选
innobackupex --defaults-file=/data/mysql3307/my.cnf --user=root --copy-back $backdir/full/
chown mysql.mysql /data/mysql3307/data/ -R
cd /usr/local/mysql
bin/mysqld_safe --defaults-file=/data/mysql3307/my.cnf &

—————–xtrarecover.sh—————————

1.7.2.4 Xtrabackup注意事项

1). Xtrabackup备份出来的文件只能恢复到同版本会更高版本的数据库实例中去，能向上兼容，不能向下兼容。比如，Mysql5.6的实例备份出来的文件，无法导入Mysql5.5版本的实例。Mariadb也一样。

2). Mysql实例备份出来的文件也无法导入到Mariadb的实例中，不兼容。

1.8 Mysql主从配置(Replication)

基于Mysqldump工具来提供备份文件以及Master的相关信息。

1.8.1 环境准备

Master: 10.10.0.222 3306

Slave: 10.10.0.222 3307

需要同步的数据库: test1,test2

1.8.2 准备备份文件

#mysqldump -uroot -p -h127.0.0.1 -P3306 --single-transaction --master-data=2 --databases test1 test2 |gzip > test1_2.2.sql.gz

1.8.3 主库上添加授权

(root@127.0.0.1:3306) [(none)]>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'127.0.0.1'  IDENTIFIED BY  'repl';

(root@127.0.0.1:3306) [(none)]>flush privileges;

1.8.4 修改从库配置文件，增加从库参数

——————————————-slave.cnf————————————–

read_only            =     1  #这个参数根据是否在同步外还需要创建别的数据库来定

slave_net_timeout     =     10

relay_log             =       /data/mysql3307/log/relay-bin

relay_log_index         =       /data/mysql3307/log/relay-bin.index

master_info_file  =       /data/mysql3307/log/master.info

relay_log_info_file     =       /data/mysql3307/log/relay.info

sync_master_info      =       1 #这两个sync值默认为10000,是缓存写的,意味着如果slave宕机重启,这俩文件缓存的信息会清空,relay_log_recovery会让slave重新去master上获取relay_log,从而导致主从不一致.是否开启要看slave磁盘IOPS是否是主从延时的原因.

sync_relay_log_info   =       1

relay_log_recovery    =       1 #slave宕机则清除本地relay_log并重新从master获取relay_log

skip_slave_start

log_slave_updates



#replicate_wild_ignore_table=mysql.%

#replicate_wild_ignore_table=test.%

replicate_wild_do_table=test1.% #使用该参数显式指定了某个库要同步,则别的需要同步的库都需要显式指定,否则,不会同步

replicate_wild_do_table=test2.%

——————————————-slave.cnf————————————–

1.8.5 从库重启，导入备份并配置同步

/etc/init.d/mysqld_multi restart 2

(root@127.0.0.1:3307) [(none)]>set sql_log_bin=0;

gunzip -f < test1_2.2.sql.gz |mysql -uroot -p -h127.0.0.1 -P3307

(root@127.0.0.1:3307) [(none)]>set sql_log_bin=1;

(root@127.0.0.1:3307) [(none)]>change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000017',master_log_pos=120;

(root@127.0.0.1:3307) [(none)]>start slave;

(root@127.0.0.1:3307) [(none)]>show slave status \G;

1.8.6 主从同步的一致性问题

由于目前我们采用的是异步复制模式，所以存在如下crash safe问题：

第一，Master crash safe

针对这个问题，我们可以通过修改如下参数来尽最大可能的减少Mysql进程宕机或者Mysql Server服务器宕机带来的crash问题。对主库的配置文件定义如下参数：

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

innodb-xa-support = 1

innodb_flush_log_at_trx_commit：

设为1，Mysql在每一次事务提交都会把innodb log buffer中的数据写入ib_logfile并刷入到磁盘，只在DB服务器宕机的情况下才会丢失一个事务。

设为0，mysql会每秒一次写入ib_logfile，然后由OS来决定刷入磁盘的策略。

设为2，mysql会每次事务提交写入ib_logfile，然后每秒一次刷入磁盘，但是这个每秒一次并不是100%保证。

sync_binlog:

设为N（N>0），Mysql会在每写N次binlog时，将binlog_cache中的数据刷到磁盘，以保证数据库中数据与binlog的一致性。

设为0，Mysql不刷binlog到磁盘，而依赖OS的策略来刷磁盘。

innodb-xa-support

设为1，支持binlog与innodb redo log之间的数据一致性。

不利影响：

增加了磁盘IO压力，部分降低性能，至于降低的百分比，需要根据具体环境具体测试才能得出。另外，Mysql5.5上存在bug，如果设定innodb_flush_log_at_trx_commit和sync_binlog为1，会导致group commit失效，导致性能大幅下降，在5.6版本中，该bug已解决。

第二，Slave crash safe

sync_master_info = 1，sync_relay_log_info = 1

这两个sync值默认为10000，这个10000是写在缓存里的，意味着如果slave宕机重启，这俩文件缓存的信息在flush到磁盘之前被清空，而relay_log_recovery=1会让slave重新去master上获取relay_log，从而导致从库重复写(出现如1062错误等)，继而导致主从不一致。是否开启要看slave机器磁盘IOPS是否是导致主从延时的原因。

relay_log_recovery = 1

slave宕机则清除本地已经接收的relay_log并重新从master获取relay_log。这个重新拉取的点，取决于relay_log_info文件中的记录。

但是在从库上，即使设定了如上三个参数，如果从库宕机，也是会丢失至少一个事务的。所以其实还有另一种配置方案，relay-log.info的信息保存在innodb的事务表中，如果出现slave crash，则会回滚：

relay_log_info_repository = TABLE

master_info_repository=TABLE

不过这种方案我还需要再验证测试，目前暂未采用。

排除链式复制的环境，从库上可以考虑将innodb_flush_log_at_trx_commit设为2，关闭binlog。

主从一致性检测与修复工具：有待调研和测试

• pt-table-checksum

• pt-table-sync

2. MariaDB安装配置文档

2.1 软件版本

采用MariaDB 10.0.20版本

2.2 软件安装

采用二进制方式安装

#yum install zlib-devel openssl-devel gcc-c++ gcc bison cmake ncurses-devel -y

#wget https://downloads.mariadb.org/interstitial/mariadb-10.0.20/bintar-linux-x86_64/mariadb-10.0.20-linux-x86_64.tar.gz/from/http%3A//mirrors.accretive-networks.net/mariadb

#groupadd -g 27 mysql  #linux默认使用27作为mysql用户的gid和uid，建议所有服务器统一。

#useradd -g 27 -u 27 -r -s /sbin/nologin -M mysql

#tar –zxvf mariadb-10.0.20-linux-x86_64.tar.gz  -C　/usr/local/

#mv /usr/local/ mariadb-10.0.20-linux-x86_64  /usr/local/mariadb10.0.20

#mkdir -p /data/mariadb3308/{data,log,tmp}

#chown mysql.mysql /data/mariadb3308/ -R

#sed -i 's/mysql3307/mariadb3308/g' my.cnf

#sed -i '/^basedir/s/.*/basedir\ =\ \/usr\/local\/mariadb10.0.20/g' my.cnf

初始化数据库实例

#cd /usr/local/mariadb10.0.20/

#scripts/mysql_install_db --defaults-file=/data/mariadb3308/my.cnf

服务控制，授权，多实例等请参考mysql的配置，在此我主要使用mysqld_safe直接启动服务来测试多主一从，具体安装配置的话就不详述了。



#/usr/local/mariadb10.0.20/bin/mysqld_safe --defaults-file=/data/mariadb3308/

my.cnf &

报了两个Warning如下，忽略：

[Warning] 'explicit-defaults-for-timestamp' is MySQL 5.6 compatible option. To be implemented in later versions.

[Warning] 'log-throttle-queries-not-using-indexes' is MySQL 5.6 compatible option. To be implemented in later versions.

2.3 Mariadb多主一从配置

2.3.1 环境介绍

Mariadb多主一从有一个前提：各个主Mysql服务器的数据库(Schema)要唯一

Master1: 10.10.0.222 3306 test1,test2

Master2: 10.10.0.222 3307 test3,test4 (与3306的主从关系在此清除掉)

Slave: 10.10.0.222 3308

2.3.2 授权与备份

在本机的3306和3307这两个实例上分别授权：

mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'127.0.0.1'  IDENTIFIED BY  'repl';

mysql>flush privileges;

分别备份3306实例的test1,test2和3307实例的test3,test4

#mysqldump -uroot -p -h127.0.0.1 -P3306 --single-transaction --master-data=2 --databases test1 test2 |gzip > test1test2.sql.gz

#mysqldump -uroot -p -h127.0.0.1 -P3307 --single-transaction --master-data=2 --databases test3 test4 |gzip > test3test4.sql.gz

2.3.3 修改从库配置，导入从库

修改配置：

———————————–slave my.cnf——————————————-

server_id      =     102223308    #为4字节整数:0-2的32次方-1(0-4294967295),命名:ip3,4段+端口号,若三段为0,则前面加1.

read_only            =     1

slave_net_timeout     =     10

relay_log             =     /data/mariadb3308/log/relay-bin

relay_log_index  =     /data/mariadb3308/log/relay-bin.index

master_info_file  =     /data/mariadb3308/log/master.info

relay_log_info_file     =     /data/mariadb3308/log/relay.info

sync_master_info      =     1 #这两个sync值默认为10000,是缓存写的,意味着如果slave宕机重启,这俩文件缓存的信息会清空,relay_log_recovery会让slave重新去master上获取relay_log,从而导致主从不一致.是否开启要看slave磁盘IOPS是否是主从延时的原因.

sync_relay_log_info   =     1

relay_log_recovery    =     1 #slave宕机则清除本地relay_log并重新从master获取relay_log

skip_slave_start

log_slave_updates



#replicate_wild_ignore_table=mysql.%

#replicate_wild_ignore_table=test.%

replicate_wild_do_table=test1.%

replicate_wild_do_table=test2.%

replicate_wild_do_table=test3.%

replicate_wild_do_table=test4.%