A couple of weeks ago, a friend asked about replication on MySQL 4.1.7. I’ve worked with replication in the past, just a quick and dirty job on MySQL 5, and soon forgot about it. This time, I wanted to do it on MySQL 4, and make sure I take good notes for my own benefit. If it can help somebody else, all the better. The official documentation is here. It took a little time to wade through it.

The process below is used to replicate all databases on the master to the slave(s). I will talk about replicating only certain selected databases in a future post.

1. At the master server, check if binary logging is on. Use show variables like ‘%bin%’ to check if binary logging is on or not. If it is not on, turn it on by adding log-bin under [mysqld] section in /etc/my.cnf. Also remember to add server-id=Number, where the number is a unique number for all MySQL servers within the network. It looks something like this:

[mysqld]
log-bin=mysql-bin
server-id=1

If mysql restart is needed, do:
shell> mysqladmin -u root shutdown
shell> mysqld_safe &

2. Set up an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege. If the account is used only for replication (which is recommended), you don’t need to grant any additional privileges.

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO ‘repl’@’%’ IDENTIFIED BY ‘slavepass’;

3. Issue ‘flush tables with read lock’. While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and position on the master. Take note of those 2 values, ’cause they will be used later:

mysql > SHOW MASTER STATUS;
+—————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| mysql-bin.003 | 73 | test | manual,mysql |
+—————+———-+————–+——————+

4. I used mysqldump, documented here, to dump out the database I am interested in. Then do ;

mysql> UNLOCK TABLES;

5. Stop the slave server, add the following lines to its my.cnf file:

[mysqld]
server-id=slave_id

The slave_id value, like the master_id value, must be a positive integer value from 1 to 232 – 1. In addition, it is necessary that the ID of the slave be different from the ID of the master. For example:

[mysqld]
server-id=2

When setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

Now start MySQL on the slave;

6. Create the database with the same name of the database where the dump was taken,

shell> mysql -u root -p -D MyDbNameHere < dump_file.sql 7. Execute the following statement on the slave, replacing the option values with the actual values relevant to your system: mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’replication_user_name’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;

8. Start the slave threads:

mysql> START SLAVE;

It should work. One thing that got me during my process was that I had firewall enabled on both the master and slave, therefore I had to open those ports up for MySQL communications. From this point on, EVERYTHING will be replicated. Notes on replicating only selected databases to follow…