MySQL 三节点复制

1.环境准备：
安装好三台MYSQL服务器。
A    192.168.1.8
B    192.168.1.88
C    192.168.1.188环形结构： 192.168.1.8主     192.168.1.88从
         192.168.1.88主   192.168.1.188从
         192.168.1.188主 192.168.1.8从2.更改配置文件/etc/my.cnf
增加以下内容：
log-bin=mysql-bin
slave-skip-errors=all
log-slave-updates
skip-name-resolve
在配置文件中修改： server-id    分别是 server-id=1 2 33.重启三个节点的MYSQL。
/home/mysql/mysql/support-files/mysql.server restart4.添加复制用户。在每个节点都如此操作。
grant replication slave on *.* to "replication"@"%" identified by "replication"; grant all privileges on *.* to replication@"%" identified by "replication";4.配置复制进程：
（1） 192.168.1.8主 192.168.1.88从配置
A节点：show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set （0.00 sec）
B节点：
CHANGE MASTER TO MASTER_HOST="192.168.1.8",MASTER_PORT=3306,MASTER_USER="replication",MASTER_PASSWORD="replication",MASTER_LOG_FILE="mysql-bin.000006",MASTER_LOG_POS=107;start slave;show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.88
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000015
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             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: 107
              Relay_Log_Space: 559
              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: 2
1 row in set （0.00 sec）（2）192.168.1.88主 192.168.1.188从配置
B节点：show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set （0.00 sec）C节点配置：
CHANGE MASTER TO MASTER_HOST="192.168.1.8",MASTER_PORT=3306,MASTER_USER="replication",MASTER_PASSWORD="replication",MASTER_LOG_FILE="mysql-bin.000006",MASTER_LOG_POS=107;start slave;show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.88
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000017
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             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: 107
              Relay_Log_Space: 559
              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: 2
1 row in set （0.00 sec）（3）192.168.1.188主 192.168.1.8从配置
C节点：show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set （0.00 sec）A节点配置：
CHANGE MASTER TO MASTER_HOST="192.168.1.8",MASTER_PORT=3306,MASTER_USER="replication",MASTER_PASSWORD="replication",MASTER_LOG_FILE="mysql-bin.000005",MASTER_LOG_POS=107;start slave;show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.88
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000015
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             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: 107
              Relay_Log_Space: 559
              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: 2
1 row in set （0.00 sec）验证：
只要表结构一样，引擎不一样也是可以同步的。
引擎也可以同步，就是你改动第二节点的引擎，第一节点也是会同步成相同的引擎的。DG修改SGA 遇ORA-00845MySQL LVS负载均衡相关资讯 MySQL节点本文评论查看全部评论（0）