| | |

From:

To:

Home > Documentation > SQLite to MySQL

SQLite to MySQL Tutorial

[SQLite to MySQL Converter] [About Migration] [Command line]

The process of database migration from SQLite to MySQL consists of the following steps:

1. Select Migration Scenario On this screen you need to select the conversion scenario. SQLite to MySQL converter provides two options to migrate SQLite database to MySQL: Move to MySQL server directly - use this approach to migrate SQLite database to the target MySQL server directly. It requires MySQL server launched and necessary privileges to write into database on the target server. See Connect to MySQL Server section for related information. Store into dump file - this option allows to export the data into a local script file instead of moving it to MySQL server. The file contains MySQL statements to create all tables and to fill them with the data. See Import MySQL script files article for related information.

2a. Connect to MySQL If "Migrate to MySQL server" option has been selected on the first wizard page, the converter displays this page to get MySQL connection settings. SQLite to MySQL migration tool can connect to MySQL using local or remote mode. Local mode is used to connect MySQL server launched on the same machine where the program is running. Remote mode allows to connect MySQL running on another computer that can be accessed via Internet or LAN. Local connection mode is the default setting. To use remote connection select "Remote" radio button and specify host and port values. Host can be either network name or IP address. Port value is necessary only if it differs from the default 3306. If you need to specify custom port for local connection, select "Remote" and type "localhost" or "127.0.0.1" into "Host" box.

2b. MySQL script file settings Through this wizard page SQLite to MySQL converter allows to specify how the resulting dump file should be formatted. There are following settings available: MySQL version - determines the syntax of SQL statements. Old versions of MySQL (before 3.23.6) apply more restrictions on syntax of queries. 'Old' syntax is accepted by 'new' versions of MySQL but not vice versa. Use 'old' syntax if not sure about the version of MySQL. Line breaks - specifies how to separate text lines in the dump file. 'MS Windows style' option means to use '\r\n', 'Unix style' means to use '\n' as end of line.
Add lock for write around 'INSERT' statements - makes import of the dump file into database much faster. To use this feature in MySQL 4.0 and higher it requires global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier you need to have SELECT, INSERT, DELETE and UPDATE privileges for the tables. Other options are self-explained.

3. Select Databases This wizard screen is designed to specify SQLite database and MySQL database or SQL-script file (depending on the conversion scenario). Existing MySQL database can be processed as follows: Overwrite the entire database Overwrite existing tables only Skip existing tables Merge Synchronize Specify log file to enable the program write execution traces. It may be used to analyze possible error messages and get more control over the conversion process. If no log file is specified, the logging is disabled.

4. Conversion Settings On this wizard screen you can customize the conversion process using the following settings: MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations. MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.
Convert table definitions only - select this checkbox to convert only table definitions of SQLite database. All the data will not be migrated. Skip converting indexes - select this checkbox if you don't want to convert indexes

5. Select Tables This wizard page is designed to select SQLite tables for migration to MySQL. To add new table select it in "Available tables" list and click "Add" button (or double-click the highlighted item). Use "Add all" button to add all available tables. To remove an item from "Selected tables" list highlight it and click "Remove" button. Use "Remove all" button to remove all items from "Selected tables" list box. To edit table or query double click the corresponding item in "Selected tables" list box. If it is a table, dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information. If selected item is a query, you will see dialog box to edit SQL-statement of the query.