[MySQL] Restore a HEX dump?

[Thomisback]

Hi,

 

I have made a backup of my database, using mysqlbackup and the --hex-blob option but now I want to restore it and I have no clue how to, do I just import the hex data into the blob fields again?

 

Kind regards

[jonsjava]

correct. just

INSERT INTO `table_name` VALUES('value1', 'hex_values');

 

[Thomisback]

Hmm, thanks for the reply. I just tried to import the data (It's an image) but after I have imported it, the image doesnt work anymore :/

 

My code:

<?php



$str_hex = "0xFFD8FFE000104A46494600010201012C012C0000FFDB0043000D090A0B0A080D0B0A0B0E0E0D0F13201513121213271C1E17202E2931302E292D2C333A4A3E333646372C2D405741464C4E525352323E5A615A50604A51524FFFDB0043010E0E0E131113261515264F352D354F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4F4FFFC0001108008C008C03012200021101031101FFC4001B00010003010101010000000000000000000005060703040201FFC4003510000201030204030507040300000000000001020304110521061231511341A107718191B1142223324261C1345253F192E1F0FFC4001801010101010100000000000000000000000001030204FFC4001C1101010002030101000000000000000000000102110321311204FFDA000C03010002110311003F00D380000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001C2E6F2D6CE3CD77734A8AEF526A3F5222E78C741B68393BF8D4C79538B97F04B945D54F021A871468F56C15ED4BB850A52E9E2BC37F023ABFB41E1DA3514615EB56CBC394293C2F9E04B2F858B5038595E5B5FDB42E6CEB42B519F4941E4F8BAD4AC2CD3FB5DEDBD1C7954A8A2FD4A8F502B577C77C3F6CDC61753B99AFD3469B7EAF0882BEF69728C26EC7499F2C5A4E75E78C67A652F777034206537BC67AE57B68558DED2B584D65F854963A744DB6F3FF7D884B8E23D4A15154A1AC5FD4A99CF33AB24BABF2F91743710675C25C5FABEA0E746E6742AF84B2E738E24D3E98C6DFECB1CF52BBA9D6A72AED15820B1020285F56A53CB9CA49F93793D7A4EBFA7EAD52AD1B6AC957A32719D296D25878CAEEBF7404A00000000A57B46A1CF6F6D279E49B70935D56375F5335AB4234AEA941B72A724B397E7D3EA6C7C5F613D4347F0E945B9C2A292C26DF6F2F799F51E0CD7AF25175A872C63BC652C26B3BF47833DEAEB6EE4DC435D53A72D2549C22A7078CA587FFB622E54E8BBB54E15E2A936BF11A6F957C93F4347B1F67F76ADEA53BCB8B7E6A8D3E7DE528F5D97CFB9E9B4F663A6D3C3BBBDB8ACFB412827F52F1F5B5E4D5919C42E29DBD3953B7BBB9E5927CD18FDD527878DB3EE395B4E6D4A0ADDD6A92794FAB5B3DB1F15F2367B4E0BE1EB4C3869D0A925E755B9FA3DBD099B7B5B6B58F2DB5BD2A31ED4E0A2BD0D36CD8BE9FA0712DC4796CF4EAD4E0DE53941417979CBDC899B4F66DABDC3E6BDBAB7A09F5597397A6DEA6AA06C51ACFD99E9B4F0EEEF2E2B3ED04A0BF92567C09C3B2B7F09594A2FFC8AACB9BEB82C80829BA77032D23509DC58DE39D29C1C5D3AB1DD6E9ADD75F912F4F48AEDFDE9462BE64D9CAE2E29DBA4EA677E98168AF71069BAA51D3A53D1553AD5D27952DA49778F937EF322A30BFB7BF72A6EAD1B9A52CB797194659C7BF3966E95F53A7051F0E3CEDAF3DB0406B1A75AEAB711B9AF4A10ACA2E2A508ACB5B633EEC13E8D3C9C37C655E5463475AF0E6D2FEA29C974C27F7A3DF7C6DF23A6A1C697116A367428C799BE57524DBC67B2F7959BFB69E8D72A1539EA42B4711A91588B7BF5C7658EBB6CCE9568C2B5F518CA4978DB45636595B7459E9B7C4F3F26597D6ABBD4F57AE15D4EE752B5AB2BC9C2556325F9238493F8BF34C9D29BC239B4D5A76B3C7E251CA6BF563CFEA5C8D3872DE3DA653B0006AE400000000000000003CBA8D353B6CB5F95E4F51F15A3CF4671EE897C59EA12549F86E696D1D8E6D274DB5DCED28B497326B2B2B3B1CA29F2BC196377DB4B15DE30A2AA689E234F146A293C3DF0F2BF923396AD7B5B1AB153FC392936DB5869F4EDD23FE8B0EBD08CB45BB536B0A1CDBBC2CADD7AA2A8AE9CEC7C39D5A49C24A6F957339BCE77DF3E59671CF8F78E51CE3E2C9A6CADECF55B3FB2CA11A519B4D2937F99FEFDF25F0CC631A8E72AE93C38A942304F1B61AFA1A55BD4F1ADE9D4FEF8A64FCF95B965B5E4C64D69D0007A9980000000000000000000219D2AD52E1D38A7B36B2CFDA76370E4F9A292FDD9300E261274BBAA5EBDA5EB3A84A7616967156CF6A95A751454F6DB18DF67BFC0FAD2781A952A125A8D453A8E2A307097372E36F358E9B742E405C25EA9B42D1E18D3A9BCD4F1AABED2A8D47FE2B625E9C21469C69D35CB18AC247D8C1D4C6440005000000000000000000000000000000000000000000000000000000000000000000001FFD9";



$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = 'dbname';
mysql_select_db($dbname);
$alt = "thomisback";
mysql_query("INSERT INTO `images` (`image`, `image_path`, `image_type`, `image_x`, `image_y`, `image_size`, `alt`, `avail`) VALUES 
('$str_hex', '', 'image/pjpeg', '', '', '', '$alt', 'Y')")or die(mysql_error());

?>

[DarkWater]

Try using hexbin() on the number.  Btw, don't need the " " around the number.

 

$hex = 0xFF8A8F9D;

$bin = hexbin($hex);

 

[Thomisback]

Thanks for the reply although I get an error:

Fatal error: Call to undefined function hexbin() in C:\xampplite\htdocs\hextobin.php on line 8

[DarkWater]

My bad, forgot that wasn't a function like the other conversions.  You'll need:

 

$bin = base_convert($hex, 16, 2);

 

>_< Sorry about that.

[Thomisback]

Hehe no problem, although now I get:

Warning: base_convert() [function.base-convert]: Number too large in C:\xampplite\htdocs\hextobin.php on line 8

[DarkWater]

Oh yeah, just realized that it wouldn't work after I posted.  I'd think that MySQL would have some sort of conversion function (I've never used the --hex-blob flag.  Why did you use it, by the way? o-O)  I'll do some research on it for you.  I'm curious on how MySQL handles it now.

[Thomisback]

Thanks Well I stored pictures in BLOB fields on my localhost, I now want to transfer the database to my webhost but I can't get the pictures to work :/ There might be a different workaround for this...

[DarkWater]

Thanks Well I stored pictures in BLOB fields on my localhost, I now want to transfer the database to my webhost but I can't get the pictures to work :/ There might be a different workaround for this...

 

I usually do LOAD DATE INFILE or a native MySQL method of loading in data when handling backups.  Have you tried any of those?

 

[Jabop]

@Thomisback: eww, storing images in a db? Why didn't you just store the image info in the db and then use the file system?

[Thomisback]

It's from a script I bought, not my own so I don't really know :/

[Thomisback]

Thanks Well I stored pictures in BLOB fields on my localhost, I now want to transfer the database to my webhost but I can't get the pictures to work :/ There might be a different workaround for this...

 

I usually do LOAD DATE INFILE or a native MySQL method of loading in data when handling backups.  Have you tried any of those?

 

Thanks, i'll try LOAD DATE INFILE now

[Thomisback]

I just tried LOAD DATA INFILE and it just creates a BLOB with nothing in it:

[bLOB - 0 B]

[DarkWater]

Try a normal backup rather than with the --hex-blob flag.

[discomatt]

@Thomisback: eww, storing images in a db? Why didn't you just store the image info in the db and then use the file system?

 

This is purely preference. There are advantages and disadvantages for both. There are times when using a DB to store images isn't smart, but for the most part it can be a great way to manage user-submitted image files.