简介
主从模式复制是用于为我们的数据库创建多个副本,这些副本可以在其它数据库上用于运行查询。
像一些非常繁重的查询可能会影响主数据库服务器的性能,或者我们可以使用它来做数据冗余,或者兼具以上两个目的。
我们可以将这个过程自动化,即主服务器到从服务器的复制过程自动进行。执行备份而不影响在主服务器上的写操作。
原理
master 服务器将数据的改变都记录到二进制 binlog 日志中,只要 master 上的数据发生改变,则将其改变写入二进制日志。
salve 服务器会在一定时间间隔内对 master 二进制日志进行探测其是否发生改变;如果发生改变,则开始一个 I/O Thread 请求 master 二进制事件,同时主节点为每个I/O线程启动一个 dump 线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中,从节点将启动 SQL 线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后 I/O Thread 和 SQL Thread 将进入睡眠状态,等待下一次被唤醒。
优缺点
优点:
- 减轻单台服务器的并发访问压力,同时提高硬件的利用率(分功能工作,各施其职)
缺点:
- 只有一台代理,也会有单点故障,当并发高时他也有压力;
- 从服务器同步数据时会有延迟;
其他优缺点待补充……
配置主从模式
准备
- 实现主从模式,至少需要2个安装了 MariaDB 的服务器
- 为了可靠性与稳定性,MariaDB 版本尽量一致,如果不一致,也尽量让主服务器的 MariaDB 版本低于从服务器
- 服务器 MariaDB 的远程访问确认成功开启,请参照 CentOS 7 离线安装、初始配置及远程登录MariaDB
主服务器配置
-
创建数据库
# 登录数据库 mysql -u root -p # 显示所有数据库名称 SHOW DATABASE; # 创建数据库 CREATE DATABASE Test; # 退出会话 quit
[root@mariadb2 /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.3.15-MariaDB MariaDB Server
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| HDS_DSR |
| MariaDB2 |
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
6 rows in set (0.001 sec)
MariaDB [(none)]> CREATE DATABASE Test;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| HDS_DSR |
| MariaDB2 |
| Test |
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
7 rows in set (0.001 sec)
MariaDB [(none)]> quit
Bye
[root@mariadb2 /]# -
修改配置文件
配置参数具体含义如下:
-
设定监听地址,可以设为特定的IP,请务必通过防火墙进行访问控制。
bind-address=0.0.0.0
-
启用二进制日志,值必须唯一。
log-bin
-
设定服务器ID,值必须唯一。
server_id=1
-
设定二进制日志的前缀
log-basename=master1
-
仅复制某个库,在实际环境中,一个mariadb服务器中可能有多个数据库,那么可以选择仅复制或者不复制某个数据库,如果需要添加多个数据库,则在新的一行添加即可。
replicate-do-db=Test
-
忽略某个库
binlog-ignore-db=mysql
还有一些参数待补充……
将配置文件写入 server.cnf 文件中 [mariadb]部分,请务必遵循 MariaDB 文档要求。
#切换到 root 用户 su root # 编辑文件 vim /etc/my.cnf.d/server.cnf
[maria@mariadb2 /]$ su root
Password:
[root@mariadb2 /]# vim /etc/my.cnf.d/server.cnf以下是 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]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#
# 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]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#
# 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]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
# 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]
bind-address=0.0.0.0
log-bin
server_id=1
log-basename=master1
replicate-do-db=Test
# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don’t understand
[mariadb-10.3]
~
~完成后重启 MariaDB 主服务器的服务:
systemctl restart mariadb
-
-
创建同步数据所需要的用户,并记录文件信息
# 进入数据库 mysql -u root -p # 创建 slave 用户 CREATE USER 'slave'@'%' IDENTIFIED BY 'slave_password'; # 分配 REPLICATION SLAVE 权限 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%'; # 刷新权限表 FLUSH PRIVILEGES; # 启用读锁 FLUSH TABLES WITH READ LOCK; # 列出主服务器的状态 SHOW MASTER STATUS;
[root@mariadb1 /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1332
Server version: 10.3.15-MariaDB MariaDB Server
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> CREATE USER ‘slave’@’%’ IDENTIFIED BY ‘1111’ ;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
±-------------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-------------------±---------±-------------±-----------------+
| master1-bin.000001 | 330 | | |
±-------------------±---------±-------------±-----------------+
1 row in set (0.001 sec)
MariaDB [(none)]>一定要启用读锁后再检查主服务器的状态,然后把File名称和Position的值记录下:
File: master1-bin.000001
Position: 330
执行以下命令解除读锁并退出会话:
# 解除读锁 UNLOCK TABLES; # 退出会话 quit
至此,主服务器的配置已全部完成。
从服务器配置
-
创建同步的数据库
# 登录数据库 mysql -u root -p # 创建数据库 CREATE DATABASE Test; # 显示所有数据库 SHOW DATABASE; #退出会话 quit
[root@mariadb2 /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.3.15-MariaDB MariaDB Server
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> SHOW DATABASE;
±-------------------+
| Database |
±-------------------+
| HDS_DSR |
| MariaDB |
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
6 rows in set (0.001 sec)
MariaDB [(none)]> CREATE DATABASE Test;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| HDS_DSR |
| MariaDB2 |
| Test |
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
7 rows in set (0.001 sec)
MariaDB [(none)]> quit
Bye
[root@mariadb2 /]# -
修改配置文件
# 编辑文件 vim /etc/my.cnf.d/server.cnf
以下是 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]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
# 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]
bind-address=0.0.0.0
server_id=2
replicate-do-db=Test
# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don’t understand
[mariadb-10.3]
~
~
~重启 MariaDB 服务
systemctl restart mariadb
-
修改 SLAVE 配置
首先准备一下 SQL 语句:CHANGE MASTER TO MASTER_HOST='172.18.107.181', MASTER_USER='slave', MASTER_PASSWORD='slave_password', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10;
参数释义:
- MASTER_HOST
master主机名(或IP地址) - MASTER_USER
连接到master主机复制账户所对应的用户名 - MASTER_PASSWORD
连接到master主机复制账户所对应的用户名密码 - MASTER_PORT
MariaDB实例端口号 - MASTER_LOG_FILE
二进制文件名 - MASTER_LOG_POS
日志记录节点 - MASTER_CONNECT_RETRY
重连到master时的超时等待时间
配置并启动复制(SLAVE)进程
``` # 进入数据库 mysql -u root -p # 配置 SLAVE CHANGE MASTER TO MASTER_HOST='172.18.107.181', MASTER_USER='slave', MASTER_PASSWORD='slave_password', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=330, MASTER_CONNECT_RETRY=10; # 启动进程 STRAT SLAVE; # 查看进程状态 SHOW SLAVE STATUS\G; ```
[root@mariadb2 /]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.3.15-MariaDB MariaDB Server
Copyright © 2000, 2018, 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=‘172.18.107.181’,
-> MASTER_USER=‘slave’,
-> MASTER_PASSWORD=‘1111’,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=‘master1-bin.000001’,
-> MASTER_LOG_POS=330,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.011 sec)
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.107.181
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master1-bin.000001
Read_Master_Log_Pos: 330
Relay_Log_File: mariadb2-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: master1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: HDS_DSR
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: 330
Relay_Log_Space: 869
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
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)以上信息主要确认以下信息:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
确认 Read_Master_Log_Pos 与 Exec_Master_Log_Pos 的值是否一致;
如果稍等片刻之后还是不一致,则主从复制可能出现了问题,也需要检查日志查找原因。主从服务器意外断开后,从服务器会尝试重连,如果连上,会再次复制同步,如果重连次数超过设定的值,则不会再次尝试,此时需要手动启动slave进程。
- MASTER_HOST
至此,从服务器的配置已全部完成。