Mysql database Common backup methods and considerations _mysql

MySQL database backup common 3 ways:
1. Direct copy (CP, Tar,gzip,cpio)
2, Mysqldump
3, Mysqlhotcopy

1. Use direct Copy database backup
Typically, such as the CP, TAR, or cpio utilities.
When you use a direct backup method, you must ensure that the table is not in use. If the server changes when you are copying a table, the copy loses its meaning.
The best way to ensure your copy integrity is to shut down the server, copy the files, and reboot the server. If you do not want to shut down the server, lock the server while performing a table check. If the server is running, the same restrictions apply to copying files, and you should use the same locking protocol to "calm down" the server.
When you have completed the backup, you need to reboot the server (if it is turned off) or release the lock added to the table (if you want the server to run).
To copy a database from one machine to another using a direct copy file, just copy the file to the appropriate data directory on another server host. To make sure that the file is in myiasm format or that the two machines have the same hardware structure, your database has strange content on another host. You should also make sure that servers on another machine do not access them while you are installing the database tables.
2.mysqldump
Basic syntax:
Shell> mysqldump [OPTIONS] database [tables] > Data_backup.sql (does not specify a database name to represent all backups)
For example:
Mysqldump-uroot-p--default-character-set=cp932--opt--extended-insert=false--hex-blob-r-x mysql > E:\mysql.sql
The beginning of the output file looks like this:
# MySQL Dump 6.0
#
# Host:localhost database:samp_db
#---------------------------------------
# Server Version 3.23.2-alpha-log
#
# Table structure for table ' absence '
#
CREATE TABLE Absence (
student_id Int (a) unsigned DEFAULT ' 0 ' not NULL,
Date Date DEFAULT ' 0000-00-00 ' not NULL,
PRIMARY KEY (student_id,date)
);
#
# Dumping data for table ' absence '
#
INSERT into Absence VALUES (3, ' 1999-09-03 ');
INSERT into Absence VALUES (5, ' 1999-09-03 ');
INSERT into Absence VALUES (10, ' 1999-09-08 ');
......
The rest of the file consists of more insert and CREATE TABLE statements. Cases:
%mysqldump samp_db >/opt/mysqldatabak/samp_db.2006-5-15
%mysqldump samp_db | Gzip >/usr/archives/mysql/samp_db.1999-10-02.gz #产生压缩备份
%mysqldump samp_db Student Score Event absence >grapbook.sql #备份数据库的某些表
%mysqladmin-h boa.snake.net Create samp_db
%mysqldump samp_db | The mysql-h boa.snake.net samp_db #直接恢复到另一个服务器上使用--add-drop-table option tells the server to write the drop TABLE IF exists statement to the backup file so that when we later use it to recover the database, If the table already exists, you will not get an error.
%mysqldump--add-drop-table samp_db | Mysql-h boa.snake.net samp_db
Other useful options for mysqldump include:
The--flush-logs and--lock-tables combinations will help with your database checkpoint. --lock-tables Lock All the tables you're dumping, while--flush-logs closes and reopen the update log file, the new update log will include only queries that modify the database from the point of backup. This will set up your update log to check the bit backup time. (However, if you have customers who need to perform an update, locking all the tables for customer access during the backup is not a good thing.) ）
If you use--flush-logs to set up checkpoints to backup, it may be best to dump the entire database. If you dump a separate file, it's harder to sync the update log checkpoint with the backup file. During recovery, you typically extract the update log content on a database basis, and you have no option to extract updates for a single table, so you have to extract them yourself.
By default, mysqldump reads the entire contents of a table into memory before writing. This is usually really unnecessary, and actually if you have a big table, it's almost a failure. You can use the--quick option to tell Mysqldump to write each line as soon as it retrieves one line. To further optimize the dumping process, use--opt rather than--quick. The--OPT option turns on other options to accelerate the dumping of data and to read them back.
Implementing backups with--opt may be the most common method, because of the advantages of backup speed. However, to warn you that the--opt option does have a price,--opt optimizes your backup process, not other clients ' access to the database. The--opt option prevents anyone from updating any of the tables you are dumping by locking all tables at once. You can easily see the effect on general database access.
An option that has the opposite effect of--opt is--dedayed. This option causes Mysqldump to write the insert delayed statement instead of the INSERT statement. --delayed is helpful if you load a data file into another database and you want to minimize the impact of this operation on queries that might appear in the database.
The--compress option is helpful when you copy the database to another machine, because it reduces the number of bytes transmitted by the network. Here's an example of a program that--compress to communicate with a server on a remote host, rather than a program that connects to a local host:
%mysqldump--opt samp_db | MySQL--compress-h boa.snake.net samp_db
Mysqldump has many other options, the main parameters:
--compatible=name
It tells Mysqldump which database or older version of the MySQL server the exported data will be compatible with. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. , you use a few values to separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.
--complete-insert,-c
The exported data takes a complete INSERT with the field name, which means that all values are written on one line. This can increase the efficiency of the insertion, but may be affected by the Max_allowed_packet parameter and cause the insertion to fail. Therefore, you need to use this parameter with caution, at least I do not recommend it.
--extended-insert = True|false
By default, Mysqldump opens the--complete-insert mode, so if you don't want to use it, use this option to set its value to false.
--default-character-set=charset
Specifies what character set to use when exporting data, and if the datasheet is not the default Latin1 character set, you must specify this option when exporting, otherwise the garbled problem will occur after you import the data again.
--disable-keys
Tell mysqldump to add/*!40000 ALTER table Table DISABLE KEYS at the beginning and end of the INSERT statement. and/*!40000 ALTER table table to ENABLE the KEYS * *; statement, which greatly increases the speed of the INSERT statement because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.
--hex-blob
Exports a binary string field using hexadecimal format. You must use this option if you have binary data. The field types that are affected are BINARY, VARBINARY, and blobs.
--lock-all-tables,-x
Commit the request to lock all tables in all databases before starting the export to ensure data consistency. This is a global read lock and automatically turns off the--single-transaction and--lock-tables options.
--lock-tables
Similar to--lock-all-tables, it locks the currently exported datasheet instead of locking the table under all the libraries at once. This option applies only to MyISAM tables, and if the Innodb table can use the--single-transaction option.
--no-create-info,-t
Exports only data without adding a CREATE TABLE statement.
--no-data,-d
Does not export any data, only the database table structure is exported.
--opt
This is just a shortcut option, equivalent to adding--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables- Quick--set-charset option. This option allows mysqldump to quickly export data, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with--skip-opt. Note that if you run mysqldump without specifying the--quick or--opt option, the entire result set is placed in memory. Problems may occur if you export a large database.
--quick,-q
This option is useful when exporting large tables, forcing mysqldump to obtain records directly from a server query instead of having all the records cached in memory.
--routines,-r
Export stored procedures and custom functions.
--single-transaction
This option submits a BEGIN SQL statement before the data is exported, and the begin does not block any applications and guarantees the consistency state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB.
This option and the--lock-tables option are mutually exclusive, because lock tables will cause any pending transactions to be implicitly committed.
To export a large table, you should use the--quick option in combination.
--triggers
The trigger is also exported. This option is enabled by default, and it is disabled with--skip-triggers.
3.mysqlhotcopy Backup
Mysqlhotcopy is a Perl script that was 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 quickest 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 backup MyISAM. It runs in Unix and NetWare
Use the method to see the following script. Join the Crotab.
#!/bin/sh
# Name:mysqlbackup.sh
# ps:mysql DataBase backup,use mysqlhotcopy script.
# Last Modify:2008-06-12
# define variables, please modify them as appropriate
# define the directory where the script is located
Scriptsdir= ' pwd '
# Data Directory for database
Datadir=/var/lib/mysql
# Data Backup Directory
Tmpbackupdir=/tmp/mysqlblackup
Backupdir=/backup/mysql
# User name and password used to back up the database
Mysqluser=root
mysqlpwd= ' You password '
# If the temporary backup directory exists, empty it, and create it if it does not 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 a list of database backups, where you can filter the databases you don't want to back up
For databases in ' find $dataDir-type D | \
Sed-e "s/\/var\/lib\/mysql\///" | \
Sed-e "s/test//"; Todo
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完成
Add to Crontab set 5 run per week
0 0 * * 5/backup/blackup.sh
Note: Restore the state of the database to the time of backup
Mysqlhotcopy Backup is the entire database directory, use can be directly copied to the mysqld specified DataDir (here is/var/lib/mysql/) directory, but also attention to the issue of permissions, the following example:
Shell> CP-RF db_name/var/lib/mysql/
Shell> chown-r mysql:mysql/var/lib/mysql/(convert db_name directory owner to Mysqld run user)
This set of backup policies can only restore the state of the database to the last backup, so that the data lost in the crash should be backed up as infrequently as possible, and the master-slave replication mechanism (replication) is used to restore the data to the state of the crash.
Small tips:
Do not want to write the password in the shell, you can create a. my.cnf file in the home directory of root to allow mysqlhotcopy to read the username/password from it.
[Mysqlhotcopy]
User=root
Password=yourpassword
Then be safe, chmod.
chmod ~/.my.cnf
Attached: mysqlhotcopy commonly used parameters:
· --allowold if the target exists do not give up (plus a _old suffix to rename it).
· --checkpoint=db_name.tbl_name inserts a checkpoint entry in the specified db_name.tbl_name.
· Debug output is enabled---Debug.
· --dryrun,-n report actions without executing them.
· Refresh the log after all tables have been locked--flushlog.
· The previously (renamed) target is not deleted after the--keepold is completed.
· --Method=command Replication method (CP or SCP).
· All index files are not included in the--noindices backup. This makes backup smaller and faster. You can reconstruct the index later with MYISAMCHK-RQ.
· --password=password,-p password The password to use when connecting to the server. Please 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 that is used when connecting to the local server.
· --quiet,-q keep silent except when there are errors.
· --regexp=expr Copies all database names that match the regular expression given.
· --socket=path,-s path is used for connecting UNIX socket files.
· --suffix=str the suffix of the database name that is replicated.
· --tmpdir=path Temp directory (instead of/tmp).
· --user=user_name,-u user_name the MySQL user name to use 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, with SELECT permissions and reload permissions for those tables (so that you can perform flush tables).