Note: Setting up the slave like this, means it can only be used to read.. don’t ever try to write to it because you will break replication. If you want to add writing to it, set replication up both ways and change the auto-increment-increment and auto-increment-offset on both/all servers to ensure the unique keys don’t collide.

#Master and Slave(s)

apt-get install mysql-server –yes

#Master

vim /etc/mysql/my.cnf
#bind-address = 127.0.0.1 #Comment it out

[Replication]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

mysql -u root --password={pass}
> grant replication slave on *.* to 'replication'@'%' identified by '{some_pass}';
> \q
/etc/init.d/mysql restart

> create database my_application;
> GRANT ALL PRIVILEGES ON my_application.* TO replication;

#Slaves

vim /etc/mysql/my.cnf
#bind-address = 127.0.0.1 #Comment it out

[Replication]
server-id = X # (pick number 2 or over)
master-host = db1.dom.ext
master-user = replication
master-password = some_pass
master-port = 3306
replicate-wild-do-table = my\_application.%

(use the slash in front of an underscore because it’s a wildcard)

/etc/init.d/mysql restart

Additional Notes

#If u ever need to change master settings, run this mysql command on the slave:

CHANGE MASTER TO
                MASTER_HOST='db1.dom.ext',
                MASTER_USER='replication',
                MASTER_PASSWORD='pass',
                MASTER_PORT=3306;

#Check if slave OK
If the Slave_IO_State is “Waiting for master to send event” then you’ve been successful.

mysql -u root --password={pass}
> show slave status \G

#Check if connection to master OK
mysql –host=db1.dom.ext –port=*mysql-port* -u replication –password={pass}

#Check connection to slave OK from master
mysql -h dbX.dom.ext -u root –password={pass}

#Troubleshoot using telnet
telnet dbX.dom.ext *mysql-port*

#Create new user

INSERT INTO mysql.user (Host, User, Password, Select_priv) VALUES ('%', 'username', password('supersecret'), 'Y');

#Change pass

use mysql
update mysql.user set password=PASSWORD("NEW-PASSWORD-HERE") where User='tom';