重庆思庄Oracle、Redhat认证学习论坛

标题: mysqlbackup备份到单个文件 [打印本页]

作者: 郑全 时间: 2015-4-24 12:08
标题: mysqlbackup备份到单个文件

一般情况下，mysqlbackup默认备份为多个文件，这样不方便拷贝，当然，可以再通过操作系统命令压缩成一个文件，不过，这样比较麻烦，能否直接备份为一个文件呢，答案是肯定的，使用backup-to-image 可以直接备份为一个文件：

[root@sztech1 u01]# mysqlbackup -uroot -proot --socket=/usr/local/mysql/mysql.sock --backup_dir=/u01/dbback1/ --backup-image=mybackup.bak backup-to-image

mysqlbackup: INFO: Starting with following command line ...
mysqlbackup -uroot -pxxxx --socket=/usr/local/mysql/mysql.sock
--backup_dir=/u01/dbback1/ --backup-image=mybackup.bak backup-to-image

mysqlbackup: INFO:
mysqlbackup: INFO: MySQL server version is '5.6.23-log'.
mysqlbackup: INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
At the end of a successful 'backup-to-image' run mysqlbackup
prints "mysqlbackup completed OK!".

150424 10:06:03 mysqlbackup: INFO: MEB logfile created at /u01/dbback1/meta/MEB_2015-04-24.10-06-03_image_backup.log

--------------------------------------------------------------------
Server Repository Options:
--------------------------------------------------------------------
datadir = /usr/local/mysql/data/
innodb_data_home_dir =
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_log_files_in_group = 3
innodb_log_file_size = 536870912
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
innodb_undo_directory = /usr/local/mysql/data/
innodb_undo_tablespaces = 0
innodb_undo_logs = 128

--------------------------------------------------------------------
Backup Config Options:
--------------------------------------------------------------------
datadir = /u01/dbback1/datadir
innodb_data_home_dir = /u01/dbback1/datadir
innodb_data_file_path = ibdata1:12M:autoextend
innodb_log_group_home_dir = /u01/dbback1/datadir
innodb_log_files_in_group = 3
innodb_log_file_size = 536870912
innodb_page_size = 16384
innodb_checksum_algorithm = innodb
innodb_undo_directory = /u01/dbback1/datadir
innodb_undo_tablespaces = 0
innodb_undo_logs = 128

Backup Image Path = /u01/dbback1/mybackup.bak
mysqlbackup: INFO: Unique generated backup id for this is 14298411634266199

mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
150424 10:06:05 mysqlbackup: INFO: Full Image Backup operation starts with following threads
                1 read-threads    6 process-threads    1 write-threads
150424 10:06:05 mysqlbackup: INFO: System tablespace file format is Antelope.
150424 10:06:05 mysqlbackup: INFO: Starting to copy all innodb files...
mysqlbackup: INFO: Copying meta file /u01/dbback1/backup-my.cnf.
mysqlbackup: INFO: Copying meta file /u01/dbback1/meta/backup_create.xml.
150424 10:06:05 mysqlbackup: INFO: Found checkpoint at lsn 379176982.
150424 10:06:05 mysqlbackup: INFO: Starting log scan from lsn 379176960.
150424 10:06:05 mysqlbackup: INFO: Copying log...
150424 10:06:05 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Antelope file format).
150424 10:06:05 mysqlbackup: INFO: Log copied, lsn 379176982.
150424 10:06:06 mysqlbackup: INFO: Copying /usr/local/mysql/data/employees/departments.ibd (Antelope file format).
150424 10:06:06 mysqlbackup: INFO: Copying /usr/local/mysql/data/employees/dept_emp.ibd (Antelope file format).
150424 10:06:06 mysqlbackup: INFO: Copying /usr/local/mysql/data/employees/dept_manager.ibd (Antelope file format).
150424 10:06:06 mysqlbackup: INFO: Copying /usr/local/mysql/data/employees/employees.ibd (Antelope file format).
150424 10:06:07 mysqlbackup: INFO: Copying /usr/local/mysql/data/employees/salaries.ibd (Antelope file format).
mysqlbackup: Progress in MB: 200
150424 10:06:10 mysqlbackup: INFO: Copying /usr/local/mysql/data/employees/titles.ibd (Antelope file format).
150424 10:06:11 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format).
150424 10:06:11 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format).
150424 10:06:11 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_master_info.ibd (Antelope file format).
150424 10:06:11 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format).
150424 10:06:11 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_worker_info.ibd (Antelope file format).
150424 10:06:11 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/actor.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/address.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/category.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/city.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/country.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/customer.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/film.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/film_actor.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/film_category.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/inventory.ibd (Antelope file format).
150424 10:06:12 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/language.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/payment.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/rental.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/staff.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sakila/store.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sztech/dept.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sztech/emp.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sztech/emp_bak.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/sztech/emp_bak1.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/city.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/country.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/countrylanguage.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/deletedcity.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Copying /usr/local/mysql/data/world/mytable.ibd (Antelope file format).
150424 10:06:13 mysqlbackup: INFO: Completing the copy of innodb files.
150424 10:06:13 mysqlbackup: INFO: Starting to copy Binlog files...
150424 10:06:14 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
150424 10:06:14 mysqlbackup: INFO: Starting to lock all the tables...
150424 10:06:14 mysqlbackup: INFO: All tables are locked and flushed to disk
150424 10:06:14 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data/'
150424 10:06:14 mysqlbackup: INFO: Starting to backup all non-innodb files in
        subdirectories of '/usr/local/mysql/data/'
150424 10:06:14 mysqlbackup: INFO: Adding database directory: datadir/employees
150424 10:06:14 mysqlbackup: INFO: Adding database directory: datadir/innodb_memcache
150424 10:06:14 mysqlbackup: INFO: Adding database directory: datadir/mysql
150424 10:06:15 mysqlbackup: INFO: Adding database directory: datadir/performance_schema
150424 10:06:15 mysqlbackup: INFO: Adding database directory: datadir/sakila
150424 10:06:15 mysqlbackup: INFO: Adding database directory: datadir/sys
150424 10:06:16 mysqlbackup: INFO: Adding database directory: datadir/sztech
150424 10:06:16 mysqlbackup: INFO: Completing the copy of all non-innodb files.
150424 10:06:16 mysqlbackup: INFO: Adding database directory: datadir/test
150424 10:06:16 mysqlbackup: INFO: Completed the copy of binlog files...
150424 10:06:16 mysqlbackup: INFO: Adding database directory: datadir/world
150424 10:06:17 mysqlbackup: INFO: A copied database page was modified at 379176982.
          (This is the highest lsn found on page)
          Scanned log up to lsn 379176982.
          Was able to parse the log up to lsn 379176982.
          Maximum page number for a log record 0
150424 10:06:17 mysqlbackup: INFO: All tables unlocked
150424 10:06:17 mysqlbackup: INFO: All MySQL tables were locked for 2.928 seconds.
150424 10:06:17 mysqlbackup: INFO: Reading all global variables from the server.
150424 10:06:17 mysqlbackup: INFO: Completed reading of all global variables from the server.
150424 10:06:17 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /u01/dbback1/
mysqlbackup: INFO: Copying meta file /u01/dbback1/meta/backup_variables.txt.
mysqlbackup: INFO: Copying meta file /u01/dbback1/datadir/ibbackup_logfile.
mysqlbackup: INFO: Copying meta file /u01/dbback1/server-all.cnf.
mysqlbackup: INFO: Copying meta file /u01/dbback1/server-my.cnf.
mysqlbackup: INFO: Copying meta file /u01/dbback1/meta/backup_content.xml.
mysqlbackup: INFO: Copying meta file /u01/dbback1/meta/image_files.xml.
150424 10:06:20 mysqlbackup: INFO: Full Image Backup operation completed successfully.
150424 10:06:20 mysqlbackup: INFO: Backup image created successfully.
mysqlbackup: INFO: Image Path = /u01/dbback1/mybackup.bak
150424 10:06:20 mysqlbackup: INFO: MySQL binlog position: filename mysql-binlog.000024, position 120

-------------------------------------------------------------
   Parameters Summary
-------------------------------------------------------------
   Start LSN                  : 379176960
   End LSN                    : 379176982
-------------------------------------------------------------

mysqlbackup completed OK!
[root@sztech1 u01]#

验证一下文件，

[root@sztech1 dbback1]# pwd
/u01/dbback1
[root@sztech1 dbback1]# ls -ltr
总用量 343496
-rw-r--r-- 1 root root       246 4月 24 10:06 backup-my.cnf
drwx------ 2 root root      4096 4月 24 10:06 datadir
drwx------ 2 root root      4096 4月 24 10:06 meta
-rw-r--r-- 1 root root      3810 4月 24 10:06 server-my.cnf
-rw-r--r-- 1 root root     12752 4月 24 10:06 server-all.cnf
-rw-r--r-- 1 root root 351706334 4月 24 10:06 mybackup.bak

看到多了一个 mybackup.bak，但原来那些单个目录还在，看来是先产生单个文件，再打的包。

我们恢复，只需要这个 mybackup.bak 文件即可。

[此贴子已经被作者于2015-04-24 12:09:15编辑过]

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)