MySQL生产库全库备份脚本

生产环境推荐全备方案：
mysqldump -uroot -hlocalhost -pmysql –single-transaction -A –master-data=2 > /backup/alldatabase.sql
–single-transaction 预防锁
-A全库备份
–master-data=2记录复制信息

mysqldump -uroot -hlocalhost -pmysql –all-databases –lock-all-tables –flush-logs –master-data=2 > /backup/alldatabase.sql
mysql> flush tables with read lock;
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 120 | | |
+——————+———-+————–+——————+
mysql> unlock tables;
# scp /backup/alldatabase.sql 192.168.1.61:/tmp

1、创建备份脚本：
[root@localhost ~]# cat > full_backup.sh < /databackup/fullbackup/\$date.sql

if [ -f /databackup/fullbackup/\$date.sql ]; then
cd /databackup/fullbackup; tar -cvzf $date.sql.tar.gz $date.sql
rm -fr $date.sql
fi
find /databackup/fullbackup/ -type f -name “*sql*” -mtime +90 | xargs rm -fr {};
[root@localhost ~]# chmod +x full_backup.sh

2、创建备份目录 ：
mkdir -p /databackup/fullbackup

3、创建单独的备份用户backup
[root@localhost ~]# mysql -uroot -p
mysql> grant SELECT,RELOAD,SHOW DATABASES,SUPER,LOCK TABLES,REPLICATION CLIENT,SHOWVIEW,EVENT,FILE on *.* to backup@’localhost’ identified by ‘MANAGER’;
flush privileges;