1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

Taking (backing up) and restoring MySQL dump files

Discussion in 'Technical chat' started by Jamie Edwards, Apr 21, 2007.

  1. Jamie Edwards

    Jamie Edwards Staff Member

    Via SSH / MySQL CLI command interface:

    Dumping a database into a file:
    Code:
    mysqldump -u [I]username [/I]-p[I]password databasename [/I]> dumpfilename.sql
    The above command will dump the contents of databasename into a MySQL dump file named dumpfilename.sql that can later be used to drop back into a MySQL database. The username you give must be for an account that has the correct access privileges to this database.

    The file will be saved relative to the location in which you executed the command. So, if you execute the command whilst in the /home/ folder, the dumpfile will be saved to /home/dumpfilename.sql.

    Note: If you add the parameter "--opt", it will create the database with options to drop existing tables and data when the dump file is imported into a database.


    Restoring a MySQL dump file:
    Code:
    mysql -u [I]username [/I]-p[I]password [/I][I]databasename [/I]< dumpfilename.sql
    The above command will drop the contents of the dump file dumpfilename.sql into the database named databasename. Normally, tables will be overwritten using this procedure if they already exist.
     
  2. bear

    bear Kayako Guru

    If you add one thing to that "--opt", it will create the database with options to drop existing tables and data on restore, avoiding errors and complaints from mysql.
    Code:
    mysqldump --opt -u ....
    Also, the -p must be followed immediately by the password, no spaces. It would see the space as part of the password and reject it. ;)
     
  3. Jamie Edwards

    Jamie Edwards Staff Member

    Thanks for the suggestion, updated!
     
  4. Neil Cavanagh

    Neil Cavanagh New Member

    Rose Wiles and Jamie Edwards like this.
  5. kpsmith

    kpsmith Member

    For anyone looking for a simple GUI that can backup remotely I have been using MySqlBackupFTP for the last year or so. It seems to be a very reliable free tool to perform automated remote backups. I even have it send in an email/ticket on success or failure.
     
    Jitender Kumar likes this.

Share This Page