素材
创建数据库school
CREATE DATABASE school;
创建student和score表
CREATE TABLE student (
id INT ( 10 ) NOT NULL UNIQUE PRIMARY KEY ,
name VARCHAR ( 20 ) NOT NULL ,
sex VARCHAR ( 4 ) ,
birth YEAR ,
department VARCHAR ( 20 ) ,
address VARCHAR ( 50 )
) ;
创建score表。SQL代码如下:
CREATE TABLE score (
id INT ( 10 ) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT ( 10 ) NOT NULL ,
c_name VARCHAR ( 20 ) ,
grade INT ( 10 )
) ;
为student表和score表增加记录
INSERT INTO student VALUES ( 901 , '张老大' , '男' , 1985 , '计算机系' , '北京市海淀区' ) ;
INSERT INTO student VALUES ( 902 , '张老二' , '男' , 1986 , '中文系' , '北京市昌平区' ) ;
INSERT INTO student VALUES ( 903 , '张三' , '女' , 1990 , '中文系' , '湖南省永州市' ) ;
INSERT INTO student VALUES ( 904 , '李四' , '男' , 1990 , '英语系' , '辽宁省阜新市' ) ;
INSERT INTO student VALUES ( 905 , '王五' , '女' , 1991 , '英语系' , '福建省厦门市' ) ;
INSERT INTO student VALUES ( 906 , '王六' , '男' , 1988 , '计算机系' , '湖南省衡阳市' ) ;
INSERT INTO score VALUES ( NULL , 901 , '计算机' , 98 ) ;
INSERT INTO score VALUES ( NULL , 901 , '英语' , 80 ) ;
INSERT INTO score VALUES ( NULL , 902 , '计算机' , 65 ) ;
INSERT INTO score VALUES ( NULL , 902 , '中文' , 88 ) ;
INSERT INTO score VALUES ( NULL , 903 , '中文' , 95 ) ;
INSERT INTO score VALUES ( NULL , 904 , '计算机' , 70 ) ;
INSERT INTO score VALUES ( NULL , 904 , '英语' , 92 ) ;
INSERT INTO score VALUES ( NULL , 905 , '英语' , 94 ) ;
INSERT INTO score VALUES ( NULL , 906 , '计算机' , 90 ) ;
INSERT INTO score VALUES ( NULL , 906 , '英语' , 85 ) ;
备份恢复要求
1. 备份数据库school到/backup目录
[ root@localhost ~]
[ root@localhost ~]
-rw-r--r-- 1 root root 3310 Apr 26 14 :59 school_1.sql
2. 备份数据库school为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库
[ root@localhost ~]
[ root@localhost ~]
-rw-r--r-- 1 root root 3453 Apr 26 15 :07 school_2.sql
[ root@localhost ~]
DROP TABLE IF EXISTS ` score` ;
DROP TABLE IF EXISTS ` student` ;
[ root@localhost ~]
CREATE TABLE ` score` (
CREATE TABLE ` student` (
3. 直接将数据库school压缩备份
[ root@localhost ~]
[ root@localhost ~]
-rw-r--r-- 1 root root 1210 Apr 26 15 :14 /backup/school_3.sql.tar
4. 备份数据库school某个(些)表(此例备份student表 )
[ root@localhost ~]
[ root@localhost ~]
-rw-r--r-- 1 root root 2390 Apr 26 15 :19 /backup/school_student.sql
5. 同时备份多个MySQL数据库(其他数据库素材自行准备)
[ root@localhost ~]
[ root@localhost ~]
-rw-r--r-- 1 root root 3599 Apr 26 15 :21 /backup/school_2-3.sql
6. 仅仅备份数据库结构
[ root@localhost ~]
[ root@localhost ~]
-rw-r--r-- 1 root root 2315 Apr 26 15 :22 /backup/school_nodata.sql
7. 备份服务器上所有数据库
[ root@localhost ~ ]
[ root@localhost ~ ]
- rw- r
8. 还原MySQL数据库
mysql> drop database school;
Query OK, 2 rows affected ( 0.00 sec)
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school2 |
| sys |
+
5 rows in set ( 0.00 sec)
[ root@localhost ~]
mysql: [ Warning] Using a password on the command line interface can be insecure.
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school |
| school2 |
| sys |
+
6 rows in set ( 0.00 sec)
mysql> use school;
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
mysql> show tables ;
+
| Tables_in_school |
+
| score |
| student |
+
2 rows in set ( 0.00 sec)
mysql> select * from score;
+
| id | stu_id | c_name | grade |
+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+
10 rows in set ( 0.00 sec)
mysql> select * from student;
+
| id | name | sex | birth | department | address |
+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+
6 rows in set ( 0.00 sec)
mysql> drop database school;
Query OK, 2 rows affected ( 0.00 sec)
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school2 |
| sys |
+
5 rows in set ( 0.00 sec)
mysql> source / backup / school_2. sql
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected, 1 warning ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 1 row affected ( 0.00 sec)
Database changed
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.01 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 10 rows affected ( 0.00 sec)
Records: 10 Duplicates: 0 Warnings : 0
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.01 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 6 rows affected ( 0.00 sec)
Records: 6 Duplicates: 0 Warnings : 0
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected, 1 warning ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
Query OK, 0 rows affected ( 0.00 sec)
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school |
| school2 |
| sys |
+
6 rows in set ( 0.00 sec)
mysql> use school
Database changed
mysql> show tables ;
+
| Tables_in_school |
+
| score |
| student |
+
2 rows in set ( 0.00 sec)
mysql> select * from score;
+
| id | stu_id | c_name | grade |
+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+
10 rows in set ( 0.00 sec)
mysql> select * from student;
+
| id | name | sex | birth | department | address |
+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+
6 rows in set ( 0.00 sec)
9. 还原压缩的MySQL数据库
mysql> drop database school;
Query OK, 2 rows affected ( 0.00 sec)
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school2 |
| sys |
+
5 rows in set ( 0.00 sec)
[ root@localhost ~]
mysql: [ Warning] Using a password on the command line interface can be insecure.
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school |
| school2 |
| sys |
+
6 rows in set ( 0.00 sec)
mysql> use school
Database changed
mysql> show tables ;
+
| Tables_in_school |
+
| score |
| student |
+
2 rows in set ( 0.00 sec)
mysql> select * from score;
+
| id | stu_id | c_name | grade |
+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+
10 rows in set ( 0.00 sec)
mysql> select * from student;
+
| id | name | sex | birth | department | address |
+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+
6 rows in set ( 0.00 sec)
10. 使用xtrabackup 备份数据库
innobackupex -u root -p "MySql@123" --socket= /tmp/mysql.sock --no-timestamp /backup/mysql/full
[ root@localhost ~]
total 12336
-rw-r----- 1 root root 424 Apr 27 10 :29 backup-my.cnf
-rw-r----- 1 root root 618 Apr 27 10 :29 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 27 10 :29 ibdata1
drwxr-x--- 2 root root 4096 Apr 27 10 :29 mysql
drwxr-x--- 2 root root 8192 Apr 27 10 :29 performance_schema
drwxr-x--- 2 root root 92 Apr 27 10 :29 school
drwxr-x--- 2 root root 20 Apr 27 10 :29 school2
drwxr-x--- 2 root root 8192 Apr 27 10 :29 sys
-rw-r----- 1 root root 113 Apr 27 10 :29 xtrabackup_checkpoints
-rw-r----- 1 root root 461 Apr 27 10 :29 xtrabackup_info
-rw-r----- 1 root root 2560 Apr 27 10 :29 xtrabackup_logfile
11. 在另外的数据库服务器上还原xtrabackup 备份
[ root@localhost ~]
[ root@localhost ~]
total 131116
-rw-r----- 1 root root 424 Apr 27 15 :45 backup-my.cnf
-rw-r----- 1 root root 618 Apr 27 15 :45 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 27 15 :51 ibdata1
-rw-r----- 1 root root 50331648 Apr 27 15 :51 ib_logfile0
-rw-r----- 1 root root 50331648 Apr 27 15 :51 ib_logfile1
-rw-r----- 1 root root 12582912 Apr 27 15 :51 ibtmp1
drwxr-x--- 2 root root 4096 Apr 27 15 :45 mysql
drwxr-x--- 2 root root 8192 Apr 27 15 :45 performance_schema
drwxr-x--- 2 root root 92 Apr 27 15 :45 school
drwxr-x--- 2 root root 20 Apr 27 15 :45 school2
drwxr-x--- 2 root root 8192 Apr 27 15 :45 sys
-rw-r----- 1 root root 113 Apr 27 15 :51 xtrabackup_checkpoints
-rw-r----- 1 root root 461 Apr 27 15 :45 xtrabackup_info
-rw-r----- 1 root root 8388608 Apr 27 15 :51 xtrabackup_logfile
[ root@localhost ~]
[ root@localhost ~]
[ root@localhost ~]
[ root@localhost ~]
total 0
[ root@localhost ~]
[ root@localhost ~]
total 122916
-rw-r----- 1 root root 618 Apr 27 15 :52 ib_buffer_pool
-rw-r----- 1 root root 12582912 Apr 27 15 :52 ibdata1
-rw-r----- 1 root root 50331648 Apr 27 15 :52 ib_logfile0
-rw-r----- 1 root root 50331648 Apr 27 15 :52 ib_logfile1
-rw-r----- 1 root root 12582912 Apr 27 15 :52 ibtmp1
drwxr-x--- 2 root root 4096 Apr 27 15 :52 mysql
drwxr-x--- 2 root root 8192 Apr 27 15 :52 performance_schema
drwxr-x--- 2 root root 92 Apr 27 15 :52 school
drwxr-x--- 2 root root 20 Apr 27 15 :52 school2
drwxr-x--- 2 root root 8192 Apr 27 15 :52 sys
-rw-r----- 1 root root 461 Apr 27 15 :52 xtrabackup_info
[ root@localhost ~]
[ root@localhost ~]
total 122916
-rw-r----- 1 mysql mysql 618 Apr 27 15 :52 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 27 15 :52 ibdata1
-rw-r----- 1 mysql mysql 50331648 Apr 27 15 :52 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 27 15 :52 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Apr 27 15 :52 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Apr 27 15 :52 mysql
drwxr-x--- 2 mysql mysql 8192 Apr 27 15 :52 performance_schema
drwxr-x--- 2 mysql mysql 92 Apr 27 15 :52 school
drwxr-x--- 2 mysql mysql 20 Apr 27 15 :52 school2
drwxr-x--- 2 mysql mysql 8192 Apr 27 15 :52 sys
-rw-r----- 1 mysql mysql 461 Apr 27 15 :52 xtrabackup_info
[ root@localhost ~]
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| sys |
+
4 rows in set ( 0.01 sec)
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school |
| school2 |
| sys |
+
6 rows in set ( 0.00 sec)
12. 使用mydumper备份数据库
[ root@localhost ~]
[ root@localhost ~]
total 32
-rw-r--r-- 1 root root 75 Apr 27 10 :01 metadata
-rw-r--r-- 1 root root 90 Apr 27 10 :01 school-schema-create.sql
-rw-r--r-- 1 root root 352 Apr 27 10 :01 school.score.00000.sql
-rw-r--r-- 1 root root 2 Apr 27 10 :01 school.score-metadata
-rw-r--r-- 1 root root 367 Apr 27 10 :01 school.score-schema.sql
-rw-r--r-- 1 root root 503 Apr 27 10 :01 school.student.00000.sql
-rw-r--r-- 1 root root 1 Apr 27 10 :01 school.student-metadata
-rw-r--r-- 1 root root 413 Apr 27 10 :01 school.student-schema.sql
13. 使用mydumper恢复数据库
mysql> drop database school;
Query OK, 2 rows affected ( 0.01 sec)
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school2 |
| sys |
+
5 rows in set ( 0.00 sec)
[ root@localhost ~]
mysql> show databases ;
+
| Database |
+
| information_schema |
| mysql |
| performance_schema |
| school |
| school2 |
| sys |
+
6 rows in set ( 0.00 sec)
mysql> use school;
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
mysql> show tables ;
+
| Tables_in_school |
+
| score |
| student |
+
2 rows in set ( 0.00 sec)
mysql> select * from score;
+
| id | stu_id | c_name | grade |
+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+
10 rows in set ( 0.00 sec)
mysql> select * from student;
+
| id | name | sex | birth | department | address |
+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+
6 rows in set ( 0.00 sec)