mysql級聯複制

需求：三個伺服器A->B->C級聯主從

版本：資料庫 mysql 5.6_64bit_binary_install 作業系統： centos 6.5_x86_64bit

Master ： 192.168.0.180 =>主節點

slave1 : 192.168.0.18 =>中繼節點

slave2 : 192.168.4.88

1: 管理iptables , selinux ，/etc/hosts 下主機IP 位址綁定

2：配置 MY.CNF 相關參數

MAster 配置： my.cnf

[mysql@master ~]$ cat /usr/local/mysql/my.cnf

# For advice on how to change settings please see

----------------

[mysqld]

port=3308

skip-name-resolve -- /* dns 反向解析時間 * grant 時，必須使用ip不能使用主機名 */

datadir=/cifpay/mysqldb

basedir=/usr/local/mysql

log-error=/cifpay/mysqldb/oracle11g.com.err

pid-file=/cifpay/mysqldb/oracle11g.com.pid

plugin-dir=/usr/local/mysql/lib/plugin

socket=/tmp/mysql.sock

# ----master-----#

#replicate-do-db

server_id=1 --/* 唯一标示 */

log-bin=/cifpay/mysql-bin.log

binlog-ignore-db=mysql --/* 複制排除 mysql 庫 */

sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION --/* 可以添加使用者 */

-------------------

Slave1(中繼節點) 配置：

[mysql@slave1 ~] $ cat /usr/local/mysql/my.cnf

---------------

log-error=/cifpay/mysqldb/dominic.mysql1.err

pid-file=/cifpay/mysqldb/dominic.mysql1.pid

# ----slave 1 -------#

server-id=2

read_only=TURE

binlog-ignore-db=mysql

log_slave_updates=1 /* 關鍵一步 */

sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

------------------

Slave2 配置：

[mysql@slave2 ~]$ cat /usr/local/mysql/my.cnf

------------------------

server_id=3

log-error=/cifpay/mysqldb/cifpay.rac1.err

pid-file=/cifpay/mysqldb/cifpay.rac1.pid

注意：關于要複制多個資料庫時，binlog-do-db和replicate-do-db選項的設定，網上很多人說是用半形逗號分隔，經過測試，這樣的說法是錯誤的，MySQL官方文檔也明确指出，如果要備份多個資料庫，隻要重複設定相應選項就可以了。

binlog-do-db=a

binlog-do-db=b

replicate-do-db=a

replicate-do-db=b

3 : 各mysql 資料庫DB 建立對應的copy user 以及授權：注意，最好使用者分開，并授予 REPLICATION SLAVE 權限。

mysql> grant all privileges on *.* to copy1@'%' identified by '123456'with grant option;

mysql> show databases;

+--------------------+

| Database |

| information_schema |

| dominic |

| mysql |

| performance_schema |

| test |

5 rows in set (0.00 sec)

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user,password,host from user;

+-------+-------------------------------------------+---------------+

| user | password | host |

| root | | localhost |

| root | | oracle11g.com |

| copy1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % |

| copy2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % |

4 rows in set (0.00 sec)

4 ：重新開機Mster 庫，使配置生效，在主伺服器上，設定讀鎖定有效，這個操作是為了確定沒有資料庫操作，以便獲得一個一緻性的快照：

mysql> flush tables with read lock;

5 ：通過mysqldump 或者其他方式：釋放master 鎖及 slave 庫恢複（約）

mysql> unlock tables;

6：啟動Slave 1 （中繼節點），通過 --skip-slave-start參數或者正常啟動後，通過mysql > stop salve ;

對從資料庫伺服器做相應設定，指定複制使用的使用者，主資料庫伺服器的 IP、端口以及開始執行複制的日志檔案和位置等，具體文法如下：

檢視 master 節點 pos ：

mysql> show master status ; --Master

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql> CHANGE MASTER TO --Slave 1

-> MASTER_HOST='master_host_name', -->主機host(IP)

-> MASTER_USER='replication_user_name', -->複制的使用者user( 如果是主從從，指定的複制使用者必須不同）

-> MASTER_PASSWORD='replication_password', -->複制使用者的密碼

-> MASTER_LOG_FILE='recorded_log_file_name', -->對應第5步的 file 編号。

-> MASTER_LOG_POS=recorded_log_position, -->對應的POS編号

-> MASTER_PORT=3308;

eg：中繼節點 Slave1 上執行

mysql> change master to

-> MASTER_HOST='192.168.0.180',

-> MASTER_USER='copy1',

-> MASTER_PASSWORD='123456',

-> MASTER_LOG_FILE='mysql-bin.000004',

-> MASTER_LOG_POS=120,

-> MASTER_PORT=3308;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G --檢視slave 1 狀态

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.180

Master_User: copy1

Master_Port: 3308

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 249

Relay_Log_File: dominic-relay-bin.000002

Relay_Log_Pos: 412

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 249

Relay_Log_Space: 587

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 1fb88a50-81b1-11e4-95e4-080027a9d0f9

Master_Info_File: /cifpay/mysqldb/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

7 : 啟動slave 2 通過 --skip-slave-start參數或者正常啟動後，通過mysql > stop salve ;

mysql> show master status \g --檢視Slave 1 對應pos 值：

[mysql@slave2]$ /etc/init.d/mysqldb start --skip-slave-start

Starting MySQL... SUCCESS!

[mysql@slave2]$ /usr/local/mysql/bin/mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to

-> MASTER_HOST='192.168.0.18',

-> MASTER_USER='copy2',

-> MASTER_PASSWORD='123456',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=302,

-> MASTER_PORT=3308;

Query OK, 0 rows affected, 2 warnings (0.19 sec)

檢視Slave2 節點狀态：

mysql> show slave status \G --Slave2 節點

Master_Host: 192.168.0.18

Master_User: copy2

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 302

Relay_Log_File: cifpay-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000001

Exec_Master_Log_Pos: 302

Relay_Log_Space: 457

Master_Server_Id: 2

Master_UUID: 9b6fc45b-81b3-11e4-95f4-0800273e24cb

1 row in set (0.01 sec)

本文轉自 linuxpp 51CTO部落格，原文連結：http://blog.51cto.com/1439337369/1910761，如需轉載請自行聯系原作者

mysql級聯複制

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List