Mysql备份

网内相关

Mysql备份锁表

mydumper

Mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具。

mydumper 介绍及使用

MySQL备份之【mydumper 学习】

sing

启用压缩备份数据库
[root@localhost~]#mysqldump -uroot -p -B nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz

主从

建议在从上作备份

dbbackup.sh

#!/bin/bash
# 04  3 * * *  /home/mon/dbbackup.sh >/dev/null  2>&1
#author: linuxsa.org  mysql backup everyday 在从库上备份 
ulimit -SHc unlimited
ulimit -SHn 65535
# ENV
export PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
source /etc/profile
DBUSER="ops"
DBPASS='123456'  ##DBHOST="127.0.0.1"
MYSQLBIN="/home/apps/mysql/bin/mysql"
MYSQLDUMPBIN="/home/apps/mysql/bin/mysqldump"
DATADIR="/home/dbbackup"
LOGDIR="/home/logs/"
TODAY=`date +%Y%m%d`
RETENTION=10
[ ! -d $DATADIR ] && /bin/mkdir -p $DATADIR
[ ! -d $LOGDIR ] && /bin/mkdir -p $LOGDIR

datapath=/home/data/mysqlbackup
backtime=`date +%Y%m%d-%H`

#多个库用空格 隔开 
dblist="lxtx_cailu_market"

# DBS=`$MYSQLBIN  -u$DBUSER -p$DBPASS -Bse "show databases"|grep -v "mysql"|grep -v "performance_schema"|grep -v "information_schema" |grep -v "test"`

# get IP 有时是  print $4}' | head -n 1
if [ -z "$ip" ];then
    intra_ip=`ifconfig|awk 'BEGIN{FS=":| +"}/eth|em|bond|br[0-9]/,/RX/{if ($2 ~ "inet$" && $4 !~ "^172.*|^192.*|^10.*" ) print $3}' | head -n 1`
    inter_ip=`ifconfig|awk 'BEGIN{FS=":| +"}/eth|em|bond|br[0-9]/,/^$/{if ($2 ~ "inet$" && $4 ~ "^172.*|^192.*|^10.*" ) print $3}' | head -n 1`
    if [ -z "${intra_ip}" ];then
        if [ -z "${inter_ip}" ];then
            echo "get ip failed"
            exit
        else
            ip="${inter_ip}"
        fi
    else
        ip="${intra_ip}"
    fi
fi


#exclude tables
DATABASE=lxtx_cailu_market

EXCLUDED_TABLES=(
t_market_exchange_kline_15min 
t_market_exchange_kline_1day   
t_market_exchange_kline_1hour  
t_market_exchange_kline_1min  
t_market_exchange_kline_1month 
t_market_exchange_kline_1week  
t_market_exchange_kline_30min  
t_market_exchange_kline_4hour  
t_market_exchange_kline_5min  
t_market_exchange_kline_6hour 

t_market_trend_1year           
t_market_trend_24hour          
t_market_trend_30day           
t_market_trend_7day            
t_market_trend_all   
)

IGNORED_TABLES_STRING=''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done





#dump && gzip  #--routines, -R  导出存储过程以及自定义函数。  如果是单机备份 不 用  --master-data=2
for dbname in $dblist; do
#source=`/home/apps/mysql/bin/mysqldump -u ${dbuser} -p${dbpwd} --single-transaction ${somedb} > ${datapath}/${backtime}/${somedb}.sql`;
$MYSQLDUMPBIN -u$DBUSER -p$DBPASS  --log-error=$LOGDIR/${dbname}_${TODAY}_error.log --default-character-set=utf8mb4 -R  --single-transaction  --master-data=2  $dbname > $DATADIR/${ip}${dbname}_${TODAY}.sql
if [ $? -eq 0 ]
then
[ -f ${LOGDIR}/${dbname}_${TODAY}_error.log ] && /bin/rm -rf  ${LOGDIR}/${dbname}_${TODAY}_error.log
fi


    /bin/ls $DATADIR/*.sql > /dev/null 2>&1  &&  /usr/bin/gzip $DATADIR/*.sql 2>> $LOGDIR/gzip_error_${TODAY}.log

    if [ $? -eq 0 ]
    then
     /bin/rm -rf $LOGDIR/gzip_error_${TODAY}.log
    fi



done
# delete more than 3 days *.tar.gz  怕变量为空 
DATADIR="/home/dbbackup"
find $DATADIR -type f -mtime +2 -name "*.sql.gz" -exec rm -rf {} \;

#find $DATADIR -type f -mtime +2 |xargs rm -rf

#远程备份 
#env RSYNC_PASSWORD=ops3636evan886back rsync -avz ${DATADIR}/*${TODAY}.sql.gz   [email protected]::opsdata

trouble shooting

** 创建 只备份权限的用户 ;授权

DROP USER 'ops'@'localhost';

CREATE USER 'ops'@'localhost' IDENTIFIED BY 'slave';

GRANT SELECT ON cailu_market.* TO 'ops'@'localhost';

GRANT REPLICATION CLIENT  ON *.* TO 'ops'@'localhost' WITH GRANT OPTION;
GRANT reload ON *.* TO 'ops'@'localhost' WITH GRANT OPTION;

不然 
mysqldump: Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

--single-transaction and --lock-all-tables这两个参数不要同时使用

mysqldump: You can't use --single-transaction and --lock-all-tables at the same time.

Mysql备份锁表

see also

使用mysqldump跳过某些表

如何优雅的备份和恢复Mysql数据库

如何优雅的备份和恢复Mysql数据库

明天看一下

https://blog.csdn.net/zfl589778/article/details/51441719

学会4种备份MySQL数据库（基本备份方面没问题了）

https://www.cnblogs.com/suoning/p/5778243.html

备份数据库需要的权限