Notes on mysql replication

Assumptions:

We'll be following the pattern described in the mysql replication configuration listed below for databases that already have data in them, and we're adding slaves (not starting from empty master/slaves) as that's our environment currently.

Resources:

1. TOC for mysql replication notes
2. mysql config stuff for binary logging and server id setup
3. Setting up replication with existing data
4. howtoforge
   

Steps:

1. Take a snapshot of the db w/ mysqldump
2. Obtain master status:
3. Edit /etc/my.cnf for the MASTER db and add the following lines (with some specific and unique id for the server:
   1. log-bin=mysql-bin
   2. binlog-do-db=<database name>
   3. binlog-ignore-db=mysql
   4. binlog-ignore-db=test
   5. server-id=1
   6. innodb_flush_log_at_trx_commit=1
   7. sync_binlog=1
4. create account on MASTER that slave will use to read (where mydomain is where slave is running and password is 'slave', change to fit where appropriate)
   
   1. GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%.mydomain.com' IDENTIFIED BY 'slave';
5. Restart the MASTER database
6. Setup the slave config should probably set up binary logging on slave as well
   1. Add to the slave /etc/my.cnf:
   2. server-id=2 (for example)
   3. master-host = ip.add.ress.master
   4. master-user = replication
   5. master-password = slave
   6. master-port = 3306
7. Restart the slave db
8. log into mysql for the slave and apply the following command to check the status:
   1. show slave status\G
   2. You should see 'YES' values for SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING