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:\\db_wordpress_bkp. 可以看到关于备份的数据库、删除表和建表的信息:

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 

备份本地数据库并恢复到远程服务器上:

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

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