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.