--use mysqldump backup
the mysqldump cient is a backup program originally written by Igor ROMANENKO.It can be used
to dump a database or collection of databases for backup or for transferring the data to another SQl server(not
necessarily a Mysql server). The dump contains sql statements to create the table or populate it,or both.
The mysqldump client program dumps table contents to files. It has the following characteristice:
  1.it can dump all databases,sepcific database,or specific tables.
  2.mysqldump can backup local or remote servers,although the destinaiong for the dump files depentds
   on how you invoke it .For tab-delimited data files made using the --tab option,the server writes tem on
   server host.For sql-format dump files that contain CREATE TABLE and INSERTstatements for
   recreating the tables,the server sends table contents to mysqldump,which writes the files on the client host.
  3.It works for tables created by any storage engine.
  4.output files are written in text format and are portable,so they can be used for transferring database contents to another server.

  mysql> show master status;
+--------------------+----------+--------------+------------------+-------------
------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gti
d_Set |
+--------------------+----------+--------------+------------------+-------------
------+
| dsgtest-bin.000011 |     1106 |              |                  |
      |
+--------------------+----------+--------------+------------------+-------------
------+
1 row in set (0.00 sec)

非事物表的一致备份
mysqldump --skip-opt --quick --extended-insert=false --lock-all-tables --master-data=2 --databases world --user=root -pmysql>./1.sql

事物表的一致备份
mysqldump --skip-opt --quick --extended-insert=false --single-transaction --master-data=2 --databases test --user=root -pmysql>2.sql

备份恢复测试
mysql> select count(*) from test3;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.02 sec)

mysql> select * from test3;
+---------------------+----+------+
| birthday | id | name |
+---------------------+----+------+
| 2013-11-20 00:00:00 | 1 | ylc |
+---------------------+----+------+
1 row in set (0.00 sec)

mysql> insert into test3(id,name) values('2','ylc');
Query OK, 1 row affected (0.03 sec)

mysql> select * from test3;
+---------------------+----+------+
| birthday | id | name |
+---------------------+----+------+
| 2013-11-20 00:00:00 | 1 | ylc |
| NULL | 2 | ylc |
+---------------------+----+------+
2 rows in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.11 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)

mysql> show binlog events in 'dsgtest-bin.000001';
+--------------------+-----+-------------+-----------+-------------+------------
----------------------------------------------+
| Log_name           | Pos | Event_type | Server_id | End_log_pos | Info
                                              |
+--------------------+-----+-------------+-----------+-------------+------------
----------------------------------------------+
| dsgtest-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver:
5.6.14-log, Binlog ver: 4                    |
| dsgtest-bin.000001 | 120 | Query       |         1 |         199 | BEGIN
                                              |
| dsgtest-bin.000001 | 199 | Query       |         1 |         317 | use `test`;
insert into test3(id,name) values('2','ylc') |
| dsgtest-bin.000001 | 317 | Xid         |         1 |         348 | COMMIT /* x
id=2686 */                                    |
| dsgtest-bin.000001 | 348 | Query       |         1 |         465 | use `test`;
DROP TABLE `test` /* generated by server */ |
| dsgtest-bin.000001 | 465 | Rotate      |         1 |         514 | dsgtest-bin
.000002;pos=4                                 |
+--------------------+-----+-------------+-----------+-------------+------------
----------------------------------------------+
6 rows in set (0.00 sec)

mysql>

grep "CHANGE MASTER" ./2.sql

mysql> drop database test;
ERROR 1010 (HY000): Error dropping database (can't rmdir '.\test\', errno: 17)
--test 目录下有别的文件直接干掉就能执行成功

--还原
C:\mysql\MySQL Server 5.6\bin>mysql --user=root -pmysql<./2.sql

mysql> select * from test3;
+---------------------+----+------+
| birthday | id | name |
+---------------------+----+------+
| 2013-11-20 00:00:00 | 1 | ylc |
+---------------------+----+------+
1 row in set (0.00 sec)

C:\mysql\MySQL Server 5.6\bin>mysqlbinlog.exe --start-position=4 --stop-position
=348 -vv ../data/dsgtest-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#131219 14:19:32 server id 1 end_log_pos 120 CRC32 0x00c81f25 Start: binlog v
4, server v 5.6.14-log created 131219 14:19:32 at startup
ROLLBACK/*!*/;
BINLOG '
9I+yUg8BAAAAdAAAAHgAAAAAAAQANS42LjE0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAD0j7JSEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAASUf
yAA=
'/*!*/;
# at 120
#131219 14:22:43 server id 1 end_log_pos 199 CRC32 0x21e87a61 Query   thread_i
d=61    exec_time=0     error_code=0
SET TIMESTAMP=1387434163/*!*/;
SET @@session.pseudo_thread_id=61/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.uniq
ue_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/
;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#131219 14:22:43 server id 1 end_log_pos 317 CRC32 0xd4f44e36 Query   thread_i
d=61    exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1387434163/*!*/;
insert into test3(id,name) values('2','ylc')
/*!*/;
# at 317
#131219 14:22:43 server id 1 end_log_pos 348 CRC32 0x7b6f3365 Xid = 2686
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

C:\mysql\MySQL Server 5.6\bin>mysqlbinlog.exe --start-position=4 --stop-position
=348 -vv ../data/dsgtest-bin.000001|mysql --user=root -pmysql
Warning: Using a password on the command line interface can be insecure.

mysql> select * from test3;
+---------------------+----+------+
| birthday | id | name |
+---------------------+----+------+
| 2013-11-20 00:00:00 | 1 | ylc |
| NULL | 2 | ylc |
+---------------------+----+------+
2 rows in set (0.00 sec)

ITPub博客

mysql备份（一）

最新文章