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.

+------------------+----------+---------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------------+------------------+
| mysql-bin.000004 | 98 | exampledb,exampledb | |
+------------------+----------+---------------------+------------------+
1 row in set (0.00 sec)

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.

+------------------+----------+---------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------------+------------------+
| mysql-bin.000003 | 958 | exampledb,exampledb | |
+------------------+----------+---------------------+------------------+
1 row in set (0.00 sec)

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;

... 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.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.

 

4 Links


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Sushant Chawla (not registered) on Tue, 2010-09-21 06:13.

This isa brilliant howto, I followed it step by step & in the last it worked in one shot as expected without any modifications.

 Good Work Meyer, keep it up :-)