MySql備份和恢復，MySql備份恢復

MySql備份和恢復，MySql備份恢復

1.mysql備份:

查詢出需要備份的數據庫:

  1: mysql -u root -psywu
  2: 
  3: mysql> show databases;
  4: +--------------------+
  5: | Database           |
  6: +--------------------+
  7: | information_schema |
  8: | cdcol              |
  9: | mysql              |
 10: | performance_schema |
 11: | phpmyadmin         |
 12: | test               |
 13: | webauth            |
 14: | wordpress          |
 15: +--------------------+
 16: 8 rows in set (0.00 sec)

備份wordpress數據庫(單個數據庫備份):

語法: mysqldump [OPTIONS] database [tables]

  1: mysqldump -u root -p wordpress > e:\temp\db_wordpress_bkp.sql
  2: Enter password: ****

打開e:\temp\db_wordpress_bkp.sql 可以看到關於備份的數據庫、刪除表和建表的信息:

  1: -- MySQL dump 10.13  Distrib 5.6.20, for Win32 (x86)
  2: --
  3: -- Host: localhost    Database: wordpress
  4: -- ------------------------------------------------------
  5: -- Server version5.6.20
  6: 
  7: /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8: /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  9: /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 10: /*!40101 SET NAMES utf8 */;
 11: /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 12: /*!40103 SET TIME_ZONE='+00:00' */;
 13: /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 14: /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 15: /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 16: /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 17: 
 18: --
 19: -- Table structure for table `wp_commentmeta`
 20: --
 21: 
 22: DROP TABLE IF EXISTS `wp_commentmeta`;
 23: /*!40101 SET @saved_cs_client     = @@character_set_client */;
 24: /*!40101 SET character_set_client = utf8 */;
 25: CREATE TABLE `wp_commentmeta` (
 26:   `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 27:   `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 28:   `meta_key` varchar(255) DEFAULT NULL,
 29:   `meta_value` longtext,
 30:   PRIMARY KEY (`meta_id`),
 31:   KEY `comment_id` (`comment_id`),
 32:   KEY `meta_key` (`meta_key`)
 33: ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 34: /*!40101 SET character_set_client = @saved_cs_client */;
 35: 

備份test和MySQL數據庫(多個數據庫):
語法:mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

  1: mysqldump -u root -p --databases test mysql > e:\temp\db_test_mysql.sql
  2: Enter password: ****

備份所有數據庫:
語法:mysqldump [OPTIONS] --all-databases [OPTIONS]

  1: mysqldump -u root -p --all-databases > e:\temp\db_all_bkp.sql
  2: Enter password: ****

2.mysql恢復:
恢復數據庫需要先創建數據庫:

  1: mysql> create database wordpress;
  2: Query OK, 1 row affected (0.00 sec)

然後從備份中還原數據庫數據:

  1: mysql -u root -p wordpress <E:\temp\db_wordpress_bkp.sql
  2: Enter password: ****

備份本地數據庫並恢復到遠程服務器上:

  1: mysql -u root -ps wordpress | mysql -u root -psss --host=remote_server -C wordpress

-C表示 --compress      Use compression in server/client protocol.

windows下Mysql 怎備份與還原？

mysql很容易操作的。直接找到MYSQL安裝路徑，找到DATA文件夾，把裡面的對應數據庫復制一下，備份好就行了。MYSQL重新安裝後建立相應的數據庫，把裡面的數據粘貼進去就OK了，不懂可以扣扣我
 

MySQL備份恢復的問題

在使用phpmyadmin備份時間，要注意把選項Add IF NOT EXISTS前的勾去掉，這樣就不會出現你的問題了。