Common backup methods and precautions for Mysql databases. For more information, see.
Common backup methods and precautions for Mysql databases. For more information, see.
There are three common methods for Mysql database backup:
1. Directly copy (cp, tar, gzip, cpio)
2. mysqldump
3. mysqlhotcopy
1. Use Direct Copy Database Backup
Typical utilities such as cp, tar, or cpio.
When using the direct backup method, you must ensure that the table is not used. If the server changes a table while you are copying it, the copy will be meaningless.
The best way to ensure your copy integrity is to close the server, copy files, and restart the server. If you do not want to shut down the server, you must lock the server while performing the table check. If the server is running, the same constraints apply to copying files, and you should use the same locking protocol to make the server "quiet ".
When you have completed the backup, you need to restart the server (if it is disabled) or release the lock added to the table (if you want the server to run ).
To copy a database from one machine to another, just copy the file to the appropriate data directory of another server host. Make sure the file is in MyIASM format or the two machines have the same hardware structure. Otherwise, your database has strange content on another host. You should also ensure that the servers on the other machine do not access them when you are installing database tables.
2. mysqldump
Basic Syntax:
Shell> mysqldump [OPTIONS] database [tables]> data_backup. SQL (if the database name is not specified, all backups are performed)
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 (10) unsigned DEFAULT '0' not null,
Date DEFAULT '2014-00-00 'not null,
Primary key (student_id, date)
);
#
# Dumping data for table 'absence'
#
Insert into absence VALUES (3, '2017-09-03 ');
Insert into absence VALUES (5, '2017-09-03 ');
Insert into absence VALUES (10, '2017-09-08 ');
......
The rest of the file consists of more INSERT and create table statements. Example:
% Mysqldump samp_db>/opt/mysqldatabak/samp_db.2006-5-15
% Mysqldump samp_db | gzip>/usr/archives/mysql/samp_db.1999-10-02.gz # produce compressed backup
% Mysqldump samp_db student score event absence> grapbook. SQL # Back up some tables in the database
% Mysqladmin-h boa.snke.net create samp_db
% Mysqldump samp_db | mysql-h boa.snke.net samp_db # restore directly to another server. Use the -- add-drop-table option to tell the server to write the drop table if exists statement to the backup file, in this way, if the table already exists when we use it to restore the database, you will not get an error.
% Mysqldump -- add-drop-table samp_db | mysql-h boa.snke.net samp_db
Other useful options for mysqldump include:
The combination of -- flush-logs and -- lock-tables will be helpful for your database checkpoints. -- Lock-tables locks all the tables you are dumping, and -- flush-logs closes and re-opens the Update log file, the new update log will only include queries for modifying the database from the backup point. This will set the backup time of your Update log check point. (However, if you have a customer who needs to perform an update, it is not a good thing to lock all tables for customer access during backup .)
If you use -- flush-logs to set the Check Point to backup, it is best to dumped the entire database. If you dumped individual files, it is difficult to synchronize the Update log checkpoint with the backup file. During the recovery period, you usually extract the updates based on the database, but you have no choice to extract updates for a single table. Therefore, you must extract them by yourself.
By default, mysqldump reads the entire content of a table into the memory before writing. This is usually unnecessary, and in fact, if you have a large table, it is almost a failure. You can use the -- quick option to tell mysqldump to write each row as long as it retrieves a row. To further optimize the dumping process, use -- opt instead of -- quick. -- Opt option opens other options to accelerate data dumping and read them back.
Using -- opt for backup may be the most common method, because of the advantages of backup speed. However, you should be warned that the -- opt option does have a price. -- opt optimizes your backup process rather than other customers accessing the database. The -- opt option prevents anyone from updating any table you are dumping by locking all tables at a time. You can easily see the effect of common database access.
An option with the opposite effect of -- opt is -- dedayed. This option causes mysqldump to write the insert delayed statement instead of the INSERT statement. If you load data files into another database and want to minimize the impact of this operation on queries that may occur in the database, -- delayed is helpful.
The -- compress option is helpful when you copy a database to another machine because it reduces the number of bytes transmitted over the network. The following example shows that -- compress only gives the program for communicating with the server on the remote host, rather than the program connected to the local host:
% Mysqldump -- opt samp_db | mysql -- compress-h boa.snke.net samp_db
Mysqldump has many other options, main parameters:
-- Compatible = name
It tells mysqldump that the exported data will be compatible with the database or the old MySQL server. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.
-- Complete-insert,-c
The exported data adopts the complete INSERT method containing the field name, that is, all values are written in one row. This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.
-- Extended-insert = true | false
By default, mysqldump enables the -- complete-insert mode, so if you do not want to use it, use this option to set its value to false.
-- Default-character-set = charset
Specifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again.
-- Disable-keys
Tell mysqldump to add/* at the beginning and end of the INSERT statement /*! 40000 alter table table disable keys */; And /*! 40000 alter table table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.
-- Hex-blob
Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.
-- Lock-all-tables,-x
Before starting the export, submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and the -- single-transaction and -- lock-tables options are automatically disabled.
-- Lock-tables
It is similar to -- lock-all-tables, but instead of locking all tables in the database. This option is only applicable to MyISAM tables. For Innodb tables, you can use the -- single-transaction option.
-- No-create-info,-t
Only export data without adding the create table statement.
-- No-data,-d
Only the database table structure is exported without exporting any data.
-- Opt
This is just a quick option, it is equivalent to adding the -- add-drop-tables -- add-locking -- create-option -- disable-keys -- extended-insert -- lock-tables -- quick -- set-charset option at the same time. This option allows mysqldump to export data quickly and export data back quickly. This option is enabled by default, but can be disabled with -- skip-opt. Note: If the -- quick or -- opt option is not specified when running mysqldump, the entire result set is stored in the memory. If you export a large database, problems may occur.
-- Quick,-q
This option is useful when exporting large tables. It forces mysqldump to retrieve records from the Server query and output them directly instead of caching all records into the memory.
-- Routines,-R
Export stored procedures and user-defined functions.
-- Single-transaction
This option submits a begin SQL statement before exporting data. BEGIN does not block any applications and ensures Database Consistency during export. It is only applicable to transaction tables, such as InnoDB and BDB.
This option and the -- lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions.
To export a large table, use the -- quick option.
-- Triggers
Export the trigger at the same time. This option is enabled by default. Use -- skip-triggers to disable it.
3. mysqlhotcopy backup
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: Restore the database to the backup status
Mysqlhotcopy backs up the entire database Directory, which can be copied directly to the datadir (/var/lib/mysql/) directory specified by mysqld, pay attention to permission issues as follows:
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
Appendix: mysqlhotcopy common parameters:
· -- Allowold if the target exists, do not give up (add a _ old suffix to rename it ).
· -- Checkpoint = db_name.tbl_name Insert the checkpoint entry in the specified db_name.tbl_name.
· --- Debug enables debugging output.
· -- Dryrun,-n reports actions without executing them.
· -- Flushlog refresh the log after all the tables are locked.
· -- After keepold is completed, the previous (renamed) targets are not deleted.
· -- Method = command COPY method (cp or scp ).
· -- Noindices backup does not include all index files. In this way, the backup is smaller and faster. You can use myisamchk-rq to re-build the index later.
· -- Password = password,-p password: the password used to connect to the server. Note that the password value of this option is optional, unlike other MySQL programs.
· -- Port = port_num,-P port_num indicates the TCP/IP port number used to connect to the local server.
· -- Quiet and-q remain silent except when an error occurs.
· -- Regexp = expr: copy the database that matches the regular expression given by all database names.
· -- Socket = path,-S path is used to connect Unix socket files.
· -- Suffix = suffix of the database name copied by str.
· -- Tmpdir = path temporary directory (instead of/tmp ).
· -- User = user_name,-u user_name: MySQL username used to connect to the server.
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 ).
