{"id":513,"date":"2012-11-17T12:44:05","date_gmt":"2012-11-17T10:44:05","guid":{"rendered":"http:\/\/www.extradrm.com\/?p=513"},"modified":"2013-06-18T20:55:11","modified_gmt":"2013-06-18T18:55:11","slug":"backup-and-restore-mysql-database-using-mysqldump","status":"publish","type":"post","link":"https:\/\/www.extradrm.com\/?p=513","title":{"rendered":"Backup and Restore MySQL Database Using mysqldump"},"content":{"rendered":"<p><strong>PhpMyAdmin<\/strong> can be used to export or backup MySQL databases easily. However, if the database size is very big, it probably won\u2019t be a good idea. This process slow the exporting process, increase database locking time and thus MySQL unavailability, slow the server and may simply crash the Apache HTTPD server if too many incoming web connections hogging the system\u2019s resources.<\/p>\n<p>The better way to backup and export MySQL database is by doing the task locally on the server, so that the tables\u2019 data can be instantly dumped on the local disk without delay. Thus export speed will be faster and reduce the time MySQL database or table is locked for accessing. This tutorial is the guide on how to backup (export) and restore (import) MySQL database(s) on the database server itself by using the mysqldump and mysql utilities. Below tutorial will concentrate on mysqldump which works for both <strong>MyISAM and InnoDB tables<\/strong>.<\/p>\n<p><strong>mysqldump<\/strong> is an effective tool to backup MySQL database. It creates a *.sql file with <strong>DROP table<\/strong>, <strong>CREATE table<\/strong> and <strong>INSERT into<\/strong> sql-statements of the source database. To restore the database,\u00a0 execute the *.sql file on destination database.<\/p>\n<h3>How to Export or Backup or Dump A MySQL Database<\/h3>\n<p>To export a MySQL database into a dump file, simply type the following command syntax in the shell. <\/p>\n<pre>mysqldump -u root -p[root_password] [database_name] &gt; dumpfilename.sql<\/pre>\n<p>How to Export A MySQL Database Structures Only<\/p>\n<p>If you no longer need the data inside the database\u2019s tables (unlikely), simply add \u2013no-data switch to export only the tables\u2019 structures. For example, the syntax is:<\/p>\n<pre>mysqldump -u username -ppassword \u2013no-data database_name > dump.sql<\/pre>\n<h3>How to Restore and Import MySQL Database<\/h3>\n<pre> mysql -u root -p[root_password] [database_name] &lt; dumpfilename.sql<\/pre>\n<h2>Some useful backup and restore instructions for a set of databases are:<\/h2>\n<h3>&#8211; Backup all the databases:<\/h3>\n<pre># mysqldump -u root -ptmppassword --all-databases &gt; \/save\/all-database.sql<\/pre>\n<h3>&#8211; Restore all databases :<\/h3>\n<p>To restore from .sql file follow this command :<\/p>\n<pre># mysql \u2013user=xxxx \u2013password=xxxx &lt; all_databases_save.sql<\/pre>\n<p>&nbsp;<br \/>\nThe <strong>import and export of MySQL 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<\/strong>, such as when switching web hosting providers.<br \/>\nHowever, do note that one common problem \u2013 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 characterset.<br \/>\nIf 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).<br \/>\nIf this case, use <strong>\u2013default-character-set=charset_name option<\/strong> to specify the character set or convert the database to UTF8.<br \/>\nCheers,<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PhpMyAdmin can be used to export or backup MySQL databases easily. However, if the database size is very big, it probably won\u2019t be a good idea. This process slow the exporting process, increase database&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":2845,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[23,35,25],"tags":[],"youtube_video":null,"_links":{"self":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/513"}],"collection":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=513"}],"version-history":[{"count":0,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/513\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/media\/2845"}],"wp:attachment":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=513"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=513"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=513"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}