mariadb主从同步

这一期看来还得继续说mariadb的应用,因为上一次说的是如何备份,这一次咱们得说说mariadb的主从同步,也叫做mariadb的扩展!

为什么需要对mariadb进行扩展?

当公司的数据达到一定规模,或者业务非常繁忙(国际业务)没有早晚之分,每时每刻都需要进行使用!(业务繁忙会导致不能进行备份,因为在上一期说过,备份是需要时间的!)

所以,如果使用数据库扩展(主从同步)mariadb就能解决备份,数据库读写的压力!当然如果扩展mariadb的功能远不止这些,详细的有:

  • 数据分布
  • 负载均衡
  • 数据备份(冗余)
  • 高可用和故障切换(高可用)
  • mariadb升级测试

关于mariadb的主从复制原理?

主节点:

首先是开启二进制日志文件,并且将新增的条目进行记录!然后将条目送给dump thread!
Dump Thread:为每个slave节点的I/O Thread 启动一个dump线程,用于向其发送binary log events

从节点:

I/O Thread:从master 请求二进制日志文件,并保存于中继日志中。
SQL Thread:从中继日志中读取日志文件,在本地完成重放。

大致的过程(图解)应该是这样的:

extend_mariadb01

不好意思,没有自己动手画图…直接从别人的博客拿来用的,不过他写的博客也挺不错的!地址在这里:https://anyisalin.com/mysql-master-slave-replication-2-anyisalin.html

主从同步的缺点和瓶颈:

最大的缺点就是:主节点可以读写,而从节点只能复制主节点的数据并且只能读取,所以,当出现写入数据情况较多时候瓶颈就这样诞生了!不过在一般互联网节点都是读取数据多,写入数据较少…谁没事会天天发表评论呢?

主从复制实验拓扑:

extend_mariadb02

主从复制实验:

首先安装mariadb,我这边使用的二进制包进行安装三个节点,版本为10.0.24。不知道如何安装的可以看这边文章

主节点配置:

[root@localhost ~]# vi /etc/mysql/my.cnf 
#打开主配置文件
# Replication Master Server (default) 
#找到这句话!
# binary logging is required for replication
log_bin=mysql-bin 
#开启二进制日志,但是如果是二进制包安装默认就有

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1 #编辑server id!可以直接默认

[root@localhost ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.0.24-MariaDB MariaDB Server

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

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

MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cookies'@'128.168.1.%' IDENTIFIED BY'用户的密码'; #创建一个能够复制的账号,如果你不怕安全可以直接用root
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER STATUS; 
#记录二进制日志的pos值,这个必须记下!
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 644 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#好了这样主节点就算搞定了!简单不?对了还得启动mysqld呢...这个您应该会吧?

从节点配置:

[root@localhost mysql]# vi /etc/mysql/my.cnf #打开主配置文件
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id = 1   #注释掉server_id号码在下面的slave定义
#找到下面这个位置
# Replication Slave (comment out master section to use this)

server-id = 2 #更改server id
relay_log = relay.log #开启relay日志
relay_log_index = relay_log.index
read_only =ON #设置mariadb只读
[root@localhost mysql]# systemctl start mysqld #启动从节点
[root@localhost mysql]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.0.24-MariaDB MariaDB Server

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

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

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='128.168.1.201', MASTER_USER='cookies', MASTER_PASSWORD='主节点密码', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=644;
Query OK, 0 rows affected (0.08 sec) 
#上面的命令为设置主节点的主机地址、用户、密码、从主节点的哪个日志文件向后读取?POS值为多少?
MariaDB [(none)]> SHOW SLAVE STATUS\G; 
#查看从节点
*************************** 1. row ***************************
 Slave_IO_State:
 Master_Host: 128.168.1.201
 Master_User: cookies
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 644
 Relay_Log_File: relay.000001
 Relay_Log_Pos: 4
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: No #还没开启所以是NO
 Slave_SQL_Running: No #还没开启所以是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: 644
 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)

ERROR: No query specified

MariaDB [(none)]> START SLAVE; 
#启动从节点
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G;
#再来看看,刚刚两个值是不是YES了?
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 128.168.1.201
 Master_User: cookies
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000001
 Read_Master_Log_Pos: 644
 Relay_Log_File: relay.000002
 Relay_Log_Pos: 535
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: Yes #看是不是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: 644
 Relay_Log_Space: 822
 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)
#从节点的相关配置就此结束!slave3与2相同只是改一下server id即可!

测试主从是否能够同步?

#在主节点查看相关数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

#在从节点查看数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

#在主节点添加一个数据库test2
MariaDB [(none)]> CREATE DATABASE test2;
Query OK, 1 row affected (0.00 sec)
#在主节点查看数据库
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
+--------------------+
5 rows in set (0.00 sec)

#在slave1节点查看数据库:
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
+--------------------+
5 rows in set (0.00 sec)
#你看test2有了!

配置相关注意事项:

在配置任何节点时,如果mariadb服务是运行的请先停止运行。或者修改完配置文件重新读取配置文件 | 重新启动即可。如果不这么做那么会出现slave的server_id会与master冲突导致Slave_IO_Running为NO并且会提示失败!

如果你恰巧真的忘记在启动slave之前没有重启mysqld,以下是解决办法:

  1. 首先还是重新启动mariadb服务,并且在启动之前查看主配置文件查看是否server_id是唯一的不与其他slave或者master重复?
  2. 进入mysql并且STOP SLAVE;
  3. 重新配置slave:RESET SLAVE;
  4. 这样就算搞定了,继续从CHANGE MASTER添加主节点开始,然后启动slave即可!

好了,关于mariadb的主从配置就算结束了,理论较为复杂而配置比较简单!不过这还没算完毕,下一期介绍半同步设置!

Comments

Leave a Reply

Your email address will not be published. Name and email are required