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 允许载入和刷新服务器缓存
以上几点是必须的.请注意