“Mysql备份”的版本间的差异
跳到导航
跳到搜索
（→网内相关） |
|||
| 第169行： | 第169行： | ||
https://blog.csdn.net/zfl589778/article/details/51441719 | https://blog.csdn.net/zfl589778/article/details/51441719 | ||
| + | |||
| + | [https://www.cnblogs.com/SQL888/p/5751631.html 学会4种备份MySQL数据库（基本备份方面没问题了） ] | ||
https://www.cnblogs.com/suoning/p/5778243.html | https://www.cnblogs.com/suoning/p/5778243.html | ||
2021年12月3日 (五) 08:56的最新版本
目录
网内相关
xtrabackup
mydumper
Mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具。
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.
see also
明天看一下
https://blog.csdn.net/zfl589778/article/details/51441719
