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

This article will explain in detail how to back up mysql for everyone, Xiaobian thinks it is quite practical, so share it with you for a reference, I hope you can gain something after reading this article.

mysqlhotcopy is a Perl script originally written 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 individual tables, but it can only run on the machine where the database catalog resides. mysqlhotcopy is only used to backup MyISAM. It runs on Unix and Intranet [@more@]

The following are quoted:

#!/ bin/sh

# Name:mysqlbackup.sh

# PS:MySQL DataBase Backup,Use mysqlhotcopy script.

# Last Modify:2008-06-12

#Define variables, please modify them according to specific circumstances

#Define the directory where the script is located

scriptsDir=`pwd`

#Data directory of database

dataDir=/var/lib/mysql

#Data Backup Directory

tmpBackupDir=/tmp/mysqlblackup

backupDir=/backup/mysql

#User name and password used to back up database

mysqlUser=root

mysqlPWD='you password'

#Empty temporary backup directory if it exists, create it if it does not exist

if [[ -e $tmpBackupDir ]]; then

rm -rf $tmpBackupDir/*

else

mkdir $tmpBackupDir

fi

#Create backup directory if it doesn't exist

if [[ ! -e $backupDir ]];then

mkdir $backupDir

fi

#Get database backup list, where you can filter databases you don't want to backup

for databases in `find $dataDir -type d |

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 "$dateTime Database:$databases backup success! " >>MySQLBackup.log

fi

done

#Compress backup files

date=`date -I`

cd $tmpBackupDir

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

#End Finished

Add to crontab Set 5 runs per week

0 0 * * 5 /backup/blackup.sh

Note: Restore database to backup state

mysqlhotcopy backups out the entire database directory, which can be copied directly to the datadir specified by mysqld (here/var/lib/mysql/) directory, and pay attention to the permissions, as follows:

shell> cp -rf db_name /var/lib/mysql/

shell> chown -R mysql:mysql /var/lib/mysql/ (Change the owner of db_name directory to mysqld run user)

This backup policy can only restore the database to the state at the time of the last backup. To minimize the data lost during the crash, backup should be performed more frequently. To restore the data to the state at the time of the crash, please use the master-slave replication mechanism.

Tips:

Instead of writing passwords in shell, create a.my.cnf file in root's home directory so mysqlhotcopy can read usernames/passwords from it.

[mysqlhotcopy]

user=root

password=YourPassword

Then, just to be safe, chmod it.

chmod 600 ~/.my.cnf

Attachment:mysqlhotcopy Common parameters:

The following are quoted:

· --allowold If the target exists do not give up (rename it with an_old suffix).

· --checkpoint=db_name.tbl_name Insert checkpoint entries at the specified db_name.tbl_name.

· ---debug Enable debug output.

· --dryrun, -n Reports actions without executing them.

· --flushlog Flushes the log after all tables are locked.

· --Keepold does not delete previous (renamed) targets after completion.

-- method=command Copy method (cp or scp).

· --noindices Backup does not include all index files. This makes backups smaller and faster. You can rebuild the index later with myisamchk -rq.

--password=password, -p password The password used when connecting to the server. Note that the password value for this option is not optional, unlike other MySQL programs.

--port=port_num, -P port_num The TCP/IP port number used when connecting to the local server.

keep silent except when something goes wrong.

--regexp=expr Copies all databases whose database names match the given regular expression.

--socket=path, -S path Unix socket file used for connection.

· --suffix= suffix of the database name copied by str.

· --tmpdir=path temporary directory (instead of/tmp).

--user=user_name, -u user_name MySQL user name used when connecting to the server.

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, SELECT permissions and RELOAD permissions on those tables (to be able to perform FLUSH TABLES).

About "mysql how to backup" this article is shared here, I hope the above content can be of some help to everyone, so that you can learn more knowledge, if you think the article is good, please share it to let more people see.

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.