MariaDB 主从分布

很早的时候接触了mysql集群的概念,一直未亲自实践过.现在来了需求,于是设计了下面的例子感受一下

过程

1.利用vmware创建一个centos7主机(下面简称A)
2.安装完MariabDB并做了简单地配置,克隆出了B主机
3.开启A主机的binlog,并创建同步帐号
4.设置B主机使它指向master
5.A主机上创建数据库...,检验B主机的数据是否同步变化

一.创建主机A

通过VMware创建CentOS7.3虚拟主机
点击Edit > Virtual Network Editor修改网段为200,
进入虚拟机A,使用下面的配置文件/etc/sysconfig/network-scripts/ifcfg-ens33,目的是将IP固定为192.168.200.100

TYPE=Ethernet
BOOTPROTO=static
DEFROUTE=yes
PEERDNS=yes
PEERROUTES=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_PEERDNS=yes
IPV6_PEERROUTES=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
UUID=27b57a2a-e57f-4720-98bf-ca9f3dd3a50f
DEVICE=ens33
ONBOOT=yes

IPADDR=192.168.200.100
GATEWAY=192.168.200.254
NETMASK=255.255.255.0
DNS1=192.168.200.254
DNS2=223.5.5.5
DNS3=223.6.6.6

不需要重启network服务,进入下一步骤

二.安装MariaDB

这里通过YUM安装最新版本的MariaDB
打开网址https://downloads.mariadb.org/mariadb/repositories/可以看到适合自己系统的最新版库的位置

复制下面的内容到文件/etc/yum.repos.d/MariaDB.repo

# MariaDB 10.2 CentOS repository list - created 2017-08-20 13:47 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

安装

yum makecache
yum install MariaDB-server MariaDB-client -y

开启服务和设置开机启动

systemctl start mysqld
systemctl enable mysqld

创建同步账户

[root@localhost my.cnf.d]# mysql -u root -p
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'replication'@'192.168.200.%' IDENTIFIED BY '123654';
MariaDB [(none)]> FLUSH PRIVILEGES;

用下面的配置修改/etc/my.cnf

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
datadir = /var/lib/mysql/
innodb_file_per_table = on
skip_name_resolve = on

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

到此mysql配置完毕

三.克隆实验主机B

关闭主机A,选中并点击菜单VM > Manage > Clone,
克隆完主机B成后打开,修改/etc/sysconfig/network-scripts/ifcfg-ens33将IP固定为192.168.200.101
使用下面的命令重启网络服务

systemctl restart network

如果你是ssh连接,网络服务会断开,重新连接即可

四,Master配置

开启A主机
修改/etc/my.cnf,mysqld段加入下面的配置

[mysqld]

log-bin=mysql-bin
binlog_format=mixed
server-id       = 1

重启mysqld服务

五.Slave配置

修改/etc/my.cnf,mysqld段加入下面的配置

[mysqld]

server-id       = 10
relay-log       = relay-bin
read_only       = on

重启mysqld服务

接下来是连接mysql,Slave的中继日志是使用SQL语句进行配置的

首先要找到Master服务器binlog日志文件和最后的位置,因为是新的服务器,所以不需要先从master导数据

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      342 |              |                  |
+------------------+----------+--------------+------------------+

可以看到主服务器二进制日志文件为mysql-bin.000003,二进制日志文件中的位置为342

接下来是开启Slave的中继日志

MariaDB [(none)]> change master to master_host='192.168.200.100',master_user='replication',master_password='123654',master_log_file='mysql-bin.000003',master_log_pos=342,master_connect_retry=5,master_heartbeat_period=2;
Query OK, 0 rows affected (0.00 sec)

开启完成后,查看Slave状态,可以看到

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.100
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 342
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000003
             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: 342
              Relay_Log_Space: 858
              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: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

看到Slave_IO_Running和Slave_SQL_Running都是Yes,表明IOthread和SQLthread都在正确运行

测试

打开workbench在Master上创建数据库,创建表和创建数据,可以看到Slave看到Master的变化,说明是正确的

接下来,关闭Slave,并继续对Master进行修改,结果看到Slave依然同步了变化,估计是重启之后立马建立了连接
(有一个疑问,如果数据库)

折腾~~

一不小心在master上调用了reset master命令,之后slave上调用show master status时看到empty set???
进入master数据目录,看到一堆mysql-bin.XXX文件,关闭服务,删了这些文件,重启,看到又从 mysql-bin.000001 开始记录日志了,大概是reset导致的
进入slave,调用show master status看到下面的内容

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      358 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

于是再次reset master;,调用show master status
再次看到mysql-bin.000001,这回该对了吧

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

依次调用

change master to master_log_file='mysql-bin.000001',master_log_pos=328;
start slave;
show slave status\G

终于再次见到你了

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.100
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 328
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-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: 328
              Relay_Log_Space: 858
              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: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

注意,slave千万不要往里面写数据,否则会掉坑了
master删除slave不存在的数据,或者同步的SQL出现错误,也会直接把同步过程挂掉,这个时候需要做的只能是重新设置主从配置(有数据情况下可能要重新导入)

但也可以到master上
reset master;
再到slave上
stop slave;
show master status;
change master to master_log_file='mysql-bin.000001',master_log_pos=328;
start slave;

之后又可以同步了