mysql資料庫企業級備份

–1.全備

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

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

–user=root –password=dengyu! –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-09-17_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 #是伺服器備份還是部分備份（0表示全伺服器備份,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=dengyu! –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=dengyu! –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-09-17_10-03-16 2015-09-17_10-11-51

mysqlbackup –backup-dir=/backup/2015-09-17_09-39-09 \

–incremental-backup-dir=/backupinc/2015-09-17_10-03-16 \

apply-incremental-backup

mysqlbackup –backup-dir=/backup/2015-09-17_09-39-09 \

–incremental-backup-dir=/backupinc/2015-09-17_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-09-17_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)