Logical backup: Common Operations, case studies, causes of unexpected termination, and solutions

Logical backup: Common Operations, case studies, causes of unexpected termination, and solutions (I) Common Operations ① full-Database Backup Syntax: mysqldump-h host name-P port-u user name-p password (-database) database Name> file name. SQL example: mysqldump-hlocalhost-P3306-urocky-p123456 db_test> bakfile1. SQL ② full database backup with table deletion MySQL database is in the format of table deletion, so that the backup can overwrite existing database without manual delete the original database Syntax: mysqldump -- add-drop-table-u {username}-p {password} {databasename}> {backfile. SQL} example: mysqldump -- add-drop-table-urocky-p123456 db_test> ba Kfile2. SQL ③ compression backup Syntax: mysqldump-h {hostname}-u {username}-p {password} {databasename} | gzip> unzip backfile. SQL .gz} example: mysqldump-hlocalhost-urocky-p123456 db_test | gzip> bakfile3. SQL .gz ④ backup certain table Syntax: mysqldump-h host name-P port-u username-p password (-tables |-quick) database Name table name 1 (table name 2 ...)> File name. SQL example: examples-hlocalhost-urocky-mongodb_test examples> bakfile4-1. SQL mysqldump-hlocalhost-P3306-urocky-mongodb_test examples> bakfile4-2. SQL mysqldump-hlocalhost-P3306-urocky-examples -- quick db_test examples> bakfile4-3. SQL examples -hlocalhost-P3306-urocky-p123456 -- tables db_test tbl_test1 tbl_test2> bakfile4-4. SQL ⑤ back up multiple library syntaxes at the same time: mysqldump-h {hostname} (-P {port })-U {username}-p {password}-databases {dbname1} {dbname2} {dbname3}> multibackfile. SQL example: mysqldump-hlocalhost-urocky-p123456-databases db_test1 db_test2 db_test3> multibackfile. SQL 6 all Database syntax on the backup server: mysqldump-all-databases> allbackupfile. SQL 7 only backup database structure Syntax: mysqldump-no-data-databases {databasename1} {databasename2}> {structurebackfile. SQL} example: mysqldump-no-data-databases db_test 1 db_test2> structurebackfile. SQL Syntax: mysqldump-u username-p Password Database Name table name -- where = "filter condition"> export file path example: mysqldump-uroot-p123456 test test_data -- where = "id> 100">/tmp/test. SQL (ii) Case studies ① case description: the mysqldump command is used to create a backup and pull it to a machine for recovery. The recovery execution is successful, and an error message is not displayed. However, after the recovery is complete, log on to the database. You can guess what the problem is, and the data is incomplete. the response is of course to check the backup file, and recover the backup file. The backup set contains incomplete content, why is the backup set incomplete? ② cause analysis: The analysis found that an error was reported when a view object was exported, and mysqldump was automatically aborted. Therefore, no backup was performed after the object. ③ Scenario Model Intended playback: [plain] Session_A: mysql> use test; Database changed mysql> create table rocky (id int, name varchar (100); Query OK, 0 rows affected (0.04 sec) mysql> create view rocky_view as select * from rocky; Query OK, 0 rows affected (0.01 sec) mysql> rename table rocky to robbin; Query OK, 0 rows affected (0.03 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) Session_ B: [mysql @ localhost bin] $. /Mysqldump -- tables test robbin rocky_view> bak. SQL mysqldump: Got error: 1356: View 'test. rocky_view 'references invalid table (s) or column (s) or function (s) or definer/invoker of view lack rights to use them when doing LOCK TABLES do not blame mysqldump, because mysqldump encountered any problems during execution, by default, it is to exit the solution directly: when executing mysqldump, append the -- force parameter. this parameter is used to ignore errors and continue the subsequent operations. Parameters provide the ignore = y parameter function similar to the exp command in the Oracle database. In fact, ignore is usually specified when executing exp in the Oracle database, corresponding to the MySQL database. I want to execute the mysqldump command the -- force parameter should also be used as a required parameter during the line process. (3) Cause of the accidental termination of mysqldump and solution ① error: lost connection to MySQL server at 'reading initial communication packet ': Cause Analysis: solution due to DNS instability: enabling the skip-name-resolve option will avoid this problem to the greatest extent. ② error: Lost connection to MySQL server at 'reading authorization packet ': Cause Analysis: obtaining an available connection from MySQL is the result of multiple handshakes. During multiple handshakes, network fluctuations may cause handshakes to fail. solution: the best solution is to have mysqldump re-initiate a connection request. ③ error: lost connection to MySQL server during query Cause Analysis: mysqldump slow Data Processing (caused by NFS and gzip) will lead to MySQL active disconnection solution: Increase the net_write_timeout settings