mysql备份

一、手动本机备份

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;