view page source - page history - about editing

Revision History: Setting up Master/Slave Replication from an existing MySQL server

This is revision 476 of the page Setting up Master/Slave Replication from an existing MySQL server, as it appeared on Wed, 16 Apr 2014 21:30:13 -0700.
MySQL

Currently https://cryptfolio.com is running off a single MySQL server. I want to switch this to a master/slave setup, which I intend will:
  1. increase performance (0.10% of queries are blocked by updates), but not too much since the database is very write-heavy (59% of queries)
  2. increase visible performance greatly (often graphs are blocked by pending writes especially to summary_instances and balances tables)
  3. improve reliability (if the slave goes down, I can point web servers to master; if the master goes down, I can enable read-only site mode)
  4. cost more money to run (since I'll be running two nodes rather than one) but still much cheaper (and more interesting) than going through S3
  5. databases suitable for memcachedb/keystores are not yet used that heavily
I'll be using the MySQL guide to set up replication and cover my steps here.

1. Setup master

16.1.1.1 Setting the Replication Master Configuration
  1. Edit my.cnf and set:
[mysqld]
log-bin=mysql-bin            # enable binary logging
server-id=1                  # this ID cannot be used by any other server
# Create a new slave user
CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
# Restart server

2. Setup slave

  1. Purchase new Linode.
  2. Set up MySQL, iptables and SSH appropriately.
  3. Tune mysql with mysqltuner and previous experience
  4. Edit my.cnf and set:
[mysqld]
server-id=99                 # this ID cannot be used by any other server
# Restart server

3. Locate master's position in binary log


Make sure you have an existing session open. Also have another command line ready to export the current master database snapshot with mysqldump.
  1. Open up a new MySQL session and execute FLUSH TABLES WITH READ LOCK; to lock all tables to prevent database inconsistency
  2. In the other existing MySQL session, execute SHOW MASTER STATUS; to display the current binary log position
  3. In another command line, execute mysqldump --all-databases --master-data > dbdump.db to create a master database snapshot
  4. Close the new MySQL session to release the database lock once the dump is complete

4. Import the master snapshot and initialise the slave

  1. Copy over the snapshot from master to slave: scp user@hostname:/path/to/dbdump.db ~/dbdump.db
  2. Import the database into slave: mysql -u user -p < dbdump.db
Categories: MySQL

view page source - what links to here? - page history - top
Last edited by jevon jevon 60 months ago