MySQL备份实践
Contents
binlog
查看是否开启了binlog
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
开启binlog
在配置文件my.cnf里添加以下内容，然后重启mysql即可.
log-bin=mysql-bin
mysql-bin就是文件名前缀，可以写绝对路径，或相对路径. 其他与binlog相关的参数及说明:
#binlog的前缀
log_bin = /var/log/mysql/mysql-bin.log
#只保留最近N天的文件
expire_logs_days = 10
#每个binlog文件的最大大小
max_binlog_size = 100M
#只对指定的数据库进行binlog，多个DB时，请使用多个binlog_do_db，而不能在一行里然后逗号分隔!!以下同理
binlog_do_db = include_database_name
#忽略以下指定的binlog，多个DB时，请使用多行binlog_ignore_db.
binlog_ignore_db = exclude_database_name
配置完后，再次查看binlog状态，可以看到已经开启了:
sudo /etc/init.d/mysql restart
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/log/mysql/mysql-bin |
| log_bin_index | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
mysql>
mysql> show variables like '%expire_logs%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 10 |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> show variables like '%binlog_size%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| max_binlog_size | 104857600 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql>
实时备份binlog
在cron里，每分钟调用以下命令.
sudo su -c "rsync -azvh /var/log/mysql/mysql-bin.* /tmp/mysql-backup"
全量备份[每周一个]
Percona XtraBackup 的安装就不多说了，参考官方网站安装.
innobackupex --defaults-file=/tmp/other-my.cnf --user=DBUSER --password=SECRET /path/to/backup/dir/
其他选项有：
-port: MySQL端口
-socket: MySQL的Socket文件
-host: MySQL主机
每小时增量备份
先进行一次基础备份（仅需要一次）
innobackupex --user=bkpuser --password=s3cret --socket=/usr/local/mysql-data/3306/3306.socket --defaults-file=/usr/local/mysql-data/3306/my.cnf /home/mysql/3306/base
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
Get the latest version of Percona XtraBackup, documentation, and help resources:
http://www.percona.com/xb/p
...中间省略输出....一定要看到有以下一句才算是成功!
150901 13:02:13 innobackupex: completed OK!
然后每小时执行一次以下使用
innobackupex --user=bkpuser --password=s3cret --socket=/usr/local/mysql-data/3306/3306.socket --defaults-file=/usr/local/mysql-data/3306/my.cnf --incremental-basedir=/home/mysql/3306/base/2015-09-01_13-02-10 --incremental /home/mysql/3306/incremental/one
下一小时的增量备份，以上一小时的备份目录为基础点:
innobackupex --user=bkpuser --password=s3cret --defaults-file=/usr/local/mysql-data/3306/my.cnf --incremental-basedir=/home/mysql/3306/incremental/one/2015-09-01_13-07-29 --incremental /home/mysql/3306/incremental/two
搭建复制[在开启bin log前提下，无需重启开启复制]
主：localhost:3306, /ihome/db/mysql/mysql-5.6 数据目录: /ihome/db/mysql/mysql-5.6/data 从：localhost:3307 /ihome/db/mysql/mysql-5.6 数据目录：/ihome/db/mysql/mysql-5.6/data1
安装Percona的工具，这里以Ubuntu 14.04 X86_64 trusty 版本为例子
wget https://repo.percona.com/apt/percona-release_0.1-3.trusty_all.deb
sudo dpkg -i percona-release_0.1-3.trusty_all.deb
sudo apt-get update
sudo aptitude install percona-xtrabackup-24
master 配置
复制是依赖于binlog的，所以必须先开启binlog，然后还要配置下server-id
````bash [mysqld] log-bin=mysql-bin server-id=1
#### 创建复制权限的用户
bash CREATE USER ‘repl’@‘%’ IDENTIFIED BY ‘123456’; GRANT REPLICATION SLAVE ON . TO ‘repl’@‘%’; flush privileges;
#### 然后执行以下命令
全量备份master.
bash innobackupex –defaults-file=/ihome/db/mysql/mysql-5.6/my.cnf –user=root /tmp/mysql-backup
…. xtrabackup: Transaction log of lsn (1629755) to (1629755) was copied. 160510 15:09:30 completed OK!
看到如果输出以上类似的信息表示基础备份完成.
然后将备份的目录，还原到slave里:
bash 准备阶段： innobackupex –defaults-file=/ihome/db/mysql/mysql-5.6/my.cnf –apply-log –use-memory=4G /tmp/mysql-backup/2016-05-10_15-09-25
恢复阶段：
先删除slave的数据目录 rm /ihome/db/mysql/mysql-5.6/data1 -rf
然后创建一个空的slave数据目录 mkdir -p /ihome/db/mysql/mysql-5.6/data1
还原 innobackupex –defaults-file=/ihome/db/mysql/mysql-5.6/my.3307.cnf –copy-back –use-memory=4G /tmp/mysql-backup/2016-05-10_15-09-25 … 160510 15:17:32 [01] Copying ./xtrabackup_info to /ihome/db/mysql/mysql-5.6/data1/xtrabackup_info 160510 15:17:32 [01] …done 160510 15:17:32 completed OK!
### slave 配置
可以直接copy master的配置，然后修改下`server-id`，这里slave的配置文件名为`my.3307.cnf`
bash [mysqld] server-id=2
#如果需要slave为read-only，则添加以下（注意，该参数只是对 普通用户生效，对于复制用户，超级管理员权限他们还是可以进行写操作的) read-only = 1
#这个是在联级复制时需要使用的，我个人认为最好也添加上，以防万一的确需要. log_slave_updates=1
*如果Master里有内存表，这时在slave里启动时，内存表数据是会清空的。这时，如果进行复制的话，而master里的相关的内存表还有数据，并且在进行操作的话，就会导致复制中断。*
启动slave:
bash ╭─sky@sky-linux /ihome/db/mysql/mysql-5.6 ╰─➤ ./bin/mysqld –defaults-file=my.3307.cnf & [1] 3608 ╭─sky@sky-linux /ihome/db/mysql/mysql-5.6 ╰─➤ 2016-05-10 15:20:43 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details). 2016-05-10 15:20:43 0 [Note] ./bin/mysqld (mysqld 5.6.30-log) starting as process 3608 …
╭─sky@sky-linux /ihome/db/mysql/mysql-5.6 ╰─➤ lsof -i:3307 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 3608 sky 13u IPv6 1057274 0t0 TCP *:3307 (LISTEN)
查看从库slave的server_id:
bash mysql> show variables like ‘server_id’; +—————+——-+ | Variable_name | Value | +—————+——-+ | server_id | 2 | +—————+——-+ 1 row in set (0.00 sec)
mysql>
#### 在slave里配置master的信息
先查看刚刚在master进行基础备份（全量备份的信息先）的bin log信息先:
bash cat /tmp/mysql-backup/2016-05-10_15-09-25/xtrabackup_binlog_info mysql-bin.000002 490
然后在slave的实例上，执行以下命令:
bash 配置master的信息:
mysql> CHANGE MASTER TO MASTER_HOST=‘10.0.0.81’, MASTER_USER=‘repl’, MASTER_PASSWORD=‘123456’, MASTER_PORT=3306, MASTER_LOG_FILE=‘mysql-bin.000002’, MASTER_LOG_POS=490; Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql>
开始slave mysql> start slave; Query OK, 0 rows affected (0.00 sec)
mysql>
查看slave状态 mysql> show slave status\G
Slave_IO_Running: Yes Slave_SQL_Running: Yes
要确认这两个都是`YES`的状态.
### 问题
Slave_SQL_Running: No
这一般是由于当前某条SQL执行失败导致的，如果允许跳过这次的回放，则：
bash stop slave; set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; start slave;
如果一条也是这样子，可以重复上面的操作即可.或者用下面的脚本来一条一条地跳过.
#### 自动跳过脚本
bash #!/bin/bash export PATH=“/home/yourname/mysql/mysql-5.6.17/bin:$PATH”
export MySQLCMD=“mysql -uroot -P3306 -p123456 -h127.0.0.1”
until ${MySQLCMD} -e “show slave status\G;” | grep -i “Slave_SQL_Running: Yes”;do ${MySQLCMD} -e “stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;“; sleep 1; done
## pt-table-checksum
该命令是在`master`上执行.
检查主从是否一致：
首先在`master`，创建一个dsn的表，假设是在`test.dsns`：
bash use test;
CREATE TABLE dsns (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id int(11) DEFAULT NULL,
dsn varchar(255) NOT NULL,
PRIMARY KEY (id)
);
然后插入slave库的dsn信息。：
bash
INSERT INTO dsns VALUES (1,NULL,“h=
最后检查master与各个slave的延迟情况
bash ╭─sky@sky-linux /ihome/db/mysql/mysql-5.6 ╰─➤ pt-table-checksum –socket=/ihome/db/mysql/mysql-5.6/data/3306.socket –ask-pass -uroot -d test –replicate –nocheck-replication-filters –replicate=test.checksums –recursion-method=dsn=D=test,t=dsns Enter MySQL password: Enter MySQL password: Enter MySQL password:
A software update is available:
* The current version for Percona::Toolkit is 2.2.14.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T17:35:14 0 0 1 1 0 0.006 test.dsns 05-10T17:35:14 0 1 1 1 0 0.008 test.t1
`--recursion-method=dsn=D=test,t=dsns`：表示使用dns方式来监测slave，这个表示是master库中的test数据库下的dsns表（即上面创建的表）.
` --socket=/ihome/db/mysql/mysql-5.6/data/3306.socket --ask-pass -uroot -d test --replicat`：表示连接到master的实例上，检测 `test` 数据库是否有延迟.（也可以指定特定的表 `-t`）.
结果中，如果`DIFFS`列不为0,则表示有延迟，如上面的`test.t1`有延迟1行的数据.
## pt-table-sync
进行主从同步.
bash
╭─sky@sky-linux ~
╰─➤ pt-table-sync –ask-pass –replicate=test.checksums D=test,t=t1,h=localhost,u=root,P=3306,S=/ihome/db/mysql/mysql-5.6/data/3306.socket D=test,t=t1,h=localhost,u=root,P=3307,S=/ihome/db/mysql/mysql-5.6/data1/3307.socket –print
Enter password for localhost:
Enter password for localhost:
REPLACE INTO test.t1(id) VALUES (‘1’) /percona-toolkit src_db:test src_tbl:t1 src_dsn:D=test,P=3306,S=/ihome/db/mysql/mysql-5.6/data/3306.socket,h=localhost,p=…,t=t1,u=root dst_db:test dst_tbl:t1 dst_dsn:D=test,P=3307,S=/ihome/db/mysql/mysql-5.6/data1/3307.socket,h=localhost,p=…,t=t1,u=root lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:7294 user:sky host:sky-linux/;
前面的配置是master的连接信息，后面的配置是配置slave连接信息的.
即，它建议在从库执行:
bash
REPLACE INTO test.t1(id) VALUES (‘1’);
就可以与master同步了.
## 取消replication
在slave上执行:
bash STOP SLAVE; RESET SLAVE; (Use RESET SLAVE ALL; for MySQL 5.5.16 and later) Edit the my.cnf and remove any information (if present) which refers to “master-…” or “replicate-…” options. … Restart mysqld.
## 问题收集
### 同步不了
在一次生产环境里，要迁移几百GB的数据到阿里云，用`innobackupex`全量备份，然后copy到阿里云CS后，自己建立DB然后搭建复制环境（按上面的`复制`步骤来做），但是在启动复制时，一直有`Slave_SQL_Running`为`No`，然后不断地跳过，虽然都为`YES`了，但是发现数据过几秒后`Slave_SQL_Running`又为`No`了。
处理：
1. 取消replication
2. `pt-table-sync --ask-pass --execute h=Master的IP及连接信息,P=3309,u=root --databases 要同步的DB h=Slave的IP及连接信息,P=3309,u=root` :: 这个是将master的数据，完全同步到slave上.
3. 再建立复制.
## 备份Dump
### 只备份结构
所有数据库:
bash mysqldump -u root -h 127.0.0.1 –no-data -P3306 –all-databases –single-transaction -R -E > schema.sql
### 只备份数据
bash nohup bash -c “mysqldump –no-create-info -h 127.0.0.1 -uroot -P 你的端口 -p你的密码 –all-databases –single-transaction > data.sql” &
### 还原数据
bash mysql -u root -h 127.0.0.1 –no-data -P3306 –single-transaction < schema.sql mysql -u root -h 127.0.0.1 –no-data -P3306 –single-transaction < data.sql
## 查看MySQL某指定引擎的所有表
比如，查看所有memory引擎的表:
bash SELECT table_name, table_schema FROM INFORMATION_SCHEMA.TABLES WHERE engine = ‘Memory’ and table_schema not in (‘information_schema’, ‘mysql’);
## Unknown table engine 'InnoDB' Error_code: 1286 错误
[stackoverflow](http://stackoverflow.com/questions/7782870/error-unknown-table-engine-innodb-on-query-after-restarting-mysql)
即:删除数据目录下的`ib_logfile0`, `ib_logfile1`。造成*不要*删除`ibdata1`，因为它是数据文件.
然后重启mysql即可。
## pt-table-checksum 完整用法
### master 上执行
bash GRANT SELECT,PROCESS,SUPER,REPLICATION SLAVE ON . TO slaveptcheck@‘%’ IDENTIFIED BY ‘mypassword’;
CREATE DATABASE percona;
use percona;
CREATE TABLE checksums ( db char(64) NOT NULL, tbl char(64) NOT NULL, chunk int NOT NULL, chunk_time float NULL, chunk_index varchar(200) NULL, lower_boundary text NULL, upper_boundary text NULL, this_crc char(40) NOT NULL, this_cnt int NOT NULL, master_crc char(40) NULL, master_cnt int NULL, ts timestamp NOT NULL, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB;
GRANT update,insert,delete ON percona.* TO ‘slaveptcheck’@‘%’;
CREATE TABLE dsns (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id int(11) DEFAULT NULL,
dsn varchar(255) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO dsns VALUES (1,NULL,“h=
整个流程:
bash mysql> GRANT SELECT,PROCESS,SUPER,REPLICATION SLAVE ON . TO slaveptcheck@‘%’ IDENTIFIED BY ‘mypassword’; Query OK, 0 rows affected (0.07 sec)
mysql> CREATE DATABASE percona; Query OK, 1 row affected (0.09 sec)
mysql> use percona; Database changed mysql> CREATE TABLE checksums ( -> db char(64) NOT NULL, -> tbl char(64) NOT NULL, -> chunk int NOT NULL, -> chunk_time float NULL, -> chunk_index varchar(200) NULL, -> lower_boundary text NULL, -> upper_boundary text NULL, -> this_crc char(40) NOT NULL, -> this_cnt int NOT NULL, -> master_crc char(40) NULL, -> master_cnt int NULL, -> ts timestamp NOT NULL, -> PRIMARY KEY (db, tbl, chunk), -> INDEX ts_db_tbl (ts, db, tbl) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.29 sec)
mysql> GRANT update,insert,delete ON percona.* TO ‘slaveptcheck’@‘%’; Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE dsns (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> parent_id int(11) DEFAULT NULL,
-> dsn varchar(255) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.42 sec)
mysql> INSERT INTO dsns VALUES (1,NULL,“h=127.0.0.1,P=,u=root,p=
mysql> INSERT INTO dsns VALUES (1,NULL,“h=127.0.0.1,P=3409,u=root,p=wei123kool”); Query OK, 1 row affected (0.04 sec)
mysql> quit Bye hellovip@srv7:~/mysql/mysql-5.6.17-ccvip$ ./bin/mysql -h 127.0.0.1 -P3309 -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10945 Server version: 5.6.17-log Source distribution
Copyright © 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases; +———————–+ | Database | +———————–+ | information_schema | | callcenter | | callcenter_1 | | callcenter_2 | | callcenter_3 | | callcenter_4 | | mysql | | percona | | performance_schema | | test | +———————–+ 10 rows in set (0.00 sec)
mysql> quit Bye hellovip@srv7:~/mysql/mysql-5.6.17-ccvip$ pt-table-checksum –nocheck-replication-filters –databases=callcenter –replicate=percona.checksums –host=127.0.0.1 –port 3309 -uslaveptcheck -pmypassword –no-check-binlog-format –recursion-method=dsn=D=percona,t=dsns TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 08-31T15:42:22 0 0 0 1 0 0.211 callcenter.temp_id 08-31T15:42:23 0 0 8 1 0 0.255 callcenter.ttt
可以看到它开始检查一致性了.
*注意*
其实那张表`checksums`，可以由工具`pt-table-checksum`自动创建最好。默认情况下，如果参数`--replicate`指定的表不存在的话，会自动创建的.
### 查看不一致的表
在`slave`上执行下面的语句即可显示.
bash select db, tbl, sum(this_cnt) as total_rows, count(*)as chunks from checksums where ( master_cnt <> this_cnt OR master_crc <> this_crc OR isnull(master_crc) <> isnull(this_crc) ) group by db, tbl;
## pt-table-sync 完整用法
打印要执行的语句:
bash pt-table-sync –print –sync-to-master h=127.0.0.1,P=4306,u=root,p=‘yourpassword’ –replicate percona.checksums –charset=utf8
直接执行:
bash pt-table-sync –execute –sync-to-master h=127.0.0.1,P=4306,u=root,p=‘wei123kool’ –replicate percona.checksums –charset=utf8 ```
这里的h=127.0.0.1,P=4306这些是slave的，因为我们指定了sync-to-master，它会自动根据复制来连接master，
--replicate percona.checksums，就是之前pt-table-checksum后的数据库和表信息，根据这个可以选择有差异的来进行同步.
*charset*：千万要记得加上，这个要与自己DB里使用的一致.因为公司主要经营微博等社交服务多，所以这里填写utf8mb4!!