Mysql数据库的备份与恢复方法
发布时间：2022-01-19 13:27:10 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍Mysql数据库的备份与恢复方法，在日常操作中，相信很多人在Mysql数据库的备份与恢复方法问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答Mysql数据库的备份与恢复方法的疑惑有所帮助！接下来，请跟着小编一起
|
这篇文章主要介绍“Mysql数据库的备份与恢复方法”，在日常操作中，相信很多人在Mysql数据库的备份与恢复方法问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”Mysql数据库的备份与恢复方法”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！ 1.备份cancer数据库 ``` mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | cancer | | cancer_utf8 | | performance_schema | | test | +--------------------+ 6 rows in set (0.05 sec) ``` 2.备份的方向符号为(>) ``` [root@mysql 3306]# mysqldump -uroot -p -S /data/3306/mysql.sock cancer >/opt/cancer_bak.sql Enter password: ``` 3.查看备份的语句 ``` [root@mysql 3306]# egrep -v "#|*|--|^$" /opt/cancer_bak.sql DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `names` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; LOCK TABLES `student` WRITE; INSERT INTO `student` VALUES (1,'???'),(2,'è???”·?-?'); UNLOCK TABLES; ``` 4.备份恢复的方向符号为(<) ``` [root@mysql 3306]# mysql -uroot -p -S /data/3306/mysql.sock cancer </opt/cancer_bak.sql ``` 加-B的备份与恢复 1.备份 ``` [root@mysql 3306]# mysqldump -uroot -p -S /data/3306/mysql.sock -B cancer >/opt/cancer_bak_B.sql Enter password: [root@mysql 3306]# cd /opt [root@mysql opt]# diff cancer_bak.sql cancer_bak_B.sql 18a19,26 > -- Current Database: `cancer` > -- > > CREATE DATABASE /*!32312 IF NOT EXISTS*/ `cancer` /*!40100 DEFAULT CHARACTER SET latin1 */; > > USE `cancer`; > > -- 51c59 < -- Dump completed on 2015-08-16 16:16:33 --- > -- Dump completed on 2015-08-16 16:22:32 ``` 2.恢复 ``` [root@mysql opt]# mysql -uroot -p -S /data/3306/mysql.sock </opt/cancer_bak_B.sql ``` 3.压缩备份 ``` [root@mysql opt]# mysqldump -uroot -p -S /data/3306/mysql.sock -B cancer|gzip >/opt/cancer_bak_gzip.sql.gz Enter password: ``` 4.备份多个库 ``` [root@mysql opt]# mysqldump -uroot -p -S /data/3306/mysql.sock -B cancer cancer_utf8 >/opt/cancer_mul_bak Enter password: ``` 5.多库备份脚本 ``` [root@mysql opt]# mysql -uroot -p -S /data/3306/mysql.sock -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -pcancer -S /data/3306/mysql.sock -B --events 1|gzip >/opt/1.sql.gz#g'|bash Enter password: ``` 脚本的显示内容 ``` mysqldump -uroot -pcancer -S /data/3306/mysql.sock -B mysql|gzip >/opt/mysql.sql.gz mysqldump -uroot -pcancer -S /data/3306/mysql.sock -B cancer|gzip >/opt/cancer.sql.gz mysqldump -uroot -pcancer -S /data/3306/mysql.sock -B cancer_utf8|gzip >/opt/cancer_utf8.sql.gz mysqldump -uroot -pcancer -S /data/3306/mysql.sock -B test|gzip >/opt/test.sql.gz ``` 1.备份具体某个库中的某个表 ``` [root@mysql ~]# mysqldump -uroot -p -S /data/3306/mysql.sock cancer student test Enter password: /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `names` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; INSERT INTO `student` VALUES (1,'???'),(2,'è???”·?-?'); /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; ``` 2.只备份表结构 ``` [root@mysql ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -d cancer Enter password: /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `names` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; ``` ``` [root@mysql ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -d cancer student Enter password: /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `student` ( `id` int(4) NOT NULL AUTO_INCREMENT, `names` char(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; ``` 3.只备份数据 ``` [root@mysql ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -t cancer student Enter password: INSERT INTO `student` VALUES (1,'???'),(2,'è???”·?-?'); ``` 到此，关于“Mysql数据库的备份与恢复方法”的学习就结束了，希望能够解决大家的疑惑。 （编辑：91站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
