當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysqldumper 与 Innobackupex的备份和恢复操作实验过程

發(fā)布時(shí)間：2024/4/15 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysqldumper 与 Innobackupex的备份和恢复操作实验过程小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

一、mysqldumper

?? ?1.1 mysqldumper 編譯安裝

?? ?wget https://launchpadlibrarian.net/185032423/mydumper-0.6.2.tar.gz
?? ?[root@mdw tang]# tar -zxvf mydumper-0.6.2.tar.gz
?? ?[root@mdw tang]# cd mydumper-0.6.2
?? ?[root@mdw mydumper-0.6.2]# yum install pcre-devel.x86_64
?? ?[root@mdw mydumper-0.6.2]# mkdir bin
?? ?[root@mdw mydumper-0.6.2]# cd bin
?? ?[root@mdw bin]# cmake ../

?? ?報(bào)錯(cuò)，提示沒有支持包：
?? ?-- checking for one of the modules 'glib-2.0'
?? ?-- checking for one of the modules 'gthread-2.0'

?? ?查看官方介紹
?? ?https://answers.launchpad.net/mydumper/+faq/349

?? ?找到：
?? ?Ubuntu or Debian: apt-get install libglib2.0-dev libmysqlclient15-dev zlib1g-dev libpcre3-dev libssl-dev

?? ?安裝支持包：
?? ?yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel

?? ?[root@mdw bin]# cmake ../
?? ?-- Using mysql-config: /usr/local/mysql/bin/mysql_config
?? ?-- Found MySQL: /usr/local/mysql/include, /usr/local/mysql/lib/libmysqlclient.so;/usr/lib64/libpthread.so;/usr/lib64/libm.so;/usr/lib64/librt.so;/usr/lib64/libdl.so

?? ?CMake Warning at docs/CMakeLists.txt:9 (message):
?? ??? ?Unable to find Sphinx documentation generator

?? ?-- ------------------------------------------------
?? ?-- MYSQL_CONFIG = /usr/local/mysql/bin/mysql_config
?? ?-- CMAKE_INSTALL_PREFIX = /usr/local
?? ?-- BUILD_DOCS = ON
?? ?-- WITH_BINLOG = OFF
?? ?-- RUN_CPPCHECK = OFF
?? ?-- Change a values with: cmake -D<Variable>=<Value>
?? ?-- ------------------------------------------------
?? ?--
?? ?-- Configuring done
?? ?-- Generating done
?? ?-- Build files have been written to: /opt/mydumper0.6.2/bin
?? ??? ?[root@mdw bin]# make
?? ??? ?Scanning dependencies of target mydumper
?? ??? ?[ 25%] Building C object CMakeFiles/mydumper.dir/mydumper.c.o
?? ??? ?[ 50%] Building C object CMakeFiles/mydumper.dir/server_detect.c.o
?? ??? ?[ 75%] Building C object CMakeFiles/mydumper.dir/g_unix_signal.c.o
?? ??? ?Linking C executable mydumper
?? ??? ?[ 75%] Built target mydumper
?? ??? ?Scanning dependencies of target myloader
?? ??? ?[100%] Building C object CMakeFiles/myloader.dir/myloader.c.o
?? ??? ?Linking C executable myloader
?? ??? ?[100%] Built target myloader
?? ??? ?[root@mdw bin]#
?? ??? ?[root@mdw bin]# make install
?? ??? ?[ 75%] Built target mydumper
?? ??? ?[100%] Built target myloader
?? ??? ?Linking C executable CMakeFiles/CMakeRelink.dir/mydumper
?? ??? ?Linking C executable CMakeFiles/CMakeRelink.dir/myloader
?? ??? ?Install the project...
?? ??? ?-- Install configuration: ""
?? ??? ?-- Installing: /usr/local/bin/mydumper
?? ??? ?-- Installing: /usr/local/bin/myloader
?? ??? ?[root@mdw bin]#

?? ??? ?[root@mdw bin]# ll -rth mydumper myloader
?? ??? ?-rwxr-xr-x 1 root root 143K Feb 27 16:00 mydumper
?? ??? ?-rwxr-xr-x 1 root root? 48K Feb 27 16:00 myloader
?? ??? ?[root@mdw bin]#

?? ?

?? ?1.2 備份全庫

?? ??? ?[root@mdw bin]# ./mydumper -u root -p sa123 -S /tmp/mysql3306.sock -o? /opt/backup/all_database_0227.dmp

?? ??? ?備份出來的sql 文件，是以數(shù)據(jù)開關(guān).表名[-schema].sql 為名。有帶[-schema]的為表結(jié)構(gòu)創(chuàng)建語句，沒有的
?? ??? ?為表數(shù)據(jù)插入語句。

?? ??? ?[root@mdw bin]# ll /opt/backup/all_database_0227.dmp/
?? ??? ?total 768
?? ??? ?-rw-r--r-- 1 root root??? 200 Feb 27 16:37 erp.order-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 393 Feb 27 16:37 erp.refundorder-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 191 Feb 27 16:37 erp.test1-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 236 Feb 27 16:37 erp.test1.sql
?? ??? ?-rw-r--r-- 1 root root??? 238 Feb 27 16:37 erp.test2-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 188 Feb 27 16:37 jfedu.t1-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 193 Feb 27 16:37 jfedu.t1.sql
?? ??? ?-rw-r--r-- 1 root root??? 130 Feb 27 16:37 metadata
?? ??? ?-rw-r--r-- 1 root root??? 722 Feb 27 16:37 mysql.columns_priv-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 1783 Feb 27 16:37 mysql.db-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 513 Feb 27 16:37 mysql.db.sql
?? ??? ?-rw-r--r-- 1 root root?? 2210 Feb 27 16:37 mysql.event-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 419 Feb 27 16:37 mysql.func-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 388 Feb 27 16:37 mysql.help_category-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 1373 Feb 27 16:37 mysql.help_category.sql
?? ??? ?-rw-r--r-- 1 root root??? 298 Feb 27 16:37 mysql.help_keyword-schema.sql
?? ??? ?-rw-r--r-- 1 root root? 11410 Feb 27 16:37 mysql.help_keyword.sql
?? ??? ?-rw-r--r-- 1 root root??? 311 Feb 27 16:37 mysql.help_relation-schema.sql
?? ??? ?-rw-r--r-- 1 root root? 13223 Feb 27 16:37 mysql.help_relation.sql
?? ??? ?-rw-r--r-- 1 root root??? 423 Feb 27 16:37 mysql.help_topic-schema.sql
?? ??? ?-rw-r--r-- 1 root root 591208 Feb 27 16:37 mysql.help_topic.sql
?? ??? ?-rw-r--r-- 1 root root??? 712 Feb 27 16:37 mysql.innodb_index_stats-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 1682 Feb 27 16:37 mysql.innodb_index_stats.sql
?? ??? ?-rw-r--r-- 1 root root??? 578 Feb 27 16:37 mysql.innodb_table_stats-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 372 Feb 27 16:37 mysql.innodb_table_stats.sql
?? ??? ?-rw-r--r-- 1 root root??? 592 Feb 27 16:37 mysql.ndb_binlog_index-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 259 Feb 27 16:37 mysql.plugin-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 1929 Feb 27 16:37 mysql.proc-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 820 Feb 27 16:37 mysql.procs_priv-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 708 Feb 27 16:37 mysql.proxies_priv-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 240 Feb 27 16:37 mysql.proxies_priv.sql
?? ??? ?-rw-r--r-- 1 root root??? 567 Feb 27 16:37 mysql.servers-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 2805 Feb 27 16:37 mysql.slave_master_info-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 1122 Feb 27 16:37 mysql.slave_relay_log_info-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 907 Feb 27 16:37 mysql.slave_worker_info-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 916 Feb 27 16:37 mysql.tables_priv-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 302 Feb 27 16:37 mysql.time_zone_leap_second-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 258 Feb 27 16:37 mysql.time_zone_name-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 300 Feb 27 16:37 mysql.time_zone-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 359 Feb 27 16:37 mysql.time_zone_transition-schema.sql
?? ??? ?-rw-r--r-- 1 root root??? 471 Feb 27 16:37 mysql.time_zone_transition_type-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 3140 Feb 27 16:37 mysql.user-schema.sql
?? ??? ?-rw-r--r-- 1 root root?? 2018 Feb 27 16:37 mysql.user.sql
?? ??? ?[root@mdw bin]#

?? ??? ?metadata這個(gè)文件記錄的是當(dāng)里的binlog文件及pos，可以使用這個(gè)信息搭建slave.
?? ??? ?[root@mdw bin]# cat /opt/backup/all_database_0227.dmp/metadata
?? ??? ?Started dump at: 2017-02-27 16:37:52
?? ??? ?SHOW MASTER STATUS:
?? ??? ??? ?Log: mysql-bin.000006
?? ??? ??? ?Pos: 6487

?? ??? ?Finished dump at: 2017-02-27 16:37:52
?? ??? ?[root@mdw bin]#

二、誤操作truncate table gyj_t1;利用mysqldump的備份和binlog日志對(duì)表gyj_t1做完全恢復(fù)

?? ??? ?2.1 備份前查看binlog文件。

?? ??? ??? ?[root@sdw2 ~]# ll /opt/mysql/data
?? ??? ??? ?total 123012
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 56 Feb 24 00:32 auto.cnf
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 28 00:32 erp
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 453 Feb 28 01:54 ib_buffer_pool
?? ??? ??? ?-rw-r----- 1 mysql mysql 12582912 Feb 28 01:55 ibdata1
?? ??? ??? ?-rw-r----- 1 mysql mysql 50331648 Feb 28 01:55 ib_logfile0
?? ??? ??? ?-rw-r----- 1 mysql mysql 50331648 Feb 24 00:32 ib_logfile1
?? ??? ??? ?-rw-r----- 1 mysql mysql 12582912 Feb 28 18:38 ibtmp1
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 28 00:45 jfedu
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 130 Feb 28 18:38 master.info
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 01:05 mysql
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:38 mysql-bin.000001
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:38 mysql-bin.000002
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 154 Feb 28 18:38 mysql-bin.000003
?? ??? ??? ?-rw-r----- 1 mysql mysql??? 10199 Feb 28 19:20 mysql-bin.000004
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 5131 Feb 28 19:27 mysql-bin.000005
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 4331 Mar? 1 14:00 mysql-bin.000006
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 5321 Mar? 1 14:31 mysql-bin.000007
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 57 Feb 28 18:38 mysql-bin.index
?? ??? ??? ?-rw-rw---- 1 root? root???????? 6 Feb 28 01:55 mysqld_safe.pid
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 00:32 performance_schema
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 61 Feb 28 18:38 relay-log.info
?? ??? ??? ?-rw-r----- 1 mysql mysql??? 42355 Feb 28 18:31 sdw2.err
?? ??? ??? ?-rw-r----- 1 mysql mysql??????? 6 Feb 28 01:55 sdw2.pid
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 325 Feb 28 18:38 sdw2-relay-bin.000020
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 273 Feb 28 18:38 sdw2-relay-bin.000021
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 48 Feb 28 18:38 sdw2-relay-bin.index
?? ??? ??? ?drwxr-x--- 2 mysql mysql??? 12288 Feb 24 00:32 sys
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 01:05 test
?? ??? ??? ?[root@sdw2 ~]#

?? ??? ?2.2 備份

?? ??? ??? ?[root@sdw2 ~]# mysqldump -uroot -p? -l -F -S /tmp/mysql3310.sock? jfedu > /tmp/0301jfedu.dmp
?? ??? ??? ?Enter password:
?? ??? ??? ?[root@sdw2 ~]# ll /tmp/0301jfedu.dmp
?? ??? ??? ?-rw-r--r-- 1 root root 2464 Feb 28 18:38 /tmp/0301jfedu.dmp

?? ??? ?2.3 備份完成后，查看 binlog生成的新文件 mysql-bin.000008

?? ??? ??? ?[root@sdw2 ~]# ll /opt/mysql/data/
?? ??? ??? ?total 188592
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 56 Feb 24 00:32 auto.cnf
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Mar? 1 14:12 erp
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 453 Feb 28 01:54 ib_buffer_pool
?? ??? ??? ?-rw-r----- 1 mysql mysql 79691776 Mar? 1 14:18 ibdata1
?? ??? ??? ?-rw-r----- 1 mysql mysql 50331648 Mar? 1 14:18 ib_logfile0
?? ??? ??? ?-rw-r----- 1 mysql mysql 50331648 Feb 24 00:32 ib_logfile1
?? ??? ??? ?-rw-r----- 1 mysql mysql 12582912 Mar? 1 14:31 ibtmp1
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 28 19:23 jfedu
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 130 Mar? 1 14:31 master.info
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 01:05 mysql
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:38 mysql-bin.000001
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:38 mysql-bin.000002
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:41 mysql-bin.000003
?? ??? ??? ?-rw-r----- 1 mysql mysql??? 10199 Feb 28 19:20 mysql-bin.000004
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 5131 Feb 28 19:27 mysql-bin.000005
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 4331 Mar? 1 14:00 mysql-bin.000006
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 5321 Mar? 1 14:31 mysql-bin.000007
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 154 Mar? 1 14:31 mysql-bin.000008
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 152 Mar? 1 14:31 mysql-bin.index
?? ??? ??? ?-rw-rw---- 1 root? root???????? 6 Feb 28 01:55 mysqld_safe.pid
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 00:32 performance_schema
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 61 Mar? 1 14:31 relay-log.info
?? ??? ??? ?-rw-r----- 1 mysql mysql??? 42908 Mar? 1 14:18 sdw2.err
?? ??? ??? ?-rw-r----- 1 mysql mysql??????? 6 Feb 28 01:55 sdw2.pid
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 325 Mar? 1 14:31 sdw2-relay-bin.000025
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 273 Mar? 1 14:31 sdw2-relay-bin.000026
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 48 Mar? 1 14:31 sdw2-relay-bin.index
?? ??? ??? ?drwxr-x--- 2 mysql mysql??? 12288 Feb 24 00:32 sys
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 01:05 test

?? ??? ?3.1 備份后，做數(shù)據(jù)插入及清空表操作

?? ??? ??? ??? ?mysql> insert into gyj_t1 values(3,'dumped 3');
?? ??? ??? ??? ?Query OK, 1 row affected (0.00 sec)

?? ??? ??? ??? ?mysql> insert into gyj_t1 values(4,'dumped 4');
?? ??? ??? ??? ?Query OK, 1 row affected (0.01 sec)

?? ??? ??? ??? ?mysql> commit;
?? ??? ??? ??? ?Query OK, 0 rows affected (0.00 sec)

?? ??? ??? ??? ?mysql> select * from gyj_t1;
?? ??? ??? ??? ?+------+----------+
?? ??? ??? ??? ?| id?? | name???? |
?? ??? ??? ??? ?+------+----------+
?? ??? ??? ??? ?|??? 1 | a name?? |
?? ??? ??? ??? ?|??? 2 | b name?? |
?? ??? ??? ??? ?|??? 3 | dumped 3 |
?? ??? ??? ??? ?|??? 4 | dumped 4 |
?? ??? ??? ??? ?+------+----------+
?? ??? ??? ??? ?4 rows in set (0.00 sec)

?? ??? ??? ??? ?mysql> truncate table gyj_t1;
?? ??? ??? ??? ?Query OK, 0 rows affected (0.01 sec)

?? ??? ??? ??? ?mysql> select * from gyj_t1;
?? ??? ??? ??? ?Empty set (0.00 sec)

?? ??? ??? ??? ?mysql> select now();
?? ??? ??? ??? ?+---------------------+
?? ??? ??? ??? ?| now()?????????????? |
?? ??? ??? ??? ?+---------------------+
?? ??? ??? ??? ?| 2017-03-01 14:33:58 |
?? ??? ??? ??? ?+---------------------+
?? ??? ??? ??? ?1 row in set (0.00 sec)

?? ??? ??? ??? ?mysql> truncate table gyj_t1;
?? ??? ??? ??? ?Query OK, 0 rows affected (0.07 sec)

?? ??? ??? ??? ?mysql> select * from gyj_t1;
?? ??? ??? ??? ?Empty set (0.00 sec)

?? ??? ?4. 恢復(fù)

?? ??? ??? ?4.1 恢復(fù)

?? ??? ??? ?[root@sdw2 ~]# mysql -uroot -p -S /tmp/mysql3310.sock jfedu? < /tmp/0301jfedu.dmp
?? ??? ??? ?Enter password:

?? ??? ??? ?4.2 查看恢復(fù)后的數(shù)據(jù)：

?? ??? ??? ?mysql> mysql> select * from gyj_t1;
?? ??? ??? ?+------+--------+
?? ??? ??? ?| id?? | name?? |
?? ??? ??? ?+------+--------+
?? ??? ??? ?|??? 1 | AAAAA? |
?? ??? ??? ?|??? 2 | BBBBBB |
?? ??? ??? ?+------+--------+
?? ??? ??? ?2 rows in set (0.00 sec)
?? ??? ??? ?只有備份前的數(shù)據(jù)

?? ??? ??? ?4.3 使用mysqlbinlog 恢復(fù)

?? ??? ??? ??? ?[root@sdw2 ~]# mysqlbinlog? '/opt/mysql/data/mysql-bin.000008' > gyj.sql
?? ??? ??? ??? ?[root@sdw2 ~]# vi gyj.sql
?? ??? ??? ??? ??? ??? ?

?? ??? ??? ??? ?
?? ??? ??? ??? ?#170301 14:34:14 server id 201710? end_log_pos 1447 CRC32 0x4e16da5d??? Anonymous_GTID? last_committed=5??????? sequence_number=6
?? ??? ??? ??? ?SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
?? ??? ??? ??? ?# at 1447
?? ??? ??? ??? ?#170301 14:34:14 server id 201710? end_log_pos 1536 CRC32 0x30efcd28??? Query?? thread_id=20??? exec_time=47??? error_code=0
?? ??? ??? ??? ?SET TIMESTAMP=1488407654/*!*/;
?? ??? ??? ??? ?SET @@session.sql_mode=1075838976/*!*/;
?? ??? ??? ??? ?truncate table gyj_t1
?? ??? ??? ??? ?/*!*/;
?? ??? ??? ??? ?SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
?? ??? ??? ??? ?DELIMITER ;
?? ??? ??? ??? ?# End of log file

?? ??? ??? ?#按前面? 查詢的當(dāng)前時(shí)間 2017-03-01 14:33:58? 查詢到truncate 語句是在 end_log_pos 1536 后?？梢曰謴?fù)可以恢復(fù)到這個(gè)點(diǎn)。

?? ??? ??? ?4.3 按時(shí)間恢復(fù)

?? ??? ??? ?[root@sdw2 ~]# mysql -uroot -p -S /tmp/mysql3310.sock jfedu? < /tmp/0301jfedu.dmp
?? ??? ??? ?Enter password:

?? ??? ??? ? mysqlbinlog? '/opt/mysql/data/mysql-bin.000008' --start-position=1036 --stop-position=1447 |mysql -uroot -p -S /tmp/mysql3310.sock

?? ??? ??? ?（前幾次按時(shí)間點(diǎn)恢復(fù)失敗，主要原因是我沒想到，最后一次使用備份/tmp/0301jfedu.dmp 做恢復(fù)時(shí)，也是會(huì)寫到binlog日志中的，
?? ??? ??? ?沒指定恢復(fù)時(shí)間段范圍，其實(shí)是又把恢復(fù)重做了一次。最后添加的數(shù)據(jù)還是沒有出來）
?? ??? ??? ?#再次查詢，發(fā)現(xiàn)數(shù)據(jù)已恢復(fù)。
?? ??? ??? ?mysql> select * from gyj_t1;
?? ??? ??? ?+------+----------+
?? ??? ??? ?| id?? | name???? |
?? ??? ??? ?+------+----------+
?? ??? ??? ?|??? 1 | a name?? |
?? ??? ??? ?|??? 2 | b name?? |
?? ??? ??? ?|??? 3 | dumped 3 |
?? ??? ??? ?|??? 4 | dumped 4 |
?? ??? ??? ?+------+----------+

三、利用Innobackupex的備份和binlog日志對(duì)MySQL數(shù)據(jù)庫做完全恢復(fù)。

誤操作MySQL數(shù)據(jù)庫：rm /u01/my3306/data/*; 再利用Innobackupex的備份和binlog日志對(duì)MySQL數(shù)據(jù)庫做完全恢復(fù)。

?? ?wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.3/binary/redhat/6/x86_64/percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

?? ?yum install percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

?? ?#完全備份
?? ??? ?
?? ??? ?#完全備份放到/opt/backup/full
?? ??? ?#日志備份放到 /opt/backup/log
?? ??? ?#增量備份放到 /opt/backup/inc

?? ??? ?[root@sdw2 ~]# ll /opt/backup
?? ??? ?total 12
?? ??? ?drwxr-xr-x 2 mysql mysql 4096 Mar? 2 00:40 config
?? ??? ?drwxr-xr-x 2 mysql mysql 4096 Mar? 2 00:26 full
?? ??? ?drwxr-xr-x 2 mysql mysql 4096 Mar? 2 00:27 inc
?? ??? ?[root@sdw2 ~]# mkdir /opt/backup/log
?? ??? ?[root@sdw2 ~]# chown mysql:mysql /opt/backup/log
?? ??? ?[root@sdw2 ~]# innobackupex -uroot -psa123 -S /tmp/mysql3310.sock /opt/backup/full?? ?#做個(gè)完全備份
?? ??? ?170302 18:28:40 innobackupex: Starting the backup operation

?? ??? ?IMPORTANT: Please check that the backup run completes successfully.
?? ??? ??? ??? ??? ??? ??? ? At the end of a successful backup run innobackupex
?? ??? ??? ??? ??? ??? ??? ? prints "completed OK!".

?? ??? ?170302 18:28:41? version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql3310.sock' as 'root'? (using password: YES).
?? ??? ?170302 18:28:41? version_check Connected to MySQL server
?? ??? ?......

?? ??? ?170302 18:28:51 Executing UNLOCK TABLES
?? ??? ?170302 18:28:51 All tables unlocked
?? ??? ?170302 18:28:51 [00] Copying ib_buffer_pool to /opt/backup/full/2017-03-02_18-28-40/ib_buffer_pool
?? ??? ?170302 18:28:51 [00]??????? ...done
?? ??? ?170302 18:28:51 Backup created in directory '/opt/backup/full/2017-03-02_18-28-40/'
?? ??? ?MySQL binlog position: filename 'mysql-bin.000008', position '9677'
?? ??? ?170302 18:28:51 [00] Writing backup-my.cnf
?? ??? ?170302 18:28:51 [00]??????? ...done
?? ??? ?170302 18:28:52 [00] Writing xtrabackup_info
?? ??? ?170302 18:28:52 [00]??????? ...done
?? ??? ?xtrabackup: Transaction log of lsn (3145618) to (3145627) was copied.
?? ??? ?170302 18:28:52 completed OK!
?? ??? ?[root@sdw2 ~]#
?? ??? ?[root@sdw2 ~]#
?? ??? ?[root@sdw2 ~]# ll /opt/backup/full
?? ??? ?total 4
?? ??? ?drwxr-x--- 8 root root 4096 Mar? 2 18:28 2017-03-02_18-28-40

?? ??? ?#在做備份的同時(shí)，多插入幾條數(shù)據(jù)，確認(rèn)后面恢復(fù)后是否已恢復(fù)期間插入的數(shù)據(jù)
?? ??? ?mysql> select * from gyj_t1;
?? ??? ?+------+----------+
?? ??? ?| id?? | name???? |
?? ??? ?+------+----------+
?? ??? ?|??? 1 | a name?? |
?? ??? ?|??? 2 | b name?? |
?? ??? ?|??? 4 | dumped 4 |
?? ??? ?+------+----------+
?? ??? ?3 rows in set (0.00 sec)

?? ??? ?mysql> insert into gyj_t1 values(5,'innobakupex ing insert');
?? ??? ?ERROR 1406 (22001): Data too long for column 'name' at row 1
?? ??? ?mysql> insert into gyj_t1 values(5,'bakupex ing insert');
?? ??? ?Query OK, 1 row affected (0.07 sec)

?? ??? ?mysql> insert into gyj_t1 values(5,'bakupex ing insert');
?? ??? ?Query OK, 1 row affected (0.14 sec)

?? ??? ?mysql> insert into gyj_t1 values(5,'bakupex ing insert');
?? ??? ?Query OK, 1 row affected (1.63 sec)

?? ??? ?mysql> select * from gyj_t1;
?? ??? ?+------+--------------------+
?? ??? ?| id?? | name?????????????? |
?? ??? ?+------+--------------------+
?? ??? ?|??? 1 | a name???????????? |
?? ??? ?|??? 2 | b name???????????? |
?? ??? ?|??? 4 | dumped 4?????????? |
?? ??? ?|??? 5 | bakupex ing insert |
?? ??? ?|??? 5 | bakupex ing insert |
?? ??? ?|??? 5 | bakupex ing insert |
?? ??? ?+------+--------------------+
?? ??? ?6 rows in set (0.00 sec)

?? ??? ?mysql>

?? ??? ?#刪除mysql 數(shù)據(jù)目錄中所有文件
?? ??? ??? ?[root@sdw2 ~]# ll /opt/mysql/data/
?? ??? ??? ?total 188748
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 56 Feb 24 00:32 auto.cnf
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Mar? 1 14:12 erp
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 453 Feb 28 01:54 ib_buffer_pool
?? ??? ??? ?-rw-r----- 1 mysql mysql 79691776 Mar? 2 18:33 ibdata1
?? ??? ??? ?-rw-r----- 1 mysql mysql 50331648 Mar? 2 18:33 ib_logfile0
?? ??? ??? ?-rw-r----- 1 mysql mysql 50331648 Feb 24 00:32 ib_logfile1
?? ??? ??? ?-rw-r----- 1 mysql mysql 12582912 Mar? 2 18:27 ibtmp1
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Mar? 2 18:27 jfedu
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 130 Mar? 1 01:18 master.info
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 01:05 mysql
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:38 mysql-bin.000001
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:38 mysql-bin.000002
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 201 Feb 28 18:41 mysql-bin.000003
?? ??? ??? ?-rw-r----- 1 mysql mysql??? 10199 Feb 28 19:20 mysql-bin.000004
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 5131 Feb 28 19:27 mysql-bin.000005
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 4331 Mar? 1 14:00 mysql-bin.000006
?? ??? ??? ?-rw-r----- 1 mysql mysql???? 5321 Mar? 1 14:31 mysql-bin.000007
?? ??? ??? ?-rw-r----- 1 mysql mysql??? 10520 Mar? 2 18:33 mysql-bin.000008
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 152 Mar? 1 14:31 mysql-bin.index
?? ??? ??? ?-rw-rw---- 1 root? root???????? 6 Feb 28 01:55 mysqld_safe.pid
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 00:32 performance_schema
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 61 Mar? 1 14:31 relay-log.info
?? ??? ??? ?-rw-r----- 1 mysql mysql?? 194838 Mar? 2 18:35 sdw2.err
?? ??? ??? ?-rw-r----- 1 mysql mysql??????? 6 Feb 28 01:55 sdw2.pid
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 325 Mar? 1 14:31 sdw2-relay-bin.000025
?? ??? ??? ?-rw-r----- 1 mysql mysql????? 273 Mar? 1 14:31 sdw2-relay-bin.000026
?? ??? ??? ?-rw-r----- 1 mysql mysql?????? 48 Mar? 1 14:31 sdw2-relay-bin.index
?? ??? ??? ?drwxr-x--- 2 mysql mysql??? 12288 Feb 24 00:32 sys
?? ??? ??? ?drwxr-x--- 2 mysql mysql???? 4096 Feb 24 01:05 test
?? ??? ??? ?[root@sdw2 ~]# rm -rif /opt/mysql/data/*
?? ??? ??? ?[root@sdw2 ~]# ll /opt/mysql/data
?? ??? ??? ?total 0
?? ??? ??? ?[root@sdw2 ~]#

?? ??? ?#關(guān)閉進(jìn)程
?? ??? ?[root@sdw2 ~]# ps -ef|grep mysqld
?? ??? ?root???? 23771???? 1? 0 03:42 ???????? 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
?? ??? ?mysql??? 23944 23771? 0 03:42 ???????? 00:00:48 ./bin/mysqld --basedir=/opt/mysql/mysql5.7.17 --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/mysql5.7.17/lib/plugin --user=mysql --log-error=/opt/mysql/data/sdw2.err --pid-file=/opt/mysql/data/sdw2.pid --socket=/tmp/mysql3310.sock --port=3310
?? ??? ?root???? 37391 37073? 0 18:40 pts/2??? 00:00:00 grep mysqld
?? ??? ?[root@sdw2 ~]# kill -9 23771
?? ??? ?[root@sdw2 ~]# kill -9 23944
?? ??? ?[root@sdw2 ~]# ps -ef|grep mysqld
?? ??? ?root???? 37396 37073? 0 18:40 pts/2??? 00:00:00 grep mysqld
?? ??? ?[root@sdw2 ~]#

?? ??? ?#恢復(fù)
?? ??? ?[root@sdw2 /]# innobackupex --defaults-file=/opt/backup/config/backup-my.cnf -uroot -psa123? --copy-back --rsync? /opt/backup/full/2017-03-02_18-33-34/
?? ??? ?170302 21:49:23 innobackupex: Starting the copy-back operation

?? ??? ?IMPORTANT: Please check that the copy-back run completes successfully.
?? ??? ??? ??? ??? ??? ??? ? At the end of a successful copy-back run innobackupex
?? ??? ??? ??? ??? ??? ??? ? prints "completed OK!".

?? ??? ?innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
?? ??? ?170302 21:49:23 [01] Copying ib_logfile0 to /opt/mysql/data/ib_logfile0
?? ??? ?170302 21:49:24 [01]??????? ...done
?? ??? ?......

?? ??? ?#修改目錄用戶

?? ??? ??? ?[root@sdw2 config]# chown -R mysql:mysql /opt/mysql/data

?? ??? ?#啟動(dòng)mysqld
?? ??? ??? ?[root@sdw2 mysql5.7.17]# ps -ef|grep mysqld
?? ??? ??? ?root???? 45731 37073? 0 21:50 pts/2??? 00:00:00 /bin/sh bin/mysqld_safe -user=mysql
?? ??? ??? ?mysql??? 45905 45731? 6 21:50 pts/2??? 00:00:00 ./bin/mysqld --basedir=/opt/mysql/mysql5.7.17 --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/mysql5.7.17/lib/plugin --user=mysql -user=mysql --log-error=/opt/mysql/data/sdw2.err --pid-file=/opt/mysql/data/sdw2.pid --socket=/tmp/mysql3310.sock --port=3310
?? ??? ??? ?root???? 45937 37073? 0 21:51 pts/2??? 00:00:00 grep mysqld

?? ??? ??? ?#登錄 mysql 測(cè)試數(shù)據(jù)是否已恢復(fù)。
?? ??? ??? ?[root@sdw2 mysql5.7.17]# mysql -uroot -psa123 -S /tmp/mysql3310.sock
?? ??? ??? ?mysql: [Warning] Using a password on the command line interface can be insecure.
?? ??? ??? ?Welcome to the MySQL monitor.? Commands end with ; or \g.
?? ??? ??? ?Your MySQL connection id is 3
?? ??? ??? ?Server version: 5.7.17-log MySQL Community Server (GPL)

?? ??? ??? ?Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

?? ??? ??? ?Oracle is a registered trademark of Oracle Corporation and/or its
?? ??? ??? ?affiliates. Other names may be trademarks of their respective
?? ??? ??? ?owners.

?? ??? ??? ?Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

?? ??? ??? ?mysql> use jfedu;
?? ??? ??? ?Reading table information for completion of table and column names
?? ??? ??? ?You can turn off this feature to get a quicker startup with -A

?? ??? ??? ?Database changed
?? ??? ??? ?mysql> select *from gyj_t1;
?? ??? ??? ?+------+--------------------+
?? ??? ??? ?| id?? | name?????????????? |
?? ??? ??? ?+------+--------------------+
?? ??? ??? ?|??? 1 | a name???????????? |
?? ??? ??? ?|??? 2 | b name???????????? |
?? ??? ??? ?|??? 4 | dumped 4?????????? |
?? ??? ??? ?|??? 5 | bakupex ing insert |
?? ??? ??? ?|??? 5 | bakupex ing insert |
?? ??? ??? ?+------+--------------------+
?? ??? ??? ?5 rows in set (0.00 sec)

?? ??? ??? ?mysql>

?? ??? ??? ?#昨天和同事討論MYSQL 的恢復(fù)備份，說binlog是不能刪除的，刪除后不能恢復(fù)，但我的binlog 和數(shù)據(jù)文件是在同一目錄下。
?? ??? ??? ?刪除了，照樣可以恢復(fù)。
?? ??? ??? ?

總結(jié)

以上是生活随笔為你收集整理的mysqldumper 与 Innobackupex的备份和恢复操作实验过程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。