Site Overlay

MySQL安装以及配置主从复制

需求

三台服务器上搭建MySQL一主两从主从复制配置

安装环境

实际中是三台实体机上分别部署,这里为了演示截图,采用虚拟机中部署了三个Linux环境,网络设置为桥接模式

准备好安装包

https://downloads.mysql.com/archives/community/ 中选择5.7.32-el7-x86_64.tar.gz

复制至安装环境中

如果是内网环境没有外网yum,可以通过powerShell 中scp指令 先放好压缩包

scp localFile root@192.168.0.106:/usr/

系统配置

配置/etc/hosts
vi /etc/hosts

192.168.253.100  db1
192.168.253.101  db2
192.168.253.102  db3
//换成自己对应的各个ip

关闭防火墙
systemctl stop firewalld        		#停止firewall
systemctl disable firewalld			#禁止firewall开机启动
禁掉Selinux
setenforce 0
sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
修改limits.conf 文件
vi /etc/security/limits.conf
mysql        soft  nproc      65535
mysql        hard  nproc      65535
mysql        soft  nofile     65535
mysql        hard  nofile     65535
创建mysql用户
useradd mysql 
passwd mysql				#密码自行配置,这里用的 password
创建安装目录
mkdir -p  /data/{log,data,tmp,binlog,relaylog}
卸载系统自带的mariadb
rpm -qa|grep mariadb
rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
解压安装文件
tar -xzvf /root/mysql-5.7.32-el7-x86_64.tar.gz -C /
mv /mysql-5.7.32-el7-x86_64 /mysql
chown -R mysql:mysql /mysql /data	     #递归改变文件用户所有权
配置mysql环境变量
cp /mysql/support-files/mysql.server /etc/init.d/mysqld
lfconfig						
echo "PATH=$PATH:/mysql/bin" > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
chkconfig mysqld on
配置my.cnf
vi /etc/my.cnf
#(master和slave只有server_id不同)
[client]
socket=/mysql/mysql.sock
[mysqld] 
port=3306
user=mysql
basedir=/mysql #mysql路径
socket=/mysql/mysql.sock
#加载半同步复制主备插件
plugin-load-add=semisync_master.so
plugin-load-add=semisync_slave.so
symbolic-links=0
log-error=/data/log/mysqld.log
slow_query_log_file=/data/log/slow.log
slow_query_log=1
long_query_time=0.3
#server_id三台分别是1,2,3
server_id=1
#开启gtid模式
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=1
binlog_checksum=NONE
log_slave_updates=ON
log_bin=/data/binlog/binlog
relay_log=/data/relaylog/relaylog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
datadir=/data/data
slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=1
slave_parallel_workers =4  
innodb_file_per_table
sync_binlog = 1
binlog-group-commit-sync-delay=20  
binlog_group_commit_sync_no_delay_count=5  
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = ON
innodb_io_capacity = 5000  
innodb_io_capacity_max=15000
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 200
innodb_spin_wait_delay = 6
innodb_status_file = 1
innodb_purge_threads=4
innodb_undo_log_truncate=1  
innodb_max_undo_log_size=4G
innodb_use_native_aio = 1
innodb_autoinc_lock_mode = 2
log_slow_admin_statements=1
expire_logs_days=7  
character-set-server=utf8mb4
collation-server= utf8mb4_bin
skip-name-resolve
lower_case_table_names
skip-external-locking
max_allowed_packet = 1024M
table_open_cache = 4000
table_open_cache_instances=16
max_connections = 4000
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 1024M
max_heap_table_size = 1024M
innodb_log_files_in_group = 3
innodb_log_file_size = 1024M
innodb_flush_method= O_DIRECT
log_timestamps=SYSTEM
#三个节点auto_increment_offset的值可以跟server_id相同
auto_increment_offset=1
auto_increment_increment=6  
explicit_defaults_for_timestamp
log_bin_trust_function_creators = 1
transaction-isolation = READ-COMMITTED
innodb_buffer_pool_instances=8
innodb_write_io_threads=4  
innodb_read_io_threads=4 
#生产环境增加
innodb_buffer_pool_size=2G  
innodb_flush_log_at_trx_commit=1
#设置从节点应用relaylog后产生binlog
log_slave_updates=1
#设置不自动删除relaylog
#relay_log_purge=0
#设置增强半同步复制参数
rpl_semi_sync_master_wait_point= AFTER_SYNC
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_slave_enabled=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
初始化mysql
/mysql/bin/mysqld --user=mysql --basedir=/mysql --datadir=/data/data --initialize
主从节点都启动mysql服务
启动前先创建mysql日志文件
su - mysql
touch /data/log/mysqld.log
exit
systemctl start mysqld
#确保启动没报错,ps -ef|grep mysql 可以看到进程
修改root密码
#(主从节点均需执行,注意root密码)
root初始密码查找: grep password  /data/log/mysqld.log 
mysql -uroot -pR00T_12344
mysql>SET SQL_LOG_BIN=0;
set password='R00T_12344'; 
FLUSH PRIVILEGES;
use mysql;
update user set host = '%' where user = 'R00T_12344';
flush privileges;
SET SQL_LOG_BIN=1;
#select user,host from mysql.user 可以查看当前已开放
#如查询后root没有开放权限,尝试使用  
GRANT ALL PRIVILEGES ON * . * TO 'root'@'%' IDENTIFIED BY 'R00T_12344' WITH GRANT OPTION;
flush privileges;

主从配置

2个从节点配置复制通道(db2/db3)
mysql>change master to master_host='db1',master_port=3306,master_user='root',master_password='R00T_12344',MASTER_AUTO_POSITION=1;	 

#因为开启了gtid,可以设置MASTER_AUTO_POSITION=1使主从复制自动按照gtid的位置复制

从节点启动复制进程并检查复制状态
#启动slave
mysql>START SLAVE; 
#查看从库的状态
mysql>SHOW SLAVE STATUS\G;

#确保 :
#Slave_IO_Running: Yes 
#Slave_SQL_Running: Yes 

尝试连接过程中会遇到 两个No 的可能是未启动start salve;

如果是connectting yes 的,可能情况就有多种

  1. 首先测试ping db1,能否ping通,如果不能说明hosts设置失败,重新设置;
  2. 查看防火墙以及端口开放情况是否开放;
  3. Change maset指令有误,检查用户名及密码是否正确;
  4. 直接本地远程登录mysql -uroot -pR00T_12344 -hdb1,如果不能再排查远程登录问题;
从节点开启super_read_only (slave只读)
set global super_read_only=1;
主节点创建test库(db1)
mysql>
create database test;
use test;
create table test (id int primary key);
insert into test values(1);

测试主从切换
#从节点db2:(新主节点)
mysql>
stop slave;
set global read_only=0;

#原主节点db1:
mysql>
set global super_read_only=1;
change master to master_host='db2',master_port=3306,master_user='rpl_user',master_password='R00T_12344',MASTER_AUTO_POSITION=1;
start slave;
show slave status \G;

#从节点db3:
mysql>
show slave status \G;
stop slave;
change master to master_host='db2',master_port=3306,master_user='rpl_user',master_password='R00T_12344',MASTER_AUTO_POSITION=1;
start slave;
show slave status \G;

#db2上插入数据,检查db1,db3是否数据同步

重新将主节点切成db1

测试过程略。

后续文章中将会更新MHA高可用以及读写分离相关内容。

本文章仅用于个人记录及学习参考。

1 thought on “MySQL安装以及配置主从复制

发表评论

邮箱地址不会被公开。 必填项已用*标注

A beliving heart is your magic My heart
Copyright © 2022 Diuut. All Rights Reserved. | Catch Vogue by Catch Themes | 蜀ICP备2021011635号-1 | magnet:?xt=urn:btih: