#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    111
    Rep Power
    4

    How to back up database?


    Hi. My web hosting provide will charge me extra money if I want to do backup to my mysql database. My size of my db is very small but I've alot of tables though.

    What's the best way to backup my database schema and data in a way so I can easily restore to a different server from scatch?

    Can you tell me not only how to backup and also how to restore from the backup?
    I don't have like a backup drive or tape. So it has to be something easy to do.

    Thank you.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    i use heidisql, which is a windows based desktop utility program, that connects my computer to my internet service provider's mysql database

    backups and restores are trivially easy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    2
    Rep Power
    0

    How to backup database?


    The best way to backup & restore MySQL database is by using mysqldump command. Following is the command to backup a single database:
    mysqldump -u root -p[root password] [database_name] > backupfilename.sql

    To restore MySQL database you first need to create an empty database and then use below command:
    mysql -u root -p[root password] [database_ name1]< databasename.sql

    Above command will restore the database_name1 file to databasename.sql.

    To know more about this command you can search on Google by typing “MySQL backup commands”.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by diawilliam
    The best way to backup & restore MySQL database is by using mysqldump command. Following is the command to backup a single database:
    mysqldump -u root -p[root password] [database_name] > backupfilename.sql
    please explain to beebac exactly how to do this on a remote server belonging to the web hosting provider, and where exactly on the server the backup file will be created
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Location
    Italy
    Posts
    36
    Rep Power
    2
    The solution suggested by diawilliam is the best, because a backup solution should be reliable even when network does not work or is too slow.

    But the "right solution" can only be done if the hoster provides you a remote access to the command line.
    If so, just check mysql.com manual for mysqldump and Linux man page for cron.

    The reason why I think that this solution is better is that I think that your provider keeps the db and your site files on different machines. So if a machine burn, hopefully the other one will not. But it would be better to have a local script which daly downloads the latest backup.

IMN logo majestic logo threadwatch logo seochat tools logo