【MySQL备份恢复】MySQL逻辑备份

 

• MySQL数据库创建备份用户BACKUP

#创建数据库备份用户，赋权
create user backup@'localhost' identified with mysql_native_password by 'noPass';
GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO `backup`@`localhost` ;
GRANT BACKUP_ADMIN ON *.* TO `backup`@`localhost`;
GRANT SELECT ON `performance_schema`.* TO `backup`@`localhost` ;

• 备份方案一

# crontab -e
00 22 * * * /bin/bash /home/mysql/mysql/backup/bin/mysqldump_backup.sh

$ cat /home/mysql/mysql/backup/bin/mysqldump_backup.sh
#!/bin/bash

dbbak_dir=/data/mysql/backup
cur_day=`date +%Y%m%d`

/home/mysql/mysql/bin/mysqldump -ubackup -p"noPass" --single-transaction --master-data=2 -R --triggers --databases testdb -q | gzip > $dbbak_dir/testdb_mysqldump_$cur_day.bak.gz

find $dbbak_dir -mtime +6 -type f | xargs rm -rf