时间：2021-07-01 10:21:17 帮助过：3人阅读
还原1
MariaDB [(none)]> source /tmp/hellodb-2017-02-21;
如下已完成数据库恢复至最初的数据；
MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec)
还原2
MariaDB [(none)]> source /tmp/bin.log
还原数据如下已还原至更改过得表信息：
MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 88 | tangceng | 88 | M | 3 | 7 | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec)
还原完成后开启MariaDB [(none)]> set @@session.sql_log_bin=ON;
安装percona-xtrabackup-2.3.2-1.el7.x86_64.rpm软件
创建备份目录并备份数据库
[root@localhost ~]# mkdir -p /data/backup[root@localhost ~]# innobackupex --user=root --host=localhost /data/backupxtrabackup: Transaction log of lsn (1756943) to (1756943) was copied.170221 14:01:41 completed OK! [root@localhost mysql]# ll /data/backup/2017-01-19_10-40-32/ total 118804-rw-r----- 1 root root 385 Jan 19 10:41 backup-my.cnfdrwx------ 2 root root 138 Jan 19 10:41 hellodb-rw-r----- 1 root root 18874368 Jan 19 10:43 ibdata1-rw-r--r-- 1 root root 50331648 Jan 19 10:43 ib_logfile0-rw-r--r-- 1 root root 50331648 Jan 19 10:43 ib_logfile1drwx------ 2 root root 4096 Jan 19 10:41 mysqldrwx------ 2 root root 4096 Jan 19 10:41 performance_schemadrwx------ 2 root root 19 Jan 19 10:41 test-rw-r----- 1 root root 113 Jan 19 10:43 xtrabackup_checkpoints-rw-r----- 1 root root 438 Jan 19 10:41 xtrabackup_info-rw-r----- 1 root root 2097152 Jan 19 10:43 xtrabackup_logfile[root@localhost mysql]# vim /etc/my.conf.d/server.conf innodb_log_file_size = 50331648
[root@localhost mysql]# innobackupex --apply-log /data/backup/2017-01-19_10-40-32/ [root@localhost mysql]# innobackupex --copy-back /data/backup/2017-01-19_10-40-32/ [root@localhost mysql]# chown -R mysql.mysql ./* [root@localhost mysql]# systemctl start mariadb
第一次备份
[root@localhost ~]# innobackupex --user=root --host=localhost /data/backup/
170119 14:50:49 [00] ...done xtrabackup: Transaction log of lsn (1680362) to (1680362) was copied. 170119 14:50:49 completed OK! [root@localhost ~]# ll /data/backup/ total 4drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46[root@localhost ~]# cat /data/backup/2017-01-19_14-50-46/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0to_lsn = 1680362last_lsn = 1680362compact = 0recover_binlog_info = 0[root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 27 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> show databases;+--------------------+| Database | +--------------------+| information_schema | | mysql | | performance_schema | | test | +--------------------+4 rows in set (0.12 sec) MariaDB [(none)]>
在数据库中增加hellodb数据库
[root@localhost ~]# mysql < hellodb.sql [root@localhost ~]# msyql-bash: msyql: command not found [root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 29Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+| Database | +--------------------+| information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+5 rows in set (0.00 sec) MariaDB [(none)]>
第一次增量备份
[root@localhost ~]# innobackupex --incremental --user=root --host=localhost /data/backup/ --incremental-basedir=/data/backup/2017-01-19_14-50-46/
[root@localhost ~]# ll /data/backup/total 8drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46drwx------ 6 root root 4096 Jan 19 14:56 2017-01-19_14-56-12[root@localhost ~]# cat /data/backup/2017-01-19_14-56-12/xtrabackup_checkpoints backup_type = incremental from_lsn = 1680362to_lsn = 1708884last_lsn = 1708884compact = 0recover_binlog_info = 0[root@localhost ~]#
在hellodb数据库中增加haha表；
[root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> ls -> ;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘ls‘ at line 1 MariaDB [(none)]> use hellodb; 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 MariaDB [hellodb]> create table haha(id int,name char(100));Query OK, 0 rows affected (0.01 sec) MariaDB [hellodb]>
进行第二次增量备份
[root@localhost ~]# innobackupex --incremental --user=root --host=localhost /data/backup/ --incremental-basedir=/data/backup/2017-01-19_14-56-12/
[root@localhost ~]# ll /data/backup/total 12drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46drwx------ 6 root root 4096 Jan 19 14:56 2017-01-19_14-56-12drwx------ 6 root root 4096 Jan 19 15:02 2017-01-19_15-02-14[root@localhost ~]# cat /data/backup/2017-01-19_15-02-14/xtrabackup_checkpoints backup_type = incremental from_lsn = 1708884to_lsn = 1711375last_lsn = 1711375compact = 0recover_binlog_info = 0[root@localhost ~]#
*差异备份
[root@localhost ~]# innobackupex --incremental --user=root --host=localhost /data/backup/ --incremental-basedir=/data/backup/2017-01-19_14-50-46/
[root@localhost ~]# ll /data/backup/total 16drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46drwx------ 6 root root 4096 Jan 19 14:56 2017-01-19_14-56-12drwx------ 6 root root 4096 Jan 19 15:02 2017-01-19_15-02-14drwx------ 6 root root 4096 Jan 19 15:06 2017-01-19_15-06-29[root@localhost ~]# cat /data/backup/2017-01-19_15-06-29/xtrabackup_checkpoints backup_type = incremental from_lsn = 1680362to_lsn = 1711375last_lsn = 1711375compact = 0recover_binlog_info = 0[root@localhost ~]#
将数据还原至新的一台服务器上
[root@localhost ~]# innobackupex --apply-log --redo-only /data/backup/2017-01-19_14-50-46/
添加第一个增量
innobackupex --apply-log --redo-only /data/backup/2017-01-19_14-50-46/ --incremental-dir=/data/backup/2017-01-19_14-56-12/
添加第二个增量
[root@localhost ~]# innobackupex --apply-log --redo-only /data/backup/2017-01-19_14-50-46/ --incremental-dir=/data/backup/2017-01-19_15-02-14/
执行还原命令：
[root@localhost mysql]# innobackupex --copy-back /data/backup/2017-01-19_14-50-46/
更改/var/lib/mysql/文件的属主及属组
[root@localhost mysql]# chown -R mysql.mysql ./*
启动mysql
[root@localhost mysql]# systemctl start mariadb
查看还原信息
[root@localhost mysql]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> show databases; +------------------------------+ | Database | +------------------------------+ | information_schema | | #mysql50#2017-01-19_15-22-30 | | hellodb | | mysql | | performance_schema | | test | +------------------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> use databases; ERROR 1049 (42000): Unknown database ‘databases‘ MariaDB [(none)]> use hellodb; 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 MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | haha | | scores | | students | | teachers | | toc | +-------------------+ 8 rows in set (0.01 sec) MariaDB [hellodb]> quit Bye [root@localhost mysql]#
本文出自 “guo_ruilin” 博客，请务必保留此出处http://guoruilin198.blog.51cto.com/12567311/1900354
如何使用mysqldump/xtrabackup备份
标签：mariadb magedu