mysqldump Backup and data restore

mysqldump It is recommended to use data below 10g for backup, which is a logical backup of MySQL database

mysqldump instruction-A all tables-all-databases-b--databases db1 DB2 ...: Back up the specified libraries while creating the library (cannot be used with A)-X lock table--lock-all-tables please To lock all tables and then back up, do Win Bei--single-transaction for MyISAM, InnoDB, Aria: The InnoDB storage engine can be hot prepared, (to determine that the table is InnoDB and the lock table cannot be used simultaneously)--flush-logs: Rolling logs before, after, and after the backup; Sync location tag when restoring with data and scrolling binary log copy:--master-data=[0|1|2] (0: Not logged) (1: Record as Change master statement) (2: Change recorded as comment) Master statement) Backup code:--events: Backup Event Scheduler Code--routines: Backup stored procedure and storage function--triggers: Backup trigger Recommendation: Turn off the binary log and close its it the user connects;

manual lock Table backup:

mysql> flush tables with read lock;mysql> flush logs;mysql>  show master status;+-------------------+----------+--------------+------------------+--------------- ----+| file              |  position | binlog_do_db | binlog_ignore_db | executed_gtid_set |+---------- ---------+----------+--------------+------------------+-------------------+| master-bin.000004 |       120 |               |                   |                    |+-------------------+----------+--------------+------------------+------------- ------+ #mysql > insert into t3  (Id,data)  values (2, ' n '); #ERROR  1223  (HY000):  can ' t  Execute the query because you have a conflicting read lock[[email  protected] ~]# mysqldump -hlocalhost -uroot -p  -B martin    > a.sqlmysql> unlock tables;

automatic backup:

[[Email protected] ~]# mysqldump -hlocalhost -uroot -p  -b martin   -x --flush-logs --master-data=2 > a.sql[[email protected] ~]#  cat a.sql -- change master to master_log_file= ' master-bin.000005 ',  master_log_pos=120,---- Current Database:  ' Martin '--create database /*!32312 if  NOT EXISTS*/  ' Martin '  /*!40100 DEFAULT CHARACTER SET latin1 */; use  ' Martin '; mysql> show master status;+-------------------+----------+--------------+- -----------------+-------------------+| file               | Position | Binlog_Do_DB | Binlog_Ignore_DB |  executed_gtid_set |+-------------------+----------+--------------+------------------+-------------------+ | master-bin.000005 |      120 |               |                   |                    |+-------------------+----------+--------------+------------------+------- ------------+

Restores:

Add data after a simulated backup:

mysql> create table if not exists  ' T1 '   (    ->     ' id '  varchar (+)  NOT NULL,    ->    ' Data '  text NOT NULL ,    ->    ' expire '  int (11)   NOT NULL DEFAULT  ' 0 ',    ->   primary  key  (' id ')     -> )  ENGINE=MyISAM DEFAULT CHARSET=utf8; query ok, 0 rows affected  (0.00 sec) mysql> insert into t1   (Id,data)  values (2, ' 22 '); query ok, 1 row affected  (0.00 sec) mysql> show master status;+ -------------------+----------+--------------+------------------+-------------------+| file               | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |+-------------------+----------+-------- ------+------------------+-------------------+| master-bin.000005 |       927 |              |                   |                    |+ -------------------+----------+--------------+------------------+-------------------+

data loss

mysql> drop database Martin; Query OK, 2 rows Affected (0.00 sec) [[email protected] local]# service mysqld stopshutting down MySQL. success!

View data:

[[email protected] local]# cd /mydata/mysql/log-bin/[[email protected] log-bin]#  mysqlbinlog --start-position=120   master-bin.000005/*!50530 set @ @SESSION . pseudo_slave_mode=1*/;/*!40019 set @ @session. max_insert_delayed_threads=0*/;/*!50003 set @ [email protected] @COMPLETION_TYPE, completion_type=0*/;D elimiter /*!*/;# at 4#160524  15:29:25 server id 1  end_log_pos 120 crc32 0xa1962627 start:  binlog v 4, server v 5.6.30-log created 160524 15:29:25binlog   ' 1qjevw8baaaadaaaahgaaaaaaaqans42ljmwlwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaezgnaagaegaebaqeegaax Aaegggaaaaicagcaaaacgokgrkaascmlqe= '/*!*/;# at 120#160524 15:32:29 server id 1   end_log_pos 249 crc32 0x74e33ea9 querythread_id=2exec_time=0error_code=0use& NBsP; ' Martin '/*!*/; set timestamp=1464075149/*!*/; set @ @session. pseudo_thread_id=2/*!*/; set @ @session. foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1,  @ @session. autocommit=1/*!*/; set @ @session. sql_mode=1075838976/*!*/; set @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;/*!\c latin1  *//*!*/; set @ @session. character_set_client=8,@ @session. collation_connection=8,@ @session. collation_server=8/*!*/; set @ @session. lc_time_names=0/*!*/; set @ @session. collation_database=default/*!*/;D rop table if exists  ' T1 '  /*  Generated by server *//*!*/;# at 249#160524 15:32:40 server id 1   end_log_pos 378 crc32 0x7ae6077a querythread_id=2exec_time=0error_code=0set  timestamp=1464075160/*!*/;D rop table if exists  ' T1 '  /* generated by  server *//*!*/;# at 378#160524 15:32:40 server id 1  end_log_pos 643  CRC32 0x10173878 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1464075160/*!*/; create table if not exists  ' T1 '   (   ' id '  varchar (+)  not  NULL,   ' data '  text NOT NULL ,   ' expire '  int (one)    not null default  ' 0 ',  primary key  (' id '))  engine=myisam default  charset=utf8/*!*/;# at 643#160524 15:32:54 server id 1  end_log_ pos 726 crc32 0xad187d7d querythread_id=2exec_time=0error_code=0set timestamp= 1464075174/*!*/; begin/*!*/;# at 726#160524 15:32:54 server id 1  end_log_pos 843  CRC32 0x861ba6b0 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1464075174/*!*/; Insert into t1  (Id,data)  values (2, ' $ ')/*!*/;# at 843#160524 15:32:54 server id  1  end_log_pos 927 crc32 0xa1a5bf4a querythread_id=2exec_time=0error_code= 0set timestamp=1464075174/*!*/; commit/*!*/;# at 927#160524 15:35:30 server id 1  end_log_pos  1025 crc32 0x77d2ec0e querythread_id=2exec_time=0error_code=0set timestamp=1464075330/*!*/ ;d rop database martin/*!*/;# at 1025#160524 15:37:18 server id 1   end_log_pos 1048 crc32 0xb1a3e339 stopdelimiter ;# end of log  fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET [email  protected]_completion_type*/;/*!50530 set @ @SESSION. pseudo_slave_mode=0*/;

Find at 927 before recovering data to drop

[Email protected] log-bin]# mysqlbinlog--start-position=120--stop-position=927 master-bin.000005>~/a.bin.sql

Connect MySQL: Unique connection guaranteed Data unique:

Mysql> Set Session sql_log_bin=0;mysql> source ~/a.sqlmysql> Source ~/a.bin.sqlmysql> set Session Sql_log_ Bin=1;mysql> Show tables;+------------------+| Tables_in_martin |+------------------+| T1 | | T3 |+------------------+

Over

mysqldump Backup and data restore