利用mysqldump 实现每天备份方案

1.创建单独的导出导入数据库帐号

    grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on game to jackluo@localhost identified by 'jackluo';
    grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on platform to jackluo@localhost identified by 'jackluo';
    grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on game to jackluo@192.168.10.56 identified by 'jackluo';
    grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on platform to jackluo@192.168.10.56 identified by 'jackluo';

2.要执行的mysqlbackup.sh

#!/bin/bash
############################
# backup mysql             #
# author jackluo           #
# createtime 2013-9-16     #    
############################

db_user=jack #备份mysql用户名
db_password=test #备份mysql密码
db_host=192.168.10.56 #备份mysql
local_user=root #本地数据库
local_password=admin #本地数据库
gamedatadb=$(date +%Y%m%d)"-game.sql" #临时数据库
platformdatadb=$(date +%Y%m%d)"-plat.sql" #临时数据库
#mysqldump -u$db_user -h$db_host -p$db_password --all-databases > $datafile
mysqldump -u$db_user -h$db_host -p$db_password game > $gamedatadb # backup game #导出数据
mysqldump -u$db_user -h$db_host -p$db_password platform > $platformdatadb # backup platform
mysql -u${local_user} -p${local_password} game< $gamedatadb #导入本地数据库
mysql -u${local_user} -p${local_password} platform< $platformdatadb #导入本地数据库
rm -rf $gamedatadb #删除临时文件
rm -rf $platformdatadb #删除临时文件

3.写计划任务:

       编辑/etc/crontab 文件配置cron

02 3 * * * /home/wwwroot/sh/mysqlbackup.sh 

然后，就ＯＫ了

简单说一下，这种只适合小型的，大公司都是主从的

附录:

         datadbblack 192.168.1.10 是 SELECT, RELOAD, SHOW DATABASES, LOCK TABLES  否

 

权限列表

 

1.Select  读取

 

2.SHOW DATABASES 允许访问完整的数据库列表

 

4. LOCK TABLES 允许锁定表

 

5.RELOAD 允许载入和刷新服务器缓存

 

以上几点是必须的.请注意