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.