Sharing of MySQL – Chukong MySQL 数据库培训 – 第三天
常见数据库备份方式 – 主从备份
a. 冷备份: 先关闭数据库, copy data file
b. mysqldump: – hot copy
c. xtrabackup: – hot copy
d. master/slave: 实时, 基于 a-c 实现
其中 a-c 只能恢复到某一时间点, d 为主从实时备份(略有延时,但是动态自动进行同步备份的方案)
- 冷备份准备:
停用 DB:
mysqladmin -u root -p shutdown -S /tmp/mysql.socket
拷贝文件:
scp …..
修改相关设置:
目录权限: chown -R mysql:mysql /data/mysql/配置文件: 目标机器 IP, server_id, basedir, datadir
启动 mysql:
/opt/soft/percona/bin/mysqld_safe –defaults-file=/data/mysql/my.cnf –basedir=/opt/soft/percona/ –datadir=/data/mysql/ –user=mysql &
- mysqldump 方案:
开启 bin-log 日志, 默认 lock-tables 为真(意味执行备份时会进行 table 锁直到备份结束), 优点是无需停数据库, 执行方便
[mysqld]log-bin = /data/mysql/mysql-bin
mysqldump -h HOST -P PORT -u root -p PASSWD –default-charactor-set=utf8 –master-data DBNAME | gzip > DBNAME.sql.gz
其中出现中文信息时 使用 –default-charactor-set 尤为重要.
该语句不会备份索引.
记录备份的 binlog 的 MASTER_LOG_POS=… MASTER_LOG_FILE=…
恢复是采用语句 mysql < DBNAME.sql
更多信息参考 mysqldump –help
- Xtrabackup 备份
基于 InnoDB 的 crash-recovery 原理, 备份时不锁表, 逐页(16k 每页)进行数据拷贝, 同时拷贝备份进程中变化的日志页
开启 xtrabackup_log->copy .ibd & ibdata1 文件->FlushTableSWithReadLock->copy .frm, .myd, .myi 和 misc 文件->获取 bin-log 位置->UnlockTable->stop & copy xtrabackup_log
回复时应用日志回复 DB
优点:速度快; 缺点拷贝 bin-log 占用大量空间.
基础前题: 开启 Bin-log; 配置相应数据参数
[mysqld]
log-bin = /data/mysql/mysql-bin
basedir=/opt/soft/perconadatadir=/data/mysql
备份操作:
innobackupex –user=USER –password=PWD –defaults-file=/data/mysql/my.cnf /data0/backup/mysql > /tmp/mysqlbackup.log
恢复操作:
innobackupex –apply-log –user=USER –password=PWD –defaults-file=/data/mysql/my.conf /data0/backup/mysql
注: InnoDB 类型数据库不可以只通过直接拷贝数据文件进行备份, 要涉及事务相关的判断等信息.
- Master-Slave 方案:
实现原理:
a. Master 开启 bin-log 日志记录每次 DML 操作,写入磁盘文件.
b. Slave 开启子线程 A 读取 Master 的 bin-log 文件, 将同步标记信息写入自己的 Replay log, 子线程 B 读取该日志将其解析成 SQL 语句写入 Slave 数据库, 同时产生 Slave 数据库的 bin-log
实现方案: 冷备份/mysqldump/Xtrabackup + binlog
修改数据库配置文件中的/etc/my.cnf 中的 server-id 值
mysql 客户端操作:
主服务器授权, 查看 Master 状态, 客户端执行 change master to …, 启用 slave
验证主从复制正确性:
show slave status
抽样对比记录条数
mysql 日志是否有异常
主从切换方法:
a. 先确认同步完成, 停掉所有操作数据库的应用, 在 master 执行 show processlist, 如果提示 “master 已经发送全部 bin-log 到 slave. 等待 binlog 更新”
b. 停掉从数据库 stop slave
c. change master to …
服务器监控:
端口, 运行时间, 连接数, sql 量, 慢查询, 磁盘, 内存, salve 同步情况 – 使用 zabbix 工具有较好界面管理
思考问题:
a. 主从数据进行读写分离时有延时, 应该如何处理?
b. key point: memcache 缓存, 分库分表(4kw 非关联 data 为上限)
