Backup and recovery of MySQL database under Linux

Author: Grey

Original address: Backup and recovery of MySQL database under Linux

overview

Backup modeexplainremarks
mysqldumpBy default, only tables, views, and triggers are backed upBy 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
binlogThere is a time limit, which can be configured. Generally, the record can be kept for 5 days
mysqlpumpDefault backup table, view, stored procedure, stored function, trigger, event,Only MySQL version 5.7 and above is supported
xtrabackupThe database needs to be restarted, and the data directory of the database needs to be cleared during recovery. Incremental backup can be supportedOnly 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:

mysqldump

mysqlpump

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

reference material

mysqldump

mysqlpump

Tags: Linux Database MySQL

Posted on Sun, 21 Nov 2021 02:27:27 -0500 by dmb