User:Aaron Schulz/Quick Vagrant DB Replication

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

Step 1
Set server-id and binlog in  (uncomment the lines).

Restart MySQL so the change takes affect with.

Step 2
Update  to account for mysql1 and mysqld1 dirs. E.g. add:

Then run: sudo service apparmor restart

Step 3
Make an executable file called :

Run  to become root and run: ./new_mysql_instance 1 Note that using  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  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  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 ): 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 ): UNLOCK TABLES; exit

In shell: mysql --defaults-file=/etc/mysql1/my.cnf -u root -p < mysqldump.sql On slave (via running ): RESET SLAVE; CHANGE MASTER TO	 MASTER_HOST='127.0.0.1', MASTER_USER='root', MASTER_PASSWORD='vagrant', MASTER_PORT=3306, MASTER_LOG_FILE='<>', MASTER_LOG_POS='<>', MASTER_CONNECT_RETRY=10;

Also on slave (assuming the above worked): START SLAVE; SHOW SLAVE STATUS\G exit

Make sure  says the Slave and IO threads are running.

Getting pt-heartbeat (optional)
Get pt-heartbeat via: sudo apt-get install percona-toolkit

Create the  database in   (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  if helpful:

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

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