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.

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里:

准备阶段:
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

[mysqld]
server-id=2

#如果需要slave为read-only,则添加以下(注意,该参数只是对 普通用户生效,对于复制用户,超级管理员权限他们还是可以进行写操作的)
read-only = 1

#这个是在联级复制时需要使用的,我个人认为最好也添加上,以防万一的确需要.
log_slave_updates=1

如果Master里有内存表,这时在slave里启动时,内存表数据是会清空的。这时,如果进行复制的话,而master里的相关的内存表还有数据,并且在进行操作的话,就会导致复制中断。

启动slave:

╭─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:

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信息先:

cat /tmp/mysql-backup/2016-05-10_15-09-25/xtrabackup_binlog_info
mysql-bin.000002	490

然后在slave的实例上,执行以下命令:

配置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执行失败导致的,如果允许跳过这次的回放,则:

 stop slave;
 set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
 start slave;

如果一条也是这样子,可以重复上面的操作即可.或者用下面的脚本来一条一条地跳过.

自动跳过脚本

#!/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

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信息。:

INSERT INTO dsns VALUES (1,NULL,"h=<slave IP/hostname>,P=<slave port>,u=root,p=<password>");

最后检查master与各个slave的延迟情况

╭─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

进行主从同步.

╭─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连接信息的.

即,它建议在从库执行:

REPLACE INTO `test`.`t1`(`id`) VALUES ('1');

就可以与master同步了.

取消replication

在slave上执行:

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_RunningNo,然后不断地跳过,虽然都为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

只备份结构

所有数据库:

mysqldump -u root -h 127.0.0.1  --no-data -P3306 --all-databases --single-transaction -R -E  > schema.sql

只备份数据

nohup bash -c "mysqldump --no-create-info  -h 127.0.0.1 -uroot -P 你的端口 -p你的密码 --all-databases --single-transaction > data.sql" &

还原数据

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引擎的表:

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

即:删除数据目录下的ib_logfile0, ib_logfile1。造成*不要*删除ibdata1,因为它是数据文件. 然后重启mysql即可。

pt-table-checksum 完整用法

master 上执行

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=<slave IP/hostname>,P=<slave port>,u=root,p=<password>");

整个流程:

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=<password>")\c
mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3309  |
+---------------+-------+
1 row in set (0.00 sec)

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 (c) 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上执行下面的语句即可显示.

 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 完整用法

打印要执行的语句:

pt-table-sync --print --sync-to-master h=127.0.0.1,P=4306,u=root,p='yourpassword' --replicate percona.checksums --charset=utf8

直接执行:

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!!

参考资料

percona segmentfault sina blog