MySQL database logical and physical backup

MySQL database logic and physical backup logical backup method 1: generate a specific format of plain text www.2cto.com SELECT * into outfile 'f: \ data.txt 'fields TERMINATED ', 'From g_user load data infile' F: \ data.txt 'into TABLE g_user fields terminated by', 'ignore number LINES. If you do not specify a FIELDS clause, the default value is the same as if you write: fields terminated by '\ t' enclosed by ''' escaped by' \ '. If you do not specify a LINES clause, the default value is the same as if you write: lines terminated by '\ n', send 2: Generate Insert statement backup, use tool mysqldump, physical backup My All data of the ISAM storage engine MyISAM storage engine is stored in the "datadir" directory set in MySQL configuration. In fact, no matter whether we use the MyISAM storage engine or any other storage engine, each database will have a folder under the "datadir" Directory (the same is true for the database mysql that includes system information ). Each MyISAM storage engine table in each database has three files, which are the ". frm file, which stores table data. MYD "file, and". MYI file. MyISAM is a non-transactional storage engine, so it does not have its own log file. Therefore, apart from backing up a total of physical files in the MySQL system, you only need to back up the above three files. Www.2cto.com Innodb Storage engine is a transactional storage engine, and the data storage location may be different from that of MyISAM storage engine, which depends on our "" configuration of Innodb. The configuration of Innodb's data storage location is set to "innodb_data_home_dir", "innodb_data_file_path", and "innodb_log_group_home_dir, and another parameter "innodb_file_per_table" that determines the storage mode of the tablespace in Innodb ". The preceding three Parameters specify the storage location of data and log files. The last parameter determines whether Innodb stores data in a shared tablespace or in an exclusive tablespace. The usage instructions of these parameters have been explained in the first "MySQL storage engine Introduction", which is also described in more detail in the MySQL official manual, so I will not go into details here.
If we use the storage of the shared tablespace, Innodb needs to back up all the data files set by the "innodb_data_home_dir" and "innodb_data_file_path" parameters, all Innodb Storage engines under the corresponding database directory in "datadir". frm files. If we use exclusive tablespaces, we need to back up all the files that need to be backed up in the preceding table space sharing mode, we also need to back up all "in the corresponding database directory of" datadir ". idb file, which stores data in the Innodb Storage engine table in the exclusive tablespace mode. In addition to the data files mentioned above, Innodb also has its own log file for storing redo information and related transaction information in the location specified by the "innodb_log_group_home_dir" parameter. Therefore, if Innodb physical backup can be used effectively, we need to back up all the log files at the location specified by the "innodb_log_group_home_dir" parameter. Flush tables with read lock; physical backup, you know unlock tables; backup policy important content physical backup and other logical backup author bengda