Migration of SQLite to MySQL

In my first Blog ever, I am going to cover the migration of a SQLite-Database to MySQL. The Tool used is MySQL-Workbench, which you can Download from the MySQL website. In this particular case, it is about the upgrade of mocenter 0.2 to 0.3.

In the Workbench on the right side, you have the button “Database Migration”. Once you click on it, the introduction to the migration wizard will show up.

Setting up source and target

On the bottom of the screen there is a button called “Start Migration”. Click it to get to the source selection.

On the first drop down menu choose “SQLite”. The menu will now change and give you the possibility to load a file. After you did this you can test the connection with the button on the bottom left, or go to the bottom right and click next.

You get a new menu, where you can choose your target of the database. You can use a stored connection or a new one. If you have no idea which connection type to use, TCP/IP usually works fine. Here I recommend you to test the connection, so you see if you can reach target. Then click next.

If the schema fetch does not return any error, you get to the schema selection.

There you have to select a target schema, before you can click next and go to fetch the source. Once you have done that too, click next once more.

Object selection

Now the database is ready to copy.

If you want to remove some of the tables from the migration, you can do this under “Show Selection”.

In case you see some warnings, you can ignore them.

Now click next and make sure there are no errors or failures, until you can select how to create the target database.

If you want to, you can create the SQL file to import the database structure somewhere else. I am just going to put it onto my server.

Clicking next after this step will create the database on the server. If you chose to create the SQL file, it would be created now as well.

Click next, when the creation is done, and you should get something, that looks like this picture. If you select a line, you will be shown the command that was executed.

This was the structure. Now to the data:

To transfer your data from the SQLite directly into the MySQL-Database, you can make an online copy. If you want to, or have to, do it from the command line you could also make a bash file.

If you click the option “Truncate target tables”, all the tables that already exist will be cleared of any data, so be careful using that checkbox.

Once complete, you should get report that looks somewhat like this.

------------------------------------------------------------------------------------
MySQL Workbench Migration Wizard Report

Date: Sun Oct 18 17:36:53 2015
Source: SQLite 1.0.0
Target: MySQL 5.6.24
------------------------------------------------------------------------------------

I. Migration

1. Summary

Number of migrated schemas: 1

1. mocenter
Source Schema:   mocenter

- Tables:             10
- Triggers:           0
- Views:              0
- Stored Procedures:  0
- Functions:          0


2. Migration Issues
  - versions
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - PRIMARY
    warning  Truncated key column length for column  from 0 to 255
  - nodes
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - name_ui
    warning  Truncated key column length for column  from 0 to 255
  - clusters
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - cluster_name
    warning  Truncated key column length for column  from 0 to 255
  - vips
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - ip_address
    warning  Truncated key column length for column  from 0 to 255
  - servers
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - server_name
    warning  Truncated key column length for column  from 0 to 255
  - users
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - login_name
    warning  Truncated key column length for column  from 0 to 255
  - moc_identifier
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - PRIMARY
    warning  Truncated key column length for column  from 0 to 255
  - checks
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.
  - PRIMARY
    warning  Truncated key column length for column  from 0 to 255
  - name_dc
    warning  Truncated key column length for column  from 0 to 255
  - jobs
    warning  Source table has a PRIMARY KEY allowing NULL values, which is not supported by MySQL. Column was changed to NOT NULL.


3. Object Creation Issues


4. Migration Details

4.1. Table mocenter.versions (versions)

Columns:
  - version LONGTEXT    
  - mr_version INT  NULL  
  - timestamp INT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (version(255))


4.2. Table mocenter.licenses (licenses)

Columns:
  - license LONGTEXT  NULL  

Foreign Keys:

Indices:


4.3. Table mocenter.nodes (nodes)

Columns:
  - node_id INT    
  - name LONGTEXT  NULL  
  - last_change_ts INT  NULL  
  - status LONGTEXT  NULL  
  - node_type LONGTEXT  NULL  
  - hostname LONGTEXT  NULL  
  - basedir LONGTEXT  NULL  
  - datadir LONGTEXT  NULL  
  - my_cnf LONGTEXT  NULL  
  - port INT  NULL  
  - database_user LONGTEXT  NULL  
  - database_user_password LONGTEXT  NULL  
  - error_log LONGTEXT  NULL  
  - pid_file LONGTEXT  NULL  
  - read_only INT  NULL  
  - server_id INT  NULL  
  - role_id INT  NULL  
  - cluster_id INT  NULL  
  - master_id INT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (node_id)
  - name_ui (name(255))


4.4. Table mocenter.clusters (clusters)

Columns:
  - cluster_id INT    
  - name LONGTEXT  NULL  
  - last_change_ts INT  NULL  
  - type INT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (cluster_id)
  - cluster_name (name(255))


4.5. Table mocenter.vips (vips)

Columns:
  - vip_id INT    
  - ip_address LONGTEXT  NULL  
  - name LONGTEXT  NULL  
  - ipaddr_type INT  NULL  
  - interface LONGTEXT  NULL  
  - alias INT  NULL  
  - primary_id INT  NULL  
  - failover_id INT  NULL  
  - location_id INT  NULL  
  - cluster_id INT  NULL  
  - last_change_ts INT  NULL  
  - fo_sync_only INT  NULL  
  - fo_wait_sync INT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (vip_id)
  - ip_address (ip_address(255))


4.6. Table mocenter.servers (servers)

Columns:
  - server_id INT    
  - name LONGTEXT  NULL  
  - default_ip LONGTEXT  NULL  
  - os_user LONGTEXT  NULL  
  - cluster_id INT  NULL  
  - last_change_ts INT  NULL  
  - myenv_basedir LONGTEXT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (server_id)
  - server_name (name(255))


4.7. Table mocenter.users (users)

Columns:
  - user_id INT    
  - login_name LONGTEXT  NULL  
  - password_hash LONGTEXT  NULL  
  - email_address LONGTEXT  NULL  
  - first_name LONGTEXT  NULL  
  - last_name LONGTEXT  NULL  
  - mobile LONGTEXT  NULL  
  - role_id INT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (user_id)
  - login_name (login_name(255))


4.8. Table mocenter.moc_identifier (moc_identifier)

Columns:
  - moc_identifier LONGTEXT    

Foreign Keys:

Indices:
  - PRIMARY (moc_identifier(255))


4.9. Table mocenter.checks (checks)

Columns:
  - unit_id INT    
  - type LONGTEXT  NULL  
  - name LONGTEXT    
  - last_check_ts INT  NULL  
  - last_check_status LONGTEXT  NULL  
  - last_successful_check_ts INT  NULL  
  - last_successful_check_status LONGTEXT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (unit_id, name(255))
  - name_dc (name(255))


4.10. Table mocenter.jobs (jobs)

Columns:
  - job_id INT    
  - name LONGTEXT  NULL  
  - server LONGTEXT  NULL  
  - pid INT  NULL  
  - start_ts INT  NULL  
  - status LONGTEXT  NULL  
  - check_interval INT  NULL  
  - last_check_ts INT  NULL  
  - end_ts INT  NULL  
  - error_code INT  NULL  
  - error_message LONGTEXT  NULL  
  - command LONGTEXT  NULL  

Foreign Keys:

Indices:
  - PRIMARY (job_id)


II. Data Copy

  - `mocenter`.`moc_identifier`
            Succeeded : copied 1 of 1 rows from "mocenter"."moc_identifier"    
  - `mocenter`.`clusters`
            Succeeded : copied 1 of 1 rows from "mocenter"."clusters"    
  - `mocenter`.`jobs`
            Succeeded : copied 0 of 0 rows from "mocenter"."jobs"    
  - `mocenter`.`users`
            Succeeded : copied 1 of 1 rows from "mocenter"."users"    
  - `mocenter`.`vips`
            Succeeded : copied 1 of 1 rows from "mocenter"."vips"    
  - `mocenter`.`versions`
            Succeeded : copied 2 of 2 rows from "mocenter"."versions"    
  - `mocenter`.`servers`
            Succeeded : copied 2 of 2 rows from "mocenter"."servers"    
  - `mocenter`.`checks`
            Succeeded : copied 46 of 46 rows from "mocenter"."checks"    
  - `mocenter`.`licenses`
            Succeeded : copied 1 of 1 rows from "mocenter"."licenses"    

Conclusion

Migrating from SQLite to MySQL is very easy when using MySQL Workbench.