开启log_bin 日志
1、找到 my.cnf
find / -name my.cnf
find / -name my-medium.cnf
cp /usr/share/mysql my-medium.cnf /etc my.cnf
2、修改my.cnf
[mysqld]
log-bin = "/home/mysql/logbin.log"
binlog-format = ROW
log-bin-index = "/home/mysql/logindex"
binlog_cache_size=32m
max_binlog_cache_size=512m
max_binlog_size=512m
用到的Linx 命令
touch 文件名
chmod +x 文件名
crontab -e
service crond status|start
mysql -u XXX -p XXX
source XXX.sql
find / -name 文件夹|文件(名字)
/etc/init.d/mysql start | systemctl mysqld start
MySQL 的全量备份脚本
1、全量备份需要找到mysqldump所在的路径
find / -name mysqldump
2、创建执行脚本赋予权限
touch testfulldose.sh
chmod +x testfulldose.sh
3、编辑脚本
db_user=root
db_pass=root
db_name=test
backupdir=/root/mysqlscript/backup
m_dump=/usr/bin/mysqldump
$m_dump -u $db_user -p$db_pass $db_name | gzip > $backupdir/$db_name$time.sql.gz
find $backupdir -name $db_name"*.sql.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1
4、开启定任务 crontab
crontab -e
* * * * * /root/mysqlscript/fulldose.sh
https://www.runoob.com/w3cnote/linux-crontab-tasks.html
MySQL的增量备份
1、检查log_bin 是否开启
mysql -u XXX -p XXX
show variables like '%log_bin%';
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
2、查看 mysql_bin.000*** 日志文件
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
3、脚本编写
BinDir=/var/lib/mysql
LogFile=/root/mysqlscript/backup/binlog.log
BinFile=/var/lib/mysql/mysql-bin.index
mysqladmin -uroot -proot flush-logs
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
for file in `cat $BinFile`
do
base=`basename $file`
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $LogFile
else
dest=$BakDir/$base
if(test -e $dest)
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BakDir
echo $base copying >> $LogFile
fi
fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` Bakup succ! >> $LogFile