Importing entire Wikipedia into MySQL
Wikipedia is the greatest encyclopedia which has ever existed, because everyone can contribute to the massive knowledge corpus. Analyzing this data with computers is becoming more and more indispensable, as nobody can survey the information by hand anymore. In order to work with the data, we have to import it into MySQL and here is how it works.
I'll show how to do this on Debian Jessie, but it should be easily adaptable to other distributions.
Installing Percona Server
If you already have a proper MySQL set-up, you can skip this section. I use the Percona Server - a MySQL Fork with performance in mind - almost everywhere as it has nice features built in, like Handlersocket or TokuDB and a well tuned InnoDB engine. But, let's get started.
Install Percona release information
wget https://repo.percona.com/apt/percona-release_0.1-3.jessie_all.deb dpkg -i percona-release_0.1-3.jessie_all.deb
Install MySQL server
apt-get update apt-get install percona-server-server-5.7
Add the following lines into the
[mysqld] section of
innodb_file_per_table collation_server = utf8_general_ci character_set_server = utf8 skip-character-set-client-handshake
Download the german Wikipedia corpus:
or similarly the english:
MWDumper, which we use to translate the XML files to actual SQL statements:
Log in into MySQL and create a new database:
CREATE DATABASE wiki;
Now download and install the table schema from the official mediawiki table:
wget <raw_link> -O create-mediawiki.sql mysql wiki < create-mediawiki.sql
And finally import the actual Wikipedia data:
bunzip2 -c enwiki-latest-pages-articles-multistream.xml.bz2 | \ java -jar mwdumper.jar --format=sql:1.25 | mysql wiki
Finally you can remove enwiki-latest-pages-articles-multistream.xml.bz2 to clean up:
If you see an error like
ERROR 1054 (42S22) at line 84: Unknown column 'page_counter' in 'field list' or similar, you should double check the
--format=... parameter, if it is still the most current.
You might also be interested in the following
- SQL-modes right to exist
- Use MySQL binlog to collect accesslogs
- Is it possible to avoid query parsing inside of MySQL?
- Disable ON UPDATE CURRENT_TIMESTAMP in MySQL
Sorry, comments are closed for this article. Contact me if you want to leave a note.