• ARTICLES
SEARCH

How-To Geek

Restore MySQL database from a backup file

There are many reasons you would want to restore a database from a backup file… But you should also test this on a test server just to make sure that your database backups are working correctly. Here’s the syntax:

mysql -h hostname -u username -pthepassword databasename < dumpfile.sql

Here’s an example:

mysql -h localhost -u root -p72aDufi8 db01 < thedumpfile.sql

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 09/11/06

Comments (8)

  1. Abdo

    do we use a space between ‘p’ and the password or not ?
    thanks for ur greate tutos.
    /abdo

  2. serg

    -uUser -pPassword (correct)

  3. judeson

    nice codding . thanks its working

  4. Manoj Pandey

    On my Windows 2003 machine I’m issuing the following command:

    mysql -h localhost -u root -p passwd mynewdb < D:\MySQLBackup\newdb_suite.sql

    … but its giving me following:

    mysql Ver 14.14 Distrib 5.1.51, for Win32 (ia32)
    Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL v2 license
    Usage: mysql [OPTIONS] [database]
    -?, –help Display this help and exit.
    -I, –help Synonym for -?
    –auto-rehash Enable automatic rehashing. One doesn't need to use
    'rehash' to get table and field completion, but startup
    and reconnecting may take a longer time. Disable with
    –disable-auto-rehash.
    -A, –no-auto-rehash
    No automatic rehashing. One has to use 'rehash' to get
    table and field completion. This gives a quicker start of
    mysql and disables rehashing on reconnect.
    -B, –batch Don't use history file. Disable interactive behavior.
    (Enables –silent.)
    –character-sets-dir=name
    Directory for character set files.
    –column-type-info Display column type information.
    -c, –comments Preserve comments. Send comments to the server. The
    default is –skip-comments (discard comments), enable
    with –comments.
    -C, –compress Use compression in server/client protocol.
    -#, –debug[=#] This is a non-debug version. Catch this and exit.
    –debug-check Check memory and open file usage at exit.
    -T, –debug-info Print some debug info at exit.
    -D, –database=name Database to use.
    –default-character-set=name
    Set the default character set.
    –delimiter=name Delimiter to be used.
    -e, –execute=name Execute command and quit. (Disables –force and history

    Default options are read from the following files in the given order:
    C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\Documents and Settings\manojp\WINDOWS\my.
    ini C:\Documents and Settings\manojp\WINDOWS\my.cnf C:\my.ini C:\my.cnf D:\Progr
    am Files\MySQL\MySQL Server 5.1\my.ini D:\Program Files\MySQL\MySQL Server 5.1\m
    y.cnf
    The following groups are read: mysql client
    The following options may be given as the first argument:
    –print-defaults Print the program argument list and exit.
    –no-defaults Don't read default options from any option file.
    –defaults-file=# Only read default options from the given file #.
    –defaults-extra-file=# Read this file after the global files are read.

    Variables (–variable-name=value)
    and boolean options {FALSE|TRUE} Value (after reading options)
    ——————————— —————————–
    auto-rehash TRUE
    character-sets-dir (No default value)
    column-type-info FALSE
    comments FALSE
    compress FALSE
    debug-check FALSE

    …. and many more lines…

    … please help… Thanks

  5. yoeurm Mut

    my name mut who i studies IT as webdevaloper Programming, but i have one question i would like you help me to resovle this Problem. the question is i have 1 database backup with mysql file but when i would like to restore this file backup of my database but it is impossible, so could you tell me how to restore database with mysql as my problem? hope you coudl tell me detail about this program.

    thank you in advance!

    Mut
    ————

  6. Saul

    Hi, i did the backup, but when i go to the mysql command line and check the database i see that it doesn’t have any tables anymore, it drops them, and when i restore them, the tables are back but with no data.

    Help me with this please.

  7. Tommy

    TYVM you’ve solved all my prloembs

  8. nitu

    hello

Enter Your Email Here to Get Access for Free:

Go check your email!