MySQL Samples and Snippets

Often times I find myself in a situation where I need to do a certain task in MySQL, but my memory fails me. So I’ve decided to compile this little list to help jog my memory, when senility rears its’ ugly head.

mysqldump Database Layout Only

mysqldump -u USERNAME -pPASSWORD --add-drop-table --compact --no-data --skip-comments DBNAME > DBNAME.sql

mysqldump Database Content Only

mysqldump -u USERNAME -pPASSWORD --compact --extended-insert --skip-comments DBNAME > DBNAME.sql

Edit Tables with Foreign Key Constraints

This should be used sparingly, as you can seriously mess up your data if you do so. But I have found a few occassions (i.e. importing a portion of a database that has 1 large table that is a foreign key in another table) where this has come in handy.

# turn off the constraints
SET FOREIGN_KEY_CHECKS = 0;

# process your data
DELETE FROM `tablename` ...;
UPDATE `tablename` SET ...;

# turn the constraints back on (IMPORTANT!)
SET FOREIGN_KEY_CHECKS = 1;

Comments

comments

Posted in MySQL Tagged with: