MariaDB——(三) MariaDB 10.0.15 standard replication主从复制搭建

最近看了一下MariaDB的常规复制章节,就按部就班的搭建了一下最简单的主从复制。需要的硬件环境很简单(在虚拟机VMware中搭建):

    1:两台server:Master: 192.168.6.133  Slave:192.168.6.132

    2:网络配置,这里图个简单,直接关闭master的防火墙

[root@master Desktop]# service iptables stop


概览实现主从复制需要完成的配置:

    1:主从集群里面的每台server需要有一个唯一的server_id,下面的配置中,将master的server_id设置为1, slave1的server_id设置为2;

    2:master需要启用二进制日志,slave需要启动relay日志。

    3:master上创建一个用户,供slave登录到master上复制二进制日志数据。

 

具体配置过程如下:

1:master配置。

    首先停掉mysql服务(用root用户执行service mysql status查看是否在正在运行):

[root@master Desktop]# service mysql stop
Shutting down MySQL.. SUCCESS!

    网上的教程都是在my.cnf中添加相关配置,这个文件初始内容如下:

[mariadb@master Desktop]$ cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

    如果在[client-server]节点添加配置项,会造成mysql无法启动,不知是什么原因,看了下错误日志master.err文件也没发现有用的信息。看这个文件的内容,server端和client端配置文件位于/etc/my.cnf.d/目录下:

[mariadb@master Desktop]$ ls /etc/my.cnf.d
mysql-clients.cnf  server.cnf  tokudb.cnf

    修改server.cnf文件如下,完成服务端配置:

[mariadb@master Desktop]$ vi /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
log-basename = master
log-bin = /var/lib/mysql/master.bin
binlog-format = row
server_id = 1
# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers dont understand
[mariadb-10.0]

    (红色部分为新增内容)
    接下来启动mysql服务:

[root@master mysql]# service mysql start
Starting MySQL. SUCCESS!

    登录到mariadb:

[mariadb@master Desktop]$ mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.0.16-MariaDB-log MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type help; or \h for help. Type \c to clear the current input statement.

    授权用户(这个用户稍后会在配置slave节点用到)

MariaDB [(none)]> grant replication slave,replication client on *.* to repluser@192.168.6.133 identified by replpass;
Query OK, 0 rows affected (0.00 sec)

2.slave配置

    首先停掉salve节点的mysql服务,方法同上。

    修改server.cnf文件,具体内容如下:

[mariadb@slave1 Desktop]$ vi /etc/my.cnf.d/server.cnf

   

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
server_id = 2
relay-log = /var/lib/mysql/relay-bin
# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers dont understand
[mariadb-10.0]

(红色部分为新增)
    启动mysql服务

[root@slave1 Desktop]# service mysql status
 SUCCESS! MySQL running (1495)

    登录到mysql数据库,查看relay log中继日志状态:

[root@slave1 Desktop]# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.16-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type help; or \h for help. Type \c to clear the current input statement.

MariaDB [(none)]> show global variables like %relay%;
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| max_relay_log_size    | 1073741824               |
| relay_log             | /var/lib/mysql/relay-bin |
| relay_log_index       |                          |
| relay_log_info_file   | relay-log.info           |
| relay_log_purge       | ON                       |
| relay_log_recovery    | OFF                      |
| relay_log_space_limit | 0                        |
| sync_relay_log        | 0                        |
| sync_relay_log_info   | 0                        |
+-----------------------+--------------------------+
9 rows in set (0.00 sec)

(relay_log参数值是我们设定的路径下的文件,说明中继日志设定OK)
    连接到主服务器master

MariaDB [(none)]> change master to master_host=192.168.6.133, master_user=repluser, master_password=replpass;
Query OK, 0 rows affected (0.09 sec)

    在masterserver上查看master进程数量

MariaDB [(none)]> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
|  4 | root | localhost | NULL | Query   |    0 | init  | show processlist |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)

    在master上查看操作日志状态

MariaDB [(none)]> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| master.000002 |      526 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

    在slave上查看slave状态

技术分享
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.6.133
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 0
              Relay_Log_Space: 248
              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: NULL
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: 0
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)
View Code

    关键看Slave_IO_Running: No            Slave_SQL_Running: No 这两个,现在是未启动,接下来在salve上启动slave节点:

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

    再次查看slave状态

技术分享
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.133
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master.000002
          Read_Master_Log_Pos: 709
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 993
        Relay_Master_Log_File: master.000002
             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: 709
              Relay_Log_Space: 1629
              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: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)
View Code

            Slave_IO_State: Waiting for master to send event

            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

    从节点已经处于接收master节点发送事件的状态,可以进行主从复制了,现在去master上创建一个数据库:

MariaDB [(none)]> create database testsync;
Query OK, 1 row affected (0.01 sec)

    去从节点查看,是否已经自动同步:

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testsync           |
+--------------------+
5 rows in set (0.02 sec)

    已经完成同步。再次查看主从状态:
技术分享

技术分享

    其中read_master_log_pos也是838,表示主从状态一致。

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。