be备份mysql_mysql备份（一）

--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> show binary logs;

+--------------------+-----------+

| Log_name           | File_size |

+--------------------+-----------+

| dsgtest-bin.000001 |       465 |

+--------------------+-----------+

1 row in set (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.13 sec)

mysql> show binary logs;

+--------------------+-----------+

| Log_name           | File_size |

+--------------------+-----------+

| dsgtest-bin.000001 |       514 |

| dsgtest-bin.000002 |       120 |

+--------------------+-----------+

2 rows in set (0.00 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 -pmysqlmysql> 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)