User Tools

Site Tools


db:replicationdump

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

db:replicationdump [2013/03/16 17:40] (current)
Line 1: Line 1:
 +==== Setup replication ​ - using SQL dump ====
 +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.
 +
 +=== Operations for MASTER server ===
 +== Modify the configuration file ==
 +
 +Usually ///​etc/​my.cnf//​. Make sure that you have skip-networking commented out, and you have the next lines:
 +
 +<​code>​
 +#​skip-networking ​
 +...
 +server-id ​      = 1
 +# Replication Master Server (default)
 +# binary logging is required for replication
 +log-bin=mysql-bin
 +</​code>​
 +
 +Restart as needed.
 +
 +
 +== Create an user account for replication purposes ==
 +
 +In the mysql console:
 +
 +<​code>​
 +mysql> GRANT REPLICATION SLAVE ON *.* TO '​replicationuser'​@'​%'​ IDENTIFIED BY  '​somepass';​
 +</​code>​
 +
 +
 +== Create the initial dump for the database ==
 +
 +<​code>​
 +mysqldump -u root -p --master-data --single-transaction ReplicatedDBName > /​tmp/​replicated_db.sql
 +</​code>​
 +
 +Send this file to the slave host.
 +
 +=== Operations for SLAVE server ===
 +== Modify the configuration file ==
 +
 +For mysqld > 5.1.0 you'll have //​bind-address//​ instead of //​skip-networking//​.
 +
 +<​code>​
 +#
 +# 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
 +</​code>​
 +
 +== Do the initial import ​ ==
 +
 +In the mysql console (on the slave), we issue (check for the path of the sql file):
 +
 +<​code>​
 +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";​
 +</​code>​
 +
 +
 +== Start the slave  ==
 +
 +<​code>​
 +mysql > START SLAVE;
 +mysql > SHOW SLAVE STATUS\G
 +</​code>​
 +
 +
 +=== TROUBLESHOOTING ===
 +
 +If you are able to connect to mysql locally but not remote, check first the local port:
 +
 +<​code>​
 +nmap localhost -p 3306
 +</​code>​
 +
 +If it's closed, check the my.cnf for skip-networking thing, and ALSO the startup script. Sometimes, the SKIP parameter is set there.
db/replicationdump.txt ยท Last modified: 2013/03/16 17:40 (external edit)