Top

NSD RDBMS1 DAY06

  1. 案例1：数据完全备份与恢复
  2. 案例2：恢复单张表
  3. 案例3：增量备份与恢复

1 案例1：数据完全备份与恢复

1.1 问题

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：安装XtraBackup软件包

1）安装软件

textpop-up
  1. [root@host50 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
  2. [root@host50 ~]# yum -y  install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
  3. 警告：percona-xtrabackup-24-2.4.6-2.el7.x86_64.rpm: 头V4 DSA/SHA1 Signature, 密钥 ID cd2efd2a: NOKEY
  4. 准备中...                          ################################# [100%]
  5. 正在升级/安装...
  6.    1:percona-xtrabackup-24-2.4.6-2.el7################################# [ 33%]
  7.    2:percona-xtrabackup-test-24-2.4.6-################################# [ 67%]
  8.    3:percona-xtrabackup-24-debuginfo-2################################# [100%]

2）确认安装的主要程序/脚本

  1. [root@host50 ~]# rpm -qa  | grep -i percona
  2. percona-xtrabackup-24-2.4.7-1.el7.x86_64
  3. [root@host50 ~]# rpm -ql percona-xtrabackup-24
  4. /usr/bin/innobackupex
  5. /usr/bin/xbcloud
  6. /usr/bin/xbcloud_osenv
  7. /usr/bin/xbcrypt
  8. /usr/bin/xbstream
  9. /usr/bin/xtrabackup
  10. /usr/share/doc/percona-xtrabackup-24-2.4.7
  11. /usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING
  12. /usr/share/man/man1/innobackupex.1.gz
  13. /usr/share/man/man1/xbcrypt.1.gz
  14. /usr/share/man/man1/xbstream.1.gz
  15. /usr/share/man/man1/xtrabackup.1.gz
  16. [root@host50 ~]#
  17.  
  18. [root@host50 ~]# innobackupex --help  //查看简单帮助
  19. [root@host50 ~]#
  20. [root@host50 ~]# man  innobackupex //查看详细帮助
  21. /usr/share/man/man1/xtrabackup.1.gz

步骤二：备份所有数据到/allbak目录下

1）备份所有数据

  1. [root@host50 ~]# innobackupex --user root --password 123456 /allbak --no-timestamp //执行备份命令
  2. 170425 11:05:44 innobackupex: Starting the backup operation
  3.  
  4. IMPORTANT: Please check that the backup run completes successfully.
  5.            At the end of a successful backup run innobackupex
  6.            prints "completed OK!".
  7. Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
  8. 170425 11:05:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
  9. Using server version 5.7.17
  10. innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
  11. xtrabackup: uses posix_fadvise().
  12. xtrabackup: cd to /var/lib/mysql
  13. xtrabackup: open files limit requested 0, set to 1024
  14. xtrabackup: using the following InnoDB configuration:
  15. xtrabackup:   innodb_data_home_dir = .
  16. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  17. xtrabackup:   innodb_log_group_home_dir = 
  18. xtrabackup:   innodb_log_files_in_group = 2
  19. xtrabackup:   innodb_log_file_size = 50331648
  20. InnoDB: Number of pools: 1
  21. 170425 11:05:45 >> log scanned up to (2543893)
  22. xtrabackup: Generating a list of tablespaces
  23. InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
  24. 170425 11:05:45 [01] Copying ./ibdata1 to /backup/ibdata1
  25. 170425 11:05:45 [01]        ...done
  26. 170425 11:05:46 [01] Copying ./mysql/plugin.ibd to /backup/mysql/plugin.ibd
  27. 170425 11:05:46 [01]        ...done
  28. 170425 11:05:46 [01] Copying ./mysql/servers.ibd to /backup/mysql/servers.ibd
  29. 170425 11:05:46 [01]        ...done
  30. 170425 11:05:46 [01] Copying ./mysql/help_topic.ibd to /backup/mysql/help_topic.ibd
  31. 170425 11:05:46 [01]        ...done
  32. 170425 11:05:46 >> log scanned up to (2543893)
  33. .. ..
  34. 170425 11:06:00 [01] Copying ./sys/x@0024waits_global_by_latency.frm to /backup/sys/x@0024waits_global_by_latency.frm
  35. 170425 11:06:00 [01]        ...done
  36. 170425 11:06:00 [01] Copying ./sys/session_ssl_status.frm to /backup/sys/session_ssl_status.frm
  37. 170425 11:06:00 [01]        ...done
  38. 170425 11:06:00 [01] Copying ./db1/db.opt to /backup/db1/db.opt
  39. 170425 11:06:00 [01]        ...done
  40. 170425 11:06:00 [01] Copying ./db1/tb1.frm to /backup/db1/tb1.frm
  41. 170425 11:06:00 [01]        ...done
  42. 170425 11:06:00 Finished backing up non-InnoDB tables and files
  43. 170425 11:06:00 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
  44. xtrabackup: The latest check point (for incremental): '2543884'
  45. xtrabackup: Stopping log copying thread.
  46. .170425 11:06:00 >> log scanned up to (2543893)
  47.  
  48. 170425 11:06:00 Executing UNLOCK TABLES
  49. 170425 11:06:00 All tables unlocked
  50. 170425 11:06:00 [00] Copying ib_buffer_pool to /backup/ib_buffer_pool
  51. 170425 11:06:00 [00]        ...done
  52. 170425 11:06:00 Backup created in directory '/backup/'
  53. 170425 11:06:00 [00] Writing backup-my.cnf
  54. 170425 11:06:00 [00]        ...done
  55. 170425 11:06:00 [00] Writing xtrabackup_info
  56. 170425 11:06:00 [00]        ...done
  57. xtrabackup: Transaction log of lsn (2543884) to (2543893) was copied.
  58. 170425 11:06:01 completed OK

2) 确认备份好的文件数据：

  1. [root@host50 ~]# ls /allbak
  2. backup-my.cnf  ib_buffer_pool  mysql      sys                   xtrabackup_info
  3. db1  ibdata1      performance_schema  xtrabackup_checkpoints  xtrabackup_logfile

3）把备份文件传递给 目标服务器51

  1. [root@host50 ~]#
  2. [root@host50 ~]# scp -r /allbak root@192.168.4.51:/root/
  3. [root@host50 ~]#

步骤三：在51主机，使用备份文件恢复数据

1）安装软件包，提供恢复命令

  1. [root@host51 ~]# rpm -ivh  libev-4.15-1.el6.rf.x86_64.rpm
  2. [root@host51 ~]# yum -y  install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

2）恢复数据

  1.  
  2.     
  3. [root@host51 ~]# systemctl  stop mysqld
  4. [root@host51 ~]# ls /var/lib/mysql
  5. [root@host51 ~]# rm -rf /var/lib/mysql/* //清空数据
  6.  
  7. [root@host51 ~]#innobackupex--apply-log  --redo-only /root/allbak //恢复数据
  8.  
  9. 170425 11:42:19 innobackupex: Starting the apply-log operation
  10.  
  11. IMPORTANT: Please check that the apply-log run completes successfully.
  12.            At the end of a successful apply-log run innobackupex
  13.            prints "completed OK!".
  14.  
  15. innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
  16. xtrabackup: cd to /backup/
  17. xtrabackup: This target seems to be already prepared.
  18. InnoDB: Number of pools: 1
  19. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
  20. xtrabackup: using the following InnoDB configuration for recovery:
  21. xtrabackup:   innodb_data_home_dir = .
  22. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  23. xtrabackup:   innodb_log_group_home_dir = .
  24. xtrabackup:   innodb_log_files_in_group = 2
  25. xtrabackup:   innodb_log_file_size = 50331648
  26. xtrabackup: using the following InnoDB configuration for recovery:
  27. xtrabackup:   innodb_data_home_dir = .
  28. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  29. xtrabackup:   innodb_log_group_home_dir = .
  30. xtrabackup:   innodb_log_files_in_group = 2
  31. xtrabackup:   innodb_log_file_size = 50331648
  32. xtrabackup: Starting InnoDB instance for recovery.
  33. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
  34. InnoDB: PUNCH HOLE support available
  35. InnoDB: Mutexes and rw_locks use GCC atomic builtins
  36. InnoDB: Uses event mutexes
  37. InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
  38. InnoDB: Compressed tables use zlib 1.2.7
  39. InnoDB: Number of pools: 1
  40. InnoDB: Not using CPU crc32 instructions
  41. InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
  42. InnoDB: Completed initialization of buffer pool
  43. InnoDB: page_cleaner coordinator priority: -20
  44. InnoDB: Highest supported file format is Barracuda.
  45.  
  46. xtrabackup: starting shutdown with innodb_fast_shutdown = 1
  47. InnoDB: Starting shutdown...
  48. InnoDB: Shutdown completed; log sequence number 2544177
  49. InnoDB: Number of pools: 1
  50. 170425 11:42:20 completed OK!
  51.  
  52. [root@host51 ~]#
  53. [root@host51 ~]#  innobackupex --copy-back /root/allbak //拷贝数据
  54. 170425 11:42:55 innobackupex: Starting the apply-log operation
  55. IMPORTANT: Please check that the apply-log run completes successfully.
  56.            At the end of a successful apply-log run innobackupex
  57.            prints "completed OK!".
  58. innobackupex version 2.4.6 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 8ec05b7)
  59. incremental backup from 2543884 is enabled.
  60. xtrabackup: cd to /backup/
  61. xtrabackup: This target seems to be already prepared with --apply-log-only.
  62. InnoDB: Number of pools: 1
  63. xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(2549924)
  64. xtrabackup: using the following InnoDB configuration for recovery:
  65. xtrabackup:   innodb_data_home_dir = .
  66. xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
  67. xtrabackup:   innodb_log_group_home_dir = /incr01/
  68. xtrabackup:   innodb_log_files_in_group = 1
  69. xtrabackup:   innodb_log_file_size = 8388608
  70. xtrabackup: Generating a list of tablespaces
  71. InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
  72. xtrabackup: page size for /incr01//ibdata1.delta is 16384 bytes
  73. Applying /incr01//ibdata1.delta to ibdata1...
  74. ... ...
  75. 170425 11:43:09 [01] Copying /incr01/performance_schema/global_status.frm to performance_schema/global_status.frm
  76. 170425 11:43:09 [01]        ...done
  77. 170425 11:43:09 [01] Copying /incr01/performance_schema/session_status.frm to performance_schema/session_status.frm
  78. 170425 11:43:09 [01]        ...done
  79. 170425 11:43:09 [00] Copying /incr01//xtrabackup_info to xtrabackup_info
  80. 170425 11:43:09 [00]        ...done
  81. 170425 11:43:10 completed OK!
  82.  
  83. [root@host50 ~]# chown  -R mysql:mysql /var/lib/mysql //修改所有者与组

步骤四：验证数据恢复

1）启动服务

  1. [root@host51 ~]# systemctl  start mysqld
  2.  
  3. [root@host51 ~]# mysql -uroot -p123456
  4. mysql> show databases;
  5. mysql> select * from db3.user2;
  6. mysql>select count(*) from db3.user;
  7. mysql>

2）查看数据

  1. [root@host51 ~]# mysql -uroot -p123456
  2. mysql> show databases;
  3. mysql> select * from db3.user2;
  4. mysql> select count(*) from db3.user;

2 案例2：恢复单张表

2.1 问题

2.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：具体操作如下

1）执行删除数据命令

  1. [root@host50 ~]# mysql –uroot  -p123456
  2. mysql> delete from db3.user2; //误删除数据操作   
  3. mysql>

2) 删除表空间

  1. mysql> alter table db3.user2 discard  tablespace;

3) 导出表信息

  1. [root@host50 ~ ]# innobackupex --apply-log --export  /allbak

4) 拷贝表信息文件到数据库目录下

  1.  [root@host50 ~]# cp /allbak/db3/user2.{cfg,exp,ibd} /var/lib/mysql/db3/

5) 修改表信息文件的所有者及组用户为mysql

  1. [root@host50 ~]# chown mysql:mysql /var/lib/mysql/db3/user2.*

6) 导入表空间

  1. mysql> alter  table db3.user2   import  tablespace;

7) 删除数据库目录下的表信息文件

  1. [root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.cfg
  2. [root@host50 ~]# rm -rf /var/lib/mysql/db3/user2.exp

8) 查看表记录

  1. mysql> select  * from db3.user2;

3 案例3：增量备份与恢复

3.1 问题

3.2 步骤

实现此案例需要按照如下步骤进行。

步骤一：备份所有数据,在50主机执行

1）完全备份 （备份所有数据到/fullbak目录）

  1. [root@host50 ~]# innobackupex --user root --password 123456  /fullbak --no-timestamp

步骤二：增量备份 （每次执行备份，值备份新数据,在50主机执行）

1) 插入新记录，并做增量备份

  1. mysql> insert into db3.user2 values(5,"jack");// 插入新记录,多写几条
  2. [root@host50 ~]# innobackupex --user root --password 123456 --incremental /new1dir --incremental-basedir=/fullbak  --no-timestamp //第1次增量备份 ，数据存储目录/new1dir

2) 插入新记录，并做增量备份

mysql> insert into db3.user2 values(6,"jack");// 插入新记录,多写几条

[root@host50 ~]# innobackupex --user root --password 123456 --incremental /new2dir --incremental-basedir=/newdir1 --no-timestamp //第2次增量备份 ，数据存储目录/new2dir

3) 把备份文件拷贝给目标主机51

  1. [root@host50 ~]# scp -r /fullbak  root@192.168.4.51:/root/
  2. [root@host50 ~]# scp -r /new1dir/  root@192.168.4.51:/root/
  3. [root@host50 ~]# scp -r /new2dir/  root@192.168.4.51:/root/

步骤三：在主机51 恢复数据

1) 停止服务，并清空数据

  1. [root@host51 ~]# systemctl  stop  mysqld
  2. [root@host51 ~]# rm -rf /var/lib/mysql/*

2) 合并日志

  1. [root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak //准备恢复数据
  2. [root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new1dir  //合并日志
  3. [root@host51 ~ ]# innobackupex --apply-log --redo-only /root/fullbak --incremental-dir=/root/new2dir //合并日志
  4. [root@host51 ~ ]# rm -rf  /root/new2dir  //恢复后，可以删除了
  5. [root@host51 ~ ]# rm -rf  /root/new1dir  //恢复后，可以删除了

3) 恢复数据

  1. [root@host51 ~ ]# innobackupex --copy-back /root/fullbak   //拷贝文件到数据库目录下
  2. [root@host51 ~ ]# chown  -R mysql:mysql /var/lib/mysql //修改所有者与组用户
  3. [root@host51 ~ ]# systemctl  start mysqld //启动服务
  4. [root@host51 ~ ]# mysql -uroot -p123456 //登录
  5. mysql> select  count(*)  from db3.user; //查看数据