MySQL Replication

My favorite replication links:

  1. How To Set Up MySQL Database Replication With SSL Encryption On Ubuntu 9.10

    My first attempt at master/slave replication was between two CentOS servers, using SSL encryption. I finally abandoned that effort after deciding I really didn't need to replicate any databases on the master server. In retrospect, I think the reason for failure was probably because the master server was firewalling port 3306.

  2. MySQL's own documentation on Replication

    After abandoning my first attempt, I succeeded in having the CentOS slave (mentioned above), replicate a large database on an Ubuntu 10.4 server—but without SSL. A second replication trial used a Mac OS X slave to replicate data on a CentOS server. It, too, skipped SSL. I will eventually revisit SSL encryption.

Steps I followed:

I assume the following:

  1. The master server is named master.example.com. (It could also be its IP address.)
  2. The slave server is named slave.example.com. (It could also it its IP address.)
  3. The database to replicate is named exampledb
  4. The user name for replication will be replicator.

On the master:

  • Edit /etc/my.cnf (/etc/mysql/my.cnf on Ubuntu):
log-bin=mysql-bin
server-id=1
binlog-do-db=exampledb

I wanted to replicate only a single database. If I had wanted to replicate ALL databases on the master, except for information_schema and mysql (for example), I could have used the following instead of the binlog-do-db line:

binlog-ignore-db=information_schema,mysql
  • Restart mysqld
  • In mysql (as root), grant rights to a user on the slave computer to read the master's binary logs:
GRANT REPLICATION SLAVE ON *.*
-> TO 'replicator'@'slave.example.com'
-> IDENTIFIED BY 'slavepassword';

On the slave:

  • Edit /etc/my.cnf:
server-id=37

Each participating server must have a unique server-id in the range 1-4294967295

On the master: The database exampledb already had lots of data on the master, so I wanted to have the slave start out with an identical database. Therefore I did the following:

  • Entered the command in mysql as root:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output from SHOW MASTER STATUS told me the bin-log file name and position, which I jotted down:

  • File: mysql-bin.000255
  • Position: 595

I opened another terminal as root (without closing the mysql session above—in order to keep the tables locked). Then I issued the following command (from the bash command line):

mysqldump -u root -p exampledb --lock-all-tables > exampledb.sql

Then I returned to the mysql session in the first terminal and unlocked the tables:

UNLOCK TABLES;

I used scp to copy the dump file (exampledb.sql) to the slave computer. Then

On the slave (still as root):

  • In mysql client, create the exampledb database:
CREATE DATABASE exampledb;
  • in the bash shell, populate the database on the slave with the information from the master's dump file:
mysql -u root -p exampledb < ./exampledb.sql

The above assumes that the current working directory contains the file exampledb.sql.

  • In the mysql client, stop the slave (in case it is already running; if it isn't running, ignore the error message). Then issue the CHANGE MASTER command:
STOP SLAVE;
CHANGE MASTER TO
-> MASTER_HOST='master.example.com',
-> MASTER_USER='replicator',
-> MASTER_PASSWORD='slavepassword',
-> MASTER_LOG_FILE='mysql-bin.000255',
-> MASTER_LOG_POS=595;

Note: I never really created the user replicator on the slave server. Just referencing it in CHANGE MASTER TO was sufficient.

  • Start the slave and display its status. In mysql (as root):
START SLAVE;
SHOW SLAVE STATUS \G
  • Make sure the slave is listening successful. Check for the following three lines in the output of SHOW SLAVE STATUS \G:
  • Slave_IO_State: Waiting for master to send event
  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes

If you see the above output, you have succeeded. (If not, make sure that the master's port 3306 isn't firewalled.) Finally, modify the database on the master, and it should be mirrored in the slave.