MySQL Replication

From UNIX Systems Administration
Jump to navigation Jump to search

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;
  6. mysql> SHOW MASTER STATUS;

On the MASTER Server SECOND WINDOW

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

On the SLAVE Server

  1. # mysql -p -uroot
  2. mysql> STOP SLAVE;
  3. mysql> \q
  4. Restore the backup from the master server.
    1. # mysql -p -uroot < alldb.sql
  5. # mysql -p -uroot
  6. mysql> RESET SLAVE;
  7. 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=<###>;
  8. mysql> START SLAVE;

On the MASTER Server MAIN WINDOW

  1. mysql> UNLOCK TABLES;