Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the backup tools of mysql". In the daily operation, I believe that many people have doubts about the backup tools of mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what are the backup tools of mysql?" Next, please follow the editor to study!

Mysqlhotcopy is a Perl script originally written and provided by TimBunce. It uses LOCKTABLES, FLUSHTABLES, and cp or scp to quickly back up the database. It is the fastest way to back up a database or a single table, but it can only run on the same machine as the database directory. Mysqlhotcopy is used only for backing up MyISAM. It runs in Unix and NetWare

See the script below for how to use it. Join crotab.

#! / bin/sh

# Name:mysqlbackup.sh

# PS:MySQLDataBaseBackup,Usemysqlhotcopyscript.

# LastModify:2008-06-12

# define variables, please modify them according to the specific situation

# define the directory where the script is located

ScriptsDir= `pwd`

# data directory of the database

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='youpassword'

# clear the temporary backup directory if it exists, and create it if it doesn't exist

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

# get the list of database backups, where you can filter databases that you do not want to back up

Fordatabasesin`find $dataDir-typed |\

Sed-e "s /\ / var\ / lib\ / mysql\ / /" |\

Sed-e "s/test//" `; do

If [[$databases== ""]]; then

Continue

Else

# backup database

/ usr/bin/mysqlhotcopy--user=$mysqlUser--password=$mysqlPWD-q "$databases" $tmpBackupDir

DateTime= `date "+% Y.%m.%d%H:%M:%S" `

Echo "$dateTimeDatabase:$databasesbackupsuccess!" > > MySQLBackup.log

Fi

Done

# compress backup files

Date= `date-I`

Cd$tmpBackupDir

Tarczf$backupDir/mysql-$date.tar.gz./

# End completed

Join in crontab and set up to run every Friday.

00**5/backup/blackup.sh

Note: when restoring the database to the backup, mysqlhotcopy backs up the entire database directory, which can be directly copied to the datadir (/ var/lib/mysql/ in this case) directory specified by mysqld. At the same time, pay attention to the permissions, such as the following example:

Shell > cp-rfdb_name/var/lib/mysql/

Shell > chown-Rmysql:mysql/var/lib/mysql/ (change the owner of the db_name directory to mysqld running user)

This backup strategy can only restore the database to the state of the last backup. In order to minimize the data lost during the crash, backup should be done more frequently. To restore the data to the state of the crash, please use the master-slave replication mechanism (replication).

What are the useful mysql backup tools?

Tips:

If you don't want to write the password in shell, you can create a .my.cnf file in root's home directory so that mysqlhotcopy can read the user name / password from it.

[mysqlhotcopy]

User=root

Password=YourPassword

Then, just to be on the safe side, chmod.

Chmod600~/.my.cnf

Mysqlhotcopy reads the [client] and [mysqlhotcopy] option groups from the options file. To perform mysqlhotcopy, you must have access to the backed-up table files with SELECT and RELOAD permissions for those tables (in order to be able to execute FLUSHTABLES).

At this point, the study of "what are the backup tools for mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.