Master-Master Replication With MySQL 5 On Fedora 8 - Page 3

Want to support HowtoForge? Become a subscriber!

Submitted by o.meyer (Contact Author) (Forums) on Sun, 2008-02-17 19:10. ::

3.4 Export MySQL Dump On System 1

Now we create a dump of the existing database and transfer it to system 2.

mysql -u root -p

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output should look like this. Note down the file and the position - you'll need both later.

Open a second terminal for system 1, create the dump and transfer it to system 2. Don't leave the MySQL-shell at this point - otherwise you'll loose the read-lock.

cd /tmp/
mysqldump -u root -p%mysql_root_password% --opt exampledb > sqldump.sql
scp sqldump.sql root@192.168.0.200:/tmp/

Afterwards close the second terminal and switch back to the first. Remove the read-lock and leave the MySQL-shell.

UNLOCK TABLES;
quit;

3.5 Import MySQL Dump On System 2

Time to import the database dump on system 2.

mysqladmin --user=root --password=%mysql_root_password% stop-slave
cd /tmp/
mysql -u root -p%mysql_root_password% exampledb < sqldump.sql

3.6 System 2 As Master

Now we need information about the master status on system 2.

mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output should look like this. Note down the file and the position - you'll need both later.

Afterwards remove the read-lock.

UNLOCK TABLES;

At this point we're ready to become the master for system 1. Replace %mysql_slaveuser_password% with the password you choose and be sure that you replace the values for MASTER_LOG_FILE and MASTER_LOG_POS with the values that you noted down at step 3.4!

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave2_user', MASTER_PASSWORD='%mysql_slaveuser_password%', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=98;

Now start the slave ...

START SLAVE;

... and take a look at the slave status. It's very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they're not, something went wrong and you should take a look at the logs.

SHOW SLAVE STATUS;

+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+---------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB     | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+---------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.100 | slave2_user |        3306 |            60 | mysql-bin.000004 |                  98 | slave-relay.000002 |           235 | mysql-bin.000004      | Yes              | Yes               | exampledb,exampledb |                     |                    |                        |                         |                             |          0 |            |            0 |                  98 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | 
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+---------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

Afterwards leave the MySQL-shell.

quit;

3.7 System 1 As Master

Open a MySQL-shell on system 1 ...

mysql -u root -p

... and stop the slave.

STOP SLAVE;

At this point we're ready to become the master for system 2. Replace %mysql_slaveuser_password% with the password you choose and be sure that you replace the values for MASTER_LOG_FILE and MASTER_LOG_POS with the values that you noted down at step 3.6!

CHANGE MASTER TO MASTER_HOST='192.168.0.200', MASTER_USER='slave1_user', MASTER_PASSWORD='%mysql_slaveuser_password%', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=958;

Now start the slave ...

START SLAVE;

SHOW SLAVE STATUS;

+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+---------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB     | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+---------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.200 | slave1_user |        3306 |            60 | mysql-bin.000003 |                 958 | slave-relay.000002 |           235 | mysql-bin.000003      | Yes              | Yes               | exampledb,exampledb |                     |                    |                        |                         |                             |          0 |            |            0 |                 958 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | 
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+---------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

Afterwards leave the MySQL shell.

quit;

If all went ok, the master-master replication is working now. Check your logs on both systems if you encounter problems.