数据库的备份与还原系列——全备份+两增量的备份与还原
场景:周日全备份,周一,周二增量备份,周三出现问题,数分钟后发现;
数据库的安装: yum install mariadb-server -y systemctl start mariadb yum install percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 在10.2的高版本上默认即时innodb_file_per_table;这里的版本是5.5,暂时没开启,需要手动写到配置文件中。 [root@MySQL ~]$ vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 innodb_file_per_table log_bin
[root@mysql ~]$mysql < hellodb_InnoDB.sql 全备份: [root@mysql ~]$mkdir -pv /backups [root@mysql ~]$ll /backups total 0 [root@mysql ~]$innobackupex /backups/ 周一数据修改后;晚上对数据进行增量备份; 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]> insert students(name,age,gender) values('a','28','F'); Query OK, 1 row affected (0.01 sec) [root@mysql ~]$mkdir /backups/{inc1,inc2} -pv mkdir: created directory ‘/backups/inc1’ mkdir: created directory ‘/backups/inc2’ [root@mysql ~]$innobackupex --incremental /backups/inc1/ --incremental-basedir=/backups/2018-02-25_15-21-53/ --incremental /backups/inc1/是指这是增量备份,以及备份的位置; --incremental-basedir=/backups/2018-02-25_15-21-53/是指基于哪个全备份或者增量备份的增量备份; [root@mysql ~]$ll /backups/ total 0 drwxr-x--- 6 root root 187 Feb 25 15:21 2018-02-25_15-21-53 drwxr-xr-x 3 root root 33 Feb 25 15:31 inc1 drwxr-xr-x 2 root root 6 Feb 25 15:25 inc2 [root@mysql ~]$ll /backups/inc1/ total 0 drwxr-x--- 6 root root 213 Feb 25 15:31 2018-02-25_15-31-17 周二的时候修改数据,并进行增量备份; MariaDB [hellodb]> insert students(name,age,gender) values('b','28','F'); Query OK, 1 row affected (0.01 sec) [root@mysql ~]$innobackupex --incremental /backups/inc2/ --incremental-basedir=/backups/inc1/2018-02-25_15-31-17/ [root@mysql ~]$du -sh /backups/ 23M /backups/ [root@mysql ~]$scp -pr /backups/ 192.168.27.17:/app/ [root@mysql17 ~]$ls /app/backups/ 2018-02-25_15-21-53 inc1 inc2 [root@mysql17 ~]$
让客户满意是我们工作的目标,不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户,将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴,公司提供的服务项目有:域名与空间、雅安服务器托管、营销软件、网站建设、明溪网站维护、网站推广。
数据库的还原与数据整理操作:
备份还原: 还原的过程中MySQL服务是不启动的; 数据预整理: [root@mysql17 ~]$innobackupex --apply-log --redo-only /app/backups/2018-02-25_15-21-53/ --redo-only表示有相应的增量备份; 将第一次增量备份的数据合并到完全备份中; [root@mysql17 ~]$innobackupex --apply-log --redo-only /app/backups/2018-02-25_15-21-53/ --incremental-dir=/app/backups/inc1/2018-02-25_15-31-17/ 将第二次增量备份的数据合并到完全备份中; [root@mysql17 ~]$innobackupex --apply-log --redo-only /app/backups/2018-02-25_15-21-53/ --incremental-dir=/app/backups/inc2/2018-02-25_15-34-00/ 如果数据目录非空,那么就可以删除他; [root@mysql17 ~]$ls /var/lib/mysql/ 将整理后的数据复制到数据目录中; [root@mysql17 ~]$innobackupex --copy-back /app/backups/2018-02-25_15-21-53/ [root@mysql17 ~]$ll /var/lib/mysql/ total 18444 drwxr-x--- 2 root root 146 Feb 25 02:48 hellodb -rw-r----- 1 root root 18874368 Feb 25 02:48 ibdata1 drwxr-x--- 2 root root 4096 Feb 25 02:48 mysql drwxr-x--- 2 root root 4096 Feb 25 02:48 performance_schema drwxr-x--- 2 root root 20 Feb 25 02:48 test -rw-r----- 1 root root 481 Feb 25 02:48 xtrabackup_info [root@mysql17 ~]$ [root@mysql17 ~]$chown -R mysql.mysql /var/lib/mysql/ [root@mysql17 ~]$ll /var/lib/mysql/ total 18444 drwxr-x--- 2 mysql mysql 146 Feb 25 02:48 hellodb -rw-r----- 1 mysql mysql 18874368 Feb 25 02:48 ibdata1 drwxr-x--- 2 mysql mysql 4096 Feb 25 02:48 mysql drwxr-x--- 2 mysql mysql 4096 Feb 25 02:48 performance_schema drwxr-x--- 2 mysql mysql 20 Feb 25 02:48 test -rw-r----- 1 mysql mysql 481 Feb 25 02:48 xtrabackup_info [root@mysql17 ~]$
启动数据库,核对数据库数据:
[root@mysql17 ~]$systemctl start mariadb [root@mysql17 ~]$mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select * from hellodb.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 | | 26 | a | 28 | F | NULL | NULL | | 27 | b | 28 | F | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 27 rows in set (0.01 sec)
分享题目:数据库的备份与还原系列——全备份+两增量的备份与还原
文章分享:http://scyanting.com/article/iipcce.html