Shulou(Shulou.com)06/02 Report--

Preface

Backup is the basis of disaster recovery, which 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 in order to prevent data loss caused by system errors or system failures. For some websites and systems, the database is everything, so it is very important to back up the database.

What is a backup?

In this example, the creation directory is placed under the / mnt directory, and can be placed in other directories according to the specific situation:

Cd / mntmkdir dbbackpwd/mnt/dbback

Create a shell script

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

Vim bcmysql.sh

Enter the editor and enter the following:

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

If you need this sub-file to be compressed, enter the following command:

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

In the specific operation, username,password and demo need to be replaced with the corresponding database user name, password and database name, respectively.

Add executable permissions

Chmod upright x bcmysql.sh

When you execute this script after adding execution permissions, the following exception may occur:

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

This problem should have occurred in the MySQL5.6+ version, probably because of the protection mechanism used to ensure the security of the database. The version of mysql used in this example is 5.7.22. Although there are warnings, the data can be backed up successfully.

Modify mysql configuration file

In response to the above problems, some versions may not be backed up successfully, and it is not recommended to configure the database password in the script, then you can modify the configuration file of mysql directly. In general, the configuration file for mysql is / etc/my.cnf.

Vim / etc/my.cnf

The configuration for adding mysqldump to this file is as follows:

[mysqldump] user=your_backup_user_namepassword=your_backup_password

Replace the corresponding username and password, modify the commands in the bcmysql.sh script, cancel the username and password parameters, and execute the script directly to see the backup results without restarting mysql.

Demo_20181114_193425.sql

Question one

When you use vim to view the contents of the exported sql file, you will find garbled codes in Chinese. This problem can be caused by two situations. The first is that the character set of the database itself is latain1, then you need to specify the character set and add the following parameters to the executed parameters:

-- default-character-set=gbk

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

Show variables like'% char%';-query result character_set_client utf8character_set_connection utf8character_set_database utf8mb4character_set_filesystem binarycharacter_set_results utf8character_set_server utf8character_set_system utf8character_sets_dir / usr/local/mysql/share/charsets/

If the character set is modified correctly and there is still garbled code, you can try to add the following parameters:

-- hex-blob

A careful analysis of the table structure shows that there may be a blob type in the table structure. This parameter means to export data of type BINARY, VARBINARY, and BLOB in hexadecimal mode. 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

Add scheduled task

Detect or install crontab

Execute the crontab command. If you report command not found, it indicates 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 check whether the installation is successful by using this command:

[root@iZ2zeck5vZ ~] # crontab- helpcrontab: invalid option-- hcrontab: usage error: unrecognized optionUsage:crontab [options] filecrontab [options] crontab- n [hostname] Options:-u define user-e edit user's crontab-l list user's crontab-r delete user's crontab-i prompt before deleting-n set host in cluster to run users' crontabs-c get host in cluster to run users' crontabs-s selinux context-x enable debuggingDefault operation is replace, per 1003.2

Add scheduled task

From the above command parameters, we can see the command that modifies crontab and executes the command:

Crontab-e

Go to the editing operation page of crontab, and you can edit the contents like vi and vim. Add the following command to it:

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

The purpose of this command is to execute the bcmysql.sh every other minute. Wait a minute and then use the ls command to see if a 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 as follows:

0 1 * / mnt/dbback/bcmysql.sh

Perform a backup operation at 1: 00 a. M. every day.

Log view

If execution fails, you can view the task log:

# tail-f / var/log/cron

Summary

So far, a simple version of Linux to achieve Mysql scheduled task backup data function has been implemented. Of course, directory control, history deletion and other extensions can also be carried out on the basis of this version.

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.