还剩7页未读,继续阅读
文本内容:
数据库备份,数据库为school素材如下1创建student和score表CREATETABLEstudentidINTIONOTNULLUNIQUEPRIMARYKEYnameVARCHAR20NOTNULLsexVARCHAR⑷birthYEARdepartmentVARCHAR20addressVARCHARSO;创建score表,SQL代码如下CREATETABLEscoreidINTIONOTNULLUNIQUEPRIMARYKEYAUTOJNCREMENTstujdINTIONOTNULLc_nameVARCHAR20gradeINTIO);.为student表和score表增加记录向student表插入记录的INSERT语句如下INSERTINTOstudentVALUES(901张老大丁男门985「计算机系北京市海淀区);INSERTINTOstudentVALUES(902张老二丁男1986中文系丁北京市昌平区;INSERTINTOstudentVALUES(903张三丁女1990中文系丁湖南省永州市);INSERTINTOstudentVALUES(904「李四丁男1990英语系丁辽宁省阜新市);INSERTINTOstudentVALUES(905「王五「女1991英语系丁福建省厦门市);INSERTINTOstudentVALUES(906「王六「男’1988计算机系丁湖南省衡阳市);向score表插入记录的INSERT语句如下INSERTINTOscoreVALUES(NULL901计算机98);INSERTINTOscoreVALUES(NULL901英语80);INSERTINTOscoreVALUES(NULL902计算机65);INSERTINTOscoreVALUES(NULL902中文-88);INSERTINTOscoreVALUES(NULL903中文95);INSERTINTOscoreVAWES(NULL904计算机70);INSERTINTOscoreVALUES(NULL904英语92);INSERTINTOscoreVALUES(NULL905英语94);INSERTINTOscoreVALUES(NULL906计算机90);INSERTINTOscoreVALUESNULL906英语85;mysql[school]select*fromstudent;++++++--+++----+++---+--+6rowsinset
0.00secmysql[school]|cson@小解雉~mysql[schooi]seiect*fromscore;+-——++++id|stu_id|c_name|grade+-——++++10rowsinset
0.00secmysql[school]|.备份数据库school到/backup目录[root@nodelmysqldump-uroot-pl23456-Bschool--set-gtid-purged=OFF/backup/db_school.sql[rootanodelmysqldump-uroot-pl23456-Bschool--set-gtid-purged=OFF/backup/db_school.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@n0de]〜]#|cs-.备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库[root@nodel〜]#mysqldump-uroot-pl23456-set-gtid-purged=OFF-add-drop-databasescho[rootanodel〜]#mysqldump-uroot-pl23456--set-gtid-purged=OFF--add-drop-databaseschool/backup/db_schooll.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@nodel〜]#尔“即小.直接将MySQL数据库压缩备份[root@nodel〜]#mysqldump-uroot-pl23456-Bschool--set-gtid-purged=OFF|gzip/backup/db_school
3.sql.gz[root@nodel-]#mysqldump-uroot-pl23456-Bschool--set-gtid-purged=OFF|gzip/backup/db_school
3.sql.gzmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@nodel-]#|.备份MySQL数据库某个(些)表此例备份student表[root@nodel〜]#mysqldump-uroot-set-gtid-purged=OFF-pl23456schoolstudent/backup/db_school
4.sql[root@nodel-]#mysqldump-uroot--set-gtid-purged=OFF-pl23456schoolstudent/backup/db_school
4.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@nodel〜]#|cs”小/傩..同时备份多个MySQL数据库其他数据库素材自行准备[root@nodel〜用mysqldump-uroot-set-gtid-purged=OFF-pl23456-Bschoolit/backup/db_school
5.sql[rootanodel-]#mysqldump-uroot--set-gtid-purged=OFF-pl23456-Bschoolit/backup/db_schools.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[roottanodel~]#|c皿.小.仅仅备份数据库结构[root@nodelmysqldump-uroot--set-gtid-purged=OFF-pl23456-dschool/backup/db_school
6.sql[rootanodel-]#mysqldump-uroot--set-gtid-purged=OFF-pl23456-dschool/backup/db_scl00I
6.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@nodel~]#.cs-.备份服务器上所有数据库[root@nodel~]#mysqldump-uroot--set-gtid-purged=OFF-pl23456-A/backup/db_school
7.sql[rootanodel~]#mysqldump-uroot--set-gtid-purged=OFF-pl23456-A/backup/db_school
7.sqImysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[rootQnodel-]#|
10.还原MySQL数据库1删除数据库mysql[none]dropdatabaseschool;Mysql[none]showdatabases;mysql[none]dropdatabaseschool;ERROR1008HY000:Cantdropdatabaseschool;databasedoesntexistmysql[none]showdatabases;++|Database||information_schema||company|dbl||db2|Igtid||it|Imysql|performance_schema|Isys一|+-+9rowsinset
0.00secmysql[none]|2还原数据库[root@node1~]#mysql-uroot-p123456/backup/db_school.sql[rootanodel~]#mysql-uroot-pl23456/backup/db_school.sqlmysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@nodel~]#|3查看结果mysql[school]showtables;mysql[school]showtables;++|Tables_in_school|++|score||student++2rowsinset
0.00secmysql[school]|csdn◎小野鲸〜
11.还原压缩的MySQL数据库1删除数据库mysql[school]dropdatabaseschool;mysql[none]showdatabases;mysql[school]dropdatabaseschool;QueryOK2rowsaffected
0.00secmysql[none]showdatabases;|Database++information_schemacompanydbldb2gtiditmysqlperformance_schemasys9rowsinset
0.00secmysql[none]|2还原压缩的数据库[root@node1backup]#zcat/backup/db_school
3.sql.gz|mysql-uroot-p123456[root^nodelbackup]#zcat/backup/db_school
3.sql.gz|mysql-uroot-pl23456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@nodelbackup]#■csoNaj侪M〜
(3)查看还原后的数据库的信息mysql[school]showtables;mysql[school]showtables;+4-|Tables_in_school|++|score|student++2rowsinset
0.00secmysql[school],csdnrj、睇政〜
12.使用mydumper备份数据库[root@node1backup]#mydumper-uroot-p123456-Bschool-S/tmp/mysql.sock-o/backup/db_school
9.sql[rootanodelbackup]#mydumper-uroot-p123456-Bschool-S/tmp/mysql.sock-o/backup/db_schoo
19.sqlfrootanodelbackup]#|13使用mydumper恢复数据库1删除数据库mysql[school]dropdatabaseschool;mysql[school]dropdatabaseschool;QueryOK2rowsaffected
0.00secmysql[none]|csdn小胖2还原数据库[root@node1backup]#myloader-uroot-p123456-S/tmp/mysql.sock-d/backup/db_school
9.sql-Bschool[root@nodelbackup]#myloader-uroot-p123456-S/tmp/mysql.sock-d/backup/db_schoo
19.sql-Bschool[root@nodelbackup]#,cwhim.3查看还原后的结果mysql[school]showtables;Databasechangedmysql[school]showtables;++|Tables_in_school|++|score||student++2rowsinset
0.00secmysql[school]|csdnj、胖鲸〜id|name|sex|birth|department|address■■■■-+・・一___・.-++-901张老大1985计算机系北京市海淀区902张考二1986北京市昌平区9031990湖南省永州市9041990辽宁省阜新市9051991福建省厦门市906王六1988计算标系湖南省衡阳市。