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

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 and --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 Mysqlhotcopy is a PERL program that was originally written by Tim Bunce. It uses LOCK, TABLES, FLUSH, TABLES, and CP or SCP to quickly backup the database. 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 files (including data tables, definition files, data files, index files) are located. Mysqlhotcopy can only be used to back up MyISAM and can only run on classes, Unix, and NetWare systems. Mysqlhotcopy supports copying multiple databases at once and supports regular expression. The following are a few examples: root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name /tmp (the database directory copied to db_name /tmp root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name_1)... More use db_name_n /tmp root# /usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name./regex/ /tmp please check the manual, help or call the following command to see mysqlhotcopy: perldoc /usr/local/mysql/bin/mysqlhotcopy note, want to use mysqlhotcopy there must be SELECT, RELOAD (FLUSH TABLES to execute permissions) and must have read permissions to the datadir/db_name directory. 2.2 reduction Mysqlhotcopy backup is the entire database directory, when you can use it to copy directly to the mysqld designated dataDir (here is /usr/local/mysql/data/) directory, you can also pay attention to the authority of the problem, 如下例: root # cp rf db _ name / usr / local / mysql / data / root # chown() r nobody, nobody / usr / local / mysql / data / (将 db _ name 目录的属主改成 mysqld 运行用户) 3、 sql 语法备份 3.1 备份 backup table 语法其实和 mysqlhotcopy 的工作原理差不多, 都是 锁 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf , 然后拷贝数据文件.它能实现在线备份, 但是效果不理想, 因 此不推荐使用.它只拷贝表结构文件和数据文件, 不同时拷贝索引文 件, 因此恢复时比较慢.例子: back table tbl _ name to / tmp / db _ name / '; 注意, 必须要有 file 权限才能执行本sql, 并且目录 / tmp / db _ name / 必须能被 mysqld 用户可写, 导出的文件不能 覆盖已经存在的文件, 以避免安全问题. select into outfile 则是 把数据导出来成为普通的文本文件, 可以自定义字段间隔的方式, 方便处理这些数据.例子 select * into outfile "/ tmp / db _ name / tcl _ name.txt from tcl _ name; 注 意, 必须要有 file 权限 才能执行本sql, 并且文件 / tmp / db _ name / tcl _ name.txt 必 须能被 mysqld 用户可写, 导出的文件不能覆盖已经存在的文件, 以避免安全问题. 3.2 恢复 用 backup table 方法备份出来的文件, 可以运行 restore table 语句来恢复数据表. 例子:恢复表/甲氧苄啶/ db_name /”;权限要求类似上面所述。用SELECT INTO outfile方法备份出来的文件，可以运行LOAD DATA INFILE语句来恢复数据表。例子:LOAD DATA INFILE /甲氧苄啶/ db_name / tbl_name .txt”为表tbl_name;权限要求类似上面所述。 倒入数据之前，数据表要已经存在才行。如果担心数据会发生重复，可以增加取代关键字来替换已有记录或者用忽略关键字来忽略他们。 4、启用二进制日志(binlog) 采用binlog的方法相对来说更灵活，省心省力，而且还可以支持增量备份。启用binlog时必须要重启mysqld。首先，关闭mysqld，打开我。CNF，加入以下几行:服务器ID = 1日志本= binlog日志本指数= binlog.index然后启动mysqld就可以了。运行过程中会产生binlog.000001以及binlog。指数，前面的文件是mysqld记录所有对数据的更新操作，后面的文件则是所有binlog的索引，都不能轻易删除。关于binlog的信息请查看手册。需要备份时，可以先执行一下SQL语句，让mysqld终止对当前binlog的写入，就可以把文件直接备份，这样的话就能达到增量备份的目的了:刷新日志;如果是备份复制系统中的从服务器， 还应该备份master.info和relay-log.info文件。备份出来的binlog文件可以用MySQL提供的工具mysqlbinlog来查看，如:/usr/local/ MySQL /斌/ mysqlbinlog /甲氧苄啶/ binlog.000001该工具允许你显示指定的数据库下的所有SQL语句，并且还可以限定时间范围，相当的方便，详细的请查看手册。恢复时，可以采用类似以下语句来做到:/usr/local/ MySQL /斌/ mysqlbinlog /甲氧苄啶/ binlog.000001 | MySQL uyejr - pyejr db_name把mysqlbinlog输出的SQL语句直接作为输入来执行它。如果你有空闲的机器，不妨采用这种方式来备份。由于作为奴隶的机器性能要求相对不是那么高，因此成本低，用低成本就能实现增量备份而且还能分担一部分数据查询压力，何乐而不为呢, 5、直接备份数据文件 相较前几种方法，备份数据文件最为直接、快速、方便，缺点是基本 上不能实现增量备份。为了保证数据的一致性，需要在靠背文件前， 执行以下SQL语句:FLUSH TABLES WITH READ锁;也就是把内存中 的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有 新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回 原来的数据库目录下即可。注意，对于InnoDB类型表来说， You also need to back up your log file, that is, the ib_logfile* file. Because when the Innodb table is corrupted, you can rely on these log files to recover. 6, backup strategy For systems with moderate levels of traffic, the backup policy can be set as follows: the first full volume backup, the incremental backup once a day, and a full volume backup once a week, so it has been repeated. For important and busy systems, you may need a full volume backup every day, an incremental backup per hour, even more frequently. In order not to affect online business, to achieve online backup, and to increase the backup, the best way is to use the master copy mechanism (replication), on the slave machine backup. 7, data maintenance and disaster recovery As a DBA (I am not yet, huh), one of the most important tasks is to ensure that data tables are safe, stable and high-speed. Therefore, you need to maintain your data tables regularly. The SQL statement is useful: CHECK TABLE or REPAIR TABLE, MyISAM OPTIMIZE TABLE for inspection or maintenance, optimization of the MyISAM ANALYZE TABLE table MyISAM table analysis, of course, the above command starting can be done by the tool myisamchk, here without the detail. The Innodb table is by executing the following statement defragmentation, improve the indexing speed: ALTER TABLE tbl_name ENGINE = Innodb; this is actually a NULL operation on the surface, what do not actually rearrange the pieces. The commonly used MyISAM tables can be completed using the recovery methods mentioned above. If the index is broken, you can use the myisamchk tool to rebuild the index. For the Innodb table, it's not so straightforward because it keeps all the tables in a table space. However, Innodb has an inspection mechanism called fuzzy checkpointing, and as long as the log file is saved, the error can be recovered from the log file. You can add the following parameters in the my.cnf file so that mysqld automatically checks the log file at startup: innodb_force_recovery = 4, for information about this parameter, see the manual. 8, summary Do a good job of backing up the data and decide on the right backup strategy. This is a small part of what DBA does. It's hard to begin with, so let's start now!