一、手动本机备份
cd /usr/local/mysql/data # 进入到数据据目录下
mysqldump -uroot -p123456 -R dbname | gzip > dbnameyyyyymmdd.bak # 压缩备份
二、每天定时备份
1、备份脚本
#!/bin/bash
export PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
## db user name
USER=-uroot
## db user passworld
PASSWORLD=-p123456
## mysqldump path
MYSQLDUMP="mysqldump"
## backup file path
BAKPATH="/usr/local/mysql/data/bak"
## backup file name
TIME=$(date ‘+%Y%m%d-%H%M‘)
## delete the oldest backup 3 days ago;
cd $BAKPATH
find $BAKPATH -mtime +3 -name "*.sql" -exec rm -rf {} \;
## startup to BACKUP wushen databases, and write to log file;
echo "------------------start up-------------------------" >> $BAKPATH/db_backup.log
echo "BACKUP DATE: " $(date ‘+%Y-%m-%d %H:%M:%S‘) >> $BAKPATH/db_backup.log
$MYSQLDUMP $USER $PASSWORLD -R --opt ‘rxjh‘ > $BAKPATH/rxjh_S0_$TIME.sql
## check dump file successed or Failed;
if [[ $? == 0 ]]; then
echo "dump file ok--Successed!!" >> $BAKPATH/db_backup.log
else
echo "dump file Failed!!" >> $BAKPATH/db_backup.log
fi
echo "-------------------The end------------------------" >> $BAKPATH/db_backup.log
## chown && chmod
chown -R root:root $BAKPATH
chmod -R 777 $BAKPATH
exit 0
2、增加到定时任务中
vim /etc/crontab
1 4 * * * root /usr/local/mysql/data/bakdb.sh
3、配置密钥登录（备份放异才需要）
# 生成公钥和私钥，全部直接回车 ssh-keygen # 公钥传到目标服 scp /root/.shh/id_dsa.pub root@192.168.0.121:/root/.shh/ # 登录目标服进行设置 ssh root@192.168.0.121 # 允许PubkeyAuthentication认证 # 找到#PubkeyAuthentication yes并把注释去掉, 找到#AuthorizedKeysFile ~/.ssh/authorized_keys，去掉注释 vim /etc/ssh/sshd_config # 重启ssh service ssh stop service ssh start
三、主从机实时备份
1、主数据库配置
a、创建复制帐号
grant replication slave on *.* to slave@‘192.168.0.55‘ identified by ‘123456‘;
b、编辑配置文件/etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
binlog-ignore-db=不需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
c、重启数据库
d、查看主数据库的状态mysql>show master status;
e、其它指令 mysql>show processlist;
mysql>show slave hosts;
mysql>show variables like ‘server_id’;
2、从数据库配置
a、编辑配置文件/etc/my.cnf
log-bin=mysql-bin
server-id=2
master-host=主机
master-user=用户名
master-password=密码
master-port=端口
replicate-do-db=需要备份的数据库名，如果备份多个数据库，重复设置这个选项即可
Mysql版本从5.1.7以后开始就不支持“master-host”类似的参数，需要动态设置，用户名的密码都会存储在文本文件master.info
b、重启数据库
c、其它指令
mysql>start slave #启动同步线程 mysql>stop slave mysql>change master to master_host=‘192.168.0.166‘, master_user=‘rep1‘, master_password=’123456’, master_log_file=‘mysql-bin.000001‘, master_log_pos=120; # 动态修改master信息 mysql>show slave status\G; # 加上\G可以使输出格式整齐 mysql>show processlist;