Random Randy Ramblings

Setting up a replicated MySQL server

Filed under: PHP / MySQL |

I’m pretty much taking notes here, so I put the link at the bottom of this post to the original article that I based this off of. Here are the steps to get replication setup.

1. SSH to [PRIMARY HOST], log into mysql as root and

SET GLOBAL max_connections = 0;

GRANT REPLICATION SLAVE, REPLICATION CLIENT
ON *.*
TO ‘replication’@’[SECONDARY HOST]‘
IDENTIFIED BY ‘replication’;

2. Exit to the shell.

3. Execute the following at the shell:

mysqldump –u root -p –extended-insert –all-databases –master-data > /tmp/backup.sql

4. Go back into the mysql client and run…

SET GLOBAL max_connections = 250;
exit;

5. Open an SSH session to [SECONDARY HOST], stop MySQL and add the following to /etc/my.cnf [mysqld] section

server-id = 2

master-host = [PRIMARY HOST]
master-port = 3306
master-user = replicantion
master-password = replication
log-bin = /usr/local/mysql_binlogs/bin.log
log-bin-index = /usr/local/mysql_binlogs/log-bin.index
log-error = /usr/local/mysql_binlogs/error.log
relay-log = /usr/local/mysql_binlogs/relay.log
relay-log-info-file = /usr/local/mysql_binlogs/relay-log.info
relay-log-index = /usr/local/mysql_binlogs/relay-log.index

6. Transfer the backup.sql file from [PRIMARY HOST] to [SECONDARY HOST] using AFP, SMB or SCP.

7. Start MySQLd again and at the shell on [SECONDARY HOST] execute the following:

mysql –user=root –password=my_pwd < /tmp/backup.sql

8. From the shell on [SECONDARY HOST], sign into mysql and...

START SLAVE;
SHOW SLAVE STATUS;

at this point you should see the log positions changing and are replicating.

As seen at: http://www.onlamp.com/pub/a/onlamp/2005/06/16/MySQLian.html

1:43 pm