How to implement Mysql scheduled task backup data under Linux

Preface

Backup is the basis of disaster recovery. It refers to the process of copying all or part of the data set from the hard disk or array of the application host to other storage media to prevent data loss due to system operation errors or system failures. For some websites and systems, the database is everything, so it is crucial to back up the database!

What is backup?

This example creates a directory in the /mnt directory. You can place it in other directories based on your needs:

cd /mnt
mkdir dbback
pwd
/mnt/dbback

Creating a Shell Script

The script name can be customized according to your own specifications:

vim bcmysql.sh

Enter the editor and enter the following:

#!/bin/bash
mysqldump -uusername -ppassword demo > /mnt/dbback/demo_$(date +%Y%m%d_%H%M%S).sql

If you need to compress the file, enter the following command:

#!/bin/bash
mysqldump -uusername -ppassword demo | gzip > /mnt/dbback/demo_$(date +%Y%m%d_%H%M%S).sql.gz

In specific operations, you need to replace username, password and demo with the corresponding database username, password and database name respectively.

Add executable permissions

chmod u+x bcmysql.sh

After adding the execution permission, the following exception may occur when executing this script:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

This problem should have occurred in MySQL version 5.6+, and it may be a protection mechanism adopted to ensure the security of the database. The MySQL version used in this example is 5.7.22. Although there are warning messages, the data can be backed up successfully.

Modify the mysql configuration file

In response to the above problem, some versions may not be able to be backed up successfully. In addition, it is not recommended to configure the database password in the script. You can directly modify the MySQL configuration file. Generally, the mysql configuration file is in /etc/my.cnf.

vim /etc/my.cnf

Add the following configuration to mysqldump in this file:

[mysqldump]
user=your_backup_user_name
password=your_backup_password

Replace the corresponding username and password, and modify the command in the bcmysql.sh script to cancel the username and password parameters. You can see the backup results by directly executing the script without restarting MySQL.

demo_20181114_193425.sql

Question 1

When using vim to view the contents of the exported sql file, you will find that the Chinese characters are garbled. This problem is caused by two situations. The first is that the character set of the database itself is latain1. In this case, you need to specify the character set and add the following parameters to the execution parameters:

--default-character-set=gbk

Of course, if it is utf8, change gbk to utf8. The command to view the database character set is as follows:

show variables like '%char%'; 
-- Query result character_set_client utf8
character_set_connection utf8
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/mysql/share/charsets/

If the character set is modified correctly but garbled characters still appear, try adding the following parameters:

--hex-blob

Carefully analyze the table structure. There may be blob type in the table structure. The meaning of this parameter is to export BINARY, VARBINARY, BLOB type data in hexadecimal format. The modified command is as follows:

mysqldump --default-character-set=utf8 --hex-blob demo > /mnt/dbback/demo_$(date +%Y%m%d_%H%M%S).sql

Adding a scheduled task

Detect or install crontab

If the crontab command reports command not found when executing it, it means that it is not installed.

# crontab
-bash: crontab: command not found

If it is not installed, you can install it through the yum command:

# yum -y install vixie-cron

Although crontab does not support the -h or –help command, you can use this command to check whether the installation is successful:

[root@iZ2zeck5vZ ~]# crontab -help
crontab: invalid option --h
crontab: usage error: unrecognized option
Usage:
crontab [options] file
crontab [options]
crontab -n [hostname]
Options:
-u <user> define user
-e edit user's crontab
-l list user's crontab
-r delete user's crontab
-i prompt before deleting
-n <host> set host in cluster to run users' crontabs
-c get host in cluster to run users' crontabs
-s selinux context
-x <mask> enable debugging
Default operation is replace, per 1003.2

Adding a scheduled task

Through the command parameters above, we can see the command to modify crontab and execute the command:

crontab -e

Enter the crontab editing operation page and edit its contents just like vi and vim. Add the following command in it:

*/1 * * * * /mnt/dbback/bcmysql.sh

This line of command is used to execute bcmysql.sh every minute. Wait one minute and then use the ls command to check whether the backup file is generated. It is found that the file can be generated normally. Of course, we don't need to back up every minute, so change the command to the following:

0 1 * * * /mnt/dbback/bcmysql.sh

A backup operation is performed every day at 1:00 AM.

Log View

If the execution fails, you can view the task log:

# tail -f /var/log/cron

summary

So far, a simple version of the MySQL scheduled task data backup function has been implemented under Linux. Of course, you can also perform directory control, history deletion and other extensions based on this version.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.