mysql常用的备份命令与备份策略

小灰灰 2022-06-15 00:41 141阅读 0赞

author:skate

time:2012/02/10

mysql常用的备份命令

mysqldump备份常用方法：

1.导出一个数据库  
    mysqldump -u用户名 -p密码 数据库名 > 导出的文件名  
    例如  
     \# mysqldump -hhostname -uroot -ppwd dataname >dataname.sql

2.备份服务器上所有数据库  
　　 例如  
      \# mysqldump --all-databases > allbackupfile.sql  
   
  3.备份一个表  
　　mysqldump -u用户名 -p密码 数据库名 表名> 导出的文件名  
    例如：  
　\# mysqldump -hhostname -uroot -ppwd dataname specific\_tabname1 specific\_tabname2> dataname\_specific\_tabname.sql

4.导出一个数据库结构  
    例如  
　　 \# mysqldump -hhostname -uroot -ppwd  -–add-drop-table --no-data dataname > dataname\_nodata.sql

--no-data:没有数据  
    –-add-drop-table 在每个create语句之前增加一个drop table命令

5.直接将MySQL数据库压缩备份  
    例如  
　　\# mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

6.同时备份多个MySQL数据库  
　　例如  
   mysqldump -hhostname -uusername -ppwd --databases dbname1 dbname2 dbname3 > multibackupfile.sql

7.对于支持事务级的存储引擎，为了保证数据一致性可以采用选项“--single-transaction”  
    例如  
   
 \# mysqldump -hlocalhost -uroot -p backup --single-transaction > /tmp/backup3.sql  
   
  8.导出特定格式的纯文本文件(生成两个.sql和txt两个文件)  
    例如：  
 mysqldump -root -p -T /tmp dbname tablename --fields-enclosed-by=\\" --fields-terminated-by=,  
       
   --fields-enclosed-by：将字段的内容包装起来的符号  
   --fields-terminated-by：两个字段的分隔符  
    
  如果一次dump多个表的话，每个表都会有sql和txt两个文件；这种特定格式比较适合备份  
   
  生成特定格式除了mysqldump外，还可以使用如下语句：  
  > select ... into outfile from ...  
  例如：  
     mysql> select \* into outfile '/tmp/t2.txt1'  
    -> fields terminated by  ','             
    -> optionally enclosed by '"'    
    -> lines terminated by '\\n'  
    -> from t2 limit 10;

optionally enclosed by：可以将字段的内容“包装”起来的，如果不使用“OPTIONALLY”则包括数字类型的所  
                                              有类型数据都会被“包装”，使用“OPTIONALLY”之后，则数字类型的数据不会被指 定字符“包装”。  
   lines terminated by：每条记录结束的时候需要添加什么字符

fields terminated by：设定每两个字段之间的分隔符    
          
          
    
mysqldump的逻辑备份恢复方法

1.如果是insert的语句，直接运行该脚本即可  
     方法一  
  \# mysql -hlocalhost -uroot -p backup < /tmp/t2.sql  
  当还原表时，一定要指明把表还原到哪个数据库，这里是backup库  
   
  方法二  
  在mysql里用source运行脚本  
  mysql> source /tmp/t2.sql  
   
   2. 如果备份是以特殊分隔符分隔的纯数据文本文件  
   方法一  
   把/tmp/t2.txt还原到库backup  
   \# mysqlimport -uroot -p backup --fields-enclosed-by=\\" --fields-terminated-by=, /tmp/t2.txt  
   
    方法二  
  mysql> load data infile '/tmp/t2.txt' into table t2 fields terminated by '"' enclosed by ',';

mysqlhotcopy的备份方法  
mysqlhotcopy 是一个用perl 编写的使用程序，其主要实现原理实际上就是通过先LOCK  
住表，然后执行FLUSH TABLES 动作，该正常关闭的表正常关闭，将该fsync 的数据都fsync，  
然后通过执行OS 级别的复制（cp 等）命令，将需要备份的表或者数据库的所有物理文件都  
复制到指定的备份集位置。

mysqlhotcopy db\_name\[./table\_regex/\] \[new\_db\_name | directory\]

例如：

复制数据库  
mysqlhotcopy  backup backup4

备份整库  
mysqlhotcopy  backup /tmp/

备份库的部分表  
mysqlhotcopy --allowold backup./t\*/ /tmp/   
   
   
mysqlhotcopy的恢复方法  
mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir目录下即可，  
同时要注意权限的问题，(将 db\_name 目录的属主改成 mysqld 运行用户)。此命令只能恢复数据库到最后  
一次备份时的状态。

mysql的不同场景备份策略

备份策略一：

直接拷贝数据库文件（不推荐），也称为冷物理备份，即要停止mysql数据库，然后copy物理文件，  
速度非常快，恢复也非常方便和快捷，直接把备份文件原样copy回来即可，但没有应用允许停库备份的。  
这里不存在数据一致性的问题，库都停了，当然可以保证一致性了；如果不停库的话，那就要锁住所有  
的表以保证数据的一致性  
     
      FLUSH TABLES WITH READ LOCK;  
   这个sql是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。  
     
    恢复：此方案的数据恢复也很简单，直接拷贝回原来的数据库目录下即可  
     
备份策略二：

使用mysqlhotcopy备份数据库（完全备份，适合小型数据库备份），这种情况对于数据一致性要求非常严的  
系统来对，就是一个无效的备份，因为没办法满足数据的一致完整性约束。它是备份数据库或单个表的最  
快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上，并  
且mysqlhotcopy 只能用于备份 MyISAM表。  
              
     恢复：此方案的数据恢复也很简单，直接拷贝回原来的数据库目录下即可

备份策略三：

使用mysqldump备份数据库（完全+增量备份，适合中型数据库备份），mysqldump 是采用SQL  
级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL版本之间升级时相对  
比较合适，这也是最常用的备份方法。mysqldump 比直接拷贝要慢些。对数据要求一致性非常严的  
也很难保证，只能通过增量备份保证（适用任何引擎)

恢复：此方案的恢复分为两步：

1.先恢复完全备份文件，使用类似如下命令：  
          mysql -hlocalhost -uroot -p backup < /tmp/t2.sql  
    2.恢复增量备份文件，按logbin的时间先后顺序执行  
          
        \#  ls -tr1 HOSTNAME-bin\* | xargs mysqlbinlog | mysql -uUser -pUserPWD

备份策略四：

使用主从复制机制(replication)（实现数据库实时备份,适合中大型数据库）,为了不影响线上业务，  
实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器  
上做备份。

\----------end-------