mysql数据库的备份恢复
mysqldump备份数据库
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
-e, --extended-insert
Use multiple-row INSERT syntax that include several
VALUES lists. 多行插入数据
为了保证数据的一致性，我们要把表锁起来在dump
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.
-l, --lock-tables Lock all tables for read.
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Suppress the SET NAMES statement
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
-q, --quick Don't buffer query, dump directly to stdout. 不缓存
-R, --routines Dump stored routines (functions and procedures).
--single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
--single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off --lock-tables.
--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
实践之非事务性一直备份(备份期间数据库不可写)
mysql> use test
mysql> create table tt(id int,name varchar(12));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into tt values(1,'zz');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt values(2,'yy');
Query OK, 1 row affected (0.00 sec)
[root@test4 Desktop]# mysqldump --databases test --skip-opt --quick --extended-insert=false --lock-all-tables --master-data=2 -u root -p123456 > /tmp/test.sql
这就是dump的结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | |
由于我们dump的时候用了master-data参数，这个时候记录了日志位置和日志文件名
-- CHANGE MASTER TO MASTER_LOG_FILE='mysqlbin.000166', MASTER_LOG_POS=798;
实践之事务性一直备份（备份期间数据库可写）
mysql> use test
mysql> create table tt(id int,name varchar(12)) engine=innodb ;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into tt values(1,'zz');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt values(2,'yy');
Query OK, 1 row affected (0.00 sec)
[root@test4 Desktop]# mysqldump --databases test --skip-opt --quick --extended-insert=false --single-transaction
mysql的备份和恢复的完整实践
一,备份数据库之间的环境设置
1，创建数据库test1，创建表tt插入如下数据
1 2 3 4 5 6 7 8 9 10 11 12 | |
2，由于我设置的二进制日志文件的记录格式是row，所以每一行的数据改变就会记录一次日志
mysql>showvariableslike"%format%"
->;
+---------------------+-------------------+
|Variable_name|Value|
+---------------------+-------------------+
|binlog_format|ROW|
3，此时只有一个二进制日志文件
mysql>showbinarylogs;
+-----------------+-----------+
|Log_name|File_size|
+-----------------+-----------+
|mysqlbin.000161|1133|
+-----------------+-----------+
1rowinset(0.00sec)
4，查看二进制日志文件的内容
二进制日志文件end_log_pos1133
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | |
二，备份数据库test1
1，mysqldump备份数据库
[root@test4~]#mysqldump--databasestest1--skip-opt--quick--extended-insert=false--lock-all-tables--master-data=2-uroot-p123456>/tmp/test1.sql
2，查看备份文件
我们发现这个时候记录的开始位置正好是1133，如下就是证明
CHANGEMASTERTOMASTER_LOG_FILE='mysqlbin.000161',MASTER_LOG_POS=1133;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | |
三，对表进行修改插入数据然后误删表
由于我们不小心把表tt给删除了，那么我们就要把表tt通过二进制日志给恢复过来
mysql>insertintottvalues(4,'shuijunyi','boss');
QueryOK,1rowaffected(0.01sec)
mysql>insertintottvalues(5,'zhujun','mayIknowyourname');
QueryOK,1rowaffected(0.00sec)
mysql>select*fromtt;
+------+---------------+----------------------+
|id|name|msg|
+------+---------------+----------------------+
|1|chenzhongyang|howareyou|
|2|tianhongyan|BMW|
|3|jisuanji|why|
|4|shuijunyi|boss|
|5|zhujun|mayIknowyourname|
+------+---------------+----------------------+
5rowsinset(0.01sec)
mysql>droptablett;
QueryOK,0rowsaffected(0.00sec)
四，查看执行误操作的位置
通过showmasterstatus;可以查看当前的二进制日志文件的位置
mysql>showmasterstatus;
+-----------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+-----------------+----------+--------------+------------------+
|mysqlbin.000161|1622|||
+-----------------+----------+--------------+------------------+
1rowinset(0.00sec)
我们可以看到droptablett的开始位置是1622所以只需要恢复到1622的位置就可以恢复误删除的表tt
1 2 3 4 5 6 7 8 9 10 11 12 | |
五，还原数据库
这个时候我们发现只恢复了三条数据，但是我们一共有五条数据，这个时候就要通过二进制日志文件来恢复了。
注意的是当我们在恢复数据库的时候也会产生二进制日志文件，所以一定要分清楚备份前的二进制日志文件和恢复之后的二进制日志文件
mysql>dropdatabasetest1;
QueryOK,0rowsaffected(0.00sec)
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|test|
+--------------------+
3rowsinset(0.00sec)
[root@test4~]#mysql-uroot-p123456</tmp/test1.sql
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|test|
|test1|
+--------------------+
4rowsinset(0.00sec)
mysql>usetest1
Databasechanged
mysql>select*fromtt;
+------+---------------+-------------+
|id|name|msg|
+------+---------------+-------------+
|1|chenzhongyang|howareyou|
|2|tianhongyan|BMW|
|3|jisuanji|why|
+------+---------------+-------------+
3rowsinset(0.00sec)
六，恢复到误操作之前恢复其他的两条数据
这是时候恢复就要从开始备份的位置到删除表位置。因为这个位置是插入另外两条数据的位置
我们可以很清楚的看到这两条数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | |
正式开始恢复数据
[root@test4~]#mysqlbinlog--start-position=1133--stop-position=1622-vv/tmp/mysqlbin.000161|mysql-uroot-p123456
这个时候数据就回来了
mysql>select*fromtt;
+------+---------------+----------------------+
|id|name|msg|
+------+---------------+----------------------+
|1|chenzhongyang|howareyou|
|2|tianhongyan|BMW|
|3|jisuanji|why|
|4|shuijunyi|boss|
|5|zhujun|mayIknowyourname|
+------+---------------+----------------------+
5rowsinset(0.00sec)
from: http://wolfword.blog.51cto.com/4892126/1289596
