Practical tutorial for importing data from sqlite3 to mysql, sqlite3mysql

Practical tutorial for importing data from sqlite3 to mysql, sqlite3mysql

Preface

Sqlite3 is small and lightweight, but does not support concurrent access. When the website concurrency is large, the database Request queue is long, which may cause the database operation to time out at the end of the queue and thus the operation fails. Therefore, you must switch to a database that supports concurrent access. To switch the database, you need to export the old data and then import it to the new database. However, the sqlite3 and mysql databases are not fully compatible. You need to make some adjustments before importing them to mysql. I encountered this problem in my recent work.

In a recent project, magenetico is used to capture magnetic links. Because sqlite3 is used, files become larger and larger, and distributed files are not supported. Therefore, you need to convert it into MySQL, before migration, You need to import 15 GB of data that has been crawled to MySQL. Let's take a look at the detailed introduction.

The method is as follows:

Dumping SQL statements from the sqlite3 File

sqlite3 database.sqlite3sqlite3> .output /path/to/dump.sqlsqlite3> .dumpsqlite3> .exit

Split files

When the file is large, it fails to be imported into half of the data. In this case, you need to split a new SQL file from the failed line.

awk '{if (NR>=6240863) print $0>"dump_part.sql"}' dump.sql

Mysql Parameter Modification

[mysqld]max_allowed_packet = 100M

SQL compatibility and symbol replacement

#1. Delete rows that do not contain insert into #2. Replace table name wrap #3. Replace hexsed '/insert /! D; s/"table1"/'table1'/; s/"table2"/'table2'/; s/, X/,/'dump. SQL

Import to MySQL

# Add the force parameter to prevent some problematic SQL statements from importing mysql-uroot-p-f magnet <dump. SQL

Reference

How To Use The SQLite Dump Command

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.