【MYSQL】兩主一從設置

首先先將MASTER及SLAVE的 my.cnf設定完好

A機-192.168.1.1-MASTER

B機-192.168.1.2-是A機的SALVE-亦是C機的MASTER

C機-192.168.1.3-是B機的SALVE (因為是B機SLAVE，所以存在著A機的資料庫)

第一步 ，先設定MASTER

#MASTER設定-A機-192.168.1.1-MASTER

[mysqld]

server-id         =11

user          　  = mysql

bind-address   =192.168.1.1

datadir            =/usr/local/mysql/data

basedir            = /usr/local/mysql

socket             =/tmp/mysql.sock

binlog-format = mixed

log-bin            =mysql-bin

port                 = 3306

設定好my.cnf重啟DB

mysql>service mysql restart

MASTER-A機-192.168.1.1

mysql>create user 'mysql'@'192.168.1.2' identified by '1234';

mysql>grant replication slave on *.* to 'mysql'@'192.168.1.2' identified by '1234';

mysql>flush privileges;

１。建立MYSQL用戶，允許從192.168.1.2連入。
２。給予MYSQL複製SLAVE的權限

３。套用更新

再輸入指令show master status;

參數中顯示的序號及POSITION是稍等要設定SLAVE時會用到

mysql>show master status;
+----------------------+----------+--------------------+-----------------------+-------------------------+
| File                 　   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------------+-----------------------+-------------------------+
| mysql-bin.000001 |      1564|                             | mysql                     |                                  |
+----------------------+----------+--------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)

第二步 ，設定SLAVE

SLAVE設定(先以SLAVE狀能設定，之後啟動主從後，再SHOW成MASTER，給第三台作為MASTER)

#SLAVE設定-B機-192.168.1.2

[mysqld]

  server-id         =22

  user          　  = mysql

  datadir            =  /usr/local/mysql/data

  basedir           =  /usr/local/mysql

  socket             = /tmp/mysql.sock

  binlog-format = mixed  

  log-bin            =mysql-bin

  relay-log      =mysql-relay-bin  

設定好my.cnf重啟DB

mysql> service mysql restart

 SLAVE設定-B機-192.168.1.2  

mysql> change master to master_host='192.168.1.1', master_user='mysql', master_password='1234', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=1564;

   mysql> start slave;

   #查看SALVE狀態

   mysql>  show slave status \G 

   Slave_IO_Running: Yes
   Slave_SQL_Running:Yes

   #表示連線成功設定完成

 
第三步 ，在原本的B機SALVE繼續設定

mysql> create user 'mysql'@'192.168.1.3' identified by '1234';

mysql> grant replication slave on *.* to 'mysql'@'192.168.1.3' identified by '1234';

mysql> flush privileges;

mysql>show master status;

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

| File                 　   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set  |

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

| mysql-bin.000001 |      2299 |                             | mysql                     |                                  |

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

1 row in set (0.00 sec)

１。建立MYSQL用戶，允許從192.168.1.3連入。
２。給予MYSQL複製SLAVE的權限

３。套用更新
４。show master status 此數據將由C機取用

第四步

#SLAVE設定-C機-192.168.1.3
[mysqld]

  server-id         =33

  user          　  = mysql

  datadir            =  /usr/local/mysql/data

  basedir            =  /usr/local/mysql

  socket             = /tmp/mysql.sock

  binlog-format = mixed  

  log-bin            =mysql-bin

  relay-log         =mysql-relay-bin

設定好my.cnf重啟DB

mysql> service mysql restart

SLAVE設定-C機-192.168.1.3

     

   mysql> change master to

              master_host='192.168.1.2,

              master_user='mysql',

              master_password='1234',

              master_port=3306,

              master_log_file='mysql-bin.000001,

              master_log_pos=2299;

   mysql> start slave;
   
   #查看SALVE狀態
   mysql>  show slave status \G 

   Slave_IO_Running: Yes
   Slave_SQL_Running:Yes

   #表示連線成功設定完成

最後完成後再B機上查看show processlist;

則會出現一組master及一組slave的連線

mysql> SHOW PROCESSLIST;
+----+-------------+---------------------+------+-------------+------+----------------------------------------------------+------------------+
| Id | User            | Host                | db   | Command     | Time | State                                                         | Info             |
+----+-------------+---------------------+------+-------------+------+---------------------------------------------------+------------------+
|  2 | root            | localhost           | bbb  | Query       |    0 | starting                                                      | show processlist |
|  9 | system user |                     | NULL | Connect     | 2372 | Waiting for master to send event                              | NULL             |
| 10 | system user |                     | NULL | Connect     |   39 | Slave has read all relay log; waiting for more updates        | NULL             |
| 11 | root            | 192.168.1.1:48688 | NULL | Binlog Dump |  636 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+-------------+---------------------+------+-------------+------+----------------------------------------------------+------------------+
4 rows in set (0.00 sec)