准备工作
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.004 sec) MariaDB [(none)]> create database school; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use school; Database changed MariaDB [school]> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint); Query OK, 0 rows affected (0.014 sec) MariaDB [school]> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.002 sec) MariaDB [school]> insert student(name,age) values('taochi',20),('chensonglin',23),('wangming',20); Query OK, 3 rows affected (0.002 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [school]> create table student1(id int not null primary key auto_increment,name varchar(50),age tinyint); Query OK, 0 rows affected (0.005 sec) MariaDB [school]> insert student1(name,age) values('mufeng',20),('fangxinxin',23),('leichen',20),('yuqinghao',24); Query OK, 4 rows affected (0.003 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [school]> create table student2(id int not null primary key auto_increment,name varchar(50),age tinyint); Query OK, 0 rows affected (0.005 sec) MariaDB [school]> show tables; +------------------+ | Tables_in_school | +------------------+ | student | | student1 | | student2 | +------------------+ 3 rows in set (0.000 sec) MariaDB [school]> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | taochi | 20 | | 2 | chensonglin | 23 | | 3 | wangming | 20 | +----+-------------+------+ 3 rows in set (0.001 sec) MariaDB [school]> select * from student1 ; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | mufeng | 20 | | 2 | fangxinxin | 23 | | 3 | leichen | 20 | | 4 | yuqinghao | 24 | +----+------------+------+ 4 rows in set (0.000 sec) MariaDB [school]> select * from student2; Empty set (0.001 sec) 备份数据库school里的student表 [root@master ~]# mysqldump -uroot -p123 school student > table_student.sql
备份数据库school
[root@master ~]# mysqldump -uroot -p123 school > table_school.sql
备份整个数据库
[root@master ~]# mysqldump -uroot -p123 --all-databases > all.sql [root@master ~]# ls all.sql anaconda-ks.cfg table_school.sql table_student.sql [root@master ~]# du -sh * 472K all.sql 4.0K anaconda-ks.cfg 4.0K table_school.sql 4.0K table_student.sql 误删school数据库并恢复 [root@master ~]# mysql -uroot -p123 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 25 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> drop database school; Query OK, 3 rows affected (0.007 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]> create database school; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> use school; Database changed MariaDB [school]> show tables; 删完后库里的数据没了 Empty set (0.000 sec) MariaDB [school]> source table_school.sql; 恢复数据 Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 3 rows affected (0.002 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 4 rows affected (0.001 sec) Records: 4 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.004 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.000 sec) MariaDB [school]> show tables; +------------------+ | Tables_in_school | +------------------+ | student | | student1 | | student2 | +------------------+ 3 rows in set (0.001 sec) MariaDB [school]> select * from student1 -> ; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | mufeng | 20 | | 2 | fangxinxin | 23 | | 3 | leichen | 20 | | 4 | yuqinghao | 24 | +----+------------+------+ 4 rows in set (0.000 sec) MariaDB [school]> select * from student -> ; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | taochi | 20 | | 2 | chensonglin | 23 | | 3 | wangming | 20 | +----+-------------+------+ 3 rows in set (0.000 sec) MariaDB [school]> select * from student2; Empty set (0.000 sec) 第二种方法
误删数据库school MariaDB [school]> drop database school; Query OK, 3 rows affected (0.007 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]> create database school; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> use school; Database changed MariaDB [school]> show tables; Empty set (0.000 sec) MariaDB [school]> quit Bye [root@master ~]# [root@master ~]# mysql -uroot -p123 school < table_school.sql [root@master ~]# mysql -uroot -p123 -e 'show tables from school;' +------------------+ | Tables_in_school | +------------------+ | student | | student1 | | student2 | +------------------ 备份整个数据库，恢复整个数据库 mysqldump -uroot --all-databases > all.sql MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]> drop database school; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) MariaDB [(none)]> quit Bye
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
下载安装xtrabackup 我们这里通过wget percona官方的rpm包进行安装 wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm
yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm #需要EPEL源
xtrabackup介绍 Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点： 1.备份过程快速、可靠； 2.备份过程不会打断正在执行的事务； 3.能够基于压缩等功能节约磁盘空间和流量； 4.自动实现备份检验； 5.还原速度快；