The most comprehensive MySQL backup method ever
- 2020-05-09 19:29:20
- OfStack
I have been
The backup methods used are :mysqldump, mysqlhotcopy, BACKUP TABLE, SELECT INTO
OUTFILE, or backup binary log (binlog), or directly copy data files and associated configuration files. MyISAM
Tables are saved as files, so they are relatively easy to back up, and you can use any of the methods mentioned above. All Innodb tables are stored in the same data file, ibdata1
Medium (or multiple files, or separate tablespace files) is relatively difficult to back up. The free option is to copy data files, back up binlog, or use
mysqldump.
1. mysqldump backup
mysqldump is a backup mechanism based on SQL level, which converts data tables into SQL script files. It is relatively suitable for upgrading between different versions of MySQL, which is also the most common backup method.
Example: mysqldump-uroot-p database table > /home/jobs/back.sql
mysqldump can also do incremental backup. There are many parameters related to mysqldump on the Internet, so I won't go into details here
2. mysqlhotcopy backup
mysqlhotcopy is an PERL program. 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 files (including table definition files, data files, index files) reside.
mysqlhotcopy can only be used to backup MyISAM and only run on Unix and NetWare systems.
mysqlhotcopy supports one copy of multiple databases, as well as regular expression.
Example: root # / usr local/mysql bin/mysqlhotcopy - h = localhost - u = root
-p =123456 database /tmp (copy the database directory database to /tmp
) root # / usr local/mysql bin/mysqlhotcopy - h = localhost - u = root - p = 123456
db_name_1 ... db_name_n /tmproot#/usr/local/mysql/bin/mysqlhotcopy
-h=localhost -u=root -p=123456 db_name./regex/
For more detailed instructions on how to use tmp, please refer to the manual, or call the following command to view mysqlhotcopy's help:
perldoc usr/local/mysql/bin/mysqlhotcopy note that want to use mysqlhotcopy, must want to have
SELECT, RELOAD(to execute FLUSH TABLES) permissions, and you must also be able to read the datadir/db_name directory.
Restore mysqlhotcopy backup is the entire database directory, you can directly copy to mysqld
Specified datadir (in this case, / usr local mysql/data /) directory, at the same time to pay attention to the problem of authority, the following cases: root # cp
-rf db_name /usr/local/mysql/data/root#chown -R nobody:nobody
/ usr/local/mysql data/(will db_name directory owner to mysqld running user)
3.SQL grammar backup
3.1 backup BACKUP TABLE syntax actually and mysqlhotcopy
It works like this, locking the table and copying the data file. It can be backed up online, but it's not ideal, so it's not recommended. It copies only the table structure file and data file, not the index text at the same time
B, so recovery is slow. Example: BACK TABLE tbl_name TO '/tmp/db_name/'; Note that you must have FILE
Permission is required to execute this SQL, and the directory /tmp/db_name/ must be writable by mysqld users. Exported files cannot overwrite existing files to avoid security issues.
To restore files backed up by the BACKUP TABLE method, you can run the RESTORE TABLE statement to restore the data table. Example: RESTORE TABLE FROM '/tmp/db_name/'; The permission requirements are similar to those described above.
3.2 SELECT INTO OUTFILE exports data into plain text files. You can customize the method of field spacing to facilitate the processing of the data. Example:
SELECT INTO OUTFILE '/tmp/db_name/ tbl_name. txt' FROM tbl_name; There has to be
The FILE permission is required to execute this SQL, and the file /tmp/db_name/ tbl_name.txt must be able to be mysqld
User-writable, exported files cannot overwrite existing files to avoid security issues.
You can run the LOAD DATA INFILE statement to restore the data table from the files backed up by the SELECT INTO OUTFILE method. Example: LOAD
DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE
tbl_name; The permission requirements are similar to those described above. The table must already exist before the data is poured into it. If you're worried about data duplication, you can add REPLACE
Replace existing records with keywords or ignore them with the IGNORE keyword.
4. Enable binary logging (binlog)
The binlog method is relatively flexible, saves worry and effort, and can also support incremental backup.
To enable binlog, you must restart mysqld. First, close mysqld, open my.cnf, and add the following lines:
server-id = 1
log-bin = binlog
log-bin-index = binlog.index
Then launch mysqld. When running, binlog.000001 and binlog.index will be generated. The previous file is mysqld
Record all updates to the data, and the following files are indexes of all binlog, which cannot be deleted easily. Please refer to the manual for information about binlog.
When you need a backup, you can first execute 1 SQL statement and have mysqld terminate the current binlog
, you can directly backup the file, so that you can achieve the purpose of incremental backup: FLUSH LOGS; If you are backing up a slave server in a replication system, you should also back up
master.info and relay-log.info.
The backup binlog files can be viewed using the mysqlbinlog tools provided by MySQL, such as:
/ usr/local/mysql bin/mysqlbinlog tmp/binlog. 000001 this tool allows you to display all under the specified database
SQL statement, and can also limit the time range, quite convenient, please refer to the manual for details.
Recovery, similar to the following statements can be used to do: / usr/local/mysql bin/mysqlbinlog tmp/binlog. 000001
| mysql-uyejr-pyejr db_name executes the SQL statement output by mysqlbinlog directly as input.
If you have a spare machine, back it up this way. As the machine performance requirements of slave are relatively not so high, the cost is low. With low cost, incremental backup can be realized and part of the data query pressure can be Shared. Why not?
5. Copy files
Compared with the previous methods, the direct backup of data files is the most direct, fast and convenient, with the disadvantage that incremental backup can hardly be achieved.
In order to ensure the 1 uniformity of the data, the following SQL statements need to be executed before the backrest file: FLUSH TABLES WITH READ
LOCK; This means flushing all the data in memory to disk and locking the data table to ensure that no new data is written during the copying process. This method backup out of the data recovery is also very simple, directly copy back
The original database directory can be.
Note that for the Innodb type table, you also need to back up its log file, which is the ib_logfile* file. Because when the Innodb table is broken, you can rely on these log files to recover.
6. Take advantage of rsync backup
rsync can also be used as a synchronization tool to do backups, but both the server and the client are configured
Example rsync-vzrtopg --progress --delete root@192.168.1.3::root /tmp/
Related rsync configuration reference http: / / fanqiang chinaunix. net/a6 b7/20010908/1305001258. html
The disadvantage is that rsync is an incremental backup made according to the file modification time, so the backup database is all ready, and the configuration is troublesome.
You can also refer to the following article
mysql database backup and restore method collection
The backup methods used are :mysqldump, mysqlhotcopy, BACKUP TABLE, SELECT INTO
OUTFILE, or backup binary log (binlog), or directly copy data files and associated configuration files. MyISAM
Tables are saved as files, so they are relatively easy to back up, and you can use any of the methods mentioned above. All Innodb tables are stored in the same data file, ibdata1
Medium (or multiple files, or separate tablespace files) is relatively difficult to back up. The free option is to copy data files, back up binlog, or use
mysqldump.
1. mysqldump backup
mysqldump is a backup mechanism based on SQL level, which converts data tables into SQL script files. It is relatively suitable for upgrading between different versions of MySQL, which is also the most common backup method.
Example: mysqldump-uroot-p database table > /home/jobs/back.sql
mysqldump can also do incremental backup. There are many parameters related to mysqldump on the Internet, so I won't go into details here
2. mysqlhotcopy backup
mysqlhotcopy is an PERL program. 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 files (including table definition files, data files, index files) reside.
mysqlhotcopy can only be used to backup MyISAM and only run on Unix and NetWare systems.
mysqlhotcopy supports one copy of multiple databases, as well as regular expression.
Example: root # / usr local/mysql bin/mysqlhotcopy - h = localhost - u = root
-p =123456 database /tmp (copy the database directory database to /tmp
) root # / usr local/mysql bin/mysqlhotcopy - h = localhost - u = root - p = 123456
db_name_1 ... db_name_n /tmproot#/usr/local/mysql/bin/mysqlhotcopy
-h=localhost -u=root -p=123456 db_name./regex/
For more detailed instructions on how to use tmp, please refer to the manual, or call the following command to view mysqlhotcopy's help:
perldoc usr/local/mysql/bin/mysqlhotcopy note that want to use mysqlhotcopy, must want to have
SELECT, RELOAD(to execute FLUSH TABLES) permissions, and you must also be able to read the datadir/db_name directory.
Restore mysqlhotcopy backup is the entire database directory, you can directly copy to mysqld
Specified datadir (in this case, / usr local mysql/data /) directory, at the same time to pay attention to the problem of authority, the following cases: root # cp
-rf db_name /usr/local/mysql/data/root#chown -R nobody:nobody
/ usr/local/mysql data/(will db_name directory owner to mysqld running user)
3.SQL grammar backup
3.1 backup BACKUP TABLE syntax actually and mysqlhotcopy
It works like this, locking the table and copying the data file. It can be backed up online, but it's not ideal, so it's not recommended. It copies only the table structure file and data file, not the index text at the same time
B, so recovery is slow. Example: BACK TABLE tbl_name TO '/tmp/db_name/'; Note that you must have FILE
Permission is required to execute this SQL, and the directory /tmp/db_name/ must be writable by mysqld users. Exported files cannot overwrite existing files to avoid security issues.
To restore files backed up by the BACKUP TABLE method, you can run the RESTORE TABLE statement to restore the data table. Example: RESTORE TABLE FROM '/tmp/db_name/'; The permission requirements are similar to those described above.
3.2 SELECT INTO OUTFILE exports data into plain text files. You can customize the method of field spacing to facilitate the processing of the data. Example:
SELECT INTO OUTFILE '/tmp/db_name/ tbl_name. txt' FROM tbl_name; There has to be
The FILE permission is required to execute this SQL, and the file /tmp/db_name/ tbl_name.txt must be able to be mysqld
User-writable, exported files cannot overwrite existing files to avoid security issues.
You can run the LOAD DATA INFILE statement to restore the data table from the files backed up by the SELECT INTO OUTFILE method. Example: LOAD
DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE
tbl_name; The permission requirements are similar to those described above. The table must already exist before the data is poured into it. If you're worried about data duplication, you can add REPLACE
Replace existing records with keywords or ignore them with the IGNORE keyword.
4. Enable binary logging (binlog)
The binlog method is relatively flexible, saves worry and effort, and can also support incremental backup.
To enable binlog, you must restart mysqld. First, close mysqld, open my.cnf, and add the following lines:
server-id = 1
log-bin = binlog
log-bin-index = binlog.index
Then launch mysqld. When running, binlog.000001 and binlog.index will be generated. The previous file is mysqld
Record all updates to the data, and the following files are indexes of all binlog, which cannot be deleted easily. Please refer to the manual for information about binlog.
When you need a backup, you can first execute 1 SQL statement and have mysqld terminate the current binlog
, you can directly backup the file, so that you can achieve the purpose of incremental backup: FLUSH LOGS; If you are backing up a slave server in a replication system, you should also back up
master.info and relay-log.info.
The backup binlog files can be viewed using the mysqlbinlog tools provided by MySQL, such as:
/ usr/local/mysql bin/mysqlbinlog tmp/binlog. 000001 this tool allows you to display all under the specified database
SQL statement, and can also limit the time range, quite convenient, please refer to the manual for details.
Recovery, similar to the following statements can be used to do: / usr/local/mysql bin/mysqlbinlog tmp/binlog. 000001
| mysql-uyejr-pyejr db_name executes the SQL statement output by mysqlbinlog directly as input.
If you have a spare machine, back it up this way. As the machine performance requirements of slave are relatively not so high, the cost is low. With low cost, incremental backup can be realized and part of the data query pressure can be Shared. Why not?
5. Copy files
Compared with the previous methods, the direct backup of data files is the most direct, fast and convenient, with the disadvantage that incremental backup can hardly be achieved.
In order to ensure the 1 uniformity of the data, the following SQL statements need to be executed before the backrest file: FLUSH TABLES WITH READ
LOCK; This means flushing all the data in memory to disk and locking the data table to ensure that no new data is written during the copying process. This method backup out of the data recovery is also very simple, directly copy back
The original database directory can be.
Note that for the Innodb type table, you also need to back up its log file, which is the ib_logfile* file. Because when the Innodb table is broken, you can rely on these log files to recover.
6. Take advantage of rsync backup
rsync can also be used as a synchronization tool to do backups, but both the server and the client are configured
Example rsync-vzrtopg --progress --delete root@192.168.1.3::root /tmp/
Related rsync configuration reference http: / / fanqiang chinaunix. net/a6 b7/20010908/1305001258. html
The disadvantage is that rsync is an incremental backup made according to the file modification time, so the backup database is all ready, and the configuration is troublesome.
You can also refer to the following article
mysql database backup and restore method collection