User:Aaron Schulz/Quick Vagrant DB Replication

Quick and dirty guide for having DB replication in MediaWiki-Vagrant.

Setting up MySQL

Step 1

Set server-id and binlog in /etc/mysql/my.cnf (uncomment the lines).

Restart MySQL so the change takes affect with sudo service mysql restart.

Step 2

Update /etc/apparmor.d/usr.sbin.mysqld to account for mysql1 and mysqld1 dirs. E.g. add:

  /etc/mysql1/*.pem r,
  /etc/mysql1/conf.d/ r,
  /etc/mysql1/conf.d/* r,
  /etc/mysql1/*.cnf r,
  /usr/lib/mysql1/plugin/ r,
  /usr/lib/mysql1/plugin/*.so* mr,
  /usr/share/mysql1/** r,
  /var/lib/mysql1/ r,
  /var/lib/mysql1/** rwk,
  /var/log/mysql1/ r,
  /var/log/mysql1/* rw,
  /var/run/mysqld1/mysqld.pid rw,
  /var/run/mysqld1/mysqld.sock w,
  /run/mysqld1/mysqld.pid rw,
  /run/mysqld1/mysqld.sock w,

Then run:

sudo service apparmor restart

Step 3

Make an executable file called new_mysql_instance:

#!/bin/bash
i=$1
port=$((3306+i))

if [ -z "$i" ]; then
	echo "Missing instance argument."
	exit 1
fi

echo "Creating directories..."
if [ ! -d /var/lib/mysql$i ]; then
	mkdir /var/lib/mysql$i
fi
chown -R mysql.mysql /var/lib/mysql$i/
if [ ! -d /var/log/mysql$i ]; then
	mkdir /var/log/mysql$i
fi
chown -R mysql.mysql /var/log/mysql$i

echo "Copying config..."
if [ ! -d /etc/mysql$i ]; then
	cp -R /etc/mysql/ /etc/mysql$i
	cd /etc/mysql$i/
	sed -i "s/3306/$port/g" my.cnf
	sed -i "s/var\/lib\/mysql/var\/lib\/mysql$i/g" my.cnf
	sed -i "s/var\/log\/mysql/var\/log\/mysql$i/g" my.cnf
	sed -i "s/\/mysql\//\/mysql$i\//g" my.cnf
	sed -i "s/\/mysqld\//\/mysqld$i\//g" my.cnf
	cd -
fi

if [ ! -f /etc/mysql$i/my.cnf ]; then
	echo "Failed to make /etc/mysql$i/my.cnf; aborting."
	exit 1
fi

echo "Installing mysql..."
mysql_install_db --user=mysql --datadir=/var/lib/mysql$i/
if [ $? -ne 0 ]; then
	echo "Aborted due to error. Make sure AppArmor profiles are updated."
	exit 1
fi
mysqld_safe --defaults-file=/etc/mysql$i/my.cnf &

echo "Done! Please update server-id and log_bin in my$i.cnf"

Run su to become root and run:

./new_mysql_instance 1

Note that using sudo confuses the script. Make sure it works by via:

mysql --defaults-file=/etc/mysql1/my.cnf -u root

Step 4

Set server-id and binlog in /etc/mysql1/my.cnf for the slave. The server id must be different than the master. Restart the slave via:

mysqladmin --socket=/var/run/mysqld1/mysqld.sock -u root -p shutdown
sudo mysqld_safe --defaults-file=/etc/mysql1/my.cnf

This assures that the server-id change applies. You can confirm that via:

mysql --defaults-file=/etc/mysql1/my.cnf -u root
SELECT @@server_id
exit

Step 5

Get the master DB grants via:

mysql
SHOW GRANTS
exit

Connect to the slave via:

mysql --defaults-file=/etc/mysql1/my.cnf -u root

Then paste the master grants above into the slave so they match. Note that the output of SHOW GRANTS is already formatted as a query. You want the 'vagrant' password to work for localhost. Test that via:

mysql --defaults-file=/etc/mysql1/my.cnf -u root -p

Step 6

On master (via running mysql):

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
cntrl-z

In shell:

mysqldump -uroot -p --all-databases > mysqldump.sql
fg

On master (via fg):

UNLOCK TABLES;
exit

In shell:

mysql --defaults-file=/etc/mysql1/my.cnf -u root -p < mysqldump.sql

On slave (via running mysql --defaults-file=/etc/mysql1/my.cnf -u root -p):

RESET SLAVE;
CHANGE MASTER TO
	  MASTER_HOST='127.0.0.1',
	  MASTER_USER='root',
	  MASTER_PASSWORD='vagrant',
	  MASTER_PORT=3306,
	  MASTER_LOG_FILE='<<VALUE FROM SHOW MASTER STATUS ABOVE>>',
	  MASTER_LOG_POS='<<VALUE FROM SHOW MASTER STATUS ABOVE>>',
	  MASTER_CONNECT_RETRY=10;

Also on slave (assuming the above worked):

START SLAVE;
SHOW SLAVE STATUS\G
exit

Make sure SHOW SLAVE STATUS says the Slave and IO threads are running.

Getting pt-heartbeat (optional)

Get pt-heartbeat via:

sudo apt-get install percona-toolkit

Create the heartbeat database in mysql (the master) via:

CREATE DATABASE heartbeat;

Daemonizing and convenience aliases

The mysql slave and pt-heartbeat can be be daemonized to avoid manual command usage. If using manual commands, some bash aliases can be added to ~/.bash_aliases if helpful:

alias start-mysql-slave='sudo mysqld_safe --defaults-file=/etc/mysql1/my.cnf & pt-heartbeat --create-table --database heartbeat --update &'
alias start-ptheartbeat='pt-heartbeat --create-table --database heartbeat --update &'
alias mysql-master='mysql --defaults-file=/etc/mysql/my.cnf -u root -p'
alias mysql-slave='mysql --defaults-file=/etc/mysql1/my.cnf -u root -p'

Changing MediaWiki config

Make MediaWiki use the slave by setting $wgDBServers at the bottom of LocalSettings.php. Example:

if ( !defined( 'MW_PHPUNIT_TEST' ) ) {
	$wgDBservers = array(
		array( // master
			'host'		=> '127.0.0.1:3306',
			'dbname'    => $wgDBname,
			'user'		=> $wgDBuser,
			'password'	=> $wgDBpassword,
			'type'		=> $wgDBtype,
			'load'      => 0,
			'flags'     => DBO_DEFAULT | DBO_DEBUG,
			// 'lagDetectionMethod' => 'pt-heartbeat'
		),
		array( // slave
			'host'		=> '127.0.0.1:3307',
			'dbname'    => $wgDBname,
			'user'		=> $wgDBuser,
			'password'	=> $wgDBpassword,
			'type'		=> $wgDBtype,
			'load'      => 100,
			'flags'     => DBO_DEFAULT | DBO_DEBUG,
			// 'lagDetectionMethod' => 'pt-heartbeat'
		)
	);
}

$wgShowHostnames = 1;

Test setup and lag figures by visiting http://127.0.0.1:8080/w/api.php?action=query&meta=siteinfo&siprop=dbrepllag&sishowalldb= .

Useful links

• http://www.ducea.com/2009/01/19/running-multiple-instances-of-mysql-on-the-same-machine/
• http://stackoverflow.com/questions/2366018