Guidance:In the current work,Data backupIt is necessary because some faults or problems often occur during database management, causing data loss in the database. If we back up the data, even if the data is lost in the operation, it is no big deal. Because we have backups, we can see that data backup is necessary. First of all, I will introduce some useful and usefulMysql backupTool.
Below is its introduction.
Mysqlhotcopy is a Perl script originally written and provided by Tim Bunce. It uses lock tables, flush tables, and cp or scp to quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database directory is located. Mysqlhotcopy is only used to back up MyISAM. It runs in Unix and NetWare
For usage instructions, see the following script. Add it to crotab.
#! /Bin/sh
# Name: mysqlbackup. sh
# PS: MySQL DataBase Backup, Use mysqlhotcopy script.
# Last Modify: 2008-06-12
# Define variables. Modify the variables as needed
# Define the directory where the script is located
ScriptsDir = 'pwd'
# Database Data Directory
DataDir =/var/lib/mysql
# Data Backup Directory
TmpBackupDir =/tmp/mysqlblackup
BackupDir =/backup/mysql
# Username and password used to back up the database
MysqlUser = root
MysqlPWD = 'you password'
# If the temporary Backup directory exists, clear it. If it does not exist, create it.
If [[-e $ tmpBackupDir]; then
Rm-rf $ tmpBackupDir /*
Else
Mkdir $ tmpBackupDir
Fi
# Create a backup directory if it does not exist
If [[! -E $ backupDir]; then
Mkdir $ backupDir
Fi
# Obtain the database backup list, where you can filter databases that do not want to be backed up
For databases in 'Find $ dataDir-type d | \
Sed-e "s/\/var \/lib \/mysql \ //" | \
Sed-e "s/test //" '; do
If [[$ databases = ""]; then
Continue
Else
# Backing up a database
/Usr/bin/mysqlhotcopy -- user = $ mysqlUser -- password = $ mysqlPWD-q "$ databases" $ tmpBackupDir
DateTime = 'date "+ % Y. % m. % d % H: % M: % S "'
Echo "$ dateTime Database: $ databases backup success! "> MySQLBackup. log
Fi
Done
# Compressing backup files
Date = 'date-I'
Cd $ tmpBackupDir
Tar czf $ backupDir/mysql-‑date.tar.gz ./
# End completed
Add to crontab to set to run 5 times a week
0 0 ** 5/backup/blackup. sh
Note: When the database is restored to the backup state, mysqlhotcopy backs up the entire database directory. You can directly copy it to the datadir specified by mysqld (here/var/lib/mysql /) directory, and pay attention to permission issues, as shown in the following example:
Shell> cp-rf db_name/var/lib/mysql/
Shell> chown-R mysql: mysql/var/lib/mysql/(change the owner of the db_name directory to the mysqld running user)
This backup policy can only restore the database to the state of the last backup. If you want to lose as little data as possible during the crash, you should back up the database more frequently, to restore data to the state of the crash, use the master-slave replication mechanism (replication ).
TIPS:
If you do not want to write the password in shell, you can create a. my. cnf file in the root home directory so that mysqlhotcopy can read the username/password from it.
[Mysqlhotcopy]
User = root
Password = YourPassword
Then, for security reasons, chmod.
Chmod 600 ~ /. My. cnf
Mysqlhotcopy reads the [client] and [mysqlhotcopy] consumer groups from the option files. To execute mysqlhotcopy, you must be able to access the backup table files and have SELECT and RELOAD permissions for those TABLES (so that you can execute flush tables ).