Detailed explanation of the idea of using mysqldump+expect+crontab to implement mysql periodic cold backup in linux

1. Problems encountered
2. Ideas
3. Code

1. Problems encountered

We all know that after using mysqldump, we need to manually enter the mysql password, so we cannot use mysqldump directly in crontab to achieve periodic backup. In fact, we can use the expect script to automatically enter the password , thereby achieving true periodic backup. If you don’t know what expect is, I suggest you read this article first: https://www.jb51.net/article/197865.htm

2. Ideas

Create a utils file to store shell scripts, including mysqldump, scp and other commands
Use the expect script to execute the script in utils and automatically enter the password for it
Finally, use the driver script to execute expect and pass the required parameters into the script

The idea is as follows:

insert image description here

3. Code

3.1. Single-machine cold backup

(1) mysqldump shell script
backup.sh:

#!/bin/bash

mysql_username=$1
backup_databases=$2
backup_path=$3

mysqldump -u ${mysql_username} -p --databases ${backup_databases} > ${backup_path}

(2) Executing the expect script of mysqldump can help us automatically enter the mysql code
single_cold_backup_service.exp:

#!/usr/bin/expect

set timeout 5

#Set local information set mysql_username [lindex $argv 0] 
set backup_database [lindex $argv 1] 
set backup_path [lindex $argv 2] 

#utils path set utils_path /home/hadoop/backup_script/utils

spawn bash ${utils_path}/backup.sh ${mysql_username} ${backup_database} ${backup_path}


expect {
	"*assword*" {send "nimabidecao1\r"} #Enter password}
expect eof

(3) Drive the script and execute expect, where you can pass in the required parameters
single_cold_backup_service_driver.sh:

#!/bin/bash

#The data here can be written as mysql_username=root
backup_databases=school
backup_path=$HOME/backup_data/${backup_databases}.sql

#Run the expect script expect $HOME/backup_script/single_cold_backup/single_cold_backup_service.exp ${mysql_username} ${backup_databases} ${backup_path}

You must pay close attention to your path here. It is strongly recommended to use an absolute path to execute

(4) Use crontab to periodically execute the driver script and enter the crontab editor: crontab -e
Enter the following:

0 9 * * 1 bash /home/hadoop/backup_script/single_cold_backup/single_cold_backup_driver.sh

This means: perform a backup every Monday at 9:00 AM. If you want to confirm the time you want, you can go to this website: https://crontab-generator.org/ and select the time you want.

3.2. Dual-machine cold backup

(1) Copy remote files to copy the MySQL backup files on the local machine
scp.sh:

#!/bin/bash

local_backup_path=$1
another_user=$2
another_ip=$3
another_backup_path=$4

scp ${local_backup_path} ${another_user}@${another_ip}:${another_backup_path}

(2) Executing the expect script of mysqldump can help us automatically enter the mysql code
double_cold_backup_service.exp:

#!/usr/bin/expect

set timeout 5

#Host information set mysql_username [lindex $argv 0] 
set backup_database [lindex $argv 1] 
set backup_path [lindex $argv 2] 

#Slave information set slave_user [lindex $argv 3]
set slave_ip [lindex $argv 4]
set slave_backup_path [lindex $argv 5]

#utils path set utils_path /home/hadoop/backup_script/utils


spawn bash ${utils_path}/backup.sh ${mysql_username} ${backup_database} ${backup_path}

expect {
	"*assword*" {send "nimabidecao1\r"} #Enter password}

spawn bash ${utils_path}/scp.sh ${backup_path} ${slave_user} ${slave_ip} ${slave_backup_path}

expect {
  "*assword*" {send "nimabidecao1\r"} #Enter password}

expect eof

(3) Drive the script and execute expect, where you can pass in the required parameters
double_cold_backup_service_driver.sh:

#!/bin/bash

#Local machine information mysql_username=root
backup_databases=school
backup_path=$HOME/backup_data/${backup_databases}.sql

#Slave information slave_user=meizhaowei
slave_ip=172.20.10.14
slave_backup_path=backup_data/doule_cold_backup_data/${backup_databases}.sql


#Execute write absolute path expect $HOME/backup_script/double_cold_backup/double_cold_backup_service.exp ${mysql_username} ${backup_databases} ${backup_path} ${slave_user} ${slave_ip} ${slave_backup_path}

(4) Use crontab to periodically execute the driver script

0 9 * * 1 bash /home/hadoop/backup_script/double_cold_backup/double_cold_backup_driver.sh

This means: perform a backup every week at 9 am

This is the end of this article about how to use mysqldump+expect+crontab in Linux to implement MySQL periodic cold backup. For more information about MySQL periodic cold backup, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

MySQL scheduled backup solution (using Linux crontab)
Analysis of the reasons why the mysql-libs* crontab command that comes with Linux 6.7 cannot be used after uninstallation
MySQL scheduled backup using crontab scheduled backup example under Linux
How to use crontab to backup MySQL database regularly in Linux system

<<: Vue project realizes login and registration effect

>>: Detailed explanation of the use of Docker commit