Black Friday sale is now on! 50% off Laravel: The Modular Way. Learn more

How to backup and restore a MySQL Database

David Carr

PHP & MySQL Tutorials

  • Importing sql file into a database
  • I was recently asked how do backup and restore a database. As with most things in programming, there are multiple ways to export and restore a database I’ll outlook a couple of them in this post.

    Method 1 Command Line

    Login to your Shell/Command prompt then run the following command, this will generate a dump of the database into the file specified at the end of the command, the password is omitted from the command. You will be prompted for the password, passing the password in a command is considered insecure as it can leave traces in the history logs.

    mysqldump -h [host] -p -u [user] [database name] > filename.sql

    The keys used are:
    -h = the host most likely localhost
    -p = password - leave blank to be prompted
    -u = username


    mysqldump -h localhost -p -u root mydatabase > mybackup.sql

    Method 2 Exec

    This method relies on exec been able to run on the server. 

    Set the login credentials and also the filename, in this case, the filename will be the date and time such as 2015-02-20-16-30-00.sql

    $toDay = date('d-m-Y-H-i-s');
    $dbhost = ‘localhost';
    $dbuser = '';
    $dbpass = '';
    $dbname = '';
    exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost $dbname > ".$toDay.".sql");

    Method 3 phpMyAdmin

    If the above methods are not available to you, a web-based MySQL client can be used such as phpMyAdmin:

    1) Login to phpMyAdmin
    2) Select the database from the left menu
    3) click export from the main menu
    4) select the desired options and format, if in doubt select the quick and SQL format.
    5) download the generated sql file.

    Importing sql file into a database

    Method 1 Command Line

    Like the export and import can be run by calling mysql then the credentials followed by the database to import into and the path to the sql file.

    mysql -u username -p database_name < file.sql

    Method 2 Exec

    The command is passed through exec:

    $file = 'myfile.sql';
    $dbuser = '';
    $dbpass = '';
    $dbname = '';
    exec("mysql --user=$dbuser --password='$dbpass' $dbname < $file");

    Method 3 phpMyAdmin

    To import using phpMyAdmin, the steps are revered from exporting:

    1) Login to phpMyAdmin
    2) Select the desired database
    3) Click on import
    4) Select the sql file and press go to upload and import the data

    Laravel Modules Book by David Carr

    Help support the blog so that I can continue creating new content!

    Subscribe to my newsletter

    Subscribe and get my books and product announcements.

    © 2009 - 2022 DC Blog. All code MIT license. All rights reserved.