Mysql主从配置
大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,远远不够。到了数据业务层、数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器扛,如此多的数据库连接操作,数据库必然会崩溃,数据丢失的话,后果更是不堪设想。这时候,我们会考虑如何减少数据库的联接,一方面采用优秀的代码框架,进行代码的优化,采用优秀的数据缓存技术如:memcached,如果资金丰厚的话,必然会想到假设服务器群,来分担主数据库的压力。今天总结一下利用MySQL主从配置,实现读写分离,减轻数据库压力.
这里我们一本地虚拟机为例来配置主从. mysql和mariadb是一样的配置的.我这里一mariadb为实际案例. 配置的时候两台虚拟机使用的是内网地址.
MySQL 主从复制(也称 A/B 复制) 的原理
- Master将数据改变记录到二进制日志(binary log)中,也就是配置文件log-bin指定的文件,
这些记录叫做二进制日志事件(binary log events); - Slave 通过 I/O 线程读取 Master 中的 binary log events 并写入到它的中继日志(relay log);
- Slave 重做中继日志中的事件, 把中继日志中的事件信息一条一条的在本地执行一次,完
成数据在本地的存储, 从而实现将改变反映到它自己的数据(数据重放)。
主从配置需要注意的点
- 主从服务器操作系统版本和位数一致;
- Master 和 Slave 数据库的版本要一致;
- Master 和 Slave 数据库中的数据要一致;
- Master 开启二进制日志, Master 和 Slave 的 server_id 在局域网内必须唯一;
主从配置的简要步骤
- 安装数据库;
- 修改数据库配置文件, 指明 server_id, - 开启二进制日志(log-bin);
- 启动数据库, 查看当前是哪个日志, position 号是多少;
- 登录数据库, 授权数据复制用户(IP 地址为从机 IP 地址, 如果是双向主从, 这里的还需要授权本机的 IP 地址, 此时自己的 IP 地址就是从 IP 地址);
- 备份数据库(记得加锁和解锁);
- 传送备份数据到 Slave 上;
- 启动数据库;
Slave 上的配置
- 安装数据库;
- 修改数据库配置文件, 指明 server_id(如果是搭建双向主从的话, 也要开启二进制
日志 log-bin); - 启动数据库, 还原备份;
- 查看当前是哪个日志, position 号是多少(单向主从此步不需要, 双向主从需要);
- 指定 Master 的地址、 用户、 密码等信息;
- 开启同步, 查看状态。
1、 Linux Centos 7.5 虚拟机部署环境
主机(Server1): 192.168.26.22 OS:CentOS 7.5
从机(Server1): 192.168.26.23 OS:CentOS 7.5
修改IP方式
vi /etc/sysconfig/network-scripts/ifcfg-ens33
修改网络配置
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static # 修改
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
UUID=9f5b3186-f784-4857-8c4e-2b394a3d9672
DEVICE=ens33
ONBOOT=yes # 修改
GATEWAY=192.168.26.2 # Vmware 工具栏编辑项,选择虚拟网络编辑器,Nat 模式,单击 Nat 设置 获取网关 192.168.26.2
IPADDR=192.168.26.23 # 对外访问IP
NETMASK=255.255.255.0 # 一样
DNS1=192.168.26.2
按esc、组合键shrit + : ,wq 保存退出
重启网络
service network restart
至此,服务器已经搭建好了
2、 创建数据库和表
主机创建 wtf 数据库 以及 user 表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`u_id` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`apartment` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`create_time` datetime(0) NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`phone_number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`role` int(11) NULL DEFAULT NULL,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`u_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 0 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
主机创建 wtf2 数据库 以及 user 表,SQL 同上
3、 主数据库的配置
3.1 主数据库配置(非 Docker 安装 )
首先使用命令
cd /etc
然后在执行
ls my.cnf
在使用编辑器vi打开my.cnf文件
vi my.cnf 按 i 进入编辑模式
ESC :set number 打印行号
[root@localhost etc]# vi my.cnf
[mysqld]
1 [mysqld]
2 datadir=/var/lib/mysql
3 socket=/var/lib/mysql/mysql.sock
4 # Disabling symbolic-links is recommended to prevent assorted security risks
5 symbolic-links=0
6 # Settings user and group are ignored when systemd is used.
7 # If you need to run mysqld under a different user or group,
8 # customize your systemd unit file for mariadb according to the
9 # instructions in http://fedoraproject.org/wiki/Systemd
10
11 server-id=200
12
13 innodb_flush_log_at_trx_commit=2
14
15 sync_binlog=1
16
17 log-bin=mysql-bin-200
18
19 binlog-do-db=wtf
20
21 [mysqld_safe]
22 log-error=/var/log/mariadb/mariadb.log
23 pid-file=/var/run/mariadb/mariadb.pid
24
25 #
26 # include all files from the config directory
27 #
28 !includedir /etc/my.cnf.d
几条配置文件的含义如下:
设置主服务 的ID (id可以自己随便设置但是要保证和slave的id不一样)
server-id=200
innodb_flush_log_at_trx_commit=2 #(参数的含义如下)
- 0:如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘,提交事务的时候不做任何操作(执行是由mysql的master thread线程来执行的。
-主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交)默认的日志文件是ib_logfile0,ib_logfile1 - 1:当设为默认值1的时候,每次提交事务的时候,都会将log buffer刷写到日志。
- 2:如果设为2,每次提交事务都会写日志,但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是,并不能保证100%每秒一定都会刷到磁盘,这要取决于进程的调度。
- 每次事务提交的时候将数据写入事务日志,而这里的写入仅是调用了文件系统的写入操作,而文件系统是有 缓存的,所以这个写入并不能保证数据已经写入到物理磁盘
- 默认值1是为了保证完整的ACID。当然,你可以将这个配置项设为1以外的值来换取更高的性能,但是在系统崩溃的时候,你将会丢失1秒的数据。
- 设为0的话,mysqld进程崩溃的时候,就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。
总结
设为1当然是最安全的,但性能页是最差的(相对其他两个参数而言,但不是不能接受)。如果对数据一致性和完整性要求不高,完全可以设为2,如果只最求性能,例如高并发写的日志服务器,设为0来获得更高性能
开启binlog 志同步功能
sync_binlog=1
binlog 日志文件名
log-bin=mysql-bin-200
这个表示只同步某个库 (如果没有此项,表示同步所有的库)
binlog-do-db=wtf
上面的配置写好之后按键盘ESC键在按shift+:输入wq.保存退出.
重启我们的主机数据库看自己的数据库是什么选用不同的命令
systemctl restart mysql
3.2 Mysql 主配置 (Docker Mysql 容器安装)
Docker 使用的是容器技术,其容器的配置与外界环境相隔离,按照 3.1 命令操作,其内部的Mysql Server_id 依然是 1,所以再进行主从配置配置时,报错… ,说的话比较抽象。大致可以理解为安装的 Docker 容器中有各自的配置环境,与操作系统环境相隔离。
安装详情见 linux 模块中的第三步,安装 Mysql 8.0.12。
这里在Linux 安装 3 个 Mysql 数据库,初始化密码为 root
192.168.26.22:3306 对外映射端口为 3306
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.12 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
192.168.26.23:3306 对外映射端口为 3306
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.12 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
192.168.26.23:3306 对外映射端口为 3307
docker run -p 3307:3306 --name mysql1 -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.12 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
进入正在运行中的 Mysql 容器
docker start mysql # 启动名称为 Mysql 的容器
docker exec -it mysql bash # 以 Bash 进入 Mysql 终端
apt-get update # 更新安装源
apt-get install vim # 安装 vim 命令,Docker 容器没有提供常见命令
vim /etc/mysql/my.cnf # 修改容器中的配置文件
主从文件配置(my.cnf)
192.168.26.22:3306 主数据库配置
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-bin=mysql-bin-22-3306
192.168.26.22:3306 -> 3306 从数据库配置
server-id=2
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-bin=mysql-bin-23-3306
192.168.26.22:3306 -> 3307 从主数据库配置
server-id=3
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-bin=mysql-bin-23-3307
这里使用,mysql 为 docker Mysql 的容器名称,然后之前的操作
docker restart mysql
连续两次
exit 推出容器,或者重新连接一个 Shell 表
重启 mysql 容器
3.2 授权
进入 Mysql 主数据库,进行账号授权,只有获取而二进制 log 日志权利
docker exec -it mysql bash
如果不是 docker 安装的 mysql 直接这一步
mysql -uroot -proot
说一下,登录密码依然是安装镜像的密码,真奇怪
docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.12 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
授权给用户
再来给授权给从数据库服务 192.168.26.0-255,用户mark,密码158262751
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE USER 'mark'@'192.168.26.%' IDENTIFIED WITH mysql_native_password BY '158262751';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mark'@'192.168.26.%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新授权表信息
mysql> flush privileges;
获取主节点当前binary log文件名和位置(position)
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 | 4990 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4. 从数据库配置
server-id=201
innodb_flush_log_at_trx_commit=2
sync_binlog=1
log-bin=mysql-bin-201
4.1 进入数据库,进行操作,可以在Navicat 操作
如果配置了同步的数据库,则在从机数据库上面要有一个和主机配置的数据库一样的数据库.
配置从机连接master
change master to master_host='192.168.26.22',master_user='mark' ,master_password='158262751', master_log_file='mysql-bin-22-3306.000001' ,master_log_pos=450;
参数说明:
- master_host: 主机的ip
- master_user : 主机授权的用户.
- master_password : 主机授权时候填写的密码
- master_log_file : 主机show master status;中的File
- master_log_pos: 主机show master status;中的Position.
开启主从,可以在Navicat 操作
start slave; # 开启主从
stop slave; # 关闭
reset slava; # 重置设置
show master status; # 显示主数据库 log 状态,主数据库查看
查看主从连接状态,可以在Navicat 操作
显示不明显
show slave status\G #
两个连接成功就说明成功了,主从数据库的数据库和表应该保持一致,不一致的话,会有错。
apt-get update
apt-get install vim
vim /etc/mysql/my.cnf
server-id=2
master-host=192.168.1.111
master-user=mstest
master-password=123456
master-port=3306
master-connect-retry=60