MySQL搭建主从同步数据库(一主多从)

发表于 | 更新于 | 分类于 ， | 阅读次数：

例如,我们此时有3台服务器,分别为

1
2
3
192.168.1.100 --- Master
192.168.1.101 --- Slave
192.168.1.102 --- Slave

1.Mysql默认是不允许远程连接的,首先打开每个服务器的远程访问权限,每个Mysql数据库都需要打开此项

1
2
3
4
--打开远程访问用户
grant all on *.* to 'user'@'192.168.1.%' identified by 'password' with grant option;
--刷新权限配置
flush privileges;

2.修改Master(主库)配置文件,默认安装的配置文件一般在:

1
/etc/my.cnf

在[mysqld]节点加上如下配置:

1
2
3
4
5
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
#只同步test数据库(可选配置)
binlog-do-db=test

3.重启Mysql服务

1
systemctl restart mariadb

4.查看并记录master的信息

1
show master status;

MysqlMaster.png

1
2
3
4
5
#File
master-bin.000001
#################
#Position:
2077

这时Master库已经配置完成了,接下来配置Slaver库:
1.同样的,先配置/etc/my.cnf,与Master不同的只有server-id一项

1
2
3
server-id=10
log-bin=master-bin
log-bin-index=master-bin.index

2.重启数据库服务

1
systemctl restart mariadb

3.连接Mysql配置Master信息并开启Slave

1
2
3
4
5
6
7
8
9
10
--设置Master信息
change master to 
 master_host='192.168.1.100'
,master_user='user'
,master_password='password'
,master_log_file='master-bin.000001'
,master_log_pos=2077;

--开启slave
start slave;

4.查看slave状态:

1
show slave status \G;

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.100
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 2077
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 623
        Relay_Master_Log_File: master-bin.000001
             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: 2077
              Relay_Log_Space: 919
              Until_Condition: None
                        ..........

需要注意的是
-

1
2
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两项要同时为Yes,同步服务才是正常状态.
-
配置192.168.1.102跟192.168.1.101是完全一样的,只要在配置my.cnf时,将server-id少做修改即可,比如
-

1
server-id=11