{"id":848,"date":"2018-07-09T10:29:55","date_gmt":"2018-07-09T10:29:55","guid":{"rendered":"http:\/\/orissawebhosting.in\/blog\/?p=848"},"modified":"2019-06-27T15:22:54","modified_gmt":"2019-06-27T15:22:54","slug":"how-to-import-and-export-databases-in-mysql-or-mariadb","status":"publish","type":"post","link":"https:\/\/orissawebhosting.in\/blog\/how-to-import-and-export-databases-in-mysql-or-mariadb\/","title":{"rendered":"How To Import and Export Databases in MySQL or MariaDB"},"content":{"rendered":"<h4>Import and Export Databases in MySQL or MariaDB<\/h4>\n<p>Being able to import and export your database is an important skill to have. You can use data dumps for backup and restoration purposes, so you can recover older copies of your database in case of an emergency, or you can use them to migrate data to a new server or development environment.<\/p>\n<p>To import and\/or export a MySQL or MariaDB database, you will need:<\/p>\n<ul>\n<li>Access to the Linux server running MySQL or MariaDB<\/li>\n<li>The database name and user credentials for it<\/li>\n<\/ul>\n<h4>Exporting the Database<\/h4>\n<p>The\u00a0<code>mysqldump<\/code>\u00a0console utility is used to export databases to SQL text files. These files can easily be transferred and moved around. You will need the database name itself as well as the username and password to an account with privileges allowing at least full read only access to the database.<\/p>\n<p>Export your database using the following command.<\/p>\n<pre># mysqldump -u <span class=\"highlight\">username<\/span> -p <span class=\"highlight\">database_name<\/span> &gt; <span class=\"highlight\">data-dump.sql<\/span><\/pre>\n<ul>\n<li><code>username<\/code>\u00a0is the username you can log in to the database with<\/li>\n<li><code>database_name<\/code>\u00a0is the name of the database that will be exported<\/li>\n<li><code>data-dump.sql<\/code>\u00a0is the file in the current directory that the output will be saved to<\/li>\n<\/ul>\n<p>The command will produce no visual output, but you can inspect the contents of\u00a0<code>filename.sql<\/code>\u00a0to check if it&#8217;s a legitimate SQL dump file by using:<\/p>\n<pre># head -n 5 <span class=\"highlight\">data-dump.sql<\/span><\/pre>\n<p>The top of the file should look similar to this, mentioning that it&#8217;s a MySQL dump for a database named\u00a0<code>database_name<\/code>.<\/p>\n<p>If any errors happen during the export process,\u00a0<code>mysqldump<\/code>\u00a0will print them clearly.<\/p>\n<h4 id=\"importing-the-database\">Importing the Database<\/h4>\n<p>To import an existing dump file into MySQL or MariaDB, you will have to create the new database. This is where the contents of the dump file will be imported.<\/p>\n<p>First, log in to the database as\u00a0<strong>root<\/strong>\u00a0or another user with sufficient privileges to create new databases.<\/p>\n<pre># mysql -u <span class=\"highlight\">root<\/span> -p<\/pre>\n<p>This will bring you into the MySQL shell prompt. Next, create a new database called\u00a0<code><span class=\"highlight\">new_database<\/span><\/code>.<\/p>\n<pre>mysql&gt; CREATE DATABASE <span class=\"highlight\">new_database<\/span>;<\/pre>\n<p>You&#8217;ll see this output confirming it was created.<\/p>\n<pre class=\"code-pre \"><code>Query OK, 1 row affected (0.00 sec)<\/code><\/pre>\n<p>Now exit the MySQL shell by pressing\u00a0<code>CTRL+D<\/code>. On the normal command line, you can import the dump file with the following command:<\/p>\n<pre>mysql -u <span class=\"highlight\">username<\/span> -p <span class=\"highlight\">new_database<\/span> &lt; <span class=\"highlight\">data-dump.sql<\/span><\/pre>\n<ul>\n<li><code>username<\/code>\u00a0is the username you can log in to the database with<\/li>\n<li><code>newdatabase<\/code>\u00a0is the name of the freshly created database<\/li>\n<li><code>data-dump.sql<\/code>\u00a0is the data dump file to be imported, located in the current directory<\/li>\n<\/ul>\n<p>The successfully-run command will produce no output. If any errors occur during the process,\u00a0<code>mysql<\/code>\u00a0will print them to the terminal instead. You can check that the database was imported by logging in to the MySQL shell again and inspecting the data. This can be done by selecting the new database with\u00a0<code>USE<span class=\"highlight\">new_database<\/span><\/code>\u00a0and then using\u00a0<code>SHOW TABLES;<\/code>\u00a0or a similar command to look at some of the data.<\/p>\n<p>Now we know how to import and export your database in mysql or mariaDB.<\/p>\n\n","protected":false},"excerpt":{"rendered":"<p>Import and Export Databases in MySQL or MariaDB Being able to import and export your database is an important skill to have. You can use data dumps for backup and restoration purposes, so you can recover older copies of your database in case of an emergency, or you can use them to migrate data to a new server or development environment. To import and\/or export a MySQL or MariaDB database, you will need: Access to the Linux server running MySQL or MariaDB The database name and user credentials for it&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/posts\/848"}],"collection":[{"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/comments?post=848"}],"version-history":[{"count":5,"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/posts\/848\/revisions"}],"predecessor-version":[{"id":1109,"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/posts\/848\/revisions\/1109"}],"wp:attachment":[{"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/media?parent=848"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/categories?post=848"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/orissawebhosting.in\/blog\/wp-json\/wp\/v2\/tags?post=848"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}