Pick our brains...

Mysql Database Backup & Import From the Command Line

Let’s say you’ve got root privileges, and you’re working along, and you want to quickly backup a database for USERNAME on the PM servers…

cd /home/USERNAME/_back
mysqldump  -uroot  DATABASENAME  >  DATABASENAME_YYYYMMDD_shortDescription.sql


(Check the size of the backup file, always a good idea)

ls  -lah  DATABASENAME_YYYYMMDD_shortDescription.sql


(Change the owner and group of the file, so that you don’t drive a user insane, not being able to delete the file themselves)

chown  USERNAME:USERNAME  DATABASENAME_YYYYMMDD_shortDescription.sql

 

Let’s say you DON’T got root privileges, and you’re working along, and you want to quickly backup a database for USERNAME on the PM servers…

cd /home/USERNAME/_back
mysqldump  -uUSERNAME  -pPASSWORD  DATABASENAME  >  DATABASENAME_YYYYMMDD_shortDescription.sql


(Check the size of the backup file, always a good idea)

ls  -lah  DATABASENAME_YYYYMMDD_shortDescription.sql

(No need to change the owner and group of the file, since if you don’t got root, then you’re almost assuredly running as the USERNAME themselves… and so the file should be owned and grouped by USERNAME)

**Note About Stored Procedures (ie: Functions) and Triggers:

By default, mysqldump DOES include triggers, but does NOT include stored procedures (ie: functions).  To include stored procedures in the dump file, use the “–routines” flag.

mysqldump  -uUSERNAME  -pPASSWORD  --routines DATABASENAME  >  DATABASENAME_YYYYMMDD_shortDescription.sql

To Import an .sql File Into an Existing Database, Reversing the Export Process:

mysql  -uUSERNAME  -pPASSWORD  DATABASENAME  <  DATABASENAME_YYYYMMDD_shortDescription.sql

(YES, this will clobber any existing data that’s in the existing database, so please make sure that you’re sure.)

Note that the “<” goes the other direction with the import, compared to the mysqldump command.  If all goes well, you won’t see any output at the command prompt.  But your data will be in the database that you’ve specified.  Login to mysql again and show tables, or examine the data yourself.  It’ll be fun.

Forcing an Import, Ignoring Errors:

If some day you see a bunch of errors upon importing a database, and you’d like to force the matter, ignoring and errors and pressing onward, indeed you can do that too:

mysql  --force  -uUSERNAME  -pPASSWORD  DATABASENAME  <  DATABASENAME_YYYYMMDD_shortDescription.sql