MySql backup and recovery, MySql backup and recovery

Source: Internet
Author: User
Tags mysql backup

MySql backup and recovery, MySql backup and recovery

1. mysql backup:

Query the database to be backed up:

  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)


Back up wordpress databases (single database backup ):

Syntax: mysqldump [OPTIONS] database [tables]

  1: mysqldump -u root -p wordpress > e:\temp\db_wordpress_bkp.sql
  2: Enter password: ****
Open e: \ temp \ db_wordpress_bkp. SQL to view the information about the backup database, table deletion, and table creation:
  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: 
Back up the test and MySQL databases (multiple databases ):
Syntax: mysqldump [OPTIONS] -- databases [OPTIONS] DB1 [DB2 DB3. ..]
  1: mysqldump -u root -p --databases test mysql > e:\temp\db_test_mysql.sql
  2: Enter password: ****

Back up all databases:
Syntax: mysqldump [OPTIONS] -- all-databases [OPTIONS]
  1: mysqldump -u root -p --all-databases > e:\temp\db_all_bkp.sql
  2: Enter password: ****
2. mysql recovery:
To recover a database, you must first create a database:
  1: mysql> create database wordpress;
  2: Query OK, 1 row affected (0.00 sec)
Then restore the database data from the backup:
  1: mysql -u root -p wordpress <E:\temp\db_wordpress_bkp.sql
  2: Enter password: ****

Back up the local database and restore it to a remote server:

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

-C indicates -- compress Use compression in server/client protocol.


In windows, how does one back up and restore Mysql?

Mysql is easy to operate. Find the MYSQL installation path, find the DATA folder, copy the corresponding database, and back up the database. After MYSQL is re-installed, create a database and paste the data in it. If you don't know how to buckle me

MySQL Backup Recovery Problems

When using the phpmyadmin backup time, you must remove the check box before the option Add if not exists so that your problem will NOT occur.
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.