MySQL Replication - Revision history

Maxm at 19:59, 5 March 2015

2015-03-05T19:59:11Z

Maxm at 19:15, 5 March 2015

2015-03-05T19:15:08Z

Maxm at 19:11, 5 March 2015

2015-03-05T19:11:31Z

Maxm: Created page with "MySQL replication allows you to replicate databases from a master server to a slave server. Data changes are automatically made on the slave server. This setup is only availab..."

2015-03-05T19:10:30Z

Created page with "MySQL replication allows you to replicate databases from a master server to a slave server. Data changes are automatically made on the slave server. This setup is only availab..."

New page

MySQL replication allows you to replicate databases from a master server to a slave server. Data changes are automatically made on the slave server. This setup is only available for our VPS products. This guide assumes you have a working installation of MySQL installed on both the Master and Slave server.

==Setting up replication==
'''On the Master server'''
#Edit the my.ini (Windows) or my.cnf file (Linux). On Windows this is normally in MySQL installation directory (C:\MySQL or C:\Program Files\MySQL\). On Linux it is normally /etc/my.cnf or /etc/mysql/my.cnf.
#Under the '''[mysqld]''' section add: <pre>log-bin=mysql-bin
server-id=1</pre>
#Restart the MySQL service

'''On the Slave server'''
#Edit the my.ini or my.cnf file
#Under the '''[mysqld]''' section add: <pre>server-id=2</pre>
#Restart the MySQL service

'''On the Master server'''
#You'll need to create a replication user that slave server will be using to connect to the MySQL master. Open the MySQL prompt and run the following to create the user:
#<pre>GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'10.10.0.65' IDENTIFIED BY 'slavepass';</pre>''Be sure to replace 10.10.0.65 with the real IP the slave will be connecting from and 'slavepass' with a real password for the replication user.''
#Then run<pre>Flush privileges;</pre>
#Test connecting to the mysql master server with this new user you just created from the slave server to make sure it's working.
#Now you'll need to get the current log position from MySQL master. At the MySQL prompt run:<pre>FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;</pre>This will show you the current log file and position. Save this information for the next step and run:<pre>UNLOCK TABLES;</pre>

'''On the Slave server'''
Finalize replication config
#At the mysql prompt run: <pre>CHANGE MASTER TO
MASTER_HOST='10.10.0.60',
MASTER_USER='replication_user',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=239;
START SLAVE;</pre>''Be sure to replace 10.10.0.60 with the real IP of the master server, 'slavepass' with a real password for the replication user, MASTER_LOG_FILE and MASTER_LOG_POS with the information from the previous step.''
#Run the following to check the status of the slave:<pre>SHOW SLAVE STATUS\G;</pre>This will show if you if the slave is working.

← Older revision		Revision as of 19:59, 5 March 2015
Line 26:		Line 26:
	==Additional Info==		==Additional Info==
	For additional info refer to this MySQL doc: https://dev.mysql.com/doc/refman/5.5/en/replication.html		For additional info refer to this MySQL doc: https://dev.mysql.com/doc/refman/5.5/en/replication.html
		+
		+	[[Category:MySQL]]
		+	[[Category:Windows-VPS]]
		+	[[Category:Linux-VPS]]

← Older revision		Revision as of 19:15, 5 March 2015
Line 23:		Line 23:
	#At the mysql prompt run: <pre>CHANGE MASTER TO MASTER_HOST='10.10.0.60', MASTER_USER='replication_user', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=239; START SLAVE;</pre>''Be sure to replace 10.10.0.60 with the real IP of the master server, 'slavepass' with a real password for the replication user, MASTER_LOG_FILE and MASTER_LOG_POS with the information from the previous step.''		#At the mysql prompt run: <pre>CHANGE MASTER TO MASTER_HOST='10.10.0.60', MASTER_USER='replication_user', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=239; START SLAVE;</pre>''Be sure to replace 10.10.0.60 with the real IP of the master server, 'slavepass' with a real password for the replication user, MASTER_LOG_FILE and MASTER_LOG_POS with the information from the previous step.''
	#Run the following to check the status of the slave:<pre>SHOW SLAVE STATUS\G;</pre>This will show if you if the slave is working.		#Run the following to check the status of the slave:<pre>SHOW SLAVE STATUS\G;</pre>This will show if you if the slave is working.
		+
		+	==Additional Info==
		+	For additional info refer to this MySQL doc: https://dev.mysql.com/doc/refman/5.5/en/replication.html

← Older revision		Revision as of 19:11, 5 March 2015
Line 1:		Line 1:
−	MySQL replication allows you to replicate databases from a master server to a slave server. Data changes are automatically made on the slave server. This setup is only available for our VPS products. This guide assumes you have a working installation of MySQL installed on both the Master and Slave server.	+	MySQL replication allows you to replicate databases from a master server to a slave server. This is a one way replication only: Master -> Slave. Data changes are automatically made on the slave server. This setup is only available for our VPS products. This guide assumes you have a working installation of MySQL installed on both the Master and Slave server.

	==Setting up replication==		==Setting up replication==