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;
之后又可以同步了
