Backup/Restore issue with mysqldump (table with URLs)

1
votes

I have a MySQL that that primarily has innodb tables. I did back up using mysqldump, phpmyadmin, and by saving the files in /var/lib/mysql.

When I try to restore them now, all the tables are restored except for a table that contains "URLs". The information in that table is not complete. I tried restoring the three types of backup and it's the same. (The URLs are stored using UTF8)

Any idea what did that happen? Is there a chance that mysqldump doesn't work always as expected? Do you think that there is a way for me to restore my "URL" data?

1
mysqlbackupmysqldumprestore
How are you restoring the data? This is the likely culprit. Tools like Phpmyadmin are not as reliable as direct command-line input. eg: cat dump.sql | mysql -u USER -pPASSWORD -h HOST -d DATABASE – James Andres
Also, could you attach a snippet of the dump data (assuming it can be made public)? – James Andres
I'm restoring using mysql -u root -pPASSWORD databasename < dump.sql – MTI
The problem is that the dump data is too large, and I don't think it contains the missing table (One of my colleagues opened it after 30 minutes of waiting). – MTI
Ah yes. Huge db dumps will sometimes require changes to your MySQL config file. Usually increasing the max-allowed-packet settings will do it. Obviously, do this only for trusted connections. – James Andres

1 Answers

1
votes

I've recently had trouble using mysqldump. My locales and client and server and table charsets and everything that could posibly be set to use utf8 charset was set to use it and still I was getting rubbled ASCII output on mysqldump, which lead to errors when importing because of all the ??·$^"·???. mojibake input. My solution (hope it works for you):

export:

mysqldump -u USER -pPASS -r db.sql db

import:

mysql -u USER -pPASS db
MYSQL [db]>SOURCE db.sql

Also solutions like percona xtrabackup may seem overkill at first glance, but this in particular at least works really good and the basic usage is really simple, the tool is GPL licensed and you don't need to worry about non exact replications, because it copies the binary database files as they are without generating commands that are supposed to make a database like yours but then they won't...