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密码要书写正确。

全文完。