mysql备份恢复基础.docx
- 文档编号:24710858
- 上传时间:2023-05-31
- 格式:DOCX
- 页数:24
- 大小:109.59KB
mysql备份恢复基础.docx
《mysql备份恢复基础.docx》由会员分享,可在线阅读,更多相关《mysql备份恢复基础.docx(24页珍藏版)》请在冰豆网上搜索。
mysql备份恢复基础
基本语法,备份oldboy库
mysqldump-uroot-poracleoldboy>/opt/oldboy_bak.sql
[root@localhost~]#cat/opt/oldboy_bak.sql|grep-v^#|grep-v^$
--MySQLdump10.13Distrib5.5.32,forLinux(i686)
--
--Host:
localhostDatabase:
oldboy
--------------------------------------------------------
--Serverversion5.5.32-log
/*!
40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;
/*!
40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;
/*!
40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;
/*!
40101SETNAMESlatin1*/;
/*!
40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/;
/*!
40103SETTIME_ZONE='+00:
00'*/;
/*!
40014SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;
/*!
40014SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0*/;
/*!
40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'
*/;
/*!
40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/;
--
--Tablestructurefortable`course`
--
DROPTABLEIFEXISTS`course`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`course`(
`Cno`int(10)NOTNULLCOMMENT'课程号',
`Cname`varchar(64)NOTNULLCOMMENT'课程号',
`Ccredit`tinyint
(2)NOTNULLCOMMENT'学分',
PRIMARYKEY(`Cno`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`course`
--
LOCKTABLES`course`WRITE;
/*!
40000ALTERTABLE`course`DISABLEKEYS*/;
INSERTINTO`course`VALUES(1001,'linux中高级运维',3),(1002,'linux高级
架构师',5),(1003,'mysql高级Dba',4),(1004,'python',4),(1005,'javaweb开发
',3);
/*!
40000ALTERTABLE`course`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`sc`
--
DROPTABLEIFEXISTS`sc`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`sc`(
`SCid`int(12)NOTNULLAUTO_INCREMENTCOMMENT'主键',
`Cno`int(10)NOTNULLCOMMENT'课程号',
`Sno`int(10)NOTNULLCOMMENT'学号',
`grade`tinyint
(2)NOTNULLCOMMENT'学生成绩',
PRIMARYKEY(`SCid`)
)ENGINE=InnoDBAUTO_INCREMENT=61DEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`sc`
--
LOCKTABLES`sc`WRITE;
/*!
40000ALTERTABLE`sc`DISABLEKEYS*/;
INSERTINTO`sc`VALUES(41,1001,1,4),(42,1002,1,3),(43,1003,1,1),
(44,1004,1,6),(45,1001,2,3),(46,1002,2,2),(47,1003,2,2),(48,1004,2,8),
(49,1001,3,4),(50,1002,3,4),(51,1003,3,2),(52,1004,3,8),(53,1001,4,1),
(54,1002,4,1),(55,1003,4,2),(56,1004,4,3),(57,1001,5,5),(58,1002,5,3),
(59,1003,5,2),(60,1004,5,9);
/*!
40000ALTERTABLE`sc`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`student`
--
DROPTABLEIFEXISTS`student`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`student`(
`Sno`int(10)NOTNULLCOMMENT'å¦å�·',
`Sname`varchar(16)NOTNULLCOMMENT'姓å��',
`Ssex`char(4)NOTNULLCOMMENT'性别',
`Sage`tinyint
(2)NOTNULLDEFAULT'0'COMMENT'å¦ç”Ÿå¹´é¾„',
`Sdept`varchar(32)DEFAULTNULLCOMMENT'å¦ç”Ÿæ‰€åœ¨ç³»åˆ«',
PRIMARYKEY(`Sno`),
KEY`index_Sname`(`Sname`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`student`
--
LOCKTABLES`student`WRITE;
/*!
40000ALTERTABLE`student`DISABLEKEYS*/;
INSERTINTO`student`VALUES(1,'宏志','男',30,'计算机网络'),(2,'王硕','
男',30,'computerapplication'),(3,'oldboy','男',28,'物流管理'),(4,'脉
动','男',29,'computerapplication'),(5,'oldgirl','女',26,'计算机科学与技
术'),(6,'莹莹','女',22,'护士'),(10,'?
?
','?
',30,'?
?
'),(11,'傻逼二
号','男',30,'国语'),(12,'?
?
','?
',22,'?
?
'),(13,'王八蛋','女',22,'游泳');
/*!
40000ALTERTABLE`student`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`test`
--
DROPTABLEIFEXISTS`test`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`test`(
`qq`int(16)NOTNULL,
`id`int(4)NOTNULL,
`name`char(20)NOTNULL,
`age`tinyint
(2)DEFAULTNULL,
`sex`char(4)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`index_name`(`name`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`test`
--
LOCKTABLES`test`WRITE;
/*!
40000ALTERTABLE`test`DISABLEKEYS*/;
INSERTINTO`test`VALUES(0,1,'oldboy',NULL,NULL),
(0,2,'oldgirl',NULL,NULL),(0,3,'inca',NULL,NULL),
(0,4,'zuma',NULL,NULL),(0,5,'kaka',NULL,NULL);
/*!
40000ALTERTABLE`test`ENABLEKEYS*/;
UNLOCKTABLES;
/*!
40103SETTIME_ZONE=@OLD_TIME_ZONE*/;
/*!
40101SETSQL_MODE=@OLD_SQL_MODE*/;
/*!
40014SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;
/*!
40014SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS*/;
/*!
40101SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/;
/*!
40101SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/;
/*!
40101SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/;
/*!
40111SETSQL_NOTES=@OLD_SQL_NOTES*/;
--Dumpcompletedon2016-03-2018:
00:
56
发现很多乱码怎么办
可以在倒库的时候指定字符集
[root@localhost~]#mysqldump-uroot-poracle--default-character-set=latin1oldboy>/opt/oldboy_bak.sql
[root@localhost~]#cat/opt/oldboy_bak.sql|grep-v^#|grep-v^$
--MySQLdump10.13Distrib5.5.32,forLinux(i686)
--
--Host:
localhostDatabase:
oldboy
--------------------------------------------------------
--Serverversion5.5.32-log
/*!
40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;
/*!
40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;
/*!
40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/;
/*!
40101SETNAMESlatin1*/;
/*!
40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/;
/*!
40103SETTIME_ZONE='+00:
00'*/;
/*!
40014SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0*/;
/*!
40014SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0*/;
/*!
40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/;
/*!
40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/;
--
--Tablestructurefortable`course`
--
DROPTABLEIFEXISTS`course`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`course`(
`Cno`int(10)NOTNULLCOMMENT'课程号',
`Cname`varchar(64)NOTNULLCOMMENT'课程号',
`Ccredit`tinyint
(2)NOTNULLCOMMENT'学分',
PRIMARYKEY(`Cno`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`course`
--
LOCKTABLES`course`WRITE;
/*!
40000ALTERTABLE`course`DISABLEKEYS*/;
INSERTINTO`course`VALUES(1001,'linux中高级运维',3),(1002,'linux高级架构师',5),(1003,'mysql高级Dba',4),(1004,'python',4),(1005,'javaweb开发',3);
/*!
40000ALTERTABLE`course`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`sc`
--
DROPTABLEIFEXISTS`sc`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`sc`(
`SCid`int(12)NOTNULLAUTO_INCREMENTCOMMENT'主键',
`Cno`int(10)NOTNULLCOMMENT'课程号',
`Sno`int(10)NOTNULLCOMMENT'学号',
`grade`tinyint
(2)NOTNULLCOMMENT'学生成绩',
PRIMARYKEY(`SCid`)
)ENGINE=InnoDBAUTO_INCREMENT=61DEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`sc`
--
LOCKTABLES`sc`WRITE;
/*!
40000ALTERTABLE`sc`DISABLEKEYS*/;
INSERTINTO`sc`VALUES(41,1001,1,4),(42,1002,1,3),(43,1003,1,1),(44,1004,1,6),(45,1001,2,3),(46,1002,2,2),(47,1003,2,2),(48,1004,2,8),(49,1001,3,4),(50,1002,3,4),(51,1003,3,2),(52,1004,3,8),(53,1001,4,1),(54,1002,4,1),(55,1003,4,2),(56,1004,4,3),(57,1001,5,5),(58,1002,5,3),(59,1003,5,2),(60,1004,5,9);
/*!
40000ALTERTABLE`sc`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`student`
--
DROPTABLEIFEXISTS`student`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`student`(
`Sno`int(10)NOTNULLCOMMENT'å¦å�·',
`Sname`varchar(16)NOTNULLCOMMENT'姓å��',
`Ssex`char(4)NOTNULLCOMMENT'性别',
`Sage`tinyint
(2)NOTNULLDEFAULT'0'COMMENT'å¦ç”Ÿå¹´é¾„',
`Sdept`varchar(32)DEFAULTNULLCOMMENT'å¦ç”Ÿæ‰€åœ¨ç³»åˆ«',
PRIMARYKEY(`Sno`),
KEY`index_Sname`(`Sname`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`student`
--
LOCKTABLES`student`WRITE;
/*!
40000ALTERTABLE`student`DISABLEKEYS*/;
INSERTINTO`student`VALUES(1,'宏志','男',30,'计算机网络'),(2,'王硕','男',30,'computerapplication'),(3,'oldboy','男',28,'物流管理'),(4,'脉动','男',29,'computerapplication'),(5,'oldgirl','女',26,'计算机科学与技术'),(6,'莹莹','女',22,'护士'),(10,'?
?
','?
',30,'?
?
'),(11,'傻逼二号','男',30,'国语'),(12,'?
?
','?
',22,'?
?
'),(13,'王八蛋','女',22,'游泳');
/*!
40000ALTERTABLE`student`ENABLEKEYS*/;
UNLOCKTABLES;
--
--Tablestructurefortable`test`
--
DROPTABLEIFEXISTS`test`;
/*!
40101SET@saved_cs_client=@@character_set_client*/;
/*!
40101SETcharacter_set_client=utf8*/;
CREATETABLE`test`(
`qq`int(16)NOTNULL,
`id`int(4)NOTNULL,
`name`char(20)NOTNULL,
`age`tinyint
(2)DEFAULTNULL,
`sex`char(4)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`index_name`(`name`)
)ENGINE=InnoDBDEFAULTCHARSET=latin1;
/*!
40101SETcharacter_set_client=@saved_cs_client*/;
--
--Dumpingdatafortable`test`
--
LOCKTABLES`test`WRITE;
/*!
40000ALTERTABLE`test`DISABLEKEYS*/;
INSERTINTO`test`VALUES(0,1,'oldboy',NULL,NULL),(0,2,'oldgirl',NULL,NULL),(0,3,'inca',NULL,NULL),(0,4,'zuma',NULL,NULL),(0,5,'kaka',NULL,NULL);
/*!
40000ALTERTABLE`test`ENABLEKEYS*/;
UNLOCKTABLES;
/*!
40103SETTIME_ZONE=@OLD_TIME_ZONE*/;
/*!
40101SETSQL_MODE=@OLD_SQL_MODE*/;
/*!
40014SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_K
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 备份 恢复 基础