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