Xin Xing uses mysqldump for Hot Backup and Xin Xing mysqldump

Xin Xing uses mysqldump for Hot Backup and Xin Xing mysqldump

The importance of backup is self-evident, especially for database administrators. There are many methods to back up data. I am not talking about them all here. Here I will talk about hot backup and cold backup. If the service is disabled, you can perform cold backup. If the MyISAM engine is used, you can directly back up the data files in datadir. Once a disaster occurs, you can directly recover the data, innoDB requires a little more processing, such as ibdata files and ,*. frm file box my. cnf (in windows, it is my. ini), set innodb_data_file_path to specify the original ibdata path.

The above section briefly introduces cold backup and hot backup. If you search for hot backup, most of the information found is master-slave backup and dual-host backup. I admit that these technologies are important. However, we will introduce local backup. for dual-host backup and master-slave backup, we will introduce it later.

For Hot Backup tools, we can use mysqldump that comes with mysql. Of course, we can also use xtrabackup and other third-party backup tools. This dedicated backup tool is much easier to use, however, let's take a look at mysqldump first. When we use mysqldump for backup, it locks the table, and our applications cannot write data to the database. If there are many MyISAM tables, using mysqldump is also a good choice. Let's take a look at how to use this command:

C:\Users\Administrator>mysqldump -u root -proot mysql > D:\my.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.C:\Users\Administrator>

Here I will explain it. This mysqldump is the command. Here we still need to use the user and password. Otherwise, the following error will be reported:

C:\Users\Administrator>mysqldump  mysql > D:\an.sqlmysqldump: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

It will prompt that we are not authorized to perform the operation. We used mysqldump In the first command and then used the root user to back up the database. We backed up the mysql database and then used> to write data to my. in SQL, Here> is the meaning of redirection, And it is overwrite. I think this should be understood in Linux.

Then we will find that there is an extra my under drive D. for SQL files, of course, I have a lot of content here, which is KB. Below I will just excerpt the first few lines of content from it to show you what we export:

-- MySQL dump 10.13  Distrib 5.7.3-m13, for Win64 (x86_64)---- Host: localhost    Database: mysql-- -------------------------------------------------------- Server version5.7.3-m13/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `columns_priv`--DROP TABLE IF EXISTS `columns_priv`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `columns_priv` (  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';/*!40101 SET character_set_client = @saved_cs_client */;

We found that the exported data is not pure data, but some SQL statements with annotations.

Of course, all of us who like to knock on the command line know that it can be followed by several parameters. Yes, we can directly press mysqldump and then press enter to see its common usage:

C:\Users\Administrator>mysqldumpUsage: mysqldump [OPTIONS] database [tables]OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]OR     mysqldump [OPTIONS] --all-databases [OPTIONS]For more options, use mysqldump --helpC:\Users\Administrator>

I am afraid you may not understand it clearly. Let's explain it in detail. The first one is that we can export a database or a table in a database, the second is that we can export n multiple databases, but we need to add -- databases to describe, and the third is to export all databases directly.

Since there is a backup, there will be recovery. We can use the mysql Command. For example, if I create an database and it uses the data in the backup files, let's take a look at the following operations, I won't explain it all. Here, our database an gets our backup data. By the way, this database an must be created in advance, and it does not have the ability to create a new database itself:

C:\Users\Administrator>mysql -u root -proot an < D:\my.sqlmysql: [Warning] Using a password on the command line interface can be insecure.C:\Users\Administrator>mysql -u root -prootmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 124Server version: 5.7.3-m13 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use anDatabase changedmysql> show tables;+---------------------------+| Tables_in_an              |+---------------------------+| columns_priv              || db                        || event                     || func                      || general_log               || help_category             || help_keyword              || help_relation             || help_topic                || innodb_index_stats        || innodb_table_stats        || ndb_binlog_index          || plugin                    || proc                      || procs_priv                || proxies_priv              || servers                   || slave_master_info         || slave_relay_log_info      || slave_worker_info         || slow_log                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+28 rows in set (0.00 sec)mysql>