How do i export and import mysql database?

MySQL Databases are one of the many ways to store server data, commonly used by plugins.

Your Shockbyte server comes with a free MySQL database that can be used to store new or existing data. If you do not have one yet, you can create a MySQL Database by following this tutorial: Creating a MySQL Database

Logging Into phpMyAdmin

Before getting started with this tutorial, log into phpMyAdmin where you can manage your MySQL database by following the steps below:

1. Log into your Multicraft control panel account. If you own more than one server, select the server on which you want to import your database into. 
2. On the left sidebar of your control panel, navigate to Advanced > MySQL Database. 
   
   
3. On the MySQL Database page, click on the Administration Link to log into phpMyAdmin.
   
   
4. Log into your phpMyAdmin account by entering your MySQL Username and Password then click Go.
   
   

Importing a MySQL Database

Importing is a very handy way to store existing data from another server into your server's own database. The steps below will show you how to import an existing MySQL database:

This tutorial assumes you already have an existing exported database_name.sql file. If you do not have one yet, scroll down to the section on Exporting a MySQL Database.

1. On your phpMyAdmin page, select the database you want to import data into by clicking on the database name from the left sidebar.
   
   
2. Once you've selected the database, click Import found on the top navigation bar. 
   
   
3. On the import page, click Choose File to browse and select the .sql file from your computer that you would like to import, make sure you have selected SQL under the Format section and then, click Go.
   
   
4. If importing is successful, you will be redirected to a page with a message indicating that import was successful along with all the actions executed. You will also see your data tables shown on the left sidebar under the database name. 
   
   

You're done! You can now use the imported data into your server. 

Exporting A MySQL Database

If you want to backup of your data or import it into another database, you may do so by exporting your MySQL database. 

1. On your phpMyAdmin page, select the database you want to export by clicking on the database name from the left sidebar.
   
   
2. Once you've selected the database, click Export found on the top navigation bar.
   
   
3. On the Export page, make sure your Export method is set to Quick and the Format is set to SQL and click Go. This will automatically download your database into your computer.

   If you are familiar with managing MySQL databases, or you only want to choose specific tables to be exported, you may select the Custom export method.
   

   

That's it! You've successfully exported your MySQL database. The exported SQL file is now ready to be imported into another database or simply be stored as a backup. 

Looking for more MySQL guides? Click here! 

If you require any further assistance, please contact our support at: https://shockbyte.com/billing/submitticket.php

Contents Previous Next

last modified July 10, 2020

In this part of the MySQL tutorial, we will be exporting data from MySQL database and importing data back.

Simple data export

In our first example, we will save data in a text file.

mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars';
Query OK, 8 rows affected (0.00 sec)

We select all rows (8) from the Cars table into the cars file located in the /tmp directory. We need to have permissions to write to that directory.

$ cat /tmp/cars
1       Audi    52642
2       Mercedes        57127
3       Skoda   9000
4       Volvo   29000
5       Bentley 350000
6       Citroen 21000
7       Hummer  41400
8       Volkswagen      21600

We show the contents of the file.

mysql> DELETE FROM Cars;

mysql> LOAD DATA INFILE '/tmp/cars' INTO TABLE Cars;

In the first statement we delete all rows from the table. In the second statement we load all data from the text file into the Cars table.

mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars.csv'
    -> FIELDS TERMINATED BY ',';

In the above SQL statement, we dump all data from the Cars table into a cars.csv file. The FIELDS TERMINATED BY clause controls, how the data will be terminated in the text file. We have chosen a comma character. The CSV stands for Comma Separated Values and it is a very common and very portable file format. It can be imported by numerous other applications like OpenOffice, other databases etc.

$ cat /tmp/cars.csv 
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600

This is the contents of the cars.csv file.

mysql> DELETE FROM Cars;

mysql> LOAD DATA INFILE '/tmp/cars.csv' INTO TABLE Cars
    -> FIELDS TERMINATED BY ',';

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

We delete all the data and restore it from the cars.csv file.

Exporting to XML files

It is possible to export and import XML data using the mysql monitor.

$ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Cars' > /tmp/cars.xml

The mysql monitor has an --xml option, which enables us to dump data in XML format. The -e option executes a statement and quits the monitor.

$ cat /tmp/cars.xml 
<?xml version="1.0"?>

<resultset statement="SELECT * FROM mydb.Cars
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="Id">1</field>
        <field name="Name">Audi</field>
        <field name="Cost">52642</field>
  </row>

  <row>
        <field name="Id">2</field>
        <field name="Name">Mercedes</field>
        <field name="Cost">57127</field>
  </row>

  <row>
        <field name="Id">3</field>
        <field name="Name">Skoda</field>
        <field name="Cost">9000</field>
  </row>

  <row>
        <field name="Id">4</field>
        <field name="Name">Volvo</field>
        <field name="Cost">29000</field>
  </row>

  <row>
        <field name="Id">5</field>
        <field name="Name">Bentley</field>
        <field name="Cost">350000</field>
  </row>

  <row>
        <field name="Id">6</field>
        <field name="Name">Citroen</field>
        <field name="Cost">21000</field>
  </row>

  <row>
        <field name="Id">7</field>
        <field name="Name">Hummer</field>
        <field name="Cost">41400</field>
  </row>

  <row>
        <field name="Id">8</field>
        <field name="Name">Volkswagen</field>
        <field name="Cost">21600</field>
  </row>
</resultset>

This is the XML file generated by the mysql monitor.

mysql> TRUNCATE Cars;

mysql> LOAD XML /tmp/cars.xml INTO TABLE Cars;

We truncate the Cars table. We load data from the XML file. Note that LOAD XML statement is available for MySQL 5.5 and newer.

Using mysqldump tool

The mysqldump is a command tool to create backups for MySQL. The word dump is used when we transfer data from one place to another. From a database file to a text file. From a memory to a file. And similar.

Dumping table structures

mysqldump -u root -p --no-data mydb > bkp1.sql

The above command dumps table structures of all tables in the mydb database to the bkq1.sql file. The --no-data option causes that the data is not saved, only the table structures.

--
-- Table structure for table `Cars`
--

DROP TABLE IF EXISTS `Cars`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Cars` (
  `Id` int(11) NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
  `Cost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Here we see a portion of the bkp1.sql file. This is the SQL for the creation of the Cars table.

Dumping data only

$ mysqldump -uroot -p --no-create-info mydb > bkp2.sql

This command dumps all data from all tables of the mydb databases. It omits the table structures. The omission of the table structures is caused by the --no-create-info option.

--
-- Dumping data for table `Cars`
--

LOCK TABLES `Cars` WRITE;
/*!40000 ALTER TABLE `Cars` DISABLE KEYS */;
INSERT INTO `Cars` VALUES (1,'Audi',52642),(2,'Mercedes',57127),(3,'Skoda',9000),
(4,'Volvo',29000),(5,'Bentley',350000),(6,'Citroen',21000),
(7,'Hummer',41400),(8,'Volkswagen',21600);
/*!40000 ALTER TABLE `Cars` ENABLE KEYS */;
UNLOCK TABLES;

Here we can see the data for the Cars table.

Dumping the whole database

$ mysqldump -uroot -p mydb > bkp3.sql

This command dumps all tables from the mydb database to the bkp3.sql file.

Restoring data

We show, how to restore the database from the backup SQL files.

mysql> DROP DATABASE mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb/', errno: 17)

mysql> SHOW TABLES;
Empty set (0.00 sec)

We drop the mydb database. An error is shown. The tables were dropped but not the database.

$ sudo ls /var/lib/mysql/mydb
cars  cars.txt
$ sudo rm /var/lib/mysql/mydb/cars
$ sudo rm /var/lib/mysql/mydb/cars.txt

The reason is that (in my case) while doing backups, some of the data were written in the mydb directory, in which MySQL stores the mydb database. These two alien files could not be removed, hence the above error. By removing the files the error is fixed.

mysql> DROP DATABASE mydb;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| testdb             |
| world              |
+--------------------+
4 rows in set (0.00 sec)

The mydb database was fully removed.

mysql> CREATE DATABASE mydb;

mysql> USE mydb;

mysql> source bkp3.sql

We create the mydb database. Change to the database. And use the source command to execute the bkp3.sql script. The database is recreated.

mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| AA             |
| Ages           |
| Animals        |
| Authors        |
| BB             |
| Books          |
| Books2         |
| Brands         |
| Cars           |
...

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

The data is verified.

In this part of the MySQL tutorial, we have shown several ways how we can export and import data in MySQL.

How do I export an entire MySQL database?

How do I transfer MySQL database to another computer?

What is the command to export MySQL database?

What is database import and export?