Master-Master Replication With MySQL 5 On Fedora 8 - Page 3
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 <>
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
- Fedora: http://fedoraproject.org/
- MySQL: http://www.mysql.com/
