Azitech

Azimout's Linux weblog

MySQL replication

leave a comment »

I wanted to keep two (very simple) MySQL databases synchronized. Replication follows a master-slave model, i.e. it’s one-directional. I’m assuming the structure of the tables is already the same, we’re synchronizing only the data.

Configure the master

  1. First, make sure the master database is accessible on the network by setting bind-address in /etc/mysql/my.cnf ** to 0.0.0.0 (or comment-out altogether)
  2. Uncomment server-id, log_bin and binlog-do-db and set binlog-do-db=testdb (restart MySQL for the change to take effect)
  3. Grant replication privileges: GRANT REPLICATION SLAVE ON *.* TO 'remoteuser'@'%' IDENTIFIED BY 'password';
  4. USE testdb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; UNLOCK TABLES;

Configure the slave

  • Do an initial sync of the table data between master and slave: mysqldump --host=master_ip_address -u remoteuser -p testdb > dbdump.sql; mysql -u remoteuser -p testdb < dbdump.sql
  • Add the following lines in the [mysqld] section of /etc/mysql/my.cnf (restart MySQL for the change to take effect)
server-id=2
master-host='master_ip_address'
master-user='remoteuser'
master-password='password'
master-connect-retry=60
replicate-do-db='testdb'
  • STOP SLAVE; CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='remoteuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='logfile', MASTER_LOG_POS=log_position; START SLAVE;

Under Windows

  • the MySQL configuration file is C:\Program Files\MySQL\MySQLx.x\my.ini
  • If the master MySQL is under Windows, you need to open port 3306 (or whatever you’ve set in the configuration file) in the firewall
  • You won’t be able to connect remotely to MySQL on Windows if it can’t reverse-lookup the slave’s hostname. Add a line in C:\Windows\system32\Drivers\etc\hosts for this
  • To restart MySQL, type this in a terminal: net stop mysql && net start mysql

References

Advertisements

Written by azimout

10/02/2011 at 21:40

Posted in Howto

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: