mysql backup 使用_mysql enterprise backup入门使用

**************************************************************

--1.全备

**************************************************************

#mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \

--host=127.0.0.1 --port=3306 --protocol=tcp \

--user=root --password=passwd123! --databases="wind" \

--with-timestamp --backup-dir=/backup \

--log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysqldb01-bin.index \

backup-and-apply-log

----NOTE:

mysqlbackup: INFO: Could not find binlog index file. binlogs will not be copied for this backup.

Point-In-Time-Recovery will not be possible.

If this is online backup then server may not have started with --log-bin.

You may specify its location with --log-bin-index option.

#cat /backup/2015-04-01_09-39-09/meta/backup_variables.txt |grep 'lsn'

end_lsn=70249846

start_lsn=70249472

/***************************參数

[backup_variables]

apply_log_done=1 #　是否释放检測ibbackup_logfile文件(0表示没有,1表示已经释放)

binlog_index=mysqldb01-bin.index #二进制日志索引文件

binlog_position=mysqldb01-bin.000011:338 #二进制日志文件大小

end_lsn=70249846 # 结束LSN点

has_external_plugins=0 #外部插件

is_compressed=0 #是否压缩(0:否,1:是)

is_incremental=0 #是否是增量为增量备份文件(0:否,1:是)

is_incremental_with_redo_log_only=0 仅仅配置重做日志,当输入数据大小重做日志大小时,会有一场抛出

is_onlyinnodb=0 #是否仅仅备份了innodb的表

is_partial=1 #是server备份还是部分备份(0表示全server备份,1表示部分备份)

is_skip_unused_pages=0

meb_version=3.12.0 #版本

start_lsn=70249472 #開始备份的LSN点

***************************/

**************************************************************

---2.增量备份

**************************************************************

mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \

--host=127.0.0.1 --port=3306 --protocol=tcp \

--user=root --password=passwd123! --databases="wind" \

--with-timestamp --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysqldb01-bin.index \

--incremental --start-lsn=70249846 \

--incremental-backup-dir=/backupinc backup

mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \

--host=127.0.0.1 --port=3306 --protocol=tcp \

--user=root --password=passwd123! --databases="wind" \

--with-timestamp --log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysqldb01-bin.index \

--incremental --start-lsn=138861169 \

--incremental-backup-dir=/backupinc backup

**************************************************************

--3.还原指定表空间

**************************************************************

--3.1 合并增量备份到全备

ls /backupinc/

2015-04-01_10-03-16 2015-04-01_10-11-51

mysqlbackup --backup-dir=/backup/2015-04-01_09-39-09 \

--incremental-backup-dir=/backupinc/2015-04-01_10-03-16 \

apply-incremental-backup

mysqlbackup --backup-dir=/backup/2015-04-01_09-39-09 \

--incremental-backup-dir=/backupinc/2015-04-01_10-11-51 \

apply-incremental-backup

--3.2 模拟删除指定表空间

#cat /usr/local/mysql/my.cnf |grep datadir

datadir=/usr/local/mysql/data

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| jason |

| mysql |

| performance_schema |

| test |

| wind |

+--------------------+

6 rows in set (0.01 sec)

MariaDB [wind]> select count(*) from t1;

+----------+

| count(*) |

+----------+

| 3000000 |

+----------+

1 row in set (0.60 sec)

MariaDB [(none)]> drop database wind;

Query OK, 1 row affected (0.24 sec)

MariaDB [(none)]> commit;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| jason |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

--3.3 还原指定表空间

mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \

--datadir=/usr/local/mysql/data/ \

--log-bin-index=/usr/local/mysql/mysql_logs/binary_log/mysqldb01-bin.index \

--backup-dir=/backup/2015-04-01_09-39-09 \

--databases="wind" \

copy-back

chown -R mysql /usr/local/mysql

chgrp -R mysql /usr/local/mysql

--3.4验证表空间恢复情况

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| jason |

| mysql |

| performance_schema |

| test |

| wind |

+--------------------+

6 rows in set (0.01 sec)

MariaDB [wind]> select count(*) from t1;

+----------+

| count(*) |

+----------+

| 3000000 |

+----------+

1 row in set (0.60 sec)