MySQL import and export problem I am trying to export a table from one database and import it into another. The table in question has a definition like this:

CREATE TABLE `mydb`.`people` (
  `id` BINARY(16) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)


And I'm exporting it like this:

mysqldump -u root -pmypass -h x.x.x.x mydb people &gt; /tmp/export.sql


And trying to import it like this:

mysql -u root  mydb &lt; /tmp/export.sql


To which I get the error: 

ERROR 1406 (22001) at line 1: Data too long for column 'id' at row 1


Looking at the file, It has exported the id as values like this:

'\0\0�\�\��DM�� �Ϥ6�'


Which seems to be interpreted as a string. Now, since I'm dealing with tremendous amounts of data and cannot go through it manually, is there a way I can fix the import/export to interpret this value as binary?
Use --hex-blob:

mysql&gt; create database dbase;
Query OK, 1 row affected (0.00 sec)

mysql&gt; use dbase;
Database changed
mysql&gt; create table hexhex ( id binary(16) );
Query OK, 0 rows affected (0.01 sec)

mysql&gt; insert into hexhex values (UNHEX('DEADBEEF'));
Query OK, 1 row affected (0.01 sec)

mysql&gt; select * from hexhex;
+------------------+
| id               |
+------------------+
| ޭ▒               |
+------------------+
1 row in set (0.00 sec)

mysql&gt; quit
Bye
root@ironforge:~# mysqldump --hex-blob -p dbase &gt; dbase.sql
Enter password:
root@ironforge:~#


This produces INSERT statements in the format:

INSERT INTO `hexhex` VALUES (0xDEADBEEF000000000000000000000000);


.. which are import-safe.

Re-import it, and check it's right:

root@ironforge:~# mysql -p dbase &lt; dbase.sql
Enter password:
root@ironforge:~# mysql -p dbase

mysql&gt; use dbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql&gt; select * from hexhex;
+------------------+
| id               |
+------------------+
| ޭ▒               |
+------------------+
1 row in set (0.01 sec)

mysql&gt; select hex(id) from hexhex;
+----------------------------------+
| hex(id)                          |
+----------------------------------+
| DEADBEEF000000000000000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql&gt;