MySQL
MySQL is the worst database. MySQL sucks. It is the worst sort of database, a database that almost works well enough but not quite. It gets you to a point where you are locked in only to tell you that you were an idiot for choosing MySQL in the first place.
Here are some examples of how badly it sucks:
- If your key is too long:
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
- If you think that international characters are different than regular ones, think again. You have to read an entire chapter just to understand what in the heck MySQL is doing with your text data. They document this because it makes no sense at all, unlike every other database in the universe.
- If you think that putting something that doesn't belong in a column should cause an error, you'll be sorely mistaken.
- Is your query doing something more than loading a single row, or a set of rows from a table? You know, useful things like joins and such? Forget it. MySQL is known to take a moderately complicated query and turn it into an impossible query. Seriously, why don't you just store your data in a text file? You can write better query code than MySQL can.
- Do you like putting data in your tables? Be careful. Putting a lot of data into tables can break the tables altogether. At my last company, we had a 2-day outage because we had the audacity to store lots of rows in a table. Somewhere along the line, the index got corrupted and we had to rebuild the (now very large) table.
- UPSERT. If you're a MySQL user, you wonder what all the fuss is about. If you're a PostgreSQL user, you know that UPSERT is NEVER easy. You have to know exactly what to do in all possible cases, and that is not solved by a simple statement in the database. Also, if you are a MySQL user, you know that doing a lot of UPSERTs will cause problems.
- MVCC --- done wrong. MySQL has an UNDO segment. When you update a lot of rows, the UNDO segment grows. If you want to abort the transaction, too bad. Your database will come to a screeching halt. PostgreSQL just tags each row with which transactions it should be visible for, like pretty much every other database nowadays. No UNDO segment to fill. Aborting a transaction is simply marking that transaction as invalid. Later on, PostgreSQL will clean out orphaned rows.
Don't use MySQL. Seriously. Use PostgreSQL. Friends don't let friends use broken databases.
Backing Up
Use this instead of mysqldump: http://www.percona.com/doc/percona-xtrabackup/
