mysql的备份方法

冷备: 需要停止当前正在运行mysqld,然后直接拷贝或打包数据文件

cd /data  

tar cjvf /tmp/mysql_bak.tar.bz2 ./ 

热备(逻辑备份): mysqld必须是运行着，不需要停掉mysql服务,但需要锁表）： 逻辑备份＝表结构＋数据（插入)

# mysqldump -uroot -p456 test t1 t2> /tmp/t1.sql        --备份单个或多个表
# mysql -uroot -p123 test < /var/tmp/test_t1.sql            还原test库的t1,t2表

# mysqldump -uroot -p456 --databases test test1 test2 > /tmp/test.sql --备份多个库    

# mysqldump -uroot -p456 --all-databases > /tmp/all.sql    --备份所有库

还原:  
还原一个表:  
mysql> use test;
mysql> drop table t1;
# mysql -uroot -p456 test < /tmp/t1.sql 
# mysql -uroot -p456 -e "use test; show tables;" 
  
+----------------+  
| Tables_in_test |  
+----------------+  
| imptest |  
| t1 |  
+----------------+  

还原一个库:  
mysql> drop database test;
    
# mysql -uroot -p456  -e "create database test;"
# mysql -uroot -p456 test < /tmp/test.sql 
# mysql -uroot -p456 -e "show databases;"
  
+--------------------+  
| Database |  
+--------------------+  
| information_schema |  
| mysql |  
| test |  
  
## +--------------------+

每天4点做热备

0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 wiki|gzip >/opt/backup/wiki_$(date +%F).sql.gz
参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

 

增量备份: binlog --记录着mysql服务器增删改的操作记录. mysqlbinlog --start-datetime=name 开始的时间 --stop-datetime=name 结束的时间 --start-position=# 开始的位置(POS) --stop-position=# 结束的位置

为了方便,可以对blog做日志切割

查看binlog日志

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
参数解释：
IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] ：偏移量(不指定就是0)
row_count ：查询总条数(不指定就是所有行)
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       695 |
| mysql-bin.000002 |       106 |
+------------------+-----------+

mysql>  show binlog events in 'mysql-bin.000001'\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.73-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 106
 Event_type: Query
  Server_id: 1
End_log_pos: 244
       Info: SET PASSWORD FOR 'root'@'localhost'='*22DF0FE262826C66C3A1E8BC8DC2AF2259B42F46'
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 244
 Event_type: Query
  Server_id: 1
End_log_pos: 359
       Info: CREATE USER 'slave'@'172.16.147.%' IDENTIFIED BY 'slave'
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
        Pos: 359
 Event_type: Query
  Server_id: 1
End_log_pos: 510
       Info: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.147.%'
*************************** 5. row ***************************
   Log_name: mysql-bin.000001
        Pos: 510
 Event_type: Query
  Server_id: 1
End_log_pos: 585
       Info: flush privileges
*************************** 6. row ***************************
   Log_name: mysql-bin.000001
        Pos: 585
 Event_type: Query
  Server_id: 1
End_log_pos: 695
       Info: create database arrow charset utf8
6 rows in set (0.00 sec)

用binlog日志恢复

mysql> flush logs;  #生成新的binlog日志
  
# mysqlbinlog --start-position=190 --stop-position=833 mysqld.000001|mysql -uroot -p147258369  
  
# mysqlbinlog --start-position=1554 --stop-datetime="2011-09-02 11:48:10" mysqld.000001 | mysql -uroot -p147258369 

# /usr/bin/mysqlbinlog --stop-position=391 --database=wiki /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p'147258369' -v wiki
  
## 106-280