数据库的备份与还原
作业:
数据库备份,数据库为school,素材如下
1.创建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)
);
2.为student表和score表增加记录
向student表插入记录的INSERT语句如下:
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,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下:
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);
- 备份数据库students到/backup目录
[root@localhost ~]# mkdir -p /backup/mysql
# 备份数据库
[root@localhost ~]# mysqldump -uroot -predhat123 student > /backup/mysql/school.sql
# 加上-B参数,备份数据库时创建数据库和切换数据库
[root@localhost ~]# mysqldump -uroot -predhat123 -B student > /backup/mysql/school_2.sql
# 备份时,压缩备份文件
[root@localhost ~]# mysqldump -uroot -predhat123 -B student | gzip > /backup/mysql/school_3.sql.gz
- 备份MySQL数据库test为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库
[root@localhost mysql]# mysqldump --add-drop-table -uroot -predhat123 test > t2.sql
- 直接将MySQL数据库test压缩备份
[root@localhost mysql]# mysqldump -uroot -predhat123 -B test | gzip > /backup/mysql/t3.sql.gz
- 备份MySQL数据库Ttest某个(些)表。此例备份student表
[root@localhost ~]# mysqldump -uroot -predhat123 test student > /backup/mysql/t4.sql
- 同时备份多个MySQL数据库(其他数据库素材自行准备),此例为数据库test和数据库students
[root@localhost ~]# mysqldump -uroot -predhat123 --databases test students > /backup/mysql/t5.sql
- 仅仅备份数据库结构
[root@localhost mysql]# mysqldump --no-data -uroot -predhat123 --databases test students > /backup/mysql/t6.sql
9. 备份服务器上所有数据库
[root@localhost mysql]# mysqldump --all-databases -uroot -predhat123> /backup/mysql/t7.sql
- 还原MySQL数据库test
# 直接导入可能会报错,在 mysql 执行 reset master命令,退出后再执行还原命令即可
'''
报错内容:
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
'''
mysql> reset master;
[root@localhost ~]# mysql -uroot -predhat123 test < /home/mysql/school_2.sql
# 如果数据库被误删,备份时语句应该加上-B参数,否则会报ERROR 1049 (42000): Unknown database 'zuoye_5'
- 还原压缩的MySQL数据库
# 方法一:直接恢复
[root@localhost mysql]# zcat t1_3.sql.gz | mysql -uroot -predhat123
# 方法二:解压后在 mysql 命令行恢复
[root@localhost mysql]# gzip -d school_3.sql.gz
mysql> source /home/mysql/school_3.sql
- 使用xtrabackup 备份数据库
[root@localhost mysql ~]# innobackupex -u root -p redhat123 --no-timestamp /backup/mysql/full_$(date +%F)
- 在另外的数据库服务器上还原xtrabackup 备份
# 应用日志文件的数据
[root@localhost-mysql ~]# innobackupex -u root -p redhat123 --apply-log /backup/mysql/full_2022-9-12/
# 停止 mysql 服务
[root@localhost-mysql ~]# systemctl stop mysqld
# 删除 mysql 数据目录中的文件
[root@localhost-mysql ~]# rm -rf /var/lib/mysql/*
# 恢复数据
[root@localhost-mysql ~]# innobackupex --copy-back /backup/mysql/full_2021-9-12/
# 修改目录所属者、所属组
[root@localhost-mysql ~]# chmod -R mysql.mysql /var/lib/mysql/*
# 启动 mysqld 服务
[root@localhost-mysql ~]# systemctl start mysql
- 使用mydumper备份数据库
备份数据库
# mydumper工具备份
[root@localhost-mysql ~]# mydumper -u root -p redhat123 -B test -o /mysql_back
# mydumper工具备份,同时压缩
[root@localhost-mysql ~]# mydumper -u root -p redhat123 -B test -c -o /mysql_back
# 备份表
[root@localhost-mysql ~]# mydumper -u root -p redhat123 -B test -T school,score -o /backup/tables
- 使用mydumper恢复数据库
# 恢复文件
localhost-mysql
[root@localhost-mysql ~]# myloader -u root -p redhat123 -B it -o -d /mysql_back/
# 恢复表
[root@localhost-mysql ~]# myloader -u root -p redhat123 -d /backup/tables/ -o -B school
视图索引存储过程
作业:
创建表并插入数据
字段名 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
---|---|---|---|---|---|---|
id | INT | 是 | 否 | 是 | 是 | 否 |
name | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
glass | VARCHAR(50) | 否 | 否 | 是 | 否 | 否 |
sch 表内容
id | name | glass |
---|---|---|
1 | xiaommg | glass |
2 | xiaojun | glass |
mysql> use test_m;
Database changed
mysql> create table sch(
-> id int primary key unique not null,
-> name varchar(50) not null,
-> glass varchar(50) not null);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into sch values
-> (1,'xiaoming','glass1'),
-> (2,'xiaojun','glass2');
1、创建一个可以统计表格内记录条数的存储函数 ,函数名为count_sch()
\d /
create function count_sch()
returns int
begin
declare i int default 0;
select count(1) into i from sch;
return i;
end/
2、创建一个存储过程avg_sai,有3个参数,分别是deptno,job,接收平均工资, 功能查询emp表dept为30,job为销售员的平均工资。
create procedure avg_sal(in p1 int,in p2 varchar(50),out avg_salary float)
begin
select avg(sal) into avg_salary from emp
where deptno =p1 and job = p2;
end/
call avg_sal(30,'销售员',@a)/
select @a/