sql server 一主两从集群

转载

网络安全侠 2024-10-29 13:17:46

文章标签 sql server 一主两从集群 mysql 主从复制 SQL 文章分类 SQL Server 数据库

1. 主从复制搭建

1.1 环境准备

OS: Ubuntu 18.04master: 192.168.0.3slave: 192.168.0.6

1.2 安装依赖包

# Ubuntuapt-get install -y libaio-dev# CentOSyum install -y libaio-devel

1.3 下载二进制安装包

# https://downloads.mysql.com/archives/community/wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

1.4 解压安装包

tar zxf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mv /usr/local/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.30

1.5 创建 mysql 用户和数据、日志目录

useradd mysql -s /usr/sbin/nologinmkdir -p /data/mysql/3306 /data/mysql/binlog/3306chown -R mysql:mysql /data/mysql

1.6 添加环境变量

echo "export PATH=/usr/local/mysql-5.7.30/bin:$PATH" > /etc/profile.d/mysql.shsource /etc/profile.d/mysql.sh

1.7 初始化数据

mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.30 --datadir=/data/mysql/3306 --user=mysql

1.8 简单配置

# master 节点# cat /etc/my.cnf[mysqld]user=mysqlbasedir=/usr/local/mysql-5.7.30datadir=/data/mysql/3306server_id=3port=3306socket=/tmp/mysql.socklog_bin=/data/mysql/binlog/3306/logbin[mysql]socket=/tmp/mysql.sock# mysqld 启动时加载配置文件的顺序# mysqld --help --verbose | grep my.cnf# /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf# 指定配置文件, 启动 mysqld# /etc/init.d/mysqld start --defaults-file=/etc/my.cnf

# slave 节点# cat /etc/my.cnf[mysqld]user=mysqlbasedir=/usr/local/mysql-5.7.30datadir=/data/mysql/3306server_id=6port=3306socket=/tmp/mysql.sock[mysql]socket=/tmp/mysql.sock

1.9 拷贝启动脚本

cp /usr/local/mysql-5.7.30/support-files/mysql.server /etc/init.d/mysqld

1.10 启动 mysqld

# 第一次启动需用这个命令/etc/init.d.mysqld start# 后面可以用 systemctl 命令管理# 启动/重启/停止 mysqldsystemctl start/restart/stop mysqld# 查看 mysqld 状态systemctl status mysqld

1.11 设置 root 密码

# --initialize-insecure 这个参数初始化后的 mysql 默认是没有密码的, 为了安全需要设置 root 用户密码mysqladmin -uroot password# qq.123

以上操作就是搭建mysql服务，主从节点都要做。

1.12 检查主库是否开启 binlog

# 在主库上操作mysql -uroot -pqq.123 -e "select @@log_bin;"

1.13 主库创建复制用户

# 在主库上操作mysql -uroot -pqq.123 -e "grant replication slave on *.* to repl@'192.168.0.%' identified by '123';"mysql -uroot -pqq.123 -e "select user,host from mysql.user;"

1.14 主库备份并恢复到主库

# 由于我们的数据库都是新搭建的，这一步可以不用操作# 如果生产环境主库开启时间比较早已经有业务数据的情况下这一步是要的# 在主库上操作mysqldump -uroot -pqq.123 -A --master-data=2 --single-transaction > /tmp/all.sqlscp /tmp/all.sql 192.168.0.6:/tmp/# 在从库上操作mysql -uroot -pqq.123 < /tmp/all.sql

1.15 告知从库复制信息

# grep "\-- CHANGE MASTER TO" /tmp/all.sql# -- CHANGE MASTER TO MASTER_LOG_FILE='logbin.000001', MASTER_LOG_POS=154;# help change master tochange master to master_host='192.168.0.3',master_user='repl',master_password='123',master_port=3306,master_log_file='logbin.000001',master_log_pos=154,master_connect_retry=10;

1.16 从库开启复制线程

# 在从库上操作mysql -uroot -pqq.123 -e "start slave;"

1.17 验证主从状态

# 在从库上操作mysql -uroot -pqq.123 -e "show slave status\G" | grep Running:      # Slave_IO_Running: Yes      # Slave_SQL_Running: Yes

1.18 如果以上步骤 12-17 中出现问题，可以执行以下命令重置，然后从新配置 12-17 步。

# 在从库上操作mysql -uroot -pqq.123 -e "stop slave; reset slave all;"

2. 主从复制原理

2.1 主从复制涉及到的资源

2.1.1 文件

2.1.1.1 主库 binlog 文件
2.1.1.2 从库 relay-log.bin 文件，作用是存储接收的 binlog，默认在从库的数据目录下, 手工定义方法：relay_log_basename=/data/mysql/3306/${hostname}-relay-bin
2.1.1.3 从库 master.info 文件，作用是存放连接主库的信息，已经接收的 binlog 位置点信息。默认存储在从库的数据目录下。手工定义方法：master_info_repository=FILE/TABLE
2.1.1.4 从库 relay-log.info 文件，作用是记录回放到的 relay-log 的位置点, 默认存储在从库的数据目录下。手工定义方法：relay_log_info_repository=FILE/TABLE

2.1.2 线程

2.1.2.1 主库 Binlog_dump_Thread 作用是用来接收从库请求，并且投递 binlog 给从库 show processlist; 命令可以查看
2.1.2.2 从库 IO 线程 作用是请求 binlog, 接收 binlog SQL 线程 作用是回放 relay-log show slave status; 命令可以查看

2.2 主从复制原理说明

sql server 一主两从集群_主从复制

2.2.1 S: change master to ip,port,user,password,binlog 位置信息写入到 master.info 中, 执行 slave start; (启动 SQL, IO 线程)。
2.2.2 S: 连接主库
2.2.3 M: 分配 Dump_Thread, 专门和 S_IO 通信。
2.2.4 S: IO 线程请求新日志
2.2.5 M: Dump_T 接收请求, 截取日志, 返回给 S_IO
2.2.6 S: IO 线程接收 binlog, 日志放在 TCP/IP 缓存, 此时网络层返回 ACK 给主库。主库工作完成。
2.2.7 S: IO 线程将 binlog 最终写入到 relay-log 中, 并更新 master.info, IO 线程工作结束。
2.2.8 S: SQL 线程读 relay-log.info, 获取上次执行到的位置点。
2.2.9 S: SQL 线程向后执行新的 relay-log, 再次更新 relay-log.info