MySQL Replication: Difference between revisions

From UNIX Systems Administration
Jump to navigation Jump to search
No edit summary
No edit summary
Line 26: Line 26:


On the MASTER Server SECOND WINDOW
On the MASTER Server SECOND WINDOW
# Take a dump of the MySQL environment (optional).
# Take a dump of the MySQL environment.
## <tt>'''# mysqldump -p -u root --all-databases > alldb.sql'''</tt>
## <tt>'''# mysqldump -p -u root --all-databases > alldb.sql'''</tt>
## SCP this information over to the slave server.
## SCP this information over to the slave server.
Line 34: Line 34:


On the SLAVE Server
On the SLAVE Server
# Restore the backup from the master server (optional).
# Restore the backup from the master server.
## <tt>'''# mysql -p -uroot < alldb.sql'''</tt>
## <tt>'''# mysql -p -uroot < alldb.sql'''</tt>
# <tt>'''# mysql -p -uroot'''</tt>
# <tt>'''# mysql -p -uroot'''</tt>

Revision as of 16:33, 30 September 2013

Open two windows to the Master Server and one window to the Slave Server

On the MASTER Server MAIN WINDOW

  1. # service mysqld stop
  2. # vi /data/chroot/etc/my.cnf
	log-bin         = mysql-bin
	server-id       = 1
  1. # service mysqld start

On the SLAVE Server

  1. # service mysqld stop
  2. # vi /data/chroot/etc/my.cnf
	server-id       = 2
  1. service mysqld start

On the MASTER Server MAIN WINDOW

  1. # mysql -p -uroot
  2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql-ha'@'<IP Address of Slave>' IDENTIFIED BY '<Password>';
  3. mysql> FLUSH PRIVILEGES;
  4. mysql> RESET MASTER;
  5. mysql> FLUSH TABLES WITH READ LOCK;

On the MASTER Server SECOND WINDOW

  1. Take a dump of the MySQL environment.
    1. # mysqldump -p -u root --all-databases > alldb.sql
    2. SCP this information over to the slave server.
  2. # mysql -p -uroot
  3. mysql> SHOW MASTER STATUS;
  4. mysql> UNLOCK TABLES;

On the SLAVE Server

  1. Restore the backup from the master server.
    1. # mysql -p -uroot < alldb.sql
  2. # mysql -p -uroot
  3. mysql> CHANGE MASTER TO MASTER_HOST='<IP Address of Master>', MASTER_USER='mysql-ha', MASTER_PASSWORD='<Password>', MASTER_LOG_FILE='mysql-bin.<######>', MASTER_LOG_POS=<###>;
  4. mysql> START SLAVE;

On the MASTER Server MAIN WINDOW

  1. mysql> UNLOCK TABLES;