1. Mysql安装配置文档
1.1 软件版本
采用Mysql5.6.26(Mysql5.6版本中最后版本)
1.2 软件安装
1.2.1 安装
采用源码编译安装,具体如下
cd /usr/local/src/ wget 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 #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.%
———————————–slave my.cnf——————————————-
导入从库:
(root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; (root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test2 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; (root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test3 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; (root@127.0.0.1:3308) [(none)]>CREATE DATABASE IF NOT EXISTS test4 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; gunzip -f < test1test2.sql.gz |mysql -uroot -p -h127.0.0.1 -P3308 gunzip -f < test3test4.sql.gz |mysql -uroot -p -h127.0.0.1 -P3308
2.3.4 配置多主一从
(root@127.0.0.1:3308) [(none)]>change master '3306' to master_host='127.0.0.1',MASTER_PORT=3306,master_user='repl', master_password='repl',master_log_file='mysql-bin.000019', master_log_pos=1691; (root@127.0.0.1:3308) [(none)]>change master '3307' to master_host='127.0.0.1',MASTER_PORT=3307,master_user='repl', master_password='repl',master_log_file='mysql-bin.000011', master_log_pos=3506; 多个通道操作: (root@127.0.0.1:3308) [(none)]>start all slaves; (root@127.0.0.1:3308) [(none)]>show all slaves status \G; 单个通道操作: (root@127.0.0.1:3308) [(none)]>stop slave '3306'; (root@127.0.0.1:3308) [(none)]>start slave '3306';’ (root@127.0.0.1:3308) [(none)]>show slave '3306' status \G; 清理slave信息: Mysql>reset slave 'connect_name' all; 如reset slave '3306' all; 单通道错误处理: (root@localhost:mysql.sock) [gjdyzjb]>stop slave 'zzb'; #停掉单通道 (root@localhost:mysql.sock) [gjdyzjb]>set @@default_master_connection='zzb'; #指定默认的通道 (root@localhost:mysql.sock) [gjdyzjb]>select @@default_master_connection; #确认指定的默认的通道 (root@localhost:mysql.sock) [gjdyzjb]>SET GLOBAL sql_slave_skip_counter =1; #跳过错误 (root@localhost:mysql.sock) [gjdyzjb]>start slave 'zzb'; #开启单通道
Binlog清理与调整
show binary logs; show variables like '%expire%'; set global expire_logs_days=3; PURGE MASTER LOGS TO 'mysql-bin.001989'; PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';