4、 mysqldump数据分库备份
有经验的运维或者DBA肯定不会用mysqldump备份大量的数据因为很慢，但是我们由于经验不足在此又踩了一个坑。用脚本和定时任务的方式实现数据备份，每周6晚上2点备份，前期数据量比较小整个业务系统正常，后面当数据突破100多G后，就出现一个比较奇怪的事情，每周六早上应用系统总是异常，研发人员都很郁闷，感觉跟见鬼一样，经过多次出现该问题后就考虑数据备份，研究任务执行情况，发现确实是数据备份问题，后面就采取xtrabackup备份。
脚本：
#/bin/bash
MYUSER=mysqlback
MYPASS=databack***
#SOCKET=/data/3306/mysql.sock
MYLOGIN="mysql -u$MYUSER -p$MYPASS "
MYDUMP="mysqldump -u$MYUSER -p$MYPASS -B"
DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi"Data|_schema|mysql")"
for dbname in $DATABASE
do
MYDIR=/data/backup/$dbname
[ ! -d $MYDIR ] &&mkdir -p $MYDIR
$MYDUMP $dbname|gzip>$MYDIR/${dbname}_$(date +%F).sql.gz
done
5、共享表空间转换独立表空间更改数据库配置报错
配置文件：
[server]
# this is only for the mysqld standalone daemon
[mysqld]
skip-name-resolve
character-set-server=utf8
datadir=/data/mysql
wait_timeout=1800
interactive_timeout = 288000
max_allowed_packet = 1000M
#max_connections=3000
max_connections=3000
character-set-server=utf8
#innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 200M
innodb_flush_log_at_trx_commit=2
innodb_autoextend_increment=800M
#innodb_log_buffer_size = 200M
innodb_log_file_size = 100M
key_buffer_size=800M
read_buffer_size=600M
thread_cache_size=64
innodb_file_per_table=1 #独立表空间
#innodb_flush_log_at_trx_commit=2
#innodb_log_file_size=1G #（日志文件）
innodb_buffer_pool_size=6G
为了适当的优化数据库性能，所以将参数做了适当的调整，这时比较坑的问题就出现了，数据库集群只能启动其中的一台，另外的两台都是报错，这时肯定是查看日志解决问题，看下面日志是配置文件参数设置问题导致，将更改配置文件逐个检查，最后发现是有3个innodb_buffer_pool_size参数不一致（3台服务器集群 基本配置差不多，区别就是一台上面还有其他应用程序在运行，所以就将其设置的小一点，导致整个系统启动异常）
部分日志：
InnoDB: Error: log file ./ib_logfile0 is of different size 0 104857600 bytes
InnoDB: than specified in the .cnf file 0 1073741824 bytes!
InnoDB: Possible causes for this error:
(a) Incorrect log file is used or log file size is changed
(b) In case default size is used this log file is from 10.0
(c) Log file is corrupted or there was not enough disk space
In case (b) you need to set innodb_log_file_size = 48M
170412 23:53:26 [ERROR] Plugin 'InnoDB' init function returned error.
170412 23:53:26 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
170412 23:53:26 [Note] Plugin 'FEEDBACK' is disabled.
170412 23:53:26 [ERROR] Unknown/unsupported storage engine: innodb
170412 23:53:26 [ERROR] Aborting
170412 23:53:28 [Note] WSREP: Closing send monitor...
170412 23:53:28 [Note] WSREP: Closed send monitor.
170412 23:53:28 [Note] WSREP: gcomm: terminating thread
170412 23:53:28 [Note] WSREP: gcomm: joining thread
170412 23:53:28 [Note] WSREP: gcomm: closing backend
170412 23:53:29 [Note] WSREP: view(view_id(NON_PRIM,1d5436dc,2) memb {
1d5436dc,0
} joined {
} left {
} partitioned {
effca7a8,0