289 | | |
290 | | |
291 | | |
292 | | |
| 289 | {{{ |
| 290 | mysql> SHOW SLAVE STATUS; |
| 291 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ |
| 292 | | 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 | |
| 293 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ |
| 294 | | 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 | |
| 295 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ |
| 296 | 1 row in set (0.00 sec) |
| 297 | |
| 298 | mysql> |
| 299 | }}} |
| 300 | |
| 301 | '''/* @Master2 */''' [[BR]] |
| 302 | '''步驟十二 :''' 最後你可以離開 Master2 的 MySQL shell了. 而此時 Master1 複製到 Master2 的 MySQL replication 也已經設定完成 |
| 303 | |
| 304 | 使用指令[[BR]] |
| 305 | |
| 306 | ''mysql> quit''[[BR]] |
| 307 | |
| 308 | '''/* @Master1 */''' [[BR]] |
| 309 | '''步驟十三 :''' 在 Master1 複製到 Master2 的 MySQL replication 設定完成後,接著便是要設定 Master2 複製到 Master1 的 MySQL replication. 而在這之前我們得先停止在 Master1上的從屬關係,並且反過來讓 Master1 成為 Master2 的從屬. |
| 310 | |
| 311 | 使用指令[[BR]] |
| 312 | |
| 313 | ''root@ocean1:~$ mysql -u root -p''[[BR]] |
| 314 | |
| 315 | ''mysql> STOP SLAVE;''[[BR]] |
| 316 | |
| 317 | '''/* @Master1 */''' [[BR]] |
| 318 | '''步驟十四 :''' 請確認你目前在在使用 SHOW MASTER STATUS 的值,並且在 Master2 上執行如下的指令 |
| 319 | |
| 320 | 使用指令[[BR]] |
| 321 | |
| 322 | ''mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=783;'' (Master2 IP: 192.168.0.101)[[BR]] |
| 323 | |
| 324 | '''/* @Master1 */''' [[BR]] |
| 325 | '''步驟十五 :''' 接著便可以啟動 Master1 上的從屬關係 |
| 326 | |
| 327 | 使用指令[[BR]] |
| 328 | |
| 329 | ''mysql> START SLAVE;''[[BR]] |
| 330 | |
| 331 | '''/* @Master1 */''' [[BR]] |
| 332 | '''步驟十六 :''' 最後便要檢查該從屬狀態 |
| 333 | |
| 334 | 使用指令[[BR]] |
| 335 | |
| 336 | ''mysql> SHOW SLAVE STATUS;''[[BR]] |
| 337 | |
| 338 | {{{ |
| 339 | mysql> SHOW SLAVE STATUS; |
| 340 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ |
| 341 | | 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 | |
| 342 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ |
| 343 | | 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 | |
| 344 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ |
| 345 | 1 row in set (0.00 sec) |
| 346 | |
| 347 | mysql> |
| 348 | }}} |
| 349 | |
| 350 | '''/* @Master1 */''' [[BR]] |
| 351 | '''步驟十七 :''' 最後便可以離開 MySQL shell |
| 352 | |
| 353 | 使用指令[[BR]] |
| 354 | |
| 355 | ''mysql> quit''[[BR]] |
| 356 | |
| 357 | |
| 358 | |
| 359 | Reference: |
| 360 | |
| 361 | (1) MySQL Replication: http://dev.mysql.com/doc/refman/5.0/en/replication.html |
| 362 | |
| 363 | (2) Setting Up Master-Master Replication With MySQL 5 On Debian Etch: http://www.howtoforge.com/mysql5_master_master_replication_debian_etch |
| 364 | |
| 365 | PS: 以上安裝與設定內容係參照reference(2)的操作程序並予以中文化以利後續文件撰寫之用. |
| 366 | |
| 367 | |
| 368 | |
| 369 | |
| 370 | |
| 371 | |
| 372 | |