How to backup a MySQL database from the command line

,

Simple command line options to backup a MySQL database:

  1. backup a database locally using mysqldump, date and time in the filename, and compression
    mysqldump -uUSERNAME -pPASSWORD DATABASE | gzip -9 > ~/$(date +%Y-%m-%d-%H%M)_intiotpa_stage.sql
  2. import compressed file back into database
    gunzip < ~/backups/FILENAME.sql.gz | mysql -uUSERNAME -pPASSWORD DATABASE

/0 Comments/by

Migrating a WordPress database to a new host name

, ,

process steps:

  1. export the database
  2. updated the static entries of the hostname in the database
  3. import the database

commands:

  1. mysqldump -u _USERNAME_ -p _DBNAME_ > _DBNAME_.sql
  2. cat _DBNAME_.sql | sed -e ‘s/_HOSTNAME_/_NEWHOSTNAME/g’ > _DBNAME_UPDATED.sql
  3. mysql -u _USERNAME_ -p _DBNAME_ < _DBNAME_UPDATED.sql

options & variables:

  • _USERNAME_ = the the database user name
  • -p = will prompt for the password
  • _DBNAME_ = the name of the database
  • _HOSTNAME_ = the hostname of the existing server
  • _NEWHOSTNAME = the new hostname to use
  • _DBNAME_UPDATED = the name of the new updated SQL file

/0 Comments/by