Author: Grey
Original address: Backup and recovery of MySQL database under Linux
overview
| Backup mode | explain | remarks |
|---|---|---|
| mysqldump | By default, only tables, views, and triggers are backed up | By default, stored procedures and events are not backed up, and configuration parameters need to be added. If stored procedures and events need to be backed up, the - ER parameter needs to be added, and the dump user needs to add the Global Privilege of show events |
| binlog | There is a time limit, which can be configured. Generally, the record can be kept for 5 days | |
| mysqlpump | Default backup table, view, stored procedure, stored function, trigger, event, | Only MySQL version 5.7 and above is supported |
| xtrabackup | The database needs to be restarted, and the data directory of the database needs to be cleared during recovery. Incremental backup can be supported | Only Linux is supported |
Version based
- MySQL5.7
- Deepin Linux 15.11
- xtrabackup-2.4.18
Scheduled backup script (using MySQL dump)
Front work
Prepare a database to be backed up. Suppose the database name is cargo. The example script is as follows
CREATE DATABASE IF NOT EXISTS `cargo`; USE `cargo`; CREATE TABLE IF NOT EXISTS `b_gen` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `b_gen` (`id`, `name`) VALUES (1, 'SJA1'), (2, 'SJA2'); CREATE TABLE IF NOT EXISTS `b_org` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `b_org` (`id`, `name`) VALUES (1, 'SJA'), (2, 'ITC');
Prepare a user for dump database and grant the following permissions:
-- Create user create user db_user@'%' identified by 'db_pass'; -- to grant authorization grant select,show view,lock tables,trigger on cargo.* to db_user@'%' identified by 'db_pass'; flush privileges;
Create a directory to store scripts
mkdir /data/backup/mysql
Script directory structure
- db_bk.sh → main program
- check_dir.sh → verify whether the mysql related directory exists
- options.conf → global variables and related configurations
db_bk.sh
#!/bin/bash
DIRNAME=$0
if [ "${DIRNAME:0:1}" = "/" ];then
CURDIR=`dirname $DIRNAME`
else
CURDIR="`pwd`"/"`dirname $DIRNAME`"
fi
echo $CURDIR
# Define global variables
. $CURDIR/options.conf
# Check related directories
. $CURDIR/check_dir.sh
DBname=$1
LogFile=${backup_dir}/db.log
DumpFile=${backup_dir}/DB_${DBname}_$(date +%Y%m%d_%H).sql
NewFile=${backup_dir}/DB_${DBname}_$(date +%Y%m%d_%H).tgz
OldFile=${backup_dir}/DB_${DBname}_$(date +%Y%m%d --date="${expired_days} days ago")*.tgz
[ ! -e "${backup_dir}" ] && mkdir -p ${backup_dir}
DB_tmp=$(${db_install_dir}/bin/mysql -u${dbdumpuser} -p${dbdumppwd} -e "show databases\G" | grep ${DBname})
[ -z "${DB_tmp}" ] && {
echo "[${DBname}] not exist" >>${LogFile}
exit 1
}
if [ -n "$(ls ${OldFile} 2>/dev/null)" ]; then
rm -f ${OldFile}
echo "[${OldFile}] Delete Old File Success" >>${LogFile}
else
echo "[${OldFile}] Delete Old Backup File" >>${LogFile}
fi
if [ -e "${NewFile}" ]; then
echo "[${NewFile}] The Backup File is exists, Can't Backup" >>${LogFile}
else
${db_install_dir}/bin/mysqldump -u${dbdumpuser} -p${dbdumppwd} --databases ${DBname} >${DumpFile}
pushd ${backup_dir} >/dev/null
tar czf ${NewFile} ${DumpFile##*/} >>${LogFile} 2>&1
echo "[${NewFile}] Backup success " >>${LogFile}
rm -f ${DumpFile}
popd >/dev/null
fi
And give executable permissions
chmod u+x db_bk.sh
Storage directory of database files after backup:
mkdir /data/backup/mysql/backup_files
options.conf
# The installation path of mysql can be viewed through the following SQL # select @@basedir as basePath from dual ; show variables like '%basedir%'; mysql_install_dir=/usr/local/mysql # The data storage path of mysql can be viewed through the following SQL # select @@datadir as dataPath from dual ;show variables Like '%datadir%'; mysql_data_dir=/data/mysql dbdumpuser=db_user dbdumppwd=db_pass # Backup Dest directory, change this if you have someother location backup_dir=/data/backup/mysql/backup_files # How many days before the backup directory will be removed expired_days=5
And give executable permissions
chmod u+x options.conf
check_dir.sh
#!/bin/bash
# check MySQL dir
# [ -d "${mysql_install_dir}/support-files" ] && { db_install_dir=${mysql_install_dir}; db_data_dir=${mysql_data_dir}; }
{
db_install_dir=${mysql_install_dir}
db_data_dir=${mysql_data_dir}
}
And give executable permissions
chmod u+x check_dir.sh
Add this script to the scheduled task:
crontab -e
Edit the scheduled task file and add the following line. The cron expression means: execute every hour:
*/60 * * * * /bin/bash /data/backup/mysql/db_bk.sh cargo
Scheduled cleanup script
In / data/backup/mysql/backup_files directory: deleteLegacy.sh
#!/bin/bash
for file in `find /data/backup/mysql/backup_files/ -type f -name "*"`
do
let expired_time=$[1*24*60*60] #The expiration time of the file defined here is 1 day
let currentDate=`date +%s` #Gets the system time, so the time format is seconds
let modifyDate=$(stat -c %Y $file) #Get file modification time
let existTime=$[$currentDate-$modifyDate] #Compare the time and calculate the log existence time
if [ $existTime -gt $expired_time ];
then
rm -rf $file #Delete file
fi
done
And give executable permissions
chmod u+x deleteLegacy.sh
Add scheduled task
crontab -e
Edit the scheduled task file and add the following line. The cron expression means: execute once every morning at 1 a.m.:
00 01 * * * /bin/sh /data/backup/mysql/backup_files/deleteLegacy.sh
Timed synchronization script
Regularly synchronize the cargo database to a new database (this database needs to be built in advance, assuming the name is cargo_backup)
CREATE DATABASE IF NOT EXISTS `cargo_backup`;
The previously created db_user, give cargo_ All permissions of backup, and DB needs to be set at the same time_ SUPER permission of user's Global privileges (otherwise there will be problems when importing views)
grant all privileges on cargo_backup.* to db_user@'%' identified by 'db_pass'; grant SUPER on *.* to db_user@'%'; flush privileges;
Create synchronous SQL directory
mkdir /data/backup/mysql/mysqlsync
Script reference
mysql_sync.sh
#!/bin/bash # MySQL database # Create a special synchronization user (Grant select,show view, trrigger, lock tables permissions) # Have all permissions on the backup target database # To set SUPER permissions for Global privileges DB_USER="db_user" DB_PASS="db_pass" DB_HOST="localhost" # Name of database to be backed up DB_FROM="cargo" DB_TO="cargo_backup" BIN_DIR="/usr/local/mysql/bin" SYNC_DIR="/data/backup/mysql/mysqlsync" $BIN_DIR/mysqldump -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_FROM > $SYNC_DIR/sync.sql $BIN_DIR/mysql -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_TO < $SYNC_DIR/sync.sql
Give executable permission
chmod u+x mysql_sync.sh
Add scheduled task
crontab -e
Edit the scheduled task file and add the following line. The cron expression means: execute once every morning at 1 a.m.:
00 01 * * * /bin/bash /data/backup/mysql/backup_files/mysql_sync.sh
Backup database operation with mysqlpump
Note: mysqlpump is mainly used to back up the whole sql execution process. In case of misoperation, we can take the operation records in mysqlpump and execute them one by one until the wrong operation stops, so that the last wrong operation can be cancelled. The disadvantage of this method is that the recovery time is relatively long, and the advantage is that we can control the operation records of each execution
For the difference between mysqldump and mysqlpump, please refer to the description of the two tools on the MySQL official website:
Note: mysqlpump uses MySQL features introduced in MySQL 5.7, and then estimates use with MySQL 5.7 or higher
That is, MySQL pump can only be used with MySQL 5.7 and above
ln -s /usr/local/mysql/bin/mysqlpump /usr/bin
grant all privileges on *.* to mysqlpump@'%' identified by 'mysqlpump'; -- grant reload,lock tables,replication client,create tablespace,process,super on *.* to mysqlpump@'%' identified by 'mysqlpump'; FLUSH PRIVILEGES;
Backup, assuming that the database to be backed up is t, execute
mysqlpump -umysqlpump -pmysqlpump --databases t >t.sql
To view backed up files:
t.sql
-- Dump created by MySQL pump utility, version: 5.7.29, Linux (x86_64) -- Dump start time: Tue Feb 25 19:44:18 2020 -- Server version: 5.7.29 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET @@SESSION.SQL_LOG_BIN= 0; SET @OLD_TIME_ZONE=@@TIME_ZONE; SET TIME_ZONE='+00:00'; SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET NAMES utf8mb4; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t` /*!40100 DEFAULT CHARACTER SET utf8mb4 */; CREATE TABLE `t`.`tt` ( `t` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; CREATE TABLE `t`.`x` ( `Column 1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; INSERT INTO `t`.`tt` VALUES (2),(2),(2); SET TIME_ZONE=@OLD_TIME_ZONE; SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT; SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS; SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; SET SQL_MODE=@OLD_SQL_MODE; -- Dump end time: Tue Feb 25 19:44:18 2020
Using Binlog to restore database
You can view the operation records of the whole database. For misoperation records, you can restore them to the latest backup, and then execute the sql after mysqlpump sentence by sentence until the wrong statement is executed (ignore it)
The console logs in as root
mysql -uroot -p
show variables like 'binlog_format'
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | MIXED | +---------------+-------+
Binlog is configured in / etc/my.cnf. To configure binlog, you need to stop the database service first
service mysql stop
Then configure these two parameters in my.cnf
log_bin = mysql-bin binlog_format = mixed
Restart database
service mysql start
Next, we simulated several operations. I created the tsdtas table under the demo database
-- Normal operation
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
-- Wrong operation
DELETE FROM `demo`.`tsdtas`;
-- Normal operation
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1');
The console logs in as root
mysql -uroot -p
implement
flush logs;
In the mysql data directory (generally: / data/mysql), find the latest binlog (Format: mysql bin. 00000 x) in the mysql console
Execution:
show binlog events in"mysql-bin.000008";
You can view all operation records
MySQL [(none)]> show binlog events in"mysql-bin.000008";
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000008 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.29-log, Binlog ver: 4 |
| mysql-bin.000008 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000008 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 219 | Query | 1 | 298 | BEGIN |
| mysql-bin.000008 | 298 | Query | 1 | 429 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 429 | Xid | 1 | 460 | COMMIT /* xid=282 */ |
| mysql-bin.000008 | 460 | Anonymous_Gtid | 1 | 525 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 525 | Query | 1 | 604 | BEGIN |
| mysql-bin.000008 | 604 | Query | 1 | 735 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 735 | Xid | 1 | 766 | COMMIT /* xid=284 */ |
| mysql-bin.000008 | 766 | Anonymous_Gtid | 1 | 831 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 831 | Query | 1 | 910 | BEGIN |
| mysql-bin.000008 | 910 | Query | 1 | 1041 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 1041 | Xid | 1 | 1072 | COMMIT /* xid=286 */ |
| mysql-bin.000008 | 1072 | Anonymous_Gtid | 1 | 1137 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 1137 | Query | 1 | 1216 | BEGIN |
| mysql-bin.000008 | 1216 | Query | 1 | 1317 | use `demo`; DELETE FROM `demo`.`tsdtas` |
| mysql-bin.000008 | 1317 | Xid | 1 | 1348 | COMMIT /* xid=288 */ |
| mysql-bin.000008 | 1348 | Anonymous_Gtid | 1 | 1413 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 1413 | Query | 1 | 1492 | BEGIN |
| mysql-bin.000008 | 1492 | Query | 1 | 1623 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 1623 | Xid | 1 | 1654 | COMMIT /* xid=290 */ |
| mysql-bin.000008 | 1654 | Anonymous_Gtid | 1 | 1719 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000008 | 1719 | Query | 1 | 1798 | BEGIN |
| mysql-bin.000008 | 1798 | Query | 1 | 1929 | use `demo`; INSERT INTO `demo`.`tsdtas` (`mm`,`x`) VALUES ('929','1') |
| mysql-bin.000008 | 1929 | Xid | 1 | 1960 | COMMIT /* xid=292 */ |
| mysql-bin.000008 | 1960 | Rotate | 1 | 2007 | mysql-bin.000009;pos=4 |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
In this operation record, DELETE the DELETE statement again and execute other statements again to recover.
Backup and restore database with xtrabackup
Important: With the introduction of Percona XtraBackup 8.0, Percona XtraBackup 2.4 will continue to support MySQL and Percona Server 5.6 and 5.7 databases. Due to the new MySQL redo log and data dictionary formats the Percona XtraBackup 8.0.x versions will only be compatible with MySQL 8.0.x and the upcoming Percona Server for MySQL 8.0.x
Installing Xtrabackup
tar zxvf percona-xtrabackup-2.4.18-Linux-x86_64.libgcrypt20.tar.gz cd percona-xtrabackup-2.4.18-Linux-x86_64/bin cp xtrabackup /usr/bin/xtrabackup cp innobackupex /usr/bin/innobackupex
Create user and authorize
# User authorization (Note: access to ip can be restricted. For example, local access, you can change% to localhost) create user xtrabackup@'%' identified by 'xtrabackup'; grant reload,lock tables,replication client,create tablespace,process,super on *.* to xtrabackup@'%' identified by 'xtrabackup'; # You can specify the permissions for adding, deleting and modifying specific databases here grant all privileges on *.* to xtrabackup@'%' identified by 'xtrabackup' ; FLUSH PRIVILEGES;
Create backup directory
mkdir /data/xtrabackup/
Full backup database
# View my.cnf location: mysql --help | grep 'Default options' -A 1 innobackupex --defaults-file=/etc/my.cnf --user=xtrabackup --password=xtrabackup --socket=/tmp/mysql.sock /data/xtrabackup/
After execution, there will be a folder named by timestamp under / data/xtrabackup: similar to 2020-02-24_14-42-16
Delete database
# Stop database service service mysql stop # Delete the mysql data directory: select @@datadir as dataPath from dual; # Suppose the data directory is: / data/mysql cd /data mv mysql/ mysql_bak/ mkdir mysql
Use -- apply log to roll back uncommitted transactions and synchronize committed transactions to the data file to keep the data file in a consistent state.
innobackupex --apply-log /data/xtrabackup/2020-02-24_14-42-16/
recovery
innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/xtrabackup/2020-02-24_14-42-16/
Configure data directory permissions for mysql users
chmod -R mysql.mysql /data
Start database
service mysql start
Incremental backup
We do incremental backup based on the previous full backup.
First, simulate incremental data, such as SQL
INSERT INTO `tt` (`t`) VALUES (5434), (2), (3);
innobackupex --defaults-file=/etc/my.cnf --user=xtrabackup --password=xtrabackup --socket=/tmp/mysql.sock --incremental-basedir=/data/xtrabackup/2020-02-24_14-42-16 --incremental /data/xtrabackup/
At this time, a timestamp folder will be generated under / data/xtrabackup, for example: 2020-02-25_14-55-31
This folder is the incremental backup of 2020-02-24_14-42-16
Execute delete database
# Stop database service service mysql stop # Delete the mysql data directory: select @@datadir as dataPath from dual; # Suppose the data directory is: / data/mysql cd /data mv mysql/ mysql_bak/ mkdir mysql
Incremental recovery
innobackupex --apply-log --redo-only /data/xtrabackup/2020-02-24_14-42-16 innobackupex --apply-log --redo-only /data/xtrabackup/2020-02-24_14-42-16 --incremental-dir=/data/xtrabackup/2020-02-25_14-55-31 innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /data/xtrabackup/2020-02-24_14-42-16/
Configure data directory permissions for mysql users
chown -R mysql.mysql /data
Start database
service mysql start
Using xtrabackup to back up mysql8 related commands
Note: from Percona statement:
Due to changes in MySQL 8.0.20 released by Oracle at the end of April 2020, Percona XtraBackup 8.0, up to version 8.0.11, is not compatible with MySQL version 8.0.20 or higher, or Percona products that are based on it: Percona Server for MySQL and Percona XtraDB Cluster.
In short, xtrabackup 8 only supports MySQL 8.0.20 and below, but not higher versions!
mkdir -p /data/backup/mysql/20210520/fulldb /usr/local/xtrabackup/bin/xtrabackup --defaults-file=/etc/my.cnf --user=young --password=0320 --port=3306 --backup --target-dir=/data/backup/mysql/20210520/fulldb mkdir -p /data/backup/mysql/20210520/incrdb /usr/local/xtrabackup/bin/xtrabackup --defaults-file=/etc/my.cnf --user=young --password=0320 --port=3306 --backup --target-dir=/data/backup/mysql/20210520/incrdb --incremental-basedir=/data/backup/mysql/20210520/fulldb service mysqld stop mv /data/mysql /data/mysql_bak/ /usr/local/xtrabackup/bin/xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/20210520/fulldb /usr/local/xtrabackup/bin/xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/20210520/fulldb --incremental-dir=/data/backup/mysql/20210520/incrdb /usr/local/xtrabackup/bin/xtrabackup --prepare --target-dir=/data/backup/mysql/20210520/fulldb /usr/local/xtrabackup/bin/xtrabackup -user=young --password=0320 --port=3306 --datadir=/data/mysql --copy-back --target-dir=/data/backup/mysql/20210520/fulldb chown -R mysql.mysql /data/mysql/ chmod -R 755 /data/mysql/ service mysqld restart