Avatar of TriCannon
TriCannon
 asked on

Using mysql to backup database - weird characters in the dump file

I am using mysqldump. When I open up the dump file that it created using notepad, parts of the file look like this:

Uë*O\0Š‡ã=ãúw{r€bÊð½|0ë÷(2âðÁ}ò±.ðÀ^<ò[®Ý(¶Bڐìÿ‘¶Ã*5¸ÔgJÚ¨ü3¶ñC+þE$P2´H4iÿ‡šñ ï<CŽLIØ^*Cïèļ5ÅÔ\0 ´b¹3û¯ƒD™ksœ”—ÞÂ5¼âèZF{³‡z0Àa6£e”JJ+ìdÌë¹tãÚ0«\råMþÜ‹`‘…¢²\r5.ê±ÐdÊ•—õ<¥Ž¯{ÅN¿®&Ü4ðëJ…Ø¥EtA¹ØŠçßøàG!ü¥jN³8bµ¢A†ŸWA5\nóµó¬¯Ï_€è5í¨.Î-Îk¯¬* Ï,R°„¸¾¬Ë´\"3]Z8W¯cÓJUêõ¼qkÏP÷›þ,ù¾sÉ·¡ôD£üÈ¡~a¤’\nº¶ž›DD¯A tÛ”eÝÒÑø®ŸÜX·?½¬ZYé\'Ñ£^F_ã>ó,á‘n=ÛñYÊBì¿SÎ_ÃMÙ{ã\'–½¥Û>Z}Þ£p{¤‚Àû•{owùª!_Þ…3æâ÷Ò^Ôme!=¶”ªmQü®‚´¤Ã¬jr\\9„÷áÞƒGï9„wf\\ìíÂuaåÜCVZŸ†¢ÀJ1»Û`mÞ˜ç:æ9éîî~‚ø³·;#xwûÑâ2(,Í؏%·0ӏºlÊìÍ„ÏÏ‚XøÔ;ŠÞ½5„ ügXmD‘Pí`|gßàü@}«g$™{”      ç5`ÑîÕŠ¶¨Y$JdY¿¼E‚K‹h˜=:\"òrÅqà7ÌÊOŠB+ðü’¼O6Ò%¤e®}Aún,eR.Ëx1¢;bÏe*q¸ÎDõ2P`ÁJö e“„6BJ?PóÏYeŒeâ¡‚5)ì°Û %ݐX,\Zg@o¿H:ƒ¬VNo¶Ï      {j—¦×Då69懋œRù‰€9]en«\'hësMG®hþlTbyï*ñÚ‘ÃÓl^ýVƒY¹¹*«CÖúŠœ¦·kl2CEž¢Òž;H°‘™¼9¨q‚?‘ĐfãfF˜æ~p¯õÖ¦¡t`]×’Nt€9êÿת0-yu-•Mk_}];6ÆÃj|ëL•`&ü\\Ä«£ì9ŠªÝ[ZÅ>N…!ê[\Zª@ÅQíî\"Fìlíí<x¿bÄV}KYâ3Nýá6Þf¯ºÉò+з“r5aâ3thÐsôÜõ¥‰Ï>„4ñµÕR± ›„þFW)µÁ\Zx%Bm±lÂ-µ`²ËF(š”ú¨÷ù¨¥‰å¡´Pº\0ˆ½Ùb)sï9×Ú¢Jþ9`5Œbó«rQ;)÷EY,²Ã£ÀÔøe×dÖzÌcŸÅv¤*’eïqŠŸ‰ó2\r¹½z³Ÿ¦&ŽjÖ-ö¢µÉˆ~!i£Œ¦IéÆ•ð“õyDœt4êþY•?à·I>bCŠì]‹Òjb[\"ç!7@‡D/-¸ÃAµ“rJÕ>ÚhzÁ¨käÎïÅS¯Àwšõ6§h×\"¦†^\0x9&ëZ´Ø«¥ÇèÝÓ1@ä„ÚÖeƒÉpTµ×¤V אS»À`šBK´™…LïGáµn5žX{JÛD·0ž][õIK?F’¿ªouâ—Ý¥ƒ\nûò5Æ\rÊŠï/EC’Oëç\ZӏܟN\'ÃÕS›gJè¡£F‰Là DÀ¨²\r:Y>QÝg<c‡pæ?#]      ƒì{×c®’˜ÿŠ9ÑŽ¿v\'`N-åã _yL\n!B~?Í9Ç™~b¿œÁQ„öÀz¸€gøz+Üsu)ø.B™À¬«7I¸‡£¢ºÜ§ZPÛhñtbB°˜„T®¡¤ê|دÎ,úïšÎÁÈ,3QA’bäĈ’      m8Ýü8¨H:‡V-aÑ\Z£¡;kÜF‹ðùA±V‰R²Þxç$6$§sɯ¿A÷ÒL.M¢7 Øâ’>²F6®>͐*ù

Most of the file is in readable English, though. What's the deal with this? Is this normal? Will the import work without a problem?

Here is my command:

mysqldump --opt --user root --password=1q2w3e4r wikidb > sql.dump
MySQL Server

Avatar of undefined
Last Comment
Nem Schlecht

8/22/2022 - Mon
Nem Schlecht

I hope that isn't your actual root password.
TriCannon

ASKER
It is not my actual root password.
Nem Schlecht

Yes, this should dump/load properly - as long as you don't muck with the 'sql.dump' file (opening it in any editor and then saving would be bad).
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Nem Schlecht

You did a good job of posting a cryptic root password! ;-)

You must have a BLOB() field or maybe a TEXT() field that contains some binary data of some sort.  Is this a common thing in your database (every 10th row looks like garbage) or is there only 1 or two entries that look like this?
TriCannon

ASKER
I looked in the dump file and there is actually only one section that has the weird characters.  The section begins right after this code:

--
-- Dumping data for table `wzobjectcache`
--

LOCK TABLES `wzobjectcache` WRITE;
/*!40000 ALTER TABLE `wzobjectcache` DISABLE KEYS */;
INSERT INTO `wzobjectcache` VALUES ('wikidb-wz:messages:en','K´2
Nem Schlecht

What's the CREATE statement for table 'wzobjectcache' look like?

You can either get it from the dump or via:

  SHOW CREATE TABLE wzobjectcache;
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
TriCannon

ASKER
--
-- Table structure for table `wzobjectcache`
--

DROP TABLE IF EXISTS `wzobjectcache`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wzobjectcache` (
  `keyname` varbinary(255) NOT NULL DEFAULT '',
  `value` mediumblob,
  `exptime` datetime DEFAULT NULL,
  PRIMARY KEY (`keyname`),
  KEY `exptime` (`exptime`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `wzobjectcache`
--

LOCK TABLES `wzobjectcache` WRITE;
/*!40000 ALTER TABLE `wzobjectcache` DISABLE KEYS */;
INSERT INTO `wzobjectcache` VALUES ('wikidb-wz:messages:en','K´2´ª.¶2·R\ns\r\nöô÷S²Î´2´®\0','2010-02-17 18:47:24'),('wikidb-wz:pcache:idhash:1-0!1!0!!en!2','¥VÝoâ8ßgþ\néö\ròQ ¬¡yéu¤Ý-ºV·Ò½ 7ÀªcG¶Ó”­ú¿ß8lQ—»>4a~óñ›ñÌ8·4Œ¨7cÚ€¾-l^XFôÅÐ>õ²{x¶ÞÈаÔ\" YiX\\y>—)<wóUîÿ`âôü&—ø˜0™Î¯50 ø¤ß<ç ñ-Xn\\y#²1\"ΈlÈƈ4Œâs´Ç>‹[cß‘=AxRdLÎÿ£\n€Ùqªäd/ï{žÊ…Ò³\\Éù=$+©„Z®w\Z0iÀñÛò÷ÃÍ¢p>Ó*´ÐLÌÿTI‘a1ö¡Ù+½B|\ny?èÝJ•Z©lþG4˜f      ÜòFkœ|dY>\"\rø$ô~ÔÛÅ‚\'à²M‹Äò\'n÷eÝ`ä\0‹ß6¢øØÏ­q4ñ0ÇÛŸ€È)ù\\enRd(—¤Cv´VÖæÔ÷˲ìfrVòGÞUz黿2ßZy$̘+‡      ´Dw \rû!8Ç‘qåIµPB¨Ò‹ßô—¬Òü\\5ÞÈpoøÿ“lú:?Ͻõqª­q»Ó!­ïPÎfDðŒ[tŸ+m[3|ºî7Fi\"U\n$Q…´”„~T­™2¶Ϲ[6.......................more garbled characters here...  then they stop, and then it has the following line of code: immediately after:

áóͼ(¾øåãÿ','2038-01-19 03:14:07');
/*!40000 ALTER TABLE `wzobjectcache` ENABLE KEYS */;
UNLOCK TABLES;
TriCannon

ASKER
It is a Mediawiki database that I am backing up, if that makes a difference.  And it very small in size, under 2MB.
ASKER CERTIFIED SOLUTION
Nem Schlecht

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
TriCannon

ASKER
So when I do the import, it should be fine, right?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Nem Schlecht

Yup, you should be okay.

Let me double-check, though.  If you're using phpMyAdmin, you may have problems.  It does *not* use the standard mysqldump format (its very close, but slightly different).  I would use the 'mysqldump' and 'mysql' commands to make sure the dump/load will work correctly.