mysql 备份和恢复（MySQL backup and recovery）.doc

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 its 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 lets 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 dont recommend it. --default-character-s