设置mysql5的master-master同步复制机制
?
[...]# Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.#bind-address = 127.0.0.1[...]
Restart MySQL afterwards:
[...][mysqld]server-id = 1replicate-same-server-id = 0auto-increment-increment = 2auto-increment-offset = 1master-host = 192.168.0.101master-user = slave1_usermaster-password = slave1_passwordmaster-connect-retry = 60replicate-do-db = exampledblog-bin = /var/log/mysql/mysql-bin.logbinlog-do-db = exampledbrelay-log = /var/lib/mysql/slave-relay.logrelay-log-index = /var/lib/mysql/slave-relay-log.indexexpire_logs_days = 10max_binlog_size = 500M[...]
Then restart MySQL:
[...]server-id = 2replicate-same-server-id = 0auto-increment-increment = 2auto-increment-offset = 2master-host = 192.168.0.100master-user = slave2_usermaster-password = slave2_passwordmaster-connect-retry = 60replicate-do-db = exampledblog-bin= /var/log/mysql/mysql-bin.logbinlog-do-db = exampledbrelay-log = /var/lib/mysql/slave-relay.logrelay-log-index = /var/lib/mysql/slave-relay-log.indexexpire_logs_days = 10max_binlog_size = 500M[...]
mysql> 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.000009 | 98 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 98 | 235 | None | | 0 | No | | | | | | 0 |+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+1 row in set (0.00 sec)mysql>
Afterwards, you can leave the MySQL shell on
mysql> 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.101 | slave1_user | 3306 | 60 | mysql-bin.000009 | 783 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 783 | 235 | None | | 0 | No | | | | | | 0 |+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+1 row in set (0.00 sec)mysql>
Afterwards you can leave the MySQL shell:
From：http://www.howtoforge.com/mysql5_master_master_replication_debian_etch
1 楼 anttribe 2011-06-24 JR...<><><>><><><<><><><<><<><>><><><><><><><><><><><><><><><><><><><><><><<<<><><<><><><><>>>><><><><山寨来的文章也要翻译下啊!!老大! 2 楼 wenson 2011-07-11 anttribe 写道JR...<><><>><><><<><><><<><<><>><><><><><><><><><><><><><><><><><><><><><><<<<><><<><><><><>>>><><><><山寨来的文章也要翻译下啊!!老大!