mysql 5.1 选哪个 xtrabackup
答案:1 悬赏:0 手机版
解决时间 2021-01-15 15:43
- 提问者网友:像風在裏
- 2021-01-14 21:26
mysql 5.1 选哪个 xtrabackup
最佳答案
- 五星知识达人网友:北城痞子
- 2021-01-14 22:06
生产环境究竟是使用mysqldump还是xtrabackup来备份与恢复数据库
root@client2:/var/lib/my;total77860;drwx------8mysqlmysql409;drwxr-xr-x38rootroot4096;-rw-r--r--1rootroot0Jan5;drwx------2mysqlmysql409;-rw-rw----1mysqlmysql692;-rw-rw----1mysqlmys
root@client2:/var/lib/mysql# ll
total 77860
drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:03 test/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后启动mysql,并查看test数据库的表里内容
root@client2:/var/lib/mysql# service mysql start
mysql start/running, process 12730
root@client2:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)
Copyright (c) 2000, 2012, 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 test;
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 test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
+------+
5 rows in set (0.01 sec)
可以看到数据库已经恢复完成
可能大家有个疑问,为什么我这里不像很多网上的文章里是在apply-log后,使用copy-back如果使用/usr/bin/innobackupex --copy-back命令后,会报Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网,这是xtrabackup的一个BUG。
innobackupex–copy-back was run. With this bug fix, innobackupex–copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev) will now error out of the did not check that MySQL datadir was empty before
所以在apply-log后直接复制数据目录到数据库的位置上吧。
三、对数据库的增量备份与恢复
为了进行增量备份,先对数据库添加一些数据
mysql> insert into test values(11);
Query OK, 1 row affected (0.10 sec)
mysql> insert into test values(12);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test values(13);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(14);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(15);
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 11 |
| 13 |
| 14 |
| 15 |
+------+
10 rows in set (0.00 sec)
然后进行增量的备份
root@client2:/var/lib/mysql# innobackupex --user=root --password=123456 --database=test --incremental --incremental-basedir=/tmp/restore/ /tmp/data
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
130307 21:13:38 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
130307 21:13:38 innobackupex: Connected to database with mysql child process (pid=12864) 130307 21:13:44 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44
130307 21:13:44 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
130307 21:13:44 innobackupex: Connected to database with mysql child process (pid=12891) 130307 21:13:46 innobackupex: Connection to database server closed
130307 21:13:46 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/tmp/data/2013-03-07_21-13-44 --incremental-basedir='/tmp/restore/'
innobackupex: Waiting for ibbackup (pid=12898) to suspend
innobackupex: Suspend file '/tmp/data/2013-03-07_21-13-44/xtrabackup_suspended'
root@client2:/var/lib/my;total77860;drwx------8mysqlmysql409;drwxr-xr-x38rootroot4096;-rw-r--r--1rootroot0Jan5;drwx------2mysqlmysql409;-rw-rw----1mysqlmysql692;-rw-rw----1mysqlmys
root@client2:/var/lib/mysql# ll
total 77860
drwx------ 8 mysql mysql 4096 Mar 7 21:06 ./
drwxr-xr-x 38 root root 4096 Mar 7 19:52 ../
-rw-r--r-- 1 root root 0 Jan 5 14:22 debian-5.5.flag
drwx------ 2 mysql mysql 4096 Feb 11 17:39 django/
-rw-rw---- 1 mysql mysql 69206016 Mar 7 21:02 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:02 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Mar 7 21:01 ib_logfile1
drwx------ 2 mysql mysql 4096 Jan 5 22:55 monitor/
drwx------ 2 mysql root 4096 Jan 5 14:22 mysql/
-rw-rw---- 1 root root 6 Jan 5 14:22 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 Jan 5 14:22 performance_schema/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 21:03 test/
drwxr-xr-x 2 mysql mysql 4096 Mar 7 19:58 xtrbackup/
然后启动mysql,并查看test数据库的表里内容
root@client2:/var/lib/mysql# service mysql start
mysql start/running, process 12730
root@client2:/var/lib/mysql# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.28-0ubuntu0.12.04.3-log (Ubuntu)
Copyright (c) 2000, 2012, 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 test;
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 test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 5 |
+------+
5 rows in set (0.01 sec)
可以看到数据库已经恢复完成
可能大家有个疑问,为什么我这里不像很多网上的文章里是在apply-log后,使用copy-back如果使用/usr/bin/innobackupex --copy-back命令后,会报Original data directory is not empty! at /usr/local/xtrabackup/bin/innobackupex line 538.恢复的目录必须为空。经查官网,这是xtrabackup的一个BUG。
innobackupex–copy-back was run. With this bug fix, innobackupex–copy-back operation if the destination is not empty, avoiding potential data loss or a strang combination of a restored backup and previous data. Bug Fixed: #737569 (Valentine Gostev) will now error out of the did not check that MySQL datadir was empty before
所以在apply-log后直接复制数据目录到数据库的位置上吧。
三、对数据库的增量备份与恢复
为了进行增量备份,先对数据库添加一些数据
mysql> insert into test values(11);
Query OK, 1 row affected (0.10 sec)
mysql> insert into test values(12);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test values(13);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(14);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(15);
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 11 |
| 13 |
| 14 |
| 15 |
+------+
10 rows in set (0.00 sec)
然后进行增量的备份
root@client2:/var/lib/mysql# innobackupex --user=root --password=123456 --database=test --incremental --incremental-basedir=/tmp/restore/ /tmp/data
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
130307 21:13:38 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
130307 21:13:38 innobackupex: Connected to database with mysql child process (pid=12864) 130307 21:13:44 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
innobackupex: Using mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (x86_64) using readline 6.2
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
innobackupex: Created backup directory /tmp/data/2013-03-07_21-13-44
130307 21:13:44 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
130307 21:13:44 innobackupex: Connected to database with mysql child process (pid=12891) 130307 21:13:46 innobackupex: Connection to database server closed
130307 21:13:46 innobackupex: Starting ibbackup with command: xtrabackup_55 --backup --suspend-at-end --target-dir=/tmp/data/2013-03-07_21-13-44 --incremental-basedir='/tmp/restore/'
innobackupex: Waiting for ibbackup (pid=12898) to suspend
innobackupex: Suspend file '/tmp/data/2013-03-07_21-13-44/xtrabackup_suspended'
我要举报
如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯