innobackupex 備份MySQL資料庫
環境：
rhel 7.2
MySQL：5.6.38
測試innobackupex備份、恢復MySQL過程
--建立測試資料庫
[root@rhel72 mysql]# mysql -uroot -pgyc1234
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 14
Server version: 5.6.38-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2017, 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>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.04 sec)
mysql> create database gaoyc;
Query OK, 1 row affected (0.00 sec)
mysql> use gaoyc
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table gyc as select * from mysql.user;
Query OK, 3 rows affected (0.41 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 24 rows affected (0.00 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 48 rows affected (0.05 sec)
Records: 48 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> create table gyc02 as select * from gyc;
Query OK, 96 rows affected (0.04 sec)
Records: 96 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 96 rows affected (0.05 sec)
Records: 96 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--開始備份
[root@rhel72 backup]# ps -ef | grep mysql
root 16710 14195 0 16:00 pts/3 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysql 17002 16710 0 16:00 pts/3 00:00:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/mysqld.log --pid-file=/usr/local/mysql/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root 17078 14165 0 16:01 pts/2 00:00:00 mysql -uroot -px xxxxx
root 17083 14134 0 16:33 pts/0 00:00:00 grep --color=auto mysql
[root@rhel72 backup]# innobackupex -S /tmp/mysql.sock --user=root --password=gyc1234 --no-timestamp /backup/`date +%H-%M`.dbname
180410 16:34:41 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!".
180410 16:34:42 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES).
180410 16:34:42 version_check Connected to MySQL server
180410 16:34:42 version_check Executing a version check against the server...
180410 16:34:42 version_check Done.
180410 16:34:42 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.6.38-enterprise-commercial-advanced-log
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
.......
MySQL binlog position: filename 'mysql-bin.000003', position '4415'
180410 16:34:46 [00] Writing /backup/16-34.dbname/backup-my.cnf
180410 16:34:46 [00] ...done
180410 16:34:46 [00] Writing /backup/16-34.dbname/xtrabackup_info
180410 16:34:46 [00] ...done
xtrabackup: Transaction log of lsn (1823795) to (1823795) was copied.
180410 16:34:46 completed OK!
--停止資料庫
[root@rhel72 backup]# /usr/local/mysql/bin/mysqladmin -S /tmp/mysql.sock shutdown -p
Enter password:
--備份原來資料庫
[root@rhel72 mysql]# tar -cvf data.tar data/
data/
data/mysql/
data/mysql/db.frm
....
[root@rhel72 data]# rm -rf *
[root@rhel72 data]# pwd
/usr/local/mysql/data
--開始恢復
[root@rhel72 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /backup/16-34.dbname
180410 16:36:18 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: cd to /backup/16-34.dbname/
xtrabackup: This target seems to be not prepared yet.
.....
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1824296
180410 16:36:29 completed OK!
[root@rhel72 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /backup/16-34.dbname
180410 16:37:08 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.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
180410 16:37:08 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0
......
--調整許可權，開啟資料庫
[root@rhel72 data]# chown -R mysql:mysql ../data
[root@rhel72 data]# /usr/local/mysql/bin/mysqld_safe
180410 16:38:34 mysqld_safe Logging to '/usr/local/mysql/mysqld.log'.
180410 16:38:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
--驗證資料
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gaoyc |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.07 sec)
mysql> use gaoyc
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> show tables;
+-----------------+
| Tables_in_gaoyc |
+-----------------+
| gyc |
| gyc02 |
+-----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from gyc;
+----------+
| count(*) |
+----------+
| 192 |
+----------+
1 row in set (0.08 sec)
mysql> select count(*) from gyc02;
+----------+
| count(*) |
+----------+
| 96 |
+----------+
1 row in set (0.05 sec)
rhel 7.2
MySQL：5.6.38
測試innobackupex備份、恢復MySQL過程
--建立測試資料庫
[root@rhel72 mysql]# mysql -uroot -pgyc1234
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 14
Server version: 5.6.38-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2017, 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>
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.04 sec)
mysql> create database gaoyc;
Query OK, 1 row affected (0.00 sec)
mysql> use gaoyc
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table gyc as select * from mysql.user;
Query OK, 3 rows affected (0.41 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 24 rows affected (0.00 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 48 rows affected (0.05 sec)
Records: 48 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> create table gyc02 as select * from gyc;
Query OK, 96 rows affected (0.04 sec)
Records: 96 Duplicates: 0 Warnings: 0
mysql> insert into gyc select * from gyc;
Query OK, 96 rows affected (0.05 sec)
Records: 96 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--開始備份
[root@rhel72 backup]# ps -ef | grep mysql
root 16710 14195 0 16:00 pts/3 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysql 17002 16710 0 16:00 pts/3 00:00:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/mysqld.log --pid-file=/usr/local/mysql/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root 17078 14165 0 16:01 pts/2 00:00:00 mysql -uroot -px xxxxx
root 17083 14134 0 16:33 pts/0 00:00:00 grep --color=auto mysql
[root@rhel72 backup]# innobackupex -S /tmp/mysql.sock --user=root --password=gyc1234 --no-timestamp /backup/`date +%H-%M`.dbname
180410 16:34:41 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!".
180410 16:34:42 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root' (using password: YES).
180410 16:34:42 version_check Connected to MySQL server
180410 16:34:42 version_check Executing a version check against the server...
180410 16:34:42 version_check Done.
180410 16:34:42 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.6.38-enterprise-commercial-advanced-log
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
.......
MySQL binlog position: filename 'mysql-bin.000003', position '4415'
180410 16:34:46 [00] Writing /backup/16-34.dbname/backup-my.cnf
180410 16:34:46 [00] ...done
180410 16:34:46 [00] Writing /backup/16-34.dbname/xtrabackup_info
180410 16:34:46 [00] ...done
xtrabackup: Transaction log of lsn (1823795) to (1823795) was copied.
180410 16:34:46 completed OK!
--停止資料庫
[root@rhel72 backup]# /usr/local/mysql/bin/mysqladmin -S /tmp/mysql.sock shutdown -p
Enter password:
--備份原來資料庫
[root@rhel72 mysql]# tar -cvf data.tar data/
data/
data/mysql/
data/mysql/db.frm
....
[root@rhel72 data]# rm -rf *
[root@rhel72 data]# pwd
/usr/local/mysql/data
--開始恢復
[root@rhel72 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /backup/16-34.dbname
180410 16:36:18 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
xtrabackup: cd to /backup/16-34.dbname/
xtrabackup: This target seems to be not prepared yet.
.....
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1824296
180410 16:36:29 completed OK!
[root@rhel72 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --copy-back /backup/16-34.dbname
180410 16:37:08 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.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
180410 16:37:08 [01] Copying ib_logfile0 to /usr/local/mysql/data/ib_logfile0
......
--調整許可權，開啟資料庫
[root@rhel72 data]# chown -R mysql:mysql ../data
[root@rhel72 data]# /usr/local/mysql/bin/mysqld_safe
180410 16:38:34 mysqld_safe Logging to '/usr/local/mysql/mysqld.log'.
180410 16:38:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
--驗證資料
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gaoyc |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.07 sec)
mysql> use gaoyc
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> show tables;
+-----------------+
| Tables_in_gaoyc |
+-----------------+
| gyc |
| gyc02 |
+-----------------+
2 rows in set (0.00 sec)
mysql> select count(*) from gyc;
+----------+
| count(*) |
+----------+
| 192 |
+----------+
1 row in set (0.08 sec)
mysql> select count(*) from gyc02;
+----------+
| count(*) |
+----------+
| 96 |
+----------+
1 row in set (0.05 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24585765/viewspace-2153015/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- innobackupex備份mysql大資料(全量+增量）操作記錄MySql大資料
- mysql 資料庫 備份MySql資料庫
- 利用innobackupex備份集恢復指定庫
- innobackupex遠端備份
- mysql資料庫備份命令大全MySql資料庫
- 備份mysql資料庫報告MySql資料庫
- 1. 備份MySQL資料庫MySql資料庫
- MySQL資料庫的基本備份MySql資料庫
- Mysql資料庫自動備份MySql資料庫
- 定時備份MySQL資料庫MySql資料庫
- innobackupex全備份流程圖流程圖
- (7) MySQL資料庫備份詳解MySql資料庫
- golang實現mysql資料庫備份GolangMySql資料庫
- Mysql資料庫備份及恢復MySql資料庫
- MySQL資料庫常用的備份方法MySql資料庫
- 「MySQL」資料庫備份和還原MySql資料庫
- MySQL資料備份MySql
- MySQL資料庫備份的shell指令碼MySql資料庫指令碼
- MySql資料庫備份的幾種方式MySql資料庫
- 使用MySQL Workbench進行資料庫備份MySql資料庫
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- 資料庫備份資料庫
- linux實現mysql資料庫每天自動備份定時備份LinuxMySql資料庫
- 初探MySQL資料備份及備份原理MySql
- innobackupex 部分表備份和恢復
- 實現MySQL資料庫的實時備份MySql資料庫
- 使用Mysqldump備份和恢復MySQL資料庫MySql資料庫
- MySQL-19.資料庫備份與恢復MySql資料庫
- mysql資料庫-備份與還原實操MySql資料庫
- java mysql 資料庫備份和還原操作JavaMySql資料庫
- Liunx備份mysql資料庫的shell指令碼MySql資料庫指令碼
- Mysql資料安全備份MySql
- 資料庫備份策略資料庫
- MongoDB資料庫備份MongoDB資料庫
- Centos-Mysql複製備份還原資料庫CentOSMySql資料庫
- crontab 備份docker mysql映象的資料庫不生效DockerMySql資料庫
- 【乾貨】MySQL 資料庫定時備份總結MySql資料庫
- MySQL 資料庫定時備份的幾種方式MySql資料庫