[danger] 强烈建议在执行本节所有操作前将所有操作命令操作copy到Sublime Text编辑器替换port为要配置主从的mysql实例对应的端口,避免误操作!!! [danger] 强烈建议在执行本节所有操作前将所有操作命令操作copy到Sublime Text编辑器替换port为要配置主从的mysql实例对应的端口,避免误操作!!! [danger] 强烈建议在执行本节所有操作前将所有操作命令操作copy到Sublime Text编辑器替换port为要配置主从的mysql实例对应的端口,避免误操作!!!
使用innobackupex工具进行备份(因本次涉及到的数据库实例较多,所以编写shell脚本,减少出错,单实例同样适用):
#!/bin/bash
#ports='3312 3313 3315'
port=3314 # 单实例时手动指定数据库port
# for循环和if判断只在多实例时使用
#for port in $ports;do
# if [ $port = "3313" ];then
# passwd='aaaaaaa'
# else
# passwd='bbbbbbb'
# fi
passwd='xxxxxxx'
mkdir /disk1/backup/$port
innobackupex --socket=/home/mysql/$port/mysql.sock --user=root --password=$passwd --parallel=1 /disk1/backup/$port/ > /tmp/"$port"_backup.log
# 将数据rsync由 **monitor1/mysql5** 机器同步到从库主机(myslq6):
rsync -avP --bwlimit 50000 /disk1/backup/$port adai@mysql6:/data2/backup/ > /tmp/"$port"_rsync.log
echo "Finish $port"
#done
Note: 执行脚本前保证主库、从库磁盘空间充足,并且从库服务器数据文件存放目录 (“/data2/backup/”) 存在,还有注意文件权限,否则可能会导致rsync失败!!!
如果有近期备份好的数据可以直接同步到从库服务器使用,但是 仅当 备份的binlog文件序号小于主库保留的最小binlog文件序号时即备份文件的binlog创建日期在主库保留的binlog文件后面,如mysql-bin.00008(备份文件binlog)、mysql-bin.00005(主库保留的binlog),备份才可用,即必须确保从库搭建好之后,开启同步时,主库还保留有同步位置之前的日志(保证数据连续性、一致性)。
数据库配置示例文件(Attention: 因为数据库要配置双主模式,所以在部署时主库和从库的server-id一定不能一样):
$ cat 3314.cnf
[mysqld3314]
lower_case_table_names=1
# 为PXC定制
binlog_format=ROW
default-storage-engine = InnoDB
innodb_large_prefix=on
innodb_autoinc_lock_mode=2
# 绑定地址之后只能通过内部网络访问
# 不能使用: 10.*.*.*
bind-address=10.215.33.23
auto-increment-increment = 2
auto-increment-offset = 2
# 使用 xtrabackup-v2 必须制定datadir, 因为操作可能直接对 datadir进行
datadir=/home/mysql/3314/data
# http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/privileges.html#permissions-and-privileges-needed
# 权限的配置
# xtrapbackup在Donor上执行,因此只需localhost的权限
# 约定:
server-id = 72
# wsrep模式不依赖于GTID
# 开启GTID
# enforce_gtid_consistency=1
# gtid_mode=on
# 即便临时作为Slave,也记录自己的binlog
log-slave-updates=1
# master_info_repository=TABLE
# relay_log_info_repository=TABLE
# GENERAL #
user = mysql
port = 3314
socket = /home/mysql/3314/mysql.sock
pid-file = /home/mysql/3314/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
ft-min-word-len = 4
event-scheduler = 0
# SAFETY #
max-allowed-packet = 16M
skip-name-resolve
max_connections = 2000
max_connect_errors = 30
back-log = 500
character-set-client-handshake = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
#character-set-client-handshake=1
#character-set-client=utf8
#character-set-server=utf8
#collation-server=utf8_general_ci
#key-buffer-size = 256M
table-open-cache = 2048
max-allowed-packet = 2048M
slave-skip-errors = all #Skip duplicated key
sort-buffer-size = 4M
join-buffer-size = 8M
thread-cache-size = 50
concurrent-insert = 2
thread-stack = 192K
net-buffer-length = 8K
read-buffer-size = 256K
read-rnd-buffer-size = 16M
bulk-insert-buffer-size = 64M
# 采用thread pool来处理连接
thread-handling=pool-of-threads
# 新线程创建等待时间,, 单位为10ms,50即为500ms
thread-pool-stall-limit = 50
sql-mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
# BINARY LOGGING #
log-bin = /home/mysql/3314/data/mysql-bin
expire-logs-days = 5
# LOGGING #
# log-output=file 默认就是FILE
log-error = /home/mysql/3314/data/mysql-error.log
long-query-time = 0.3
# log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /home/mysql/3314/data/mysql-slow.log
# 默认为0(MySQL不控制binlog的输出)
# sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
# 频繁修改的表不适合做query-cache, 否则反而影响效率
query-cache-type = 0
query-cache-size = 0
# query-cache-limit = 2M
# query-cache-min-res-unit = 512
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
# innodb-file-per-table = 1设置之后, 下面的配置基本失效
innodb_data_file_path = ibdata1:10M:autoextend
innodb-thread-concurrency = 32
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-large-prefix = on
# 内存: 全部内存*0.7
innodb-buffer-pool-size = 2G
performance-schema = 0
net-read-timeout = 60
# innodb-open-files 在MySQL5.6 auto-sized
# 来自May2
innodb-rollback-on-timeout
innodb-status-file = 1
# http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-multiple_io_threads.html
# http://zhan.renren.com/formysql?tagId=3942&checked=true
# 从MySQL 5.5
# innodb_file_io_threads = 4
innodb-read-io-threads = 16
innodb-write-io-threads = 8
innodb-io-capacity = 2000
# innodb-stats-update-need-lock = 0 # MySQL 5.6中无效
# innodb-stats-auto-update = 0
innodb-old-blocks-pct = 75
# innodb-adaptive-flushing-method = "estimate"
# innodb-adaptive-flushing = 1
# https://www.facebook.com/notes/mysql-at-facebook/repeatable-read-versus-read-committed-for-innodb/244956410932
# READ-COMMITTED 每次QUERY都要求调用: read_view_open_now, 而REPEATABLE-READ每次Transaction中只要求一次
# REPEATABLE-READ 会导致读写不同步
transaction-isolation = READ-COMMITTED
innodb-sync-spin-loops = 100
innodb-spin-wait-delay = 30
innodb-file-format = "Barracuda"
innodb-file-format-max = "Barracuda"
如上mysql配置,在使用innobackupex恢复数据,会有报错:
Error: datadir must be specified.
解决上述报错(在配置文件中添加如下配置):
$ vim /etc/mysql/3314.cnf
[mysqld]
# 使用 xtrabackup-v2 必须制定datadir, 因为操作可能直接对 datadir进行
datadir=/home/mysql/3314/data
恢复数据到从库:
$ cd /data2/backup/3314/
# 准备备份文件”--apply-log“
$ innobackupex --use-memory=1G --apply-log /data2/backup/3314/xxxx-xx-xx-xx
## --apply-log:根据Innodb日志文件中的更改来读取备份文件,并将更改应用到表空间,保证备份数据的一致性,应该在恢复备份之前执行。
## 参考:http://www.cnblogs.com/zhoujinyi/p/5893333.html
$ rm -rf /home/mysql/3314/data/* # 清除从库中的脏数据
# 恢复数据到从库
$ innobackupex --defaults-file=/etc/mysql/3314.cnf --copy-back /data2/backup/3314/xxxx-xx-xx-xx
$ chown -R mysql:mysql /home/mysql/3314
# 启动mysql
$ systemctl start mysqld@3314
# 数据库登录脚本(使用ansible部署时会创建):
# cat /home/mysql/3314/mysql_client.sh
#!/bin/sh
# Do NOT modify this file by hand!
# Generate by ansible
mysql -u root -pxxxxxx -S /home/mysql/3314/mysql.sock $@
注意几点:
binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录(对数据库的改动),对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制(实时备份)以及增量恢复。
Note:如果需要新从库和主库构建双主集群,从库必须不能接受任何写请求!!!
(adsbygoogle = window.adsbygoogle || []).push({});