MySQL Master-Slave 主從複制環境搭建

本文耗時30分鐘

一、首先準備兩個MySQL環境。

    Master ip:192.168.0.101

  Slave ip:192.168.0.102

     假設使用者都為: seven,密碼為seven123

二、關閉master和slave環境中的Linux自身的防火牆, service iptables stop。或者編輯vi /etc/sysconfig/iptables設定允許通路。

       很多配置不成功都是因為iptables的原因。

三、配置master的MySQL配置。

vi /etc/my.cnf
添加如下配置
log-bin                        = /var/lib/mysql/mysql-bin
# server-id要求全局唯一，常設定為本機ip位址的字尾 #
server-id = 101
           

四、配置slave的MySQL配置。

vi /etc/my.cnf
添加如下配置
log-bin                        = /var/lib/mysql/mysql-bin
# server-id要求全局唯一，常設定為本機ip位址的字尾 #
server-id = 102

           

五、重新開機master和slave來使MySQL配置生效

service mysql restart
           

六、Master伺服器授權Slave連接配接

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* to 'seven'@'%' identified by 'seven123';
MariaDB [(none)]> flush privileges;
           

七、配置Slave從Master複制

mysql>change master to master_host='192.168.0.101',master_user='seven',master_password='seven123'; 

mysql> start slave; 

MariaDB [(none)]>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: seven
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 2103
               Relay_Log_File: mysql-relay-bin.000013
                Relay_Log_Pos: 2390
        Relay_Master_Log_File: mysql-bin.000012
             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: 2103
              Relay_Log_Space: 3312
              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: 101
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.00 sec)
           

八、驗證主從複制

1、登陸Master，建立一個資料庫和表并插入兩條資料

CREATE DATABASE test2;


			USE test2;


			CREATE TABLE tb (
				id INT PRIMARY KEY,
				NAME VARCHAR (30)
			) ENGINE = INNODB, charset = utf8;


			INSERT INTO tb (id, NAME)VALUES	(1, 'name1'),	(2, 'name2');
           

2、登陸Slave檢視資料庫和表和資料

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)


MariaDB [(none)]> use test2;
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
MariaDB [test2]> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| tb              |
+-----------------+
1 row in set (0.00 sec)


MariaDB [test2]> select * from tb;
+----+-------+
| id | name  |
+----+-------+
|  1 | name1 |
|  2 | name2 |
+----+-------+
2 rows in set (0.00 sec)
           

證明Master上面的資料已經被複制到了Slave上面。

幾個注意事項：

1、注意Linux防火牆iptables是否關閉。

2、注意設定grant和change master密碼要書寫正确。

全文完。