欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysqlenterprisebackup入门使用_MySQL

程序员文章站 2022-05-13 22:12:10
...
**************************************************************
--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   #是服务器备份还是部分备份(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=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)