Goodies Lemonsoftare

27.04.2010

We'll assume that we have an up and running master mysql server, and we want to set up a replication server on another host.

Usually /etc/my.cnf. Make sure that you have skip-networking commented out, and you have the next lines:

#skip-networking 
...
server-id       = 1
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

Restart as needed.

In the mysql console:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicationuser'@'%' IDENTIFIED BY  'somepass';

mysqldump -u root -p --master-data --single-transaction ReplicatedDBName > /tmp/replicated_db.sql

Send this file to the slave host.

For mysqld > 5.1.0 you'll have bind-address instead of skip-networking.

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = <slave_ip_here>
...
server-id               = 2
binlog_do_db            = ReplicatedDBName

In the mysql console (on the slave), we issue (check for the path of the sql file):

mysql > CREATE DATABASE ReplicatedDBName;
mysql > USE ReplicatedDBName;
mysql > SOURCE /tmp/replicated_db.sql;
mysql > CHANGE MASTER TO master_host="<master_ip_here>",master_port=3306,master_user="replicationuser",master_password="somepass";

mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G

If you are able to connect to mysql locally but not remote, check first the local port:

nmap localhost -p 3306

If it's closed, check the my.cnf for skip-networking thing, and ALSO the startup script. Sometimes, the SKIP parameter is set there.