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