How to Backup and Restore (Export and Import) MySQL Databases Tutorial

»»»How to Backup and Restore (Export and Import) MySQL Databases Tutorial
phpMyAdmin can be used to export or backup MySQL or MariaDB databases easily. However, if the database size is very big, it probably won’t be a good idea. phpMyAdmin allows users to save database dump as file or display on screen, which involves exporting SQL statements from the server, and transmitting the data across slower network connection or Internet to user’s computer. This process slow the exporting process, increase database locking time and thus MySQL or MariaDB unavailability, slow the server and may simply crash the Apache HTTPD server if too many incoming web connections hogging the system’s resources.

The better way to backup and export MySQL or MariaDB database is by doing the task locally on the server, so that the tables’ data can be instantly dumped on the local disk without delay. Thus export speed will be faster and reduce the time MySQL or MariaDB database or table is locked for accessing. This tutorial is the guide on how to backup (export) and restore (import) MySQL or MariaDB database(s) on the database server itself by using the mysqldump and mysql utilities. There are basically two methods to backup MySQL or MariaDB , one is by copying all table files (*.frm, *.MYD, and *.MYI files) or by using mysqlhotcopy utility, but it only works for MyISAM tables. Below tutorial will concentrate on mysqldump which works for both MyISAM and InnoDB tables.

How to Export or Backup or Dump A MySQL or MariaDB Database

To export a MySQL or MariaDB database into a dump file, simply type the following command syntax in the shell. You can use Telnet or SSH to remotely login to the machine if you don’t have access to the physical box.

mysqldump -u username -ppassword database_name > dump.sql

Replace username with a valid MySQL or MariaDB user ID, password with the valid password for the user (IMPORTANT: no space after -p and the password, else mysqldump will prompt you for password yet will treat the password as database name, so the backup will fail) and database_name with the actual name of the database you want to export. Finally, you can put whatever name you like for the output SQL dump file, here been dump.sql.

The whole data, tables, structures and database of database_name will be backed up into a SQL text file named dump.sql with the above command.

How to Export A MySQL or MariaDB Database Structures Only

If you no longer need the data inside the database’s tables (unlikely), simply add –no-data switch to export only the tables’ structures. For example, the syntax is:

mysqldump -u username -ppassword --no-data database_name > dump.sql

How to Backup Only Data of a MySQL or MariaDB Database

If you only want the data to be backed up, use –no-create-info option. With this setting, the dump will not re-create the database, tables, fields, and other structures when importing. Use this only if you pretty sure that you have a duplicate databases with same structure, where you only need to refresh the data.

mysqldump -u username -ppassword --no-create-info database_name > dump.sql

How to Dump Several MySQL or MariaDB Databases into Text File

–databases option allows you to specify more than 1 database. Example syntax:

mysqldump -u username -ppassword --databases db_name1 [db_name2 ...] > dump.sql

How to Dump All Databases in MySQL or MariaDB Server

To dump all databases, use the –all-databases option, and no databases’ name need to be specified anymore.

mysqldump -u username -ppassword --all-databases > dump.sql

How to Online Backup InnoDB Tables

Backup the database inevitable cause MySQL or MariaDB server unavailable to applications because when exporting, all tables acquired a global read lock using FLUSH TABLES WITH READ LOCK at the beginning of the dump until finish. So although READ statements can proceed, all INSERT, UPDATE and DELETE statements will have to queue due to locked tables, as if MySQL or MariaDB is down or stalled. If you’re using InnoDB, –single-transaction is the way to minimize this locking time duration to almost non-existent as if performing an online backup. It works by reading the binary log coordinates as soon as the lock has been acquired, and lock is then immediately released.

Syntax:

mysqldump -u username -ppassword  --all-databases --single-transaction > dump.sql

How to Restore and Import MySQL or MariaDB Database

You can restore from phpMyAdmin, using Import tab. For faster way, upload the dump file to the MySQL or MariaDB server, and use the following command to import the databases back into the MySQL or MariaDB server.

mysql -u username -ppassword database_name < dump.sql
If you want to connect to MySQL or MariaDB servers remotely on another server, append -h server-name-or-IP-address to the command.

The import and export of MySQL or MariaDB database not only is important to recover the data when disaster strikes, but also provides an easy way to migrate or move to another server, such as when switching web hosting providers. However, do note that one common problem – character set encoding. Newer release of mysqldump uses UTF8 as its default charset if nothing is specified, while older versions (older than 4.1 typically) use Latin1 as default character set. If you database charset is Latin1 and dump in UTF8 collation, the data may ends up become simply rubbish, garbled, or unreadable (frequently happen with WordPress blog). If this case, use –default-character-set=charset_name option to specify the character set or convert the database to UTF8.

By | 2016-12-09T08:39:44+00:00 December 9th, 2016|Categories: Databases|Tags: , , |41 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.
  • Nice post.. I usually just copy the files.. for temporary.. and get all mysql queries for permanent backup..

  • It shows how to backup all-databases… but how to restore all of them at once from the same file? … I tried restoring the same way but receive a msg: unknown database: all-databases (I've done it before but I forget, it's been a while… I'll keep looking around)

  • mysql -u root -p < all-databases.sql
    done. 🙂

  • tc

    mysqldump worked.

    But 'mysql' to import data did not. The error is:
    ERROR at line 1: Unknown command 'm'

    any ideas?

  • thanks alot .. you saved my time and money both .. hat off to you .

  • Good post. But you can add some advanced tips to it, to make it better. Goog luck!

  • tarot

    Good post, but what about backing up the mysql users them selves?

  • ciao sto cercando di effettuare il backup in locale su vista e iis 7.0

    eseguo questo file php

    db.sql

    ?>

    ma mi restituisce il seguente errore

    Parse error: syntax error, unexpected T_DEC in C:inetpubwwwrootack.php on line 2

    qualche consiglio?

    grazie, daniele.

  • Pingback: How to export and import MySql database | WebGuru’s Blog about Digital Life()

  • aharown07

    Great article! But how do I do this on my pc under xampp? All I seem to be able to achieve are syntax errors.

  • Th

    Thanks alot 🙂

  • Spurious

    It's –all-databases, not -all-databases.

  • farooq

    Its a great article..

    It really helped me alot

  • SQL Very Slow Restore

    How about this in Windows using DOS Batch file to call mysqldump or as I use now mysql?

    mysql –host 11.12.166.33 –user=MENANE –password=password MENAME < sql_file.sql

    What about using <, << for Restore to host?

    I have tried a few methods, (I am amost. a newbie to sql), I only have cPanal, myphpadmin hosting.

    Mine take many hours 8 Plus (about 1.5Gb) looking for much faster.

    Basically I want to Insert a MySql database over 2Gb in total to a cPanel Host quickly.

    Any help?

    Thank you 🙂

  • Mohamed Asik

    Great article. Thank you guys.

  • bipin

    in my wamp5 the username is root and no password, what is the syntax for that situation when password is empty

  • Rakesh

    Its Simple.. Backup Mysql 5.0..

    click on Mysql Administrator . There you can find both Backup and Restore options + Many more.. 🙂

  • Rakesh

    Its Simple..
    To Backup/Restore Mysql 5.0..

    click on Mysql Administrator(you can find it in Start menu -> All Programs -> mysql ). There you can find both Backup and Restore options + Many more.. 😉 🙂 /)

  • danipelon

    About the encoding when mysqldump exports

    mysqldump adds this line in the file with the exported data
    <code>
    /*!40101 SET NAMES utf8 */;
    </code>
    This file is, by default, encoded in UTF8

    When mysqldump imports the data from this file, it reads this line.
    This way, though the data base is in latin1, the import will be always correct, unless you edit the export_file.sql whith a non-utf8 editor.

    If you indicate
    <code>
    –default-character-set=latin1
    </code>
    then the file will be encoded in ISO-8859-1 and mysqldump will add this line
    <code>
    /*!40101 SET NAMES latin1 */;
    </code>
    When importing, mysqldump will read this line and the import will be correct too.
    But note that if the table is latin1 you wont be able to save non-latin1 characters, with few exceptions (It exports euro character € as EUR)

  • Gamini

    Thank u for above example

    GG

  • MeenaChary

    thanks a lot ,its very useful

  • wittygraphy

    Mysqldump processes tables in alphabetical order.

    The restore works fine for MyISAM. But for Innodb, the foreign key constraints will likely complaints.

  • vivek

    This is a really good example far better than the actual mysql dump manual page available on the mysql.com website as myself being a webmaster have to take Dumps and I all the time like other lazy programmers forget the commands and google you to get those commands in my memory and then in my putty screen !

    Thanks buddy for this practical guide rather than a boring mysql manual !

  • octopusgrabbus

    Thanks. I find the MySQL documentation sometimes confusing. This solution was useful and just got me out of a bind.

  • Windows USer

    Not sure why everyone assumes the reader uses their OS ?

    Waste of time.

    • Jacob

      @Windows USer – This is not a waste of time at all. Highly useful little reference when those of us who use Windows need to make database backups and imports on remote *nix servers. I'm sure there are plenty of tutorials for command line MySQL under Windows out there, too.

      Thanks to the author for this useful article.

    • Software Testing Mik

      Dude, don't be a jerk on other people's blogs. Most web servers are run under *nix, so this serves a huge audience – thanks to the author – I just used it since I never memorize anything. If you can't figure out how to translate this to Windows, then hire a sysadmin.

      • GTITurbo

        While WindowsUser may not have understood, he (or she) does have a point in that many tech articles on the Internet there is no mention of the Operating System under which the article was written or directly pertains to. This occurs very often and it can be an annoyance.

        I have run into this myself on occasion.

        However, this is a useful article and WindowUSer was probably just confused.
        Also, public blogs are ope to criticism as well as praise 🙂

        Helpful article.

    • Bernardo

      Actually those commands if I'm not mistaken should work exactly the same in Windows.
      Very little people know that the Windows commandline is not that featureless.

      Anyway, nice article.

  • Amit verma

    thank you very much for this guidence.. This helped me very much and it is very usefull. Again thank you..

  • Johnson

    I usually get overcome by fear of backing up my database. This article helps me a lot. By the way, naming convention of tables will facilitate those who have various tables in one database.

  • yuliang11

    well, how do you create back the database from the dumped files ? by just executing it on mysql command?

  • Sheela

    I tried to export the mysql file using the command,
    mysqldump -u username -ppassword database_name > dump.sql,
    But it showed access denied. My OS is Windows7. Can anyone pls help me to export a mysql table to a pen drive, pls help me as soon as possible

    • rohit

      you can direct export your data from your ‘phpmyadmin’ by single click.
      what type of database you are using……?

  • Pingback: Exportación e importación de bases de datos MySQL en Linux – REGOREMOR()

  • kiki

    mysqldump worked.

  • Liam Zanoni

    Very nice set of examples – one correction:

    use two dashes for –no-data. In development and test it is common to need the DB structure without data.

    FORENSIC type use before the import

  • pavan

    Thanks for such a agood article… The same commands works on CENTOS 6 also. I think UNIX/LINUX users can use these cmnds..

    Thanks to the author.

    • Devin

      If you’re running mysql on a windows server, for whatever reason. You will need to navigate to the mysql install folder using cmd, and all the above commands should work

  • Vennam Anand

    Hello

    I’ve tried ur queries, but none of ’em are working…
    “mysqldump -u root -ppassword drf3 > dump.sql;”
    Ran exactly the same in those double quotes, but still i get the Error 1065
    i use MySql 5.5 Command Line Client..
    Kindly help me with this asap, as i have got urgency in my project

  • Kos Petoussis

    useless article, because you don’t explain how to import the results of the mysqldump. Which is kind of missing the point.
    LK stands for Lazy Kid?

Pin It on Pinterest

Share This

Share This

Share this post with your friends!