第一次作业

第一次作业

数据库备份，数据库为school

1.创建student和score表

create table student(
id int 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 primary key auto_increment,
stu_id int not null,
c_name varchar(20),
grade int
);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);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dn74hezd-1691023714851)(C:\Users\think\AppData\Roaming\Typora\typora-user-images\image-20230723211028609.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TmoeSl9I-1691023714853)(C:\Users\think\AppData\Roaming\Typora\typora-user-images\image-20230723211049034.png)]

3备份数据库school到/backup目录

1#mkdir /backup

#mysqldmp -uroot -pmysql@123 -B school > /backuop/db1.sql

4备份MYSQL数据库为带删除表的格式，能让该设备覆盖已有数据库而不需要手动删除原有数据库

#mysqldump --add-drop-table -uroot -pmysql@qwe -d school > /bachup/dbdrop/sql

5直接将mysql数据库压缩备份

mysql -uroot -pmsql@123 -p school | gzip > /backup/dbgzip.sql.gz

6备份MySQL数据库某些表

MySQL -uroot -pmysql@123 -p school student > /back/dbtable.sql

7同时备份多个数据库表

mysqldump -uroot -pmysl@123 -Bschool student > /backup/dbmore.sql

8仅仅备份数据库结构

mysqldump -uroot -pmysql@123 -d school /backup/dbstrcu.sql

9备份数据库服务器上所有数据库

mysqldump -uroot -pmysql@123 -A > /backuo/dball.sql

10还原MySQL数据库

mysql -uroot -pmysql@123 -e ‘source /backup/db1.sql’

11还原压缩的数据库

gunzip < /backup/dbzip.sql.gz | mysql -uroot -pmysql@123 school

12使用xtrabackup备份数据库

yum install pecona-xtrabackup-24-2.4.8-1.el.x86_64.rpm

备份： innobackup -u root -p mysql@123 -s /tmp/mysql.sock /server/backup

14使用mydumper备份数据库

安装mydumper

备份：mudumper -u root -p @123 -B school -S /tmp/mysql.sock -o /mmysqlbak

15使用mydumper恢复数据库

mydumper -u mysql@123 -S /tmp/mysql.sock -d /mysqlbak/ -o -B school