You know what’s coming by now, if you’ve read such balanced posts as MySQL sucks, MySQL makes me sick, or MySQL is a PITA.
Consider this fairly normal scenario: a database on one server that I need to move to another server. I might do it like this:
server1$ mysqldump my_database > db.sql
server2$ mysql my_database < db.sql
Unfortunately, if my_database happens to contain tables with foreign key constraints, we get the following delightfully informative error:
ERROR 1005 (HY000) at line 12: Can’t create table ‘./my_database/foo.frm’ (errno: 150)
It turns out the way to resolve this is to use SET FOREIGN_KEY_CHECKS = 0; before you do the import, and SET FOREIGN_KEY_CHECKS = 1; after the import successfully completes.
So, my questions to the MySQL apologists are these:
- Why do we get to see ERROR 1005 (HY000) instead of something intelligent?
- Why doesn’t mysqldump warn the user that it cannot safely create a dump that can be restored error-free?
- Why doesn’t mysqldump offer to prepend and append suitable FOREIGN_KEY_CHECKS lines to the output?
- Why do people still use and even recommend this joke of a database, thereby forcing me to deal with it?
Hell yeah! DIE MYSQL! Postgresql rules!
When i tried restoring my backups onto a new machine I found that only 700MB of 1.2GB worth of databases actually restored for this precise reason, which was fun.
Personally i now
FLUSH TABLES WITH READ LOCK
tar -cf mysql_backup.tar *database_dir*
then UNLOCK TABLES,
Note only works if all tables are myisam and you can tolerate not being able to write to your database for the minute or so it takes to tar up the data directory.
mysqlsnapshot http://jeremy.zawodny.com/mysql/mysqlsnapshot/ looks good but i couldn’t get it to work properly
Hmm, surely if your tables are MyISAM then they don’t support foreign keys anyhow? I thought foreign keys were only available in InnoDB tables?
yeah sorry by “this issue” i meant the fact that mysqldump will merrily dump out sql that mysql will refuse to load. If any of your users have used a reserved keyword as a column name in a table (and it appears many do and mysql doesn’t stop them when they do it) then the table won’t get created and won’t load when restoring from backup.
I would still look at mysqlsnapshot or master to slave replication if you are looking to get a backup, mysqldump doesn’t work.
Andrew, don’t lose your time. Use a REAL database, just use PostgreSQL – http://www.postgresql.org/
Postgres is better??? I’ll just say one word… VACUUM!
Yeah, PostgreSQL is better. I’ll just say one word: autovacuum.
(Postgres is not better, but since Postgres ceased maintenance in 1994, replaced first by Postgres95 and then by PostgreSQL, this is a straw-man comparison.)