User:Aaron Schulz/Quick Vagrant DB Replication
Quick and dirty guide for having DB replication in MediaWiki-Vagrant.
Setting up MySQL
editStep 1
editSet 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
editUpdate /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
editMake 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
editSet 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
editGet 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
editOn 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)
editGet 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
editThe 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
editMake 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= .