Converting mediawiki database from postgresql to mysql/mariadb: Difference between revisions

From Alpine Linux
(use https)
 
(4 intermediate revisions by 3 users not shown)
Line 1: Line 1:
==Why to use mysql/mariadb instead of postgresql?==
==Why to use mysql/mariadb instead of postgresql?==


Here is what MediaWiki says [[https://www.mediawiki.org/wiki/Manual:PostgreSQL]]:
Here is what [[MediaWiki]] says [[https://www.mediawiki.org/wiki/Manual:PostgreSQL]]:
<blockquote>Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.<br>
<blockquote>Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.<br>
Is PostgreSQL a good choice for MediaWiki?<br>
Is PostgreSQL a good choice for MediaWiki?<br>
Line 17: Line 17:
Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.<br>
Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.<br>
<br>
<br>
There is a script mentioned in http://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).
There is a script mentioned in https://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).


==Mediawiki location==
==Mediawiki location==
Line 459: Line 459:
==Setting up new mediawiki==
==Setting up new mediawiki==
{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs
{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs
wget http://releases.wikimedia.org/mediawiki/1.25/mediawiki-1.25.3.tar.gz
wget https://releases.wikimedia.org/mediawiki/1.25/mediawiki-1.25.3.tar.gz
tar zxf mediawiki-1.25.3.tar.gz
tar zxf mediawiki-1.25.3.tar.gz
mv mediawiki-1.25.3 w
mv mediawiki-1.25.3 w
Line 471: Line 471:
?></nowiki>
?></nowiki>


Open your wiki in browser http://wiki.wikiname.org, go through setup process (DB type:mysql, engin: innodb, charset: binary)<br>
Open your wiki in browser <nowiki>https://wiki.wikiname.org</nowiki>, go through setup process (DB type:mysql, engin: innodb, charset: binary)<br>
Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file<br>
Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file<br>
Take needed settings from old LocalSettings.php<br>
Take needed settings from old LocalSettings.php<br>
Line 509: Line 509:
php maintenance/rebuildLocalisationCache.php
php maintenance/rebuildLocalisationCache.php
</nowiki>}}
</nowiki>}}
==See also==
* [[MariaDB]]
[[Category:SQL]]

Latest revision as of 21:24, 25 August 2023

Why to use mysql/mariadb instead of postgresql?

Here is what MediaWiki says [[1]]:

Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.

Is PostgreSQL a good choice for MediaWiki?

Wikipedia uses MySQL so MediaWiki gets more testing on MySQL than PostgreSQL. While support for PostgreSQL is maintained by volunteers, most core functionality is working.

Challenge

Why not to use dumpBackup.php script in mediawiki? [[2]]

If you need to save only mediawiki data and pages modify history then the best and easiest way probably would be:

php maintenance/dumpBackup.php --full --include-files --uploads > /tmp/wiki.xml php maintenance/importDump.php /tmp/wiki.xml

However user accounts, user contrubutions, logs, etc will not be migrated this way.

Why not to use tools for converting postgresql databases to mysql?

Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.

There is a script mentioned in https://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).

Mediawiki location

In this example mediawiki files root is located in /var/www/wiki.wikiname.org/htdocs/w
Web server is lighttpd. /etc/lighttpd/lighttpd.conf contains lines:

simple-vhost.server-root = "/var/www" simple-vhost.default-host = "wiki.wikiname.org" simple-vhost.document-root = "/htdocs/" url.rewrite-once = ( "^/wiki/([^?]*)(?:\?(.*))?" => "/w/index.php?title=$1&$2", "^/wiki" => "/w/index.php", )

Disallowing editing mediawiki until database is converted

vi /var/www/wiki.wikiname.org/htdocs/w/LocalSettings.php

$wgReadOnly = 'This wiki is currently being upgraded to a newer software version.';

Preparing dump files for importing data to mysql

Making postgresql dump

su - postgres pg_dump wikidb > /tmp/wikidb-pg.dump exit

Making temporary postgresql database looking like mysql

su - postgres echo "CREATE DATABASE tmpdb ENCODING 'SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;" | psql psql tmpdb < /tmp/wikidb-pg.dump exit

SQL script modifying postgresql database

For avoiding issues when importing data to mysql we need to modify mediawiki postgresql database so that it would look like mediawiki mysql database. Basically the script will make field order in pg tables like in mysql and will set non-NULL values to the fields that are not allowed to be NULL in mysql
/tmp/pg-prepare-for-mysql.sql

/* archive */ CREATE VIEW tmp_view AS select ar_id, ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text, ar_timestamp, ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, ar_deleted, ar_len, ar_page_id, ar_parent_id, ar_sha1, ar_content_model, ar_content_format FROM archive; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set ar_text = '' where ar_text is NULL; update tmp_table set ar_comment = '' where ar_comment is NULL; update tmp_table set ar_user = 0 where ar_user is NULL; update tmp_table set ar_flags = '' where ar_flags is NULL; ALTER TABLE archive RENAME TO orig_archive; ALTER TABLE tmp_table RENAME TO archive; /* category */ CREATE VIEW tmp_view AS select cat_id, cat_title, cat_pages, cat_subcats, cat_files FROM category; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE category RENAME TO orig_category; ALTER TABLE tmp_table RENAME TO category; /* categorylinks */ CREATE VIEW tmp_view AS select cl_from, cl_to, cl_sortkey, cl_sortkey_prefix, cl_timestamp, cl_collation, cl_type FROM categorylinks; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set cl_sortkey = '' where cl_sortkey is NULL; ALTER TABLE categorylinks RENAME TO orig_categorylinks; ALTER TABLE tmp_table RENAME TO categorylinks; /* change_tag (OK) */ /* externallinks */ CREATE VIEW tmp_view AS select el_id, el_from, el_to, el_index FROM externallinks; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE externallinks RENAME TO orig_externallinks; ALTER TABLE tmp_table RENAME TO externallinks; /* filearchive (OK) */ /* image */ CREATE VIEW tmp_view AS select img_name, img_size, img_width, img_height, img_metadata, img_bits, img_media_type, img_major_mime, img_minor_mime, img_description, img_user, img_user_text, img_timestamp, img_sha1 FROM image; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set img_bits = 0 where img_bits is NULL; update tmp_table set img_major_mime = '' where img_major_mime is NULL; update tmp_table set img_minor_mime = '' where img_minor_mime is NULL; ALTER TABLE image RENAME TO orig_image; ALTER TABLE tmp_table RENAME TO image; /* imagelinks */ CREATE VIEW tmp_view AS select il_from, il_from_namespace, il_to FROM imagelinks; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE imagelinks RENAME TO orig_imagelinks; ALTER TABLE tmp_table RENAME TO imagelinks; /* interwiki */ CREATE VIEW tmp_view AS select iw_prefix, iw_url, iw_api, iw_wikiid, iw_local, iw_trans FROM interwiki; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE interwiki RENAME TO orig_interwiki; ALTER TABLE tmp_table RENAME TO interwiki; /* ipblocks */ CREATE VIEW tmp_view AS select ipb_id, ipb_address, ipb_user, ipb_by, ipb_by_text, ipb_reason, ipb_timestamp, ipb_auto, ipb_anon_only, ipb_create_account, ipb_enable_autoblock, ipb_expiry, ipb_range_start, ipb_range_end, ipb_deleted, ipb_block_email, ipb_allow_usertalk, ipb_parent_block_id FROM ipblocks; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set ipb_address = '' where ipb_address is NULL; update tmp_table set ipb_user = 0 where ipb_user is NULL; update tmp_table set ipb_range_start = '' where ipb_range_start is NULL; update tmp_table set ipb_range_end = '' where ipb_range_end is NULL; ALTER TABLE ipblocks RENAME TO orig_ipblocks; ALTER TABLE tmp_table RENAME TO ipblocks; /* iwlinks (OK) */ /* job */ CREATE VIEW tmp_view AS select job_id, job_cmd, job_namespace, job_title, job_timestamp, job_params, job_random, job_attempts, job_token, job_token_timestamp, job_sha1 FROM job; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE job RENAME TO orig_job; ALTER TABLE tmp_table RENAME TO job; /* l10n_cache (OK) */ /* langlinks */ CREATE VIEW tmp_view AS select ll_from, ll_lang, ll_title FROM langlinks; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set ll_lang = '' where ll_lang is NULL; update tmp_table set ll_title = '' where ll_title is NULL; ALTER TABLE langlinks RENAME TO orig_langlinks; ALTER TABLE tmp_table RENAME TO langlinks; /* logging */ CREATE VIEW tmp_view AS select log_id, log_type, log_action, log_timestamp, log_user, log_user_text, log_namespace, log_title, log_page, log_comment, log_params, log_deleted FROM logging; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set log_user = 0 where log_user is NULL; update tmp_table set log_comment = '' where log_comment is NULL; update tmp_table set log_params = '' where log_params is NULL; ALTER TABLE logging RENAME TO orig_logging; ALTER TABLE tmp_table RENAME TO logging; /* log_search (OK) */ /* module_deps (OK) */ /* msg_resource (OK) */ /* msg_resource_links (OK) */ /* objectcache */ CREATE VIEW tmp_view AS select keyname, value, exptime FROM objectcache; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set keyname = '' where keyname is NULL; ALTER TABLE objectcache RENAME TO orig_objectcache; ALTER TABLE tmp_table RENAME TO objectcache; /* oldimage */ CREATE VIEW tmp_view AS select oi_name, oi_archive_name, oi_size, oi_width, oi_height, oi_bits, oi_description, oi_user, oi_user_text, oi_timestamp, oi_metadata, oi_media_type, oi_major_mime, oi_minor_mime, oi_deleted, oi_sha1 FROM oldimage; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set oi_bits = 0 where oi_bits is NULL; update tmp_table set oi_description = '' where oi_description is NULL; update tmp_table set oi_user = 0 where oi_user is NULL; update tmp_table set oi_major_mime = '' where oi_major_mime is NULL; update tmp_table set oi_minor_mime = '' where oi_minor_mime is NULL; ALTER TABLE oldimage RENAME TO orig_oldimage; ALTER TABLE tmp_table RENAME TO oldimage; /* page */ CREATE VIEW tmp_view AS select page_id, page_namespace, page_title, page_restrictions, page_is_redirect, page_is_new, page_random, page_touched, page_links_updated, page_latest, page_len, page_content_model, page_lang FROM page; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set page_restrictions = '' where page_restrictions is NULL; /*update tmp_table set page_touched = '' where page_touched is NULL;*/ ALTER TABLE page RENAME TO orig_page; ALTER TABLE tmp_table RENAME TO page; /* pagelinks */ CREATE VIEW tmp_view AS select pl_from, pl_from_namespace, pl_namespace, pl_title FROM pagelinks; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE pagelinks RENAME TO orig_pagelinks; ALTER TABLE tmp_table RENAME TO pagelinks; /* page_props (OK) */ /* page_restrictions */ CREATE VIEW tmp_view AS select pr_id, pr_page, pr_type, pr_level, pr_cascade, pr_user, pr_expiry FROM page_restrictions; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE page_restrictions RENAME TO orig_page_restrictions; ALTER TABLE tmp_table RENAME TO page_restrictions; /* protected_titles */ CREATE VIEW tmp_view AS select pt_namespace, pt_title, pt_user, pt_reason, pt_timestamp, pt_expiry, pt_create_perm FROM protected_titles; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set pt_user = 0 where pt_user is NULL; /*update tmp_table set pt_expiry = '' where pt_expiry is NULL;*/ ALTER TABLE protected_titles RENAME TO orig_protected_titles; ALTER TABLE tmp_table RENAME TO protected_titles; /* querycache (OK) */ /* querycachetwo (OK) */ /* querycache_info */ CREATE VIEW tmp_view AS select qci_type, qci_timestamp FROM querycache_info; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set qci_type = '' where qci_type is NULL; /* update tmp_table set qci_timestamp = '' where qci_timestamp is NULL; */ ALTER TABLE querycache_info RENAME TO orig_querycache_info; ALTER TABLE tmp_table RENAME TO querycache_info; /* recentchanges (rc_cur_time was removed in 1.24wmf6) */ CREATE VIEW tmp_view AS select rc_id, rc_timestamp, rc_user, rc_user_text, rc_namespace, rc_title, rc_comment, rc_minor, rc_bot, rc_new, rc_cur_id, rc_this_oldid, rc_last_oldid, rc_type, rc_source, rc_patrolled, rc_ip, rc_old_len, rc_new_len, rc_deleted, rc_logid, rc_log_type, rc_log_action, rc_params FROM recentchanges; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set rc_user = 0 where rc_user is NULL; update tmp_table set rc_comment = '' where rc_comment is NULL; update tmp_table set rc_cur_id = 0 where rc_cur_id is NULL; /* update tmp_table set rc_ip = '' where rc_ip is NULL; */ ALTER TABLE recentchanges RENAME TO orig_recentchanges; ALTER TABLE tmp_table RENAME TO recentchanges; /* redirect (OK) */ /* revision */ CREATE VIEW tmp_view AS select rev_id, rev_page, rev_text_id, rev_comment, rev_user, rev_user_text, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, rev_content_model, rev_content_format FROM revision; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set rev_page = 0 where rev_page is NULL; update tmp_table set rev_text_id = 0 where rev_text_id is NULL; update tmp_table set rev_comment = '' where rev_comment is NULL; ALTER TABLE revision RENAME TO orig_revision; ALTER TABLE tmp_table RENAME TO revision; /* searchindex (does not exists in pg) */ /* sites (OK) */ /* site_identifiers (OK) */ /* site_stats */ CREATE VIEW tmp_view AS select ss_row_id, ss_total_edits, ss_good_articles, ss_total_pages, ss_users, ss_active_users, ss_images FROM site_stats; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE site_stats RENAME TO orig_site_stats; ALTER TABLE tmp_table RENAME TO site_stats; /* tag_summary (OK) */ /* templatelinks */ CREATE VIEW tmp_view AS select tl_from, tl_from_namespace, tl_namespace, tl_title FROM templatelinks; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; ALTER TABLE templatelinks RENAME TO orig_templatelinks; ALTER TABLE tmp_table RENAME TO templatelinks; CREATE VIEW tmp_view AS select old_id, old_text, old_flags FROM pagecontent; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set old_text = '' where old_text is NULL; update tmp_table set old_flags = '' where old_flags is NULL; ALTER TABLE pagecontent RENAME TO orig_pagecontent; ALTER TABLE tmp_table RENAME TO pagecontent; /* transcache (OK) */ /* updatelog (OK) */ /* uploadstash */ CREATE VIEW tmp_view AS select us_id, us_user, us_key, us_orig_path, us_path, us_source_type, us_timestamp, us_status, us_chunk_inx, us_props, us_size, us_sha1, us_mime, us_media_type, us_image_width, us_image_height, us_image_bits FROM uploadstash; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set us_user = 0 where us_user is NULL; update tmp_table set us_key = '' where us_key is NULL; update tmp_table set us_orig_path = '' where us_orig_path is NULL; update tmp_table set us_path = '' where us_path is NULL; /* update tmp_table set us_timestamp = '' where us_timestamp is NULL; */ update tmp_table set us_status = '' where us_status is NULL; update tmp_table set us_size = 0 where us_size is NULL; update tmp_table set us_sha1 = '' where us_sha1 is NULL; ALTER TABLE uploadstash RENAME TO orig_uploadstash; ALTER TABLE tmp_table RENAME TO uploadstash; /* user (in pg it is named mwuser)*/ CREATE VIEW tmp_view AS select user_id, user_name, user_real_name, user_password, user_newpassword, user_newpass_time, user_email, user_touched, user_token, user_email_authenticated, user_email_token, user_email_token_expires, user_registration, user_editcount, user_password_expires FROM mwuser; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set user_real_name = '' where user_real_name is NULL; update tmp_table set user_password = '' where user_password is NULL; update tmp_table set user_newpassword = '' where user_newpassword is NULL; update tmp_table set user_token = '' where user_token is NULL; update tmp_table set user_email = '' where user_email is NULL; /* update tmp_table set user_touched = '' where user_touched is NULL; */ delete from tmp_table where user_id = 0; ALTER TABLE mwuser RENAME TO orig_mwuser; ALTER TABLE tmp_table RENAME TO mwuser; /* it cannot be named 'user', like in mysql */ /* user_former_groups */ CREATE VIEW tmp_view AS select ufg_user, ufg_group FROM user_former_groups; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set ufg_user = 0 where ufg_user is NULL; ALTER TABLE user_former_groups RENAME TO orig_user_former_groups; ALTER TABLE tmp_table RENAME TO user_former_groups; /* user_groups */ CREATE VIEW tmp_view AS select ug_user, ug_group FROM user_groups; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set ug_user = 0 where ug_user is NULL; ALTER TABLE user_groups RENAME TO orig_user_groups; ALTER TABLE tmp_table RENAME TO user_groups; /* user_newtalk */ CREATE VIEW tmp_view AS select user_id, user_ip, user_last_timestamp FROM user_newtalk; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set user_ip = '' where user_ip is NULL; ALTER TABLE user_newtalk RENAME TO orig_user_newtalk; ALTER TABLE tmp_table RENAME TO user_newtalk; /* user_properties */ CREATE VIEW tmp_view AS select up_user, up_property, up_value FROM user_properties; SELECT * INTO tmp_table FROM tmp_view; DROP VIEW tmp_view; update tmp_table set up_user = 0 where up_user is NULL; ALTER TABLE user_properties RENAME TO orig_user_properties; ALTER TABLE tmp_table RENAME TO user_properties; /* valid_tag (OK) */ /* watchlist (OK) */ /* external_user (not needed) */ ALTER TABLE external_user RENAME TO orig_external_user; /* hitcounter (removed in MediaWiki 1.25) */ ALTER TABLE hitcounter RENAME TO orig_hitcounter; /* mediawiki_version ('Nobody actually uses it anymore, it just gets silently updated (and only for Postgres at that)') */ ALTER TABLE mediawiki_version RENAME TO orig_mediawiki_version; /* profiling ('It's only used in an obscure debugging mode.') */ ALTER TABLE profiling RENAME TO orig_profiling; /* trackbacks (This feature was removed completely in version MediaWiki 1.19.) */ ALTER TABLE trackbacks RENAME TO orig_trackbacks;

su - postgres psql tmpdb < /tmp/pg-prepare-for-mysql.sql exit

Making dump files for mysql import

su - postgres mkdir /tmp/wikidb-pg-dump psql -d wikidb -c "SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');" > /tmp/t cat /tmp/t | grep mediawiki | sed 's/ mediawiki.//g' | sort > /tmp/pg-tables.txt for t in `cat /tmp/pg-tables.txt`; do echo "dumping $t"; pg_dump -d tmpdb --data-only --inserts -t mediawiki.$t > /tmp/wikidb-pg-dump/$t.sql; done exit

Modifying dump files

Postgresql specific sql should be removed

cd /tmp/wikidb-pg-dump for f in *.sql; do sed -i 's/^SET statement_timeout = 0;$//g' $f; done for f in *.sql; do sed -i 's/^SET lock_timeout = 0;$//g' $f; done for f in *.sql; do sed -i "s/^SET client_encoding = 'SQL_ASCII';$//g" $f; done for f in *.sql; do sed -i 's/^SET standard_conforming_strings = on;$//g' $f; done for f in *.sql; do sed -i 's/^SET check_function_bodies = false;$//g' $f; done for f in *.sql; do sed -i 's/^SET client_min_messages = warning;$//g' $f; done for f in *.sql; do sed -i 's/^SET search_path = mediawiki, pg_catalog;$//g' $f; done for f in *.sql; do sed -i "s/^SELECT pg_catalog.setval('filearchive_fa_id_seq', 25, true);$//g" $f; done

Postgresql table mwuser corresponds to mysql table user

for f in *.sql; do sed -i "s/^INSERT INTO mwuser VALUES/INSERT INTO user VALUES/g" $f; done

Postgresql table pagecontent corresponds to mysql table text

for f in *.sql; do sed -i "s/^INSERT INTO pagecontent VALUES/INSERT INTO text VALUES/g" $f; done

Back slashes should be replaced with double backslashes

for f in *.sql; do sed -i 's/\\/\\\\/g' $f; done

Timestamp format in postgresql is different from mysql (pg: '2015-10-31 10:10:10+00', mysql: '20151031101010'). All timestamps should be converted to avoid errors like:
... Language::sprintfDate: The timestamp should be a number

for f in *.sql; do sed -i "s/'\([0-9][0-9][0-9][0-9]\)-\([0-9][0-9]\)-\([0-9][0-9]\) \([0-9][0-9]\):\([0-9][0-9]\):\([0-9][0-9]\)[+-][0-9][0-9]'/'\1\2\3\4\5\6'/g" $f; done

In postgresql ipblocks table may contain records with duplicated field ipb_address. mysql requires that ipb_address field is unique. Check manually ipblocks.sql file. Delete records with duplicated field ipb_address to prevent errors like:
# ERROR 1062 (23000) at line 152: Duplicate entry 'xxxxxx-0-0-0' for key 'ipb_address'

vi ipblocks.sql

Changing mysql root password, creating user for mediawiki

mysql -u root SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword'); SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpassword'); SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpassword'); FLUSH PRIVILEGES; CREATE USER 'wikiuser'@'localhost' IDENTIFIED BY 'wikiuserpassword'; GRANT ALL PRIVILEGES ON *.* TO 'wikiuser'@'localhost' WITH GRANT OPTION; CREATE USER 'wikiuser'@'%' IDENTIFIED BY 'wikiuserpassword'; GRANT ALL PRIVILEGES ON *.* TO 'wikiuser'@'%' WITH GRANT OPTION; exit

Setting up new mediawiki

cd /var/www/wiki.wikiname.org/htdocs wget https://releases.wikimedia.org/mediawiki/1.25/mediawiki-1.25.3.tar.gz tar zxf mediawiki-1.25.3.tar.gz mv mediawiki-1.25.3 w rm mediawiki-1.25.3.tar.gz

vi index.php

<?php
  header('Location: /wiki');
?>

Open your wiki in browser https://wiki.wikiname.org, go through setup process (DB type:mysql, engin: innodb, charset: binary)
Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file
Take needed settings from old LocalSettings.php
Copy new LocalSettings.php into mediawiki directory /var/www/wiki.wikiname.org/htdocs/w
Copy needed files from old mediawiki (images/*, extensions/..., resources/...)

cd /var/www/wiki.wikiname.org/htdocs/w find . -type d > /tmp/d while read d; do chmod 755 "$d"; done < /tmp/d find . -type f > /tmp/f while read f; do chmod 644 "$f"; done < /tmp/f chown -R lighttpd:lighttpd . cd /var/www/wiki.wikiname.org/htdocs/w php maintenance/update.php # it will activate extensions and create needed tables in mysql db

Clearing mysql tables before import

mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 -Bse 'show tables;' > /tmp/t for t in `cat /tmp/t`; do echo "TRUNCATE $t;"; done >/tmp/mysql-clear-tables.sql mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 < /tmp/mysql-clear-tables.sql

Importing data into mysql

for t in `cat /tmp/pg-tables.txt`; do echo "importing $t"; mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 < /path/to/wikidb-pg-dump/$t.sql; done

Running maintenance scripts

php maintenance/update.php php maintenance/rebuildrecentchanges.php php maintenance/rebuildtextindex.php

Regenerating cache

Old cache in l10n_cache table may couse problems. It can be deleted and regenerated again.

echo "TRUNCATE l10n_cache;" | mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 cd /var/www/wiki.wikiname.org/htdocs/w php maintenance/rebuildLocalisationCache.php

See also