MySql backup and recovery, MySql backup and recovery

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.