Importing Into MySQL from Other Databases_MySQL

Your data may originate from many sources, from an application, atom feed, or even from another database. In the first two cases, you have application code and/or stored procedures processing the data; importing directly from another Database Management System (DBMS) can be accomplished using a variety of tools from command line utilities to professional grade DBMS management software.  The tool(s) that you employ will depend largely on the import source(s) and target(s). As we will see here today, the easiest data transfers involve databases of the same type (i.e. MySQL to MySQL) that reside on the same server.  Conversely, databases of different types are far more challenging to work with because different vendors each have their own proprietary tools and SQL extensions, making it unfeasible to achieve a seamless import process.  Thankfully, there are tools that can abstract each vendor’s particular language so that data may be transferred between databases without being an expert in any one of them.

Copying Data from One MySQL Database to Another

Whatever tools you use to manage your MySQL database(s), if you can execute an SQL statement, then you can copy data from one table into another so long as both databases reside on the same server.  For database migrations between servers, there are tools that have cropped up over the years to help with those.

SQL Statement

Under the best of circumstances, your databases both reside on the same network – perhaps even on the same server.  Assuming that you can connect to both at the same time, that would allow you to SELECT from one and INSERT into the other.  The syntax for that particular type of statement appends a SELECT clause to the INSERT:

insert into destination_database.destination_table (field1, field2, ...) select field1, field2, ... from source_database.source_table

Here’s a statement that copies all of the data from one MySQL database table to another:

INSERT INTO navicat_imports_db.applicants (first_name, id, last_name)SELECT first_name, id, last_name FROM test.applicants

mysqldump

Copying data one table at a time as we did above can become a tedious process. For transferring several tables at once, the mysqldump command line tool may be utilized to create .sql files that may then be read and executed on the destination database.

First, the following command is executed on the source database to dump its contents to a file:

mysqldump -u [username] -p [database_name] > [dumpfilename.sql]

A similar command is run on the target database to import the data:

mysql -u [username] -p [database_name] <p>SQL files are an ideal transfer vehicle because they can create database objects as well as populate data.</p><h3>phpMyAdmin</h3><p>For online databases, many web hosts offer phpMyAdmin to manage your MySQL databases. To migrate a database, select it in the left column list, click the Export link, and save the database to a file. Then on the new server, select the target DB in the left column, click the Import link, and choose the file you just exported.  It`s a lot like running mysqldump, but from a GUI.</p><h2>Importing Data from Other Databases</h2><p>For importing data from other (i.e. non-MySQL) databases, you can either find a utility that generates .sql or XML files that you can then import into your MySQL database or you can use a database management system that has the capacity to connect to multiple heterogeneous database servers at once. We’re going to look at two such products.</p><h3>MySQL Workbench</h3><p> MySQL`s free Workbench application supports migrations from Microsoft SQL Server, PostgreSQL, Sybase ASE, Sybase SQL Anywhere, SQLite, and others.  Much more than a migration utility, it also includes features like server health monitoring and SQL data modeling.  Just be aware that many users have complained about MySQL Workbench being slow and sluggish (and sometimes crashing) while merely typing queries.  The user interface is considered to be clunky and unintuitive as well.  But most importantly, Oracle has not been keen on fixing any outstanding bugs to make this tool better since their primary focus is on the income generating behemoth that is the Oracle flagship DBMS. </p><h3>Navicat Premium</h3><p>For the management of multiple databases in a commercial environment, Navicat Premium is a far more robust solution. It`s geared towards database administrators and developers of small to medium sized businesses.  Its best feature is that it allows you to connect to multiple databases simultaneously as well as migrate data between them in a seamless and consistent way.  It also supports other database objects including Stored Procedures, Events, Triggers, Functions, and Views.  Supported databases include MySQL, MariaDB, SQL Server, Oracle, PostgreSQL, and SQLite, among others.</p><p> The trial version of Navicat Premium for MySQL may be downloaded from the company’s website .  The 30-day trial version of the software is fully functional and identical to the full version so you can get the full impression of all its features.  Moreover, registering with PremiumSoft via the location 3 links gives you free email support during the 30-day trial. </p><p>After you’ve downloaded the installation program, launch it and follow the instructions on each screen of the wizard to complete the installation.</p><p>For the remainder of this article, I’m going to demonstrate how to use the Navicat Database Admin Tool to acquire data from an SQLite database. SQLite is well known for its zero-configuration, which means no complex setup is needed!</p><h2>Setting up SQLite</h2><ol start="1" type="1"> <li> <strong>Download the files</strong> <br> Go to the SQLite download page , and download the appropriate files for your operating system.  For Windows, you will need the sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files.  For Linux, download sqlite-autoconf-*.tar.gz from source code section.  For Max OS X, download sqlite-shell-osx-x86-3080500.zip and te-analyzer-osx-x86-3080500.zip precompiled binaries. </li> <li> <strong>Unpack to a directory</strong> </li> <ol start="1" type="a">
<li> In Windows, Create a folder such as <em>C:/>sqlite</em> and unzip the two zipped files in this folder, which will extract sqlite3.def, sqlite3.dll and sqlite3.exe files. Add the new folder to your PATH environment variable.  <em>Tip: save yourself some trouble by unpacking them to C:/WINDOWS/system32 or any other directory that is already in your PATH.</em>   </li>
<li>Enter the following commands on Linux:</li> </ol>
</ol><pre class="brush:php;toolbar:false">$tar xvfz sqlite-autoconf-3071502.tar.gz			$cd sqlite-autoconf-3071502			$./configure --prefix=/usr/local			$make			$make install

3. SQLite comes pre-installed on Mac OS Leopard or later, but you can update it if you need to, using Homebrew .

2. Start up the Database Server

   
   At the shell or DOS prompt, enter “sqlite3” followed by the database name of “applicants_copy.db”.  That will start up the database server, create the new schema, and set it as the working schema.  You should see the version information and sqlite> prompt:
   

   C:/sqlite>sqlite3	applicants_copy.db	SQLite version 3.8.5 2014-06-04 14:06:34	Enter ".help" for usage hints.	Enter SQL statements terminated with a ";"	sqlite>

Connecting To your Databases

To start working with your source and target databases, you must establish a connection to them using the connection manager. Let’s begin with MySQL.  In Navicat Premium:

1. Click the Connection button on the far top-left of the application window and select MySQL from the dropdown menu:

   Figure 1: Connection Menu

2. On the New Connection screen:

1. Give your connection a good descriptive name.
2. By default, the MySQL server listens for connections on port 3306.  Don’t change this unless you need to.
3. Supply credentials for an account that possesses table modification rights.
4. You can verify your information by clicking the Test Connection button. If it comes back with a “Connection successful!” message, you can either go to another tab to enter more specialized connection information or simply hit the OK button to save your info.

Figure 2: The New Connection Dialog

3. To use your credentials to establish a connection to your database, either select File > Open Connection or right-click on your connection in the list under the Connection button and select Open Connection from the popup menu:

Figure 3: Open Connection Command

That will give you access to all the databases running on that server.

4. Once again, access the Connection dropdown menu on the far top-left of the application window and select SQLite from the list.
5. That will open the New Connection dialog specific to SQLite.  The Connection Name field is the only one that it has in common with the MySQL connection properties. SQLite is file-based, so we can open an existing one or create a new version 2 or 3 database.  Since we did create a new database at the command prompt, there should be a file under the SQLite root directory.

Figure 4: SQLite - New Connection Dialog

6. Click OK to close the dialog and open the connection. You should now see the Applicants Copy database in the Connections list.
7. Connect to the Applicants Copy database. Tip: Besides the methods mentioned above in point 2, you can also open a database connection by double-clicking it in the Connections list.

Create the Applicant MySQL Table

Execute the following SQL statements in MySQL to create and populate a table called “applicants”.

DROP TABLE IF EXISTS `applicants`; CREATE TABLE `applicants` ( `id` int(11) NOT NULL, `last_name` varchar(100) NOT NULL, `first_name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `applicants` VALUES ('1', 'Gravelle', 'Rob'); INSERT INTO `applicants` VALUES ('2', 'Bundy', 'Al'); INSERT INTO `applicants` VALUES ('3', 'Richard', 'Little');

Import the Applicants Table from MySQL to SQLite

Follow these steps in Navicat Premium to migrate the applicants table from MySQL into the SQLite database:

1. Select Tools > Data Transfer... from the main menu to launch the Data Transfer wizard.
2. On the General tab of the Data Transfer dialog, begin by selecting the Connection and Database of the data source.  That will populate the Database Objects tree with all of the Tables, Views, Procedures, Functions, and Events that you can choose from.  But note that objects that do not exist in the database cannot be selected.  Tip: Selecting the Connection and Database in the Connections list before launching the Data Transfer wizard will automatically populate them in the dialog.
3. Check the box beside the applicants table to import it.
4. Select the Applicants Copy SQLite Connection as the Target for the import.  If you have not explicitly added databases to the connection using the SQLite ATTACH DATABASE statement, you will only have one database called “main”.

Figure 5: Data Transfer Dialog - General Tab

Saving Data to a Text File

Selecting the File radio button produces an SQL file for a given DBMS.  It’s not required but helpful to see what kind of SQL statements are being created behind the scenes.

Figure 6: Data Transfer Dialog with File Target

5. Set advanced options.

The Advanced tab contains additional options that pertain to the target database type selected.  For example, the following options would not appear for transfers to SQLite: Lock source tables, Lock target tables, Use extended insert statements, Use delayed insert statements, Run multiple insert statements, and Create target database/schema.

Here is the full list of possible attributes:

• Create tables
  Creates tables in the target database and/or schema if required.
• Include indexes
  Imports indexes on the table.
• Include foreign key constraints  
  Imports foreign keys on the table.
• Convert object name to
  Performs a transformation on object names by converting them to Lower case or Upper case during the import process.
• Insert records  
  Transfers all records to be to the destination database and/or schema.  Conversely, leaving this option unchecked will not add any data to the destination database and/or schema.
• Lock source tables
  Locks the tables in the source database and/or schema so that update on the table is not allowed once the data transfer has begun.
• Lock target tables
  Locks the tables in the target database and/or schema during the data transfer process so that no other rows may be appended.
• Use transaction
  Check this option to use transactions during the data transfer process.
• Rows may be added using either complete or extended insert statements.
  The Use complete insert statements

  option inserts records using complete insert syntax:

  Example:

  INSERT INTO `applicants` (`id`, `last_name`, `first_name`) VALUES ('1', 'Gravelle', 'Rob');INSERT INTO `applicants` (`id`, `last_name`, `first_name`) VALUES ('2', 'Bundy', 'Al');INSERT INTO `applicants` (`id`, `last_name`, `first_name`) VALUES ('3', 'Richard', 'Little');

• The Use extended insert statements 

  option uses the shorter extended insert syntax:

  Example:

INSERT INTO `users` VALUES ('1', 'Gravelle', 'Rob'), ('2', 'Bundy', 'Al'), ('3', 'Richard', 'Little');

• Use delayed insert statements

  Inserts records using  DELAYED  insert SQL statements such as:

INSERT DELAYED INTO `users` VALUES ('1', 'Gravelle', 'Rob');INSERT DELAYED INTO `users` VALUES ('2', 'Bundy', 'Al');INSERT DELAYED INTO `users` VALUES ('3', 'Richard', 'Little');

• Run multiple insert statements
  Makes the data transfer process faster by running multiple insert statements in each execution.
• Use hexadecimal format for BLOB 
  Inserts BLOB data in hexadecimal format.
• Continue on error
  Ignores errors that are encountered during the transfer process so that errors on one record won’t cause the entire import process to be aborted.
• Drop target objects before create
  If database objects already exist in the target database and/or schema, the existing objects will be deleted before the new one is created.
• Create target database/schema if not exist
  Creates a new database/schema if the database/schema specified in target server does not exist.

Figure 7: Data Transfer Dialog - Advanced Tab for SQLite

6. Click the Start button to perform the import. 

   Progress will be relayed on the Message Log tab.

Figure 8: Data Transfer Dialog - Message Log Tab

Saving Import Settings

You can save all of your import settings as a profile using the Save button at the bottom of the dialog on the left-hand side.  All you need to do is supply a profile name:

Figure 9: Save Data Transfer Profile Dialog

The profile name will then appear in the list on the Profiles tab so that next time you open the Data Transfer wizard you don’t have to re-enter them from scratch. Just select the saved profile and click on the Load button to repopulate all of your import settings. The Profiles tab also has a Delete button to remove profiles.

Figure 10: Data Transfer Dialog - Profiles Tab

7. Click the Close button to dismiss the Data Transfer dialog and return to the main application.
8. Refreshing the schema list using the F5 key should now show the new applicants table in the main database of the SQLite Applicants Copy schema.

Figure 11: Imported applicants Table in SQLite Database

Conclusion

There are a variety of ways to import data into your MySQL databases, from command line utilities to professional grade GUI applications like Navicat Premium.  Which you decide to go with ultimately depends on the size of your business. A home-made WordPress blog likely won’t be migrating data on the same scale as a mid-sized corporation.  That being said, a tool like Navicat Premium isn’t out of reach to smaller users because there are non-commercial licenses available for all three of the big supported operating systems. The Non-Commercial Edition for Windows is $299.00 USD compared with $699.00 USD for the commercial one.  For Mac (version 11) and Linux (version 11), the non-commercial license sells for $299.00 USD as well, while the commercial license goes for $599.00 USD.

See all articles by Rob Gravelle