Cédric Bruderer: Migration of SQLite to MySQL

来源:互联网 时间:1970-01-01

In my first Blog ever, I am going to cover the migration of a SQLite-Database to MySQL. The Tool used isMySQL-Workbench, which you can Download from theMySQL website. In this particular case, it is about the upgrade ofmocenter 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 ReportDate: Sun Oct 18 17:36:53 2015Source: SQLite 1.0.0Target: MySQL 5.6.24------------------------------------------------------------------------------------I. Migration1. SummaryNumber of migrated schemas: 11. mocenterSource Schema: mocenter- Tables: 10- Triggers: 0- Views: 0- Stored Procedures: 0- Functions: 02. 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 Issues4. Migration Details4.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.


相关阅读:
Top