Sinsing interpretation using mysqldump for hot backup

The importance of backups is self-evident, especially for database administrators, where backups are especially important. Backup has a lot of different methods, and here I am not very much like to tell them all, here is a general talk about hot and cold backup. If you turn off the service, you can do cold backup, if it is the MyISAM engine, then directly back up the DataDir inside the data files can, once there is a disaster, directly recover the data can, InnoDB words need to deal with a little more things, such as ibdata files, *. frm file Box my.cnf (under Windows is My.ini), set Innodb_data_file_path to make the original Ibdata path.

The above simple introduction of the cold backup, say again hot backup, if we go to search for hot backup, most of the search is the master-slave backup and dual-machine backup, I admit that these technologies are more important, but we are here to introduce the local backup, for dual-machine backup and master-slave backup, we will introduce later.

For the hot Backup tool, we can use the MySQL mysqldump, of course, but also with the help of third-party backup tools such as Xtrabackup, this special backup tool is much more useful, but we still first understand the next mysqldump. We use mysqldump for backup, it will lock the table, our application cannot write to the database, if our MyISAM table is more, we use mysqldump is also a very good choice, below we see how this command should be used:

C:\users\administrator>mysqldump-u root-proot mysql > D:\my.sqlmysqldump: [Warning] Using a password on the Comman D line interface can insecure. C:\users\administrator>

Here I explain, this mysqldump is this command, here we still need to use user and password, otherwise we will report the following error:

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 us for insufficient permissions and cannot be manipulated. We used the first command in the mysqldump after using the root user to back up, we back up the MySQL database, and then use > to write data to the D disk under the My.sql, where > is the meaning of redirection, and is overwritten write, I want to have Linux based on this should understand it.

Then we will find that there is a my.sql file in the D-disk, of course, I have a lot of content here, a full 562KB, below I just excerpt its first few lines of content, to show you what we exported are what things:

--MySQL dump 10.13 distrib 5.7.3-m13, for Win64 (x86_64)----host:localhost Database:mysql------------------------ ----------------------------------Server version5.7.3-m13/*!40101 SET @[email protected] @CHARACTER_SET_ CLIENT */;/*!40101 set @[email protected] @CHARACTER_SET_RESULTS */;/*!40101 set @[email protected]@ Collation_connection */;/*!40101 set NAMES UTF8 */;/*!40103 set @[email protected] @TIME_ZONE */;/*!40103 set TIME_ Zone= ' +00:00 ' */;/*!40014 set @[email protected] @UNIQUE_CHECKS, unique_checks=0 */;/*!40014 set @[email  Protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;/*!40101 SET @[email protected] @SQL_MODE, sql_mode= ' No_ Auto_value_on_zero ' */;/*!40111 SET @[email protected] @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 COLLATE utf8_bin NOT null default ' ', ' Db ' char (UP) COLLATE utf8_bin NOT null default ' ', ' User ' char (+) COLL ATE utf8_bin NOT null default ' ', ' table_name ' char (+) COLLATE utf8_bin NOT null default ' ', ' column_name ' char (+) COL  Late 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 export is not pure data, but some SQL statements, but also with comments.

Of course, we like to knock the command line know, it will generally be able to follow several parameters, yes, we directly knock mysqldump and then 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>

Here I am afraid that people understand is not very clear, just to explain it, the first is that we can export a database, you can also export a database under the table, the second is that we can export n multiple databases, but need to add--databases to explain, the third one directly export all databases.

Since there is a backup, there will be recovery, we use the MySQL command, such as I create a database, it uses the data in the files we backed up, we look at the following operation, I will not be a single demo, here Our database an has got our backup data, by the way, This database an must be built in advance and does not have the ability to create a new database yourself:

C:\users\administrator>mysql-u Root-proot an < D:\my.sqlmysql: [Warning] Using a password on the command line inte Rface can be insecure. C:\users\administrator>mysql-u root-prootmysql: [Warning] Using a password on the command line interface can be insec Ure.  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) #, Oracl e and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names 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>

Sinsing interpretation using mysqldump for hot backup