这一期看来还得继续说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:从中继日志中读取日志文件,在本地完成重放。
大致的过程(图解)应该是这样的:
不好意思,没有自己动手画图…直接从别人的博客拿来用的,不过他写的博客也挺不错的!地址在这里:https://anyisalin.com/mysql-master-slave-replication-2-anyisalin.html
主从同步的缺点和瓶颈:
最大的缺点就是:主节点可以读写,而从节点只能复制主节点的数据并且只能读取,所以,当出现写入数据情况较多时候瓶颈就这样诞生了!不过在一般互联网节点都是读取数据多,写入数据较少…谁没事会天天发表评论呢?
主从复制实验拓扑:
主从复制实验:
首先安装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,以下是解决办法:
- 首先还是重新启动mariadb服务,并且在启动之前查看主配置文件查看是否server_id是唯一的不与其他slave或者master重复?
- 进入mysql并且STOP SLAVE;
- 重新配置slave:RESET SLAVE;
- 这样就算搞定了,继续从CHANGE MASTER添加主节点开始,然后启动slave即可!
好了,关于mariadb的主从配置就算结束了,理论较为复杂而配置比较简单!不过这还没算完毕,下一期介绍半同步设置!
Comments