Shell if语句实现mysqldump全量备份+mysqlbinlog二进制日志增量备份

mysqldump全量备份+mysqlbinlog二进制日志增量备份

从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备份。 

yum install mariadb-server  mariadb  mariadb-devel -y

[root@www mysql]# cat /etc/my.cnf.d/server.cnf  --开启binlog
[mysqld]]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=/var/lib/mysql/mysql-bin
expire_logs_days=7
server-id=1
symbolic-links=0


service mariadb restart
MariaDB [(none)]> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON 
[root@www ~]# cd /var/lib/mysql/
[root@www mysql]# cat mysql-bin.index 
/var/lib/mysql/mysql-bin.000001

use mysql;
update user set password=password('123456') where user='root' and host='localhost';
flush privileges; 

全量备份
[root@www ~]# date +%F
2020-05-07


[root@www tmp]# cat mysql_autofull_backup.sh 
#!/bin/bash
####备份所有的数据库(每周日使用一次)#######
user="root"
passwd="123456"
backup_dir="/data/backup"
name_dir="`date +%F`"     
sql_cmd="/usr/bin/mysqldump"


if [ ! -d $backup_dir ];then
	mkdir -p $backup_dir
	echo -e "\033[32m the dir $backup_dir create succeded  \033[0m"
fi

if [ ! -d $backup_dir/$name_dir ];then
	cd $backup_dir
	mkdir $name_dir
	echo -e "\033[32m the dir $backup_dir/$name_dir create succeded \033[0m"
fi

$sql_cmd -u $user -p$passwd --all-databases --single-transaction -R --triggers -E --master-data --flush-logs >  $backup_dir/$name_dir/$name_dir.sql

if [ $? -eq 0 ];then
	echo -e "\033[32m--------------- \033[0m"
        echo -e "\033[32mthe mysql $single_database database backup succeded.\033[0m"
        ls -l $backup_dir/$name_dir/$name_dir.sql
else
	echo -e "\033[032---------------- \033[0m"
        echo -e "\033[032m mysql $single_database  database backup failed,please check!!!\033[0m"
fi

对指定数据库备份

#!/bin/bash
user="root"
passwd="123456"
backup_dir="/data/backup"
name_dir="`date +%F`"
sql_cmd="/usr/bin/mysqldump"
single_database="$1"  #使用$1来接受指定要备份的数据库


if [ -z $single_database ];then  #如果输入的参数为空
    echo -e "\033[32mUsage: {/bin/bash $0 test1|test2|test3|help} \033[0m"  #提示信息然后退出脚本
    exit
fi

if [ ! -d $backup_dir ];then
        mkdir -p $backup_dir
        echo -e "\033[32m the dir $backup_dir create succeded  \033[0m"
fi

if [ ! -d $backup_dir/$name_dir ];then
        cd $backup_dir
        mkdir $name_dir
        echo -e "\033[32m the dir $backup_dir/$name_dir create succeded \033[0m"
fi

$sql_cmd -u $user -p$passwd --databases $single_database  --single-transaction -R --triggers -E --master-data --flush-logs >  $backup_dir/$name_dir/$single_database.${name_dir}.sql

if [ $? -eq 0 ];then
        echo -e "\033[32m--------------- \033[0m"
        echo -e "\033[32mthe mysql full database backup succeded.\033[0m"
        ls -l $backup_dir/$name_dir/
else
        echo -e "\033[032---------------- \033[0m"
        echo -e "\033[032m mysql full database backup failed,please check!!!\033[0m"
        rm -rf $backup_dir/$name_dir/$single_database.${name_dir}.sql    #对备份失败时候要删除产生的.sql文件
fi

[root@www tmp]# ./mysql_auto_backup.sh 
--------------- 
the mysql test  database backup succeded.
total 4
-rw-r--r-- 1 root root 1637 May  6 16:31 test.2020-05-06.sql


如果你要实现对多个库备份可以使用for循环逐个对数据库进行备份，$*传递给脚本或函数的所有参数。
db="$*"

for i in db
do
    $sql_cmd -u $user -p$passwd --databases $i  --single-transaction -R --triggers -E --master-data --flush-logs >  $backup_dir/$name_dir/$i.${name_dir}.sql
done

增量备份

[root@www tmp]# cd /var/lib/mysql/
[root@www mysql]# ls mysql-bin.[0-9]* | head -n -1 | sort -rg | head -n 1
mysql-bin.000003
[root@www mysql]# mysqladmin -uroot -p123456 flush-logs
[root@www mysql]# ls mysql-bin.[0-9]* | head -n -1 | sort -rg | head -n 1
mysql-bin.000004


[root@www tmp]# cat mysql_autoincre_backup.sh 
#!/bin/bash
###增量备份数据库(周一到周六每天各一次)####
user="root"
passwd="123456"
backup_dir="/data/backup"
name_dir="`date +%F`"
binlog_dir="/var/lib/mysql/"
sql_adm="/usr/bin/mysqladmin"

if [ ! -d $backup_dir ];then
        mkdir -p $backup_dir
        echo -e "\033[32m the dir $backup_dir create succeded  \033[0m"
fi

if [ ! -d $backup_dir/$name_dir ];then
        cd $backup_dir
        mkdir $name_dir
        echo -e "\033[32m the dir $backup_dir/$name_dir create succeded \033[0m"
fi

cd $binlog_dir
$sql_adm  -u$user -p$passwd flush-logs  #注意这里是先flush log然后再cp,拷贝的binlog比新产生的binlog序号少1
cp `ls mysql-bin.[0-9]* | head -n -1 | sort -rg | head -n 1` $backup_dir/$name_dir


if [ $? -eq 0 ];then
	echo -e "\033[32m increment backup succeded.\033[0m"
        ls -l  $backup_dir/$name_dir
else
        echo -e "\033[32m increment backup failed please check!"
fi


# head -n [-]K   附加"-"参数，则除了每个文件的最后K行外 显示剩余全部内容

在周日实行全备。在周一到周六实行增量备份。

[root@node5 ~]# crontab -e
0 1 * * 0 /bin/bash /root/mysql_autofull_backup.sh &>/dev/null
0 1 * * 1-6 /bin/bash /root/mysql_autoincre_backup.sh &>/dev/null