- 浏览: 981023 次
文章分类
- 全部博客 (428)
- Hadoop (2)
- HBase (1)
- ELK (1)
- ActiveMQ (13)
- Kafka (5)
- Redis (14)
- Dubbo (1)
- Memcached (5)
- Netty (56)
- Mina (34)
- NIO (51)
- JUC (53)
- Spring (13)
- Mybatis (17)
- MySQL (21)
- JDBC (12)
- C3P0 (5)
- Tomcat (13)
- SLF4J-log4j (9)
- P6Spy (4)
- Quartz (12)
- Zabbix (7)
- JAVA (9)
- Linux (15)
- HTML (9)
- Lucene (0)
- JS (2)
- WebService (1)
- Maven (4)
- Oracle&MSSQL (14)
- iText (11)
- Development Tools (8)
- UTILS (4)
- LIFE (8)
最新评论
-
Donald_Draper:
Donald_Draper 写道刘落落cici 写道能给我发一 ...
DatagramChannelImpl 解析三(多播) -
Donald_Draper:
刘落落cici 写道能给我发一份这个类的源码吗Datagram ...
DatagramChannelImpl 解析三(多播) -
lyfyouyun:
请问楼主,执行消息发送的时候,报错:Transport sch ...
ActiveMQ连接工厂、连接详解 -
ezlhq:
关于 PollArrayWrapper 状态含义猜测:参考 S ...
WindowsSelectorImpl解析一(FdMap,PollArrayWrapper) -
flyfeifei66:
打算使用xmemcache作为memcache的客户端,由于x ...
Memcached分布式客户端(Xmemcached)
安装:http://donald-draper.iteye.com/admin/blogs/2314832
全量备份:http://donald-draper.iteye.com/admin/blogs/2315812
准备:
创建表
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('1', 'jack', '23');
用到的命令顺序
增量备份:
innobackupex --user=donald --password=123456 /home/test/base --no-timestamp --parallel=4
innobackupex --user=donald --password=123456 --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4
innobackupex --user=donald --password=123456 --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
备份恢复:
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions,回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base
准备恢复:
innobackupex --copy-back --rsync /home/test/base
下面是实验:
修改mysql数据目录权限:
[donald@test home]$ ls -al
total 16
drwxr-xr-x. 8 root root 96 Aug 5 16:21 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 mysql mysql 4096 Aug 5 16:27 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 4 donald user 30 Aug 5 15:00 test
[donald@test home]$ sudo chmod -R 777 mysqldata
[sudo] password for donald:
[donald@test home]$ ls -al
total 16
drwxr-xr-x. 8 root root 96 Aug 5 16:21 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxrwxrwx 5 mysql mysql 4096 Aug 5 16:27 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 4 donald user 30 Aug 5 15:00 test
全量备份:
[donald@test test]$ innobackupex --user=donald --password=123456 /home/test/base --no-timestamp --parallel=4
....
xtrabackup: Creating suspend file '/home/test/base/xtrabackup_log_copied' with pid '15191'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 16:59:19 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/test/base'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 16:59:19 innobackupex: Connection to database server closed
160805 16:59:19 innobackupex: completed OK!
[donald@test test]$ cd base
[donald@test base]$ ls
backup-my.cnf ibdata1 mysql performance_schema test xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
[donald@test base]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0
增量备份1:
插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('2', 'mark', '24');
[donald@test base]$ innobackupex --user=donald --password=123456 --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194631350'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194631350)
xtrabackup: Creating suspend file '/home/test/base1/xtrabackup_log_copied' with pid '15639'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 17:03:55 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/test/base1'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 17:03:55 innobackupex: Connection to database server closed
160805 17:03:55 innobackupex: completed OK!
[donald@test base]$ cd ..
[donald@test test]$ cd base1
[donald@test base1]$ ls
backup-my.cnf ibdata1.meta performance_schema xtrabackup_binlog_info xtrabackup_info
ibdata1.delta mysql test xtrabackup_checkpoints xtrabackup_logfile
[donald@test base1]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0
增量备份2:
插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('3', 'doanld', '25');
[donald@test test]$ innobackupex --user=donald --password=123456 --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194634030'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194634030)
xtrabackup: Creating suspend file '/home/test/base2/xtrabackup_log_copied' with pid '15848'
xtrabackup: Transaction log of lsn (13194634030) to (13194634030) was copied.
160805 17:07:54 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/test/base2'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 624
160805 17:07:54 innobackupex: Connection to database server closed
160805 17:07:54 innobackupex: completed OK!
[donald@test test]$ cd base2
[donald@test base2]$ ls
backup-my.cnf ibdata1.meta performance_schema xtrabackup_binlog_info xtrabackup_info
ibdata1.delta mysql test xtrabackup_checkpoints xtrabackup_logfile
[donald@test base2]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194634030
last_lsn = 13194634030
compact = 0
备份恢复:
[donald@test test]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
.....
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1371, file name mysql-bin.000019
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194634923
160805 17:16:37 innobackupex: completed OK!
增量恢复1:
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
...
innobackupex: Copying '/home/test/base1/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base1/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:44:29 innobackupex: completed OK!
增量恢复2:
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
...
innobackupex: Copying '/home/test/base2/performance_schema/events_statements_summary_by_digest.frm' to '/home/test/base/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:46:40 innobackupex: completed OK!
回滚事物
[donald@test Desktop]$ innobackupex --apply-log --use-memory=100M /home/test/base
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194640304
160805 17:48:19 innobackupex: completed OK!
转移mysql数据文件:
在执行此步前删除role表中的数据
[donald@test Desktop]$ cd /home/
[donald@test home]$ ls
donald= mysqldata mysqldata1 mysqldata2 mysqllog test
[donald@test home]$ sudo service msyql stop
[sudo] password for donald:
Redirecting to /bin/systemctl stop msyql.service
Failed to stop msyql.service: Unit msyql.service not loaded.
[donald@test home]$ sudo service mysql stop
Shutting down MySQL.... SUCCESS!
[donald@test home]$ mv mysqldata mysqldata3
mv: cannot move ‘mysqldata’ to ‘mysqldata3’: Permission denied
[donald@test home]$ sudo mv mysqldata mysqldata3
[donald@test home]$ ls -al
total 16
drwxr-xr-x. 8 root root 97 Aug 5 17:50 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxrwx 5 mysql mysql 4096 Aug 5 17:50 mysqldata3
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 6 donald
增量全备恢复:
[donald@test home]$ sudo innobackupex --copy-back --rsync /home/test/base
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
........
innobackupex: back to '/home/mysqldata'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/test/base'
innobackupex: back to original InnoDB log directory '/home/mysqldata'
innobackupex: Copying '/home/test/base/ib_logfile1' to '/home/mysqldata/ib_logfile1'
innobackupex: Copying '/home/test/base/ib_logfile0' to '/home/mysqldata/ib_logfile0'
innobackupex: Finished copying back files.
160805 17:53:11 innobackupex: completed OK!
恢复mysql数据文件夹权限:
[donald@test home]$ ls -al
total 24
drwxr-xr-x. 9 root root 4096 Aug 5 17:52 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 root root 4096 Aug 5 17:53 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxrwx 5 mysql mysql 4096 Aug 5 17:50 mysqldata3
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 6 donald user 54 Aug 5 17:07 test
[donald@test home]$ sudo chown -R mysql:mysql mysqldata
[donald@test home]$ ls -al
total 24
drwxr-xr-x. 9 root root 4096 Aug 5 17:52 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 mysql mysql 4096 Aug 5 17:53 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxrwx 5 mysql mysql 4096 Aug 5 17:50 mysqldata3
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 6 donald user 54 Aug 5 17:07 test
[donald@test home]$ sudo service mysql start
Starting MySQL.. SUCCESS!
查看数据库数据:
mysql> use test;
Database changed
mysql> select * from role;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | jack | 23 |
| 2 | mark | 24 |
| 3 | doanld | 25 |
+----+--------+-----+
3 rows in set
总结:
注意msyql文件夹权限
注意备份恢复的第一步 the procedure is very straight-forward using the --redo-only option, starting with the base backup:添加 --redo-only选项
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
别忘了倒数第二步:
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions,回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base
全量备份:http://donald-draper.iteye.com/admin/blogs/2315812
准备:
创建表
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('1', 'jack', '23');
用到的命令顺序
增量备份:
innobackupex --user=donald --password=123456 /home/test/base --no-timestamp --parallel=4
innobackupex --user=donald --password=123456 --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4
innobackupex --user=donald --password=123456 --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
备份恢复:
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions,回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base
准备恢复:
innobackupex --copy-back --rsync /home/test/base
下面是实验:
修改mysql数据目录权限:
[donald@test home]$ ls -al
total 16
drwxr-xr-x. 8 root root 96 Aug 5 16:21 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 mysql mysql 4096 Aug 5 16:27 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 4 donald user 30 Aug 5 15:00 test
[donald@test home]$ sudo chmod -R 777 mysqldata
[sudo] password for donald:
[donald@test home]$ ls -al
total 16
drwxr-xr-x. 8 root root 96 Aug 5 16:21 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxrwxrwx 5 mysql mysql 4096 Aug 5 16:27 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 4 donald user 30 Aug 5 15:00 test
全量备份:
[donald@test test]$ innobackupex --user=donald --password=123456 /home/test/base --no-timestamp --parallel=4
....
xtrabackup: Creating suspend file '/home/test/base/xtrabackup_log_copied' with pid '15191'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 16:59:19 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/test/base'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 16:59:19 innobackupex: Connection to database server closed
160805 16:59:19 innobackupex: completed OK!
[donald@test test]$ cd base
[donald@test base]$ ls
backup-my.cnf ibdata1 mysql performance_schema test xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
[donald@test base]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0
增量备份1:
插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('2', 'mark', '24');
[donald@test base]$ innobackupex --user=donald --password=123456 --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194631350'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194631350)
xtrabackup: Creating suspend file '/home/test/base1/xtrabackup_log_copied' with pid '15639'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 17:03:55 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/test/base1'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 17:03:55 innobackupex: Connection to database server closed
160805 17:03:55 innobackupex: completed OK!
[donald@test base]$ cd ..
[donald@test test]$ cd base1
[donald@test base1]$ ls
backup-my.cnf ibdata1.meta performance_schema xtrabackup_binlog_info xtrabackup_info
ibdata1.delta mysql test xtrabackup_checkpoints xtrabackup_logfile
[donald@test base1]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0
增量备份2:
插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('3', 'doanld', '25');
[donald@test test]$ innobackupex --user=donald --password=123456 --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194634030'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194634030)
xtrabackup: Creating suspend file '/home/test/base2/xtrabackup_log_copied' with pid '15848'
xtrabackup: Transaction log of lsn (13194634030) to (13194634030) was copied.
160805 17:07:54 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/home/test/base2'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 624
160805 17:07:54 innobackupex: Connection to database server closed
160805 17:07:54 innobackupex: completed OK!
[donald@test test]$ cd base2
[donald@test base2]$ ls
backup-my.cnf ibdata1.meta performance_schema xtrabackup_binlog_info xtrabackup_info
ibdata1.delta mysql test xtrabackup_checkpoints xtrabackup_logfile
[donald@test base2]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194634030
last_lsn = 13194634030
compact = 0
备份恢复:
[donald@test test]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
.....
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1371, file name mysql-bin.000019
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194634923
160805 17:16:37 innobackupex: completed OK!
增量恢复1:
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
...
innobackupex: Copying '/home/test/base1/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base1/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:44:29 innobackupex: completed OK!
增量恢复2:
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
...
innobackupex: Copying '/home/test/base2/performance_schema/events_statements_summary_by_digest.frm' to '/home/test/base/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:46:40 innobackupex: completed OK!
回滚事物
[donald@test Desktop]$ innobackupex --apply-log --use-memory=100M /home/test/base
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194640304
160805 17:48:19 innobackupex: completed OK!
转移mysql数据文件:
在执行此步前删除role表中的数据
[donald@test Desktop]$ cd /home/
[donald@test home]$ ls
donald= mysqldata mysqldata1 mysqldata2 mysqllog test
[donald@test home]$ sudo service msyql stop
[sudo] password for donald:
Redirecting to /bin/systemctl stop msyql.service
Failed to stop msyql.service: Unit msyql.service not loaded.
[donald@test home]$ sudo service mysql stop
Shutting down MySQL.... SUCCESS!
[donald@test home]$ mv mysqldata mysqldata3
mv: cannot move ‘mysqldata’ to ‘mysqldata3’: Permission denied
[donald@test home]$ sudo mv mysqldata mysqldata3
[donald@test home]$ ls -al
total 16
drwxr-xr-x. 8 root root 97 Aug 5 17:50 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxrwx 5 mysql mysql 4096 Aug 5 17:50 mysqldata3
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 6 donald
增量全备恢复:
[donald@test home]$ sudo innobackupex --copy-back --rsync /home/test/base
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.
........
innobackupex: back to '/home/mysqldata'
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/test/base'
innobackupex: back to original InnoDB log directory '/home/mysqldata'
innobackupex: Copying '/home/test/base/ib_logfile1' to '/home/mysqldata/ib_logfile1'
innobackupex: Copying '/home/test/base/ib_logfile0' to '/home/mysqldata/ib_logfile0'
innobackupex: Finished copying back files.
160805 17:53:11 innobackupex: completed OK!
恢复mysql数据文件夹权限:
[donald@test home]$ ls -al
total 24
drwxr-xr-x. 9 root root 4096 Aug 5 17:52 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 root root 4096 Aug 5 17:53 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxrwx 5 mysql mysql 4096 Aug 5 17:50 mysqldata3
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 6 donald user 54 Aug 5 17:07 test
[donald@test home]$ sudo chown -R mysql:mysql mysqldata
[donald@test home]$ ls -al
total 24
drwxr-xr-x. 9 root root 4096 Aug 5 17:52 .
dr-xr-xr-x. 17 root root 4096 Jul 26 14:32 ..
drwx------. 18 donald user 4096 Aug 5 11:38 donald=
drwxr-xr-x 5 mysql mysql 4096 Aug 5 17:53 mysqldata
drwxr-xr-x 5 mysql mysql 119 Jul 26 15:51 mysqldata1
drwxrwxrwx. 5 mysql mysql 4096 Aug 5 16:19 mysqldata2
drwxrwxrwx 5 mysql mysql 4096 Aug 5 17:50 mysqldata3
drwxrwxr-x. 2 mysql mysql 64 Jul 26 16:05 mysqllog
drwxr-x--- 6 donald user 54 Aug 5 17:07 test
[donald@test home]$ sudo service mysql start
Starting MySQL.. SUCCESS!
查看数据库数据:
mysql> use test;
Database changed
mysql> select * from role;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | jack | 23 |
| 2 | mark | 24 |
| 3 | doanld | 25 |
+----+--------+-----+
3 rows in set
总结:
注意msyql文件夹权限
注意备份恢复的第一步 the procedure is very straight-forward using the --redo-only option, starting with the base backup:添加 --redo-only选项
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
别忘了倒数第二步:
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions,回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base
发表评论
-
Deadlock found when trying to get lock; try restarting transaction解决方式
2017-07-18 23:00 2062MySQL 事务的学习整理:http://blog.csdn. ... -
MySQL慢日志
2017-05-18 16:05 1034The Slow Query Log:https://dev. ... -
The table is full问题解决过程
2017-05-06 15:29 7601The table‘xxxx’is full 设置临时表大小 ... -
百万级数据-程序迁移后续
2017-04-13 18:09 1634百万级数据-程序迁移:http://donald-draper ... -
Msyql日期字符串转换
2017-04-01 14:13 542Date和String的互相转换:http://www.tui ... -
Mysql添加约束
2017-03-31 16:28 901MySQL中对三种约束的支持:http://leekai.me ... -
Mysql FEDERATED引擎
2016-11-29 15:51 605使用mysql federated引擎构建MySQL分布式数据 ... -
MySQL触发器
2016-11-24 19:04 716CHANGE MASTER:http://dev.mysql. ... -
Mysql主从配置
2016-11-11 18:31 5291、主从服务器分别作以下操作: 1)版本一致 2)初始 ... -
百万级数据-程序迁移
2016-09-29 19:03 2632JVM学习笔记:http://blog.csdn.net/cu ... -
Mysql 备份工具XtraBackup全量备份
2016-08-05 16:41 564Percona安装:http://donald-draper. ... -
Mysql 备份工具XtraBackup 安装
2016-08-05 16:28 943开源热备工具XtraBackup下载:https://www. ... -
sysbench基准测试
2016-08-01 17:45 786下载sysbench:http://dev.mysql.com ... -
mysql 事务处理
2016-07-29 16:07 510创建表: CREATE TABLE `role` ( ` ... -
mysql 全文索引
2016-07-28 11:03 614mysql大表查询的时候,'String%'模糊查询可以使用B ... -
MySQL 物理文件的迁移
2016-07-26 15:39 2343参考资料:http://www.cnblogs.com/adv ... -
centos7 安装mysql
2016-07-26 11:36 746下载MYSQL-RPM包:http://downloads.m ... -
mysql 大表添加索引注意事项
2016-07-25 16:01 2641LINXU top命令: http://www.c ... -
mysql 大表分页查询测试分析优化
2016-07-25 11:30 1502索引概念: http://blog.csdn.net/xlur ... -
MySQL事务
2016-06-01 10:49 614事务基础知识:http://my.oschina.net/je ...
相关推荐
总的来说,Xtrabackup作为一款强大的MySQL备份工具,以其高效的备份和恢复能力,以及对InnoDB引擎的优化,成为了许多企业和开发者的选择。通过合理配置和使用Xtrabackup,可以实现高效、安全的数据库备份,确保在...
MySQL 实时增量备份和 XtraBackup 备份技术详解 MySQL 是目前最流行的开源关系数据库管理系统之一,对于 MySQL 的备份和恢复机制至关重要。本文将详细介绍 MySQL 实时增量备份和 XtraBackup 备份技术,并结合实际...
`xtrabackup`是Percona公司开发的一款高效、无锁的MySQL和MariaDB热备份工具,它允许在不影响数据库服务的情况下进行全备和增量备份。本教程将详细介绍如何使用`xtrabackup`结合Shell脚本来实现MySQL数据库的备份与...
总的来说,Percona XtraBackup是MySQL备份解决方案的有力选择,特别是在需要高效、安全、无中断备份的业务环境中。通过熟练掌握和使用这款工具,可以有效地保护数据,提高系统的可用性和可靠性。
Percona XtraBackup是一个开源的热备份工具,可以在不锁定数据库的情况下进行增量备份。首先,你需要进行一次全备份,然后定期执行增量备份。以下是使用XtraBackup的示例: 1. 全备份: ```bash innobackupex --...
Xtrabackup是一个开源的物理备份工具,它能够创建数据库的增量或完整备份,并且在备份过程中不影响数据库的正常服务。它的优点在于能够进行在线备份,减少业务中断时间,同时支持快速恢复。 - **增量备份** 只备份...
Xtrabackup是一款开源的、针对InnoDB存储引擎的热备份工具,它能够在不锁定数据库的情况下进行完整备份。本文将详细探讨如何利用Xtrabackup结合Shell脚本来实现高效、灵活的备份与恢复操作。 一、Xtrabackup简介 ...
Xtrabackup是一款高效、开源的MySQL热备份工具,特别适用于InnoDB和XtraDB存储引擎。在这个主题中,我们将深入探讨Xtrabackup软件在MySQL备份中的应用及其重要特性。 首先,Xtrabackup由Percona公司开发,它允许在...
Xtrabackup是Percona公司开发的一款高效、免费的MySQL物理备份工具,尤其适用于MySQL 5.7及之前的版本。它以其对InnoDB存储引擎的出色支持和在备份过程中对数据库服务几乎零影响的特点,成为了数据库管理员的首选。 ...
### xtrabackup增量备份 `xtrabackup`是由Percona开发的一个开源工具,特别适用于InnoDB存储引擎的MySQL数据库,它提供了高效且无锁的热备份功能。全量备份后,xtrabackup可以进行增量备份,仅备份自上次备份以来...
这里我们关注的是“mysql热备份工具文档”,它主要涉及的是Percona Xtrabackup这款高效且非侵入式的MySQL热备份工具。Xtrabackup能够在线备份InnoDB和XtraDB存储引擎的数据库,甚至在数据库服务正在运行时也能完成,...
XtraBackup是一个用来备份 MySQL 的 InnoDB 数据库的开源工具。例如执行增量备份的命令是: # ./xtrabackup --backup --target-dir=/backup/delta --incremental-basedir=/backup/base
Xtrabackup是一款开源的、用于MySQL和Percona Server的热备份工具,它支持全量备份和增量备份,是实现这一目标的理想选择。 首先,我们要理解全量备份和增量备份的概念。全量备份是指备份所有数据库或数据文件,这...
本篇文章将详细讲解如何在Linux系统中设置一个自动的MySQL备份工具,以便实现数据的自动备份,降低因系统故障或人为错误导致的数据丢失风险。 首先,我们需要了解备份MySQL数据库的基本原理。MySQL提供了多种备份...
Percona XtraBackup是一款广泛使用的MySQL备份工具,它由Percona公司开发,是MySQL备份解决方案中的佼佼者。它支持全库备份、增量备份和在线(热)备份,能为DBA(数据库管理员)提供强大的备份和恢复功能。 首先,...
本测试中并未详细描述增量备份的过程,但提到全量及增量备份集均可用于恢复,说明增量备份也在恢复过程中起到了补充作用。 3. **恢复验证**:恢复后,通过检查数据库中的数据更新时间(如`mysql.innodb_table_stats...
Xtrabackup是一个开源的热备份工具,它可以对InnoDB和XtraDB存储引擎的MySQL数据库进行无锁备份。相比传统的冷备份,Xtrabackup能在不中断服务的情况下完成全量和增量备份,提高了备份效率,降低了业务影响。使用...
三、MySQL备份工具 1. mysqldump:MySQL官方提供的命令行工具,可以执行全量和增量备份。例如,执行全量备份的命令为: ``` mysqldump -u username -p password database_name > backup.sql ``` 2. Percona ...
原文链接:...2、星期二、星期三、星期五、星期六Mysql增量备份 3、根据指定日期实现Mysql数据物理恢复