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';