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;