mysql 备份和恢复（MySQL backup and recovery）mysql 备份和恢复(MySQL backup and recovery)
MySQL backup and recovery
This article discusses the backup and recovery mechanisms for MySQL and how to maintain data tables, including the two main table types: MyISAM and Innodb, and the MySQL version of the article designed for
5.0.22. Free backup tools currently supported by MySQL: mysqldump, mysqlhotcopy, also can make a backup using SQL syntax: BACKUP TABLE or SELECT INTO OUTFILE, or binary log backup (binlog), can also be directly copy the data files and related configuration files. The MyISAM table is saved in a file form, so it's relatively easy to backup, and several of the methods mentioned above can be used. Innodb all the tables are stored in the same data in the ibdata1 file (possibly multiple files, or file table space independent), relatively good solutions can be free backup, copy a data file, backup binlog, or mysqldump.
1, mysqldump
1.1 backup
Mysqldump uses the SQL level backup mechanism, which leads the data table into SQL script file, which is relatively suitable for upgrading between different versions of MySQL, and this is the most commonly used backup method. Now let's talk about some of the main parameters of mysqldump:
--compatible=name, it tells mysqldump that the exported data will be compatible with either the database or the older version of the MySQL server.
Values can be ANSI, mysql323, mysql40, PostgreSQL, Oracle, MSSQL,
DB2, maxdb, no_key_options, no_tables_options, no_field_options, etc. several values should be used and separated by commas. Of course, it is not guaranteed to be fully compatible, but compatible as much as possible.
The data exported by --complete-insert and -c uses a full INSERT
that contains field names, that is, write all values in one line. Doing so improves the insertion efficiency, but may be affected by the
max_allowed_packet parameter causing the insertion to fail. Therefore, you need to be careful with this parameter, at least I don't recommend it.
--default-character-set=charset specifies the export data with what character set, if the data table is not the default Latin1 character set, then export this option must be specified, otherwise again import data will produce garbled.
--disable-keys told mysqldump at the beginning and end of the INSERT statement added 40000 ALTER TABLE table DISABLE / *! * / / * KEYS; and 40000 ALTER TABLE table ENABLE KEYS! * /; this statement can greatly improve the speed of the insert statement, because it is in the insert
all the data after the
reconstruction of the index. This option is only suitable for MyISAM tables.
--extended-insert = true|false, by default, mysqldump opens --complete-insert mode, so if you don't want to use it, use this option and set it to false.
--hex-blob exports binary string fields using the sixteen decimal format. If you have binary data, you must use this option. The field types that affect are BINARY, VARBINARY, and BLOB.
--lock-all-tables, -x, before you start exporting, submit requests to lock all tables in all databases to ensure data consistency. This is a global read lock, and automatically closes the --single-transaction
mysql下载add produceand --lock-tables options.
--lock-tables is similar to --lock-all-tables, but locks the currently exported data table instead of locking all the tables under the library at once. This option applies only to the MyISAM table, and if it is an Innodb table, you can use the --single-transaction option.
--no-create-info, -t only exports data without adding the CREATE TABLE statement.
--no-data, -d does not export any data, only export the database table structure.
--opt, this is just a shortcut option, 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 the exported data can be quickly returned. This option is turned on by default, but can be disabled by --skip-opt. Note that if you run mysqldump without specifying the --quick or --opt option, you will put the entire result set in memory. Problems can arise if you  export large databases.
--quick,
-q this option is useful when exporting large tables, which force mysqldump to retrieve records directly from the server query, and cache them directly into memory without obtaining all records.
--routines, -R export stored procedures, and custom functions.
--single-transaction this option provides a BEGIN SQL statement before exporting data, and BEGIN does not block any application and guarantees the consistency of the database when exporting. It applies only to transaction tables, such as InnoDB and BDB. This option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be implicitly committed. If you want to export large tables, you should combine the --quick option.
--triggers exports triggers at the same time. This option is enabled by default and is disabled with --skip-triggers.
Other parameters details please refer to the manual, I usually use the following SQL to MyISAM -uyejr -pyejr backup table:
/usr/local/mysql/bin/mysqldump --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob -x db_name > db_name.sql
using the following SQL to prepare Innodb /usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt: --extended-insert=false --
triggers -R --hex-blob --single-transaction db_name >
db_name.sql in addition, if you want to achieve online backup,
You can also use the --master-data parameter to achieve, as follows: /usr/local/mysql/bin/mysqldump -uyejr -pyejr --default-character-
set=utf8 --opt --master-data=1 --single-transaction --flush-logs db_name > db_name.sql it is just at the beginning of the second request lock table, then refresh the binlog, then the CHANGE MASTER statement is added in the export file to specify the backup position of binlog, if you want to restore this file to slave, you can use this method to do.
1.2 reduction
A file backed up with mysqldump is a SQL script that can be dumped directly. There are two ways you can import data into it.
Use the MySQL client directly, for example:
/usr/local/mysql/bin/mysql, -uyejr, -pyejr, db_name, < db_name.sql  In fact, this is not the standard SOURCE syntax for the SQL syntax, but MySQL client function, for example: SOURCE /tmp/db_name.sql; absolute path here need to specify the file, and must be running mysqld users (such as nobody) have permission to read the file.
2, mysqlhotcopy
2.1 backup

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。