MySql ±¸·ÝÓë»Ö¸´
±¾ÎÄÌÖÂÛ MySQL µÄ±¸·ÝºÍ»Ö¸´»úÖÆ£¬ÒÔ¼°ÈçºÎά»¤Êý¾Ý±í£¬°üÀ¨×îÖ÷ÒªµÄÁ½ÖÖ±íÀàÐÍ£ºMyISAM ºÍ Innodb£¬ÎÄÖÐÉè¼ÆµÄ MySQL °æ±¾Îª 5.0.22¡£
Ä¿Ç° MySQL Ö§³ÖµÄÃâ·Ñ±¸·Ý¹¤¾ßÓУºmysqldump¡¢mysqlhotcopy£¬»¹¿ÉÒÔÓà SQL Óï·¨½øÐб¸·Ý£ºBACKUP TABLE »òÕß SELECT INTO OUTFILE£¬ÓÖ»òÕß±¸·Ý¶þ½øÖÆÈÕÖ¾£¨binlog£©£¬»¹¿ÉÒÔÊÇÖ±½Ó¿½±´Êý¾ÝÎļþºÍÏà¹ØµÄÅäÖÃÎļþ¡£MyISAM ±íÊDZ£´æ³ÉÎļþµÄÐÎʽ£¬Òò´ËÏà¶Ô±È½ÏÈÝÒ×±¸·Ý£¬ÉÏÃæÌáµ½µÄ¼¸ÖÖ·½·¨¶¼¿ÉÒÔʹÓá£Innodb ËùÓÐµÄ±í¶¼±£´æÔÚͬһ¸öÊý¾ÝÎļþ ibdata1 ÖУ¨Ò²¿ÉÄÜÊǶà¸öÎļþ£¬»òÕßÊǶÀÁ¢µÄ±í¿Õ¼äÎļþ£©£¬Ïà¶ÔÀ´Ëµ±È½Ï²»ºÃ±¸·Ý£¬Ãâ·ÑµÄ·½°¸¿ÉÒÔÊÇ¿½±´Êý¾ÝÎļþ¡¢±¸·Ý binlog£¬»òÕßÓà mysqldump¡£
1¡¢mysqldump
1.1 ±¸·Ý
mysqldump ÊDzÉÓÃSQL¼¶±ðµÄ±¸·Ý»úÖÆ£¬Ëü½«Êý¾Ý±íµ¼³É SQL ½Å±¾Îļþ£¬ÔÚ²»Í¬µÄ MySQL °æ±¾Ö®¼äÉý¼¶Ê±Ïà¶Ô±È½ÏºÏÊÊ£¬ÕâÒ²ÊÇ×î³£Óõı¸·Ý·½·¨¡£
ÏÖÔÚÀ´½²Ò»Ï mysqldump µÄһЩÖ÷Òª²ÎÊý£º
* --compatible=name
Ëü¸æËß mysqldump£¬µ¼³öµÄÊý¾Ý½«ºÍÄÄÖÖÊý¾Ý¿â»òÄĸö¾É°æ±¾µÄ MySQL ·þÎñÆ÷Ïà¼æÈÝ¡£Öµ¿ÉÒÔΪ ansi¡¢mysql323¡¢mysql40¡¢postgresql¡¢oracle¡¢mssql¡¢db2¡¢maxdb¡¢no_key_options¡¢no_tables_options¡¢no_field_options µÈ£¬ÒªÊ¹Óü¸¸öÖµ£¬ÓöººÅ½«ËüÃǸô¿ª¡£µ±È»ÁË£¬Ëü²¢²»±£Ö¤ÄÜÍêÈ«¼æÈÝ£¬¶øÊǾ¡Á¿¼æÈÝ¡£
* --complete-insert£¬-c
µ¼³öµÄÊý¾Ý²ÉÓðüº¬×Ö¶ÎÃûµÄÍêÕû INSERT ·½Ê½£¬Ò²¾ÍÊÇ°ÑËùÓеÄÖµ¶¼Ð´ÔÚÒ»ÐС£Õâô×öÄÜÌá¸ß²åÈëЧÂÊ£¬µ«ÊÇ¿ÉÄÜ»áÊܵ½ max_allowed_packet ²ÎÊýµÄÓ°Ïì¶øµ¼Ö²åÈëʧ°Ü¡£Òò´Ë£¬ÐèÒª½÷É÷ʹÓøòÎÊý£¬ÖÁÉÙÎÒ²»ÍƼö¡£
* --default-character-set=charset
Ö¸¶¨µ¼³öÊý¾Ýʱ²ÉÓúÎÖÖ×Ö·û¼¯£¬Èç¹ûÊý¾Ý±í²»ÊDzÉÓÃĬÈ쵀 latin1 ×Ö·û¼¯µÄ»°£¬ÄÇôµ¼³öʱ±ØÐëÖ¸¶¨¸ÃÑ¡Ï·ñÔòÔٴε¼ÈëÊý¾Ýºó½«²úÉúÂÒÂëÎÊÌâ¡£
* --disable-keys
¸æËß mysqldump ÔÚ INSERT Óï¾äµÄ¿ªÍ·ºÍ½áβÔö¼Ó /*!40000 ALTER TABLE table DISABLE KEYS */; ºÍ /*!40000 ALTER TABLE table ENABLE KEYS */; Óï¾ä£¬ÕâÄÜ´ó´óÌá¸ß²åÈëÓï¾äµÄËٶȣ¬ÒòΪËüÊÇÔÚ²åÈëÍêËùÓÐÊý¾Ýºó²ÅÖؽ¨Ë÷ÒýµÄ¡£¸ÃÑ¡ÏîÖ»ÊÊºÏ MyISAM ±í¡£
* --extended-insert = true|false
ĬÈÏÇé¿öÏ£¬mysqldump ¿ªÆô --complete-insert ģʽ£¬Òò´Ë²»ÏëÓÃËüµÄµÄ»°£¬¾ÍʹÓñ¾Ñ¡ÏÉ趨ËüµÄֵΪ false ¼´¿É¡£
* --hex-blob
ʹÓÃÊ®Áù½øÖƸñʽµ¼³ö¶þ½øÖÆ×Ö·û´®×ֶΡ£Èç¹ûÓжþ½øÖÆÊý¾Ý¾Í±ØÐëʹÓñ¾Ñ¡Ïî¡£Ó°Ïìµ½µÄ×
Ïà¹ØÎĵµ£º
SQL±ê×¼ÔÚÊý¾Ý´æ´¢µÄÎïÀí·½ÃæûÓÐÌṩ̫¶àµÄÖ¸ÄÏ¡£SQLÓïÑÔµÄʹÓöÀÁ¢ÓÚËüËùʹÓõÄÈκÎÊý¾Ý½á¹¹»òͼ±í¡¢±í¡¢ÐлòÁÐϵĽéÖÊ¡£µ«ÊÇ£¬´ó²¿·Ö¸ß¼¶Êý¾Ý¿â¹ÜÀíϵͳÒѾ¿ª·¢ÁËһЩ¸ù¾ÝÎļþϵͳ¡¢Ó²¼þ»òÕßÕâÁ½ÕßÀ´È·¶¨½«ÒªÓÃÓÚ´æ´¢Ìض¨Êý¾Ý¿éÎïÀíλÖõķ½·¨¡£ÔÚMySQLÖУ¬InnoDB´æ´¢ÒýÇ泤ÆÚÖ§³Ö±í¿Õ¼äµÄ¸ÅÄ²¢ÇÒMySQL·þÎñÆ÷ÉõÖÁÔÚ ......
´¥·¢³ÌÐòÊÇÓë±íÓйصÄÃüÃûÊý¾Ý¿â¶ÔÏ󣬵±±íÉϳöÏÖÌض¨Ê¼þʱ£¬½«¼¤»î¸Ã¶ÔÏó¡£ÔÚijЩ´¥·¢³ÌÐòµÄÓ÷¨ÖУ¬¿ÉÓÃÓÚ¼ì²é²åÈëµ½±íÖеÄÖµ£¬»ò¶Ô¸üÐÂÉæ¼°µÄÖµ½øÐмÆËã¡£
´¥·¢³ÌÐòÓë±íÏà¹Ø£¬µ±¶Ô±íÖ´ÐÐINSERT¡¢DELETE»òUPDATEÓï¾äʱ£¬½«¼¤»î´¥·¢³ÌÐò¡£¿ÉÒÔ½«´¥·¢³ÌÐòÉèÖÃΪÔÚÖ´ÐÐÓï¾ä֮ǰ»òÖ®ºó¼¤»î¡£ÀýÈ磬¿ÉÒÔÔÚ´Ó±íÖÐɾ³ýÿһÐÐÖ® ......
¶¨ÒåÓαê
DECLARE cur_Ãû³Æ CURSOR
FOR
SELECT ×Ö¶Î1,×Ö¶Î2,...×Ö¶În from ±í where ×Ö¶Î1=±äÁ¿;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ±äÁ¿1=null,±äÁ¿2=null;
OPEN cur_Ãû³Æ;
FETCH cur_Ãû³Æ INTO ±äÁ¿1,±äÁ¿2...;
WHILE ( ±äÁ¿1 IS NOT NULL) DO
&nbs ......
mysqlÖ´ÐÐsqlÓï¾äʱÓÐʱ»á³öÏÖCR_COMMANDS_OUT_OF_SYNC´íÎó£¨2014£©
ÌáʾÓïΪ
commands out of sync; you can't run this command now
ÍøÉ϶Դ˴íÎóµÄÃèÊöºÜÉÙ£¬¶øÇÒûÓиø³ö½â¾ö·½·¨¡£
ÆäʵÕâÊÇÒòΪÉÏÒ»¸ösqlÖ´Ðзµ»ØÁ˶à¸ö½á¹û¼¯£¬µ«Ã»Óаѽá¹û¶¼È¡³öÀ´£¬¾ÍÖ´ÐÐÁËеÄsqlÓï¾ä£¬¾Í»á²úÉúÕâ¸ö´íÎó¡£
Ó¦ÔÚsqlÓï¾äÖ´Ðкó ......
Æô¶¯£ºnet start mySql;
¡¡¡¡½øÈ룺mysql -u root -p/mysql -h localhost -u root -p databaseName;
¡¡¡¡ÁгöÊý¾Ý¿â£ºshow databases;
¡¡¡¡Ñ¡ÔñÊý¾Ý¿â£ºuse databaseName;
¡¡¡¡Áгö±í¸ñ£ºshow tables£»
¡¡¡¡ÏÔʾ±í¸ñÁеÄÊôÐÔ£ºshow columns from tableName£»
¡¡¡¡½¨Á¢Êý¾Ý¿â£ºsource fileName.txt;
¡¡¡¡Æ¥Åä× ......