700字范文 > mysqldumper 与 Innobackupex的备份和恢复操作实验过程

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

时间：2019-07-05 16:50:08

一、mysqldumper

1.1 mysqldumper 编译安装

wget /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 ../

报错，提示没有支持包：

-- checking for one of the modules 'glib-2.0'

-- checking for one of the modules 'gthread-2.0'

查看官方介绍

/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 文件，是以数据开关.表名[-schema].sql 为名。有带[-schema]的为表结构创建语句，没有的

为表数据插入语句。

[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 Feb 27 16:37 mysql.user.sql

[root@mdw bin]#

metadata这个文件记录的是当里的binlog文件及pos，可以使用这个信息搭建slave.

[root@mdw bin]# cat /opt/backup/all_database_0227.dmp/metadata

Started dump at: -02-27 16:37:52

SHOW MASTER STATUS:

Log: mysql-bin.000006

Pos: 6487

Finished dump at: -02-27 16:37:52

[root@mdw bin]#

二、误操作truncate table gyj_t1;利用mysqldump的备份和binlog日志对表gyj_t1做完全恢复

2.1 备份前查看binlog文件。

[root@sdw2 ~]# ll /opt/mysql/data

total 123012

-rw-r----- 1 mysql mysql 56 Feb 24 00:32 f

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 f

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 备份后，做数据插入及清空表操作

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() |

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

| -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. 恢复

4.1 恢复

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

Enter password:

4.2 查看恢复后的数据：

mysql> mysql> select * from gyj_t1;

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

| id | name |

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

| 1 | AAAAA |

| 2 | BBBBBB |

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

2 rows in set (0.00 sec)

只有备份前的数据

4.3 使用mysqlbinlog 恢复

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

[root@sdw2 ~]# vi gyj.sql

#170301 14:34:14 server id 10 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 10 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

#按前面查询的当前时间 -03-01 14:33:58 查询到truncate 语句是在 end_log_pos 1536 后。可以恢复可以恢复到这个点。

4.3 按时间恢复

[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

（前几次按时间点恢复失败，主要原因是我没想到，最后一次使用备份/tmp/0301jfedu.dmp 做恢复时，也是会写到binlog日志中的，

没指定恢复时间段范围，其实是又把恢复重做了一次。最后添加的数据还是没有出来）

#再次查询，发现数据已恢复。

mysql> select * from gyj_t1;

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

| id | name |

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

| 1 | a name |

| 2 | b name |

| 3 | dumped 3 |

| 4 | dumped 4 |

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

三、利用Innobackupex的备份和binlog日志对MySQL数据库做完全恢复。

误操作MySQL数据库：rm /u01/my3306/data/*; 再利用Innobackupex的备份和binlog日志对MySQL数据库做完全恢复。

wget /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 #做个完全备份

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/-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/-03-02_18-28-40/'

MySQL binlog position: filename 'mysql-bin.000008', position '9677'

170302 18:28:51 [00] Writing backup-f

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 ~]# ll /opt/backup/full

total 4

drwxr-x--- 8 root root 4096 Mar 2 18:28 -03-02_18-28-40

#在做备份的同时，多插入几条数据，确认后面恢复后是否已恢复期间插入的数据

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 |

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

6 rows in set (0.00 sec)

mysql>

#删除mysql 数据目录中所有文件

[root@sdw2 ~]# ll /opt/mysql/data/

total 188748

-rw-r----- 1 mysql mysql 56 Feb 24 00:32 f

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 ~]#

#关闭进程

[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 ~]#

#恢复

[root@sdw2 /]# innobackupex --defaults-file=/opt/backup/config/backup-f -uroot -psa123 --copy-back --rsync /opt/backup/full/-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

#启动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 测试数据是否已恢复。

[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)

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;