TokuDB 官方热备方案「建议收藏」

percona 5.7 版本已经将 tokudb 集成进去中，并且原生支持了热备，民间方案可以退役了。

安装
    1.ps-admin -uroot -p”123456″ -S /tmp/mysql_5001.sock  –defaults-file=/etc/my_5001.cnf –enable-tokubackup
    2.配置文件添加 innodb_use_native_aio = 0
    3.restart mysql
    4.ps-admin -uroot -p”123456″ -S /tmp/mysql_5001.sock  –defaults-file=/etc/my_5001.cnf –enable-tokubackup
    5.创建备份文件夹(次文件夹必须存在，且为空，否则备份将报错)  mkdir /data0/backup/5001_tokudb ; chown -R mysql:mysql  /data0/backup/backup/ 
备份
    6.备份 set tokudb_backup_dir=”/data0/backup/5001_tokudb”;
恢复
    7.恢复 直接拷贝备份文件至你的数据目录下，启动mysqld进程即可。
监控

    mysql>  SHOW PROCESSLIST ;
+----+-----------------+-----------+------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+---------------+
| Id | User            | Host      | db   | Command | Time  | State                                                                                                                                                                                                                         | Info                                               | Rows_sent | Rows_examined |
+----+-----------------+-----------+------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+---------------+
|  1 | event_scheduler | localhost | NULL | Daemon  | 10894 | Waiting on empty queue                                                                                                                                                                                                        | NULL                                               |         0 |             0 |
|  6 | root            | localhost | NULL | Query   |     0 | tokudb backup about 51% done: Backup progress 469726501 bytes, 300 files.  Copying file: 164626432/268435456 bytes done of /data0/mysql/5001_toku_backup/ib_logfile1 to /data0/backup/5001_tokudb/mysql_data_dir/ib_logfile1. | set tokudb_backup_dir="/data0/backup/5001_tokudb/" |         0 |             0 |
|  8 | root            | localhost | NULL | Query   |     0 | starting                                                                                                                                                                                                                      | SHOW PROCESSLIST                                   |         0 |             0 |
+----+-----------------+-----------+------+---------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+-----------+---------------+

代码100分

排除文件
    比如要排除 lost+found 文件夹，

代码100分    mysql> SET tokudb_backup_exclude="/lost\+found($|/)";

备份限速

     The default and max value is 18446744073709551615 bytes .
     mysql> SET tokudb_backup_throttle=1000000;

查看报错

代码100分    mysql> SET tokudb_backup_dir="/tmp/backupdir";
    ERROR 1231 (42000): Variable "tokudb_backup_dir" can"t be set to the value of "/tmp/backupdir"

    mysql> SELECT @@tokudb_backup_last_error;
    +----------------------------+
    | @@tokudb_backup_last_error |
    +----------------------------+
    |                         17 |
    +----------------------------+

    mysql> SELECT @@tokudb_backup_last_error_string;
    +---------------------------------------------------+
    | @@tokudb_backup_last_error_string                 |
    +---------------------------------------------------+
    | tokudb backup couldn"t create needed directories. |
    +---------------------------------------------------+

限制
1.You must disable InnoDB asynchronous IO if backing up InnoDB tables with TokuBackup. Otherwise you will have inconsistent, unrecoverable backups. The appropriate setting is innodb_use_native_aio=0.
2.To be able to run Point-In-Time-Recovery you’ll need to manually get the binary log position.
3.Transactional storage engines (TokuDB and InnoDB) will perform recovery on the backup copy of the database when it is first started.
4.Tables using non-transactional storage engines (MyISAM) are not locked during the copy and may report issues when starting up the backup. It is best to avoid 5.operations that modify these tables at the end of a hot backup operation (adding/changing users, stored procedures, etc.).
6.The database is copied locally to the path specified in /path/to/backup. This folder must exist, be writable, be empty, and contain enough space for a full copy of the database.
7.TokuBackup always makes a backup of the MySQL datadir and optionally the tokudb_data_dir, tokudb_log_dir, and the binary log folder. The latter three are only backed up separately if they are not the same as or contained in the MySQL datadir. None of these three folders can be a parent of the MySQL datadir.
8.No other directory structures are supported. All InnoDB, MyISAM, and other storage engine files must be within the MySQL datadir.
9.TokuBackup does not follow symbolic links.
10.TokuBackup does not backup MySQL configuration file(s).
11.TokuBackup does not backup tablespaces if they are out of datadir.
12.Due to upstream bug #80183, TokuBackup can’t recover backed-up table data if backup was taken while running OPTIMIZE TABLE or ALTER TABLE … TABLESPACE.
13.TokuBackup doesn’t support incremental backups.
 

备：

阿里的方案
    SET TOKUDB_CHECKPOINT_LOCK=ON;
    开始拷贝TokuDB的数据文件(不包含日志文件)；
    FLUSH TABLES WITH READ LOCK;
    记录binlog位置，拷贝最新的binlog和TokuDB的日志文件(*.tokulog)；
    UNLOCK TABLES;
    SET TOKUDB_CHECKPOINT_LOCK=OFF;

改进阿里的方案
    SET TOKUDB_CHECKPOINT_LOCK=ON;
    FLUSH TABLES WITH READ LOCK;
    记录binlog位置，拷贝最新的binlog和TokuDB的日志文件(*.tokulog)；
    UNLOCK TABLES;
    开始拷贝TokuDB的数据文件(不包含日志文件) –移动到这里
    SET TOKUDB_CHECKPOINT_LOCK=OFF;