View source for Converting mediawiki database from postgresql to mysql/mariadb

==Why to use mysql/mariadb instead of 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>
Is PostgreSQL a good choice for MediaWiki?<br>
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.</blockquote>

==Challenge==
====Why not to use dumpBackup.php script in mediawiki? [[https://www.mediawiki.org/wiki/Manual:DumpBackup.php]]====
If you need to save only mediawiki data and pages modify history then the best and easiest way probably would be:
{{Cmd|<nowiki>php maintenance/dumpBackup.php --full --include-files --uploads > /tmp/wiki.xml
php maintenance/importDump.php /tmp/wiki.xml
</nowiki>}}
However user accounts, user contrubutions, logs, etc will not be migrated this way.<br>

====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.<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).

==Mediawiki location==
In this example mediawiki files root is located in '''/var/www/wiki.wikiname.org/htdocs/w'''<br>
Web server is '''lighttpd'''. /etc/lighttpd/lighttpd.conf contains lines:
{{Cmd|<nowiki>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",
)
</nowiki>}}

==Disallowing editing mediawiki until database is converted==
{{Cmd|vi /var/www/wiki.wikiname.org/htdocs/w/LocalSettings.php}}
 <nowiki>$wgReadOnly = 'This wiki is currently being upgraded to a newer software version.';</nowiki>

==Preparing dump files for importing data to mysql==
====Making postgresql dump====
{{Cmd|<nowiki>su - postgres
	pg_dump wikidb > /tmp/wikidb-pg.dump
exit
</nowiki>}}

====Making temporary postgresql database looking like mysql====
{{Cmd|<nowiki>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
</nowiki>}}

====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<br>
'''/tmp/pg-prepare-for-mysql.sql'''
{{Cmd|<nowiki>/* 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;
</nowiki>}}

{{Cmd|<nowiki>su - postgres
	psql tmpdb < /tmp/pg-prepare-for-mysql.sql
exit
</nowiki>}}

====Making dump files for mysql import====
{{Cmd|<nowiki>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
</nowiki>}}

====Modifying dump files====
Postgresql specific sql should be removed
{{Cmd|<nowiki>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
</nowiki>}}

Postgresql table '''mwuser''' corresponds to mysql table '''user'''<br>
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/^INSERT INTO mwuser VALUES/INSERT INTO user VALUES/g" $f; done</nowiki>}}

Postgresql table '''pagecontent''' corresponds to mysql table '''text'''<br>
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/^INSERT INTO pagecontent VALUES/INSERT INTO text VALUES/g" $f; done</nowiki>}}

Back slashes should be replaced with double backslashes
{{Cmd|<nowiki>for f in *.sql; do sed -i 's/\\/\\\\/g' $f; done</nowiki>}}

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:<br>
<code>... Language::sprintfDate: The timestamp should be a number</code>
{{Cmd|<nowiki>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</nowiki>}}

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:<br>
<code># ERROR 1062 (23000) at line 152: Duplicate entry 'xxxxxx-0-0-0' for key 'ipb_address'</code>
{{Cmd|vi ipblocks.sql}}

==Changing mysql root password, creating user for mediawiki==
{{Cmd|<nowiki>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
</nowiki>}}

==Setting up new mediawiki==
{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs
wget http://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
</nowiki>}}

{{Cmd|vi index.php}}
 <nowiki>
<?php
  header('Location: /wiki');
?></nowiki>

Open your wiki in browser http://wiki.wikiname.org, 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>
Take needed settings from old LocalSettings.php<br>
Copy new LocalSettings.php into mediawiki directory /var/www/wiki.wikiname.org/htdocs/w<br>
Copy needed files from old mediawiki (images/*, extensions/..., resources/...)

{{Cmd|<nowiki>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
</nowiki>}}

==Clearing mysql tables before import==
{{Cmd|<nowiki>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
</nowiki>}}

==Importing data into mysql==
{{Cmd|<nowiki>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
</nowiki>}}

==Running maintenance scripts==
{{Cmd|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.
{{Cmd|<nowiki>echo "TRUNCATE l10n_cache;" | mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8
cd /var/www/wiki.wikiname.org/htdocs/w
php maintenance/rebuildLocalisationCache.php
</nowiki>}}

==See also==
* [[MariaDB]]

[[Category:SQL]]