Common backup methods and considerations for Mysql databases
- 2020-05-09 19:28:41
- OfStack
There are three common methods for Mysql database backup:
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 the direct backup method, you must make sure that the table is not in use. If the server changes a table while you are copying it, the copy is meaningless.
The best way to ensure copy integrity is to shut down the server, copy the files, and restart the server. If you don't want to shut down the server, 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 "quiet down" the server.
When you have done the backup, either restart the server (if it is shut down) or release the lock placed on the table (if you let the server run).
To copy a database from one machine to another with a direct copy file, simply copy the file to the appropriate data directory on the other server host. Make sure the files are in MyIASM format or the two machines have the same hardware structure, otherwise your database will have strange content on another host. You should also ensure that the server on the other machine does not access the database tables while you are installing them.
2.mysqldump
Basic grammar:
Shell > mysqldump [OPTIONS] database [tables] > data_backup.sql (do not specify a database name for all backups)
Such as:
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 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. Ex. :
%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 # backs up some tables of the database
%mysqladmin -h boa.snake.net create samp_db
%mysqldump samp_db | mysql-h boa.snake.net samp_db # restore directly to another server using the -- add_snake.net samp_db option tells the server to write the DROP TABLE IF EXISTS statement to the backup file, so that when we restore the database later, if the table already exists, You're not going to get 1 error.
%mysqldump --add-drop-table samp_db | mysql -h boa.snake.net samp_db
Other useful options for mysqldump include:
The combination of -- flush-logs and -- lock-tables will help you with your database checkpoint. -- lock-tables locks all the tables you are dumping, while -- flush-logs closes and re-opens the update log file. The new update log will only include queries that modify the database from the backup point. This will set your update log check point 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's probably best to dump the entire database. If you dump individual files, it is harder to synchronize the update log checkpoint with the backup files. During recovery, you typically extract the update log content on a database basis, and there is no option to extract updates to individual tables, so you must extract them yourself.
By default, mysqldump reads the entire contents of a table into memory before writing. This is usually not necessary, and in fact if you have a big table, it's almost a failure. You can use the --quick option to tell mysqldump to write out each row as soon as it retrieves one. To further optimize the dumping process, use --opt instead of --quick. --opt option opens other options to speed up the dumping of data and read it back.
Using --opt for backup is probably the most common method because of the speed advantage. However, I should warn you that -- the opt option does come at a cost --opt is optimized for your backup process, not for other customers' access to the database. -- the opt option prevents anyone from updating any table you're dumping by locking all tables once. You can easily see the effect on type 1 database access.
One option that has the opposite effect of --opt is --dedayed. This option causes mysqldump to write INSERT DELAYED statements instead of INSERT statements. If you load a data file into another database and you want to minimize the impact on queries that might appear in that database --delayed is helpful.
The compress option is useful when you copy a database to another machine because it reduces the number of bytes transferred over the network. Here is an example, notice that --compress gives a program that communicates with a server on a remote host, not a program that connects to a localhost:
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump has many other options. The main parameters are:
--compatible=name
It tells mysqldump which database or which older version of MySQL server the exported data will be compatible with. The values can be ansi, mysql323, mysql40, postgresql, oracle, db2, maxdb, no_key_options, no_tables_options, no_field_options, no_key_options, no_field_options, and so on, separated by a comma. Of course, it's not guaranteed to be completely compatible, but as compatible as possible.
-- complete - insert - c
The exported data takes the full INSERT approach with field names, which means that all values are written in line 1. This improves insertion efficiency, but may be affected by the max_allowed_packet parameter and cause the insertion to fail. Therefore, this parameter needs to be used with caution, at least I don't recommend it.
--extended-insert = true|false
By default, mysqldump is on -- complete-insert, so if you don't want to use it, use this option and set it to false.
--default-character-set=charset
Specify the character set to use when exporting data. If the data table is not in the default latin1 character set, then this option must be specified when exporting, otherwise the data will be imported again.
--disable-keys
Tell mysqldump to add /*! To the beginning and end of INSERT statements. 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 rebuilds the index after all the data has been inserted. This option applies only to the MyISAM table.
--hex-blob
Export a binary string field using the base 106 format. You must use this option if you have data in base 2. The field types affected are BINARY, VARBINARY, BLOB.
- lock all - tables - x
Before starting the export, submit a request to lock all tables in all databases to ensure the 1 uniqueness of the data. This is a global read lock and automatically closes the -- single-transaction and -- lock-tables options.
--lock-tables
It is similar to -- lock-all-tables, except that it locks the currently exported table, rather than 1 subset locking all the tables under the library. This option only applies to the MyISAM table. For the Innodb table, you can use the -- single-transaction option.
- no create - info - t
Export only data without adding CREATE TABLE statements.
-- no - data - d
No data is exported, only the database table structure is exported.
--opt
This is just one shortcut option and is equivalent to adding -- add-drop-tables -- add-locking -- disable-keys -- extended-tables --quick -- set-charset 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 that if running mysqldump does not specify the --quick or --opt option, the entire result set is placed in memory. This can be problematic if you export a large database.
-- quick - q
This option is useful when exporting large tables, as it forces mysqldump to fetch records directly from a server query rather than fetch all records and cache them in memory.
-- routines - R
Export stored procedures as well as custom functions.
--single-transaction
This option commits an BEGIN SQL statement before exporting the data. BEGIN does not block any application and guarantees the 1 uniqueness 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 implicitly commits any pending transaction.
To export large tables, use the --quick option in combination.
--triggers
Export the trigger at the same time. This option is enabled by default and disabled with -- skip-triggers.
3. mysqlhotcopy backup
mysqlhotcopy is an Perl script originally written and provided by Tim Bunce. It USES LOCK TABLES, FLUSH TABLES, 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 only runs on the machine where the database directory resides. mysqlhotcopy is only used 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:MySQL DataBase Backup,Use mysqlhotcopy script.
# Last Modify:2008-06-12
Define variables, please modify them accordingly
Define the directory in which the script resides
scriptsDir=`pwd`
Data directory for # database
dataDir=/var/lib/mysql
Data backup directory
tmpBackupDir=/tmp/mysqlblackup
backupDir=/backup/mysql
Username and password used to backup the database
mysqlUser=root
mysqlPWD='you password'
Empty the temporary backup directory if it exists, or create it if it does not
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 database backup list, where you can filter out databases that you don't want to back up
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 the backup file
date=`date -I`
cd $tmpBackupDir
tar czf $backupDir/mysql-$date.tar.gz ./
# End complete
Join crontab to set up 5 runs per week
0 0 * * 5 /backup/blackup.sh
Note: restore the database to its backup state
mysqlhotcopy backup is the entire database directory, when using can be directly copied into mysqld specified datadir (in this case, / var/lib mysql /) directory, at the same time to pay attention to the problem of authority, the following cases:
shell > cp -rf db_name /var/lib/mysql/
shell > chown - R mysql: mysql/var/lib/mysql/(will db_name directory owner to mysqld running user)
This set of backup policy can only restore the state of the database to the last backup. In order to lose as little data as possible in the crash, more frequent backups should be conducted. In order to restore the data to the state in the crash, please use the master-slave replication mechanism (replication).
Tip:
Instead of writing your password in shell, create a.my.cnf file in root's home directory so that mysqlhotcopy can read your username/password from it.
[mysqlhotcopy]
user=root
password=YourPassword
Then to be safe, chmod1.
chmod 600 ~/.my.cnf
Attachment :mysqlhotcopy common parameters:
· --allowold if the target exists do not abandon (add a _old suffix to rename it).
· --checkpoint= db_name.tbl_name inserts a checkpoint entry at the specified db_name.tbl_name.
· --debug enables debug output.
· --dryrun, -n reports actions without executing them.
· -- refresh the log after all flushlog tables are locked.
· --keepold does not delete the previous (renamed) target after completion.
· -- method=command copy method (cp or scp).
· -- not all index files are included in the noindices backup. This makes backups smaller and faster. You can rebuild the index later with myisamchk-rq.
· --password=password, -p password password used when connecting to a server. Note that the password value for this option is not optional, unlike other MySQL programs.
· --port=port_num, -P port_num TCP/IP port number used when connecting to a local server.
· --quiet, -- q keep silent except when there are mistakes.
· --regexp=expr copies all the database names that match the given regular expression.
· --socket=path, -S path socket file for connection.
· -- suffix of database name copied by suffix=str.
· --tmpdir=path temporary directory (instead of /tmp).
· --user=user_name, -u user_name MySQL user name used when connecting to a server.
mysqlhotcopy reads the [client] and [mysqlhotcopy] option groups from the options file. To execute mysqlhotcopy, you must have access to the backup table files, have the SELECT permissions for those tables, and have the RELOAD permissions for those tables (in order to be able to execute FLUSH TABLES).
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 the direct backup method, you must make sure that the table is not in use. If the server changes a table while you are copying it, the copy is meaningless.
The best way to ensure copy integrity is to shut down the server, copy the files, and restart the server. If you don't want to shut down the server, 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 "quiet down" the server.
When you have done the backup, either restart the server (if it is shut down) or release the lock placed on the table (if you let the server run).
To copy a database from one machine to another with a direct copy file, simply copy the file to the appropriate data directory on the other server host. Make sure the files are in MyIASM format or the two machines have the same hardware structure, otherwise your database will have strange content on another host. You should also ensure that the server on the other machine does not access the database tables while you are installing them.
2.mysqldump
Basic grammar:
Shell > mysqldump [OPTIONS] database [tables] > data_backup.sql (do not specify a database name for all backups)
Such as:
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 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. Ex. :
%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 # backs up some tables of the database
%mysqladmin -h boa.snake.net create samp_db
%mysqldump samp_db | mysql-h boa.snake.net samp_db # restore directly to another server using the -- add_snake.net samp_db option tells the server to write the DROP TABLE IF EXISTS statement to the backup file, so that when we restore the database later, if the table already exists, You're not going to get 1 error.
%mysqldump --add-drop-table samp_db | mysql -h boa.snake.net samp_db
Other useful options for mysqldump include:
The combination of -- flush-logs and -- lock-tables will help you with your database checkpoint. -- lock-tables locks all the tables you are dumping, while -- flush-logs closes and re-opens the update log file. The new update log will only include queries that modify the database from the backup point. This will set your update log check point 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's probably best to dump the entire database. If you dump individual files, it is harder to synchronize the update log checkpoint with the backup files. During recovery, you typically extract the update log content on a database basis, and there is no option to extract updates to individual tables, so you must extract them yourself.
By default, mysqldump reads the entire contents of a table into memory before writing. This is usually not necessary, and in fact if you have a big table, it's almost a failure. You can use the --quick option to tell mysqldump to write out each row as soon as it retrieves one. To further optimize the dumping process, use --opt instead of --quick. --opt option opens other options to speed up the dumping of data and read it back.
Using --opt for backup is probably the most common method because of the speed advantage. However, I should warn you that -- the opt option does come at a cost --opt is optimized for your backup process, not for other customers' access to the database. -- the opt option prevents anyone from updating any table you're dumping by locking all tables once. You can easily see the effect on type 1 database access.
One option that has the opposite effect of --opt is --dedayed. This option causes mysqldump to write INSERT DELAYED statements instead of INSERT statements. If you load a data file into another database and you want to minimize the impact on queries that might appear in that database --delayed is helpful.
The compress option is useful when you copy a database to another machine because it reduces the number of bytes transferred over the network. Here is an example, notice that --compress gives a program that communicates with a server on a remote host, not a program that connects to a localhost:
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump has many other options. The main parameters are:
--compatible=name
It tells mysqldump which database or which older version of MySQL server the exported data will be compatible with. The values can be ansi, mysql323, mysql40, postgresql, oracle, db2, maxdb, no_key_options, no_tables_options, no_field_options, no_key_options, no_field_options, and so on, separated by a comma. Of course, it's not guaranteed to be completely compatible, but as compatible as possible.
-- complete - insert - c
The exported data takes the full INSERT approach with field names, which means that all values are written in line 1. This improves insertion efficiency, but may be affected by the max_allowed_packet parameter and cause the insertion to fail. Therefore, this parameter needs to be used with caution, at least I don't recommend it.
--extended-insert = true|false
By default, mysqldump is on -- complete-insert, so if you don't want to use it, use this option and set it to false.
--default-character-set=charset
Specify the character set to use when exporting data. If the data table is not in the default latin1 character set, then this option must be specified when exporting, otherwise the data will be imported again.
--disable-keys
Tell mysqldump to add /*! To the beginning and end of INSERT statements. 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 rebuilds the index after all the data has been inserted. This option applies only to the MyISAM table.
--hex-blob
Export a binary string field using the base 106 format. You must use this option if you have data in base 2. The field types affected are BINARY, VARBINARY, BLOB.
- lock all - tables - x
Before starting the export, submit a request to lock all tables in all databases to ensure the 1 uniqueness of the data. This is a global read lock and automatically closes the -- single-transaction and -- lock-tables options.
--lock-tables
It is similar to -- lock-all-tables, except that it locks the currently exported table, rather than 1 subset locking all the tables under the library. This option only applies to the MyISAM table. For the Innodb table, you can use the -- single-transaction option.
- no create - info - t
Export only data without adding CREATE TABLE statements.
-- no - data - d
No data is exported, only the database table structure is exported.
--opt
This is just one shortcut option and is equivalent to adding -- add-drop-tables -- add-locking -- disable-keys -- extended-tables --quick -- set-charset 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 that if running mysqldump does not specify the --quick or --opt option, the entire result set is placed in memory. This can be problematic if you export a large database.
-- quick - q
This option is useful when exporting large tables, as it forces mysqldump to fetch records directly from a server query rather than fetch all records and cache them in memory.
-- routines - R
Export stored procedures as well as custom functions.
--single-transaction
This option commits an BEGIN SQL statement before exporting the data. BEGIN does not block any application and guarantees the 1 uniqueness 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 implicitly commits any pending transaction.
To export large tables, use the --quick option in combination.
--triggers
Export the trigger at the same time. This option is enabled by default and disabled with -- skip-triggers.
3. mysqlhotcopy backup
mysqlhotcopy is an Perl script originally written and provided by Tim Bunce. It USES LOCK TABLES, FLUSH TABLES, 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 only runs on the machine where the database directory resides. mysqlhotcopy is only used 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:MySQL DataBase Backup,Use mysqlhotcopy script.
# Last Modify:2008-06-12
Define variables, please modify them accordingly
Define the directory in which the script resides
scriptsDir=`pwd`
Data directory for # database
dataDir=/var/lib/mysql
Data backup directory
tmpBackupDir=/tmp/mysqlblackup
backupDir=/backup/mysql
Username and password used to backup the database
mysqlUser=root
mysqlPWD='you password'
Empty the temporary backup directory if it exists, or create it if it does not
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 database backup list, where you can filter out databases that you don't want to back up
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 the backup file
date=`date -I`
cd $tmpBackupDir
tar czf $backupDir/mysql-$date.tar.gz ./
# End complete
Join crontab to set up 5 runs per week
0 0 * * 5 /backup/blackup.sh
Note: restore the database to its backup state
mysqlhotcopy backup is the entire database directory, when using can be directly copied into mysqld specified datadir (in this case, / var/lib mysql /) directory, at the same time to pay attention to the problem of authority, the following cases:
shell > cp -rf db_name /var/lib/mysql/
shell > chown - R mysql: mysql/var/lib/mysql/(will db_name directory owner to mysqld running user)
This set of backup policy can only restore the state of the database to the last backup. In order to lose as little data as possible in the crash, more frequent backups should be conducted. In order to restore the data to the state in the crash, please use the master-slave replication mechanism (replication).
Tip:
Instead of writing your password in shell, create a.my.cnf file in root's home directory so that mysqlhotcopy can read your username/password from it.
[mysqlhotcopy]
user=root
password=YourPassword
Then to be safe, chmod1.
chmod 600 ~/.my.cnf
Attachment :mysqlhotcopy common parameters:
· --allowold if the target exists do not abandon (add a _old suffix to rename it).
· --checkpoint= db_name.tbl_name inserts a checkpoint entry at the specified db_name.tbl_name.
· --debug enables debug output.
· --dryrun, -n reports actions without executing them.
· -- refresh the log after all flushlog tables are locked.
· --keepold does not delete the previous (renamed) target after completion.
· -- method=command copy method (cp or scp).
· -- not all index files are included in the noindices backup. This makes backups smaller and faster. You can rebuild the index later with myisamchk-rq.
· --password=password, -p password password used when connecting to a server. Note that the password value for this option is not optional, unlike other MySQL programs.
· --port=port_num, -P port_num TCP/IP port number used when connecting to a local server.
· --quiet, -- q keep silent except when there are mistakes.
· --regexp=expr copies all the database names that match the given regular expression.
· --socket=path, -S path socket file for connection.
· -- suffix of database name copied by suffix=str.
· --tmpdir=path temporary directory (instead of /tmp).
· --user=user_name, -u user_name MySQL user name used when connecting to a server.
mysqlhotcopy reads the [client] and [mysqlhotcopy] option groups from the options file. To execute mysqlhotcopy, you must have access to the backup table files, have the SELECT permissions for those tables, and have the RELOAD permissions for those tables (in order to be able to execute FLUSH TABLES).