Mysql-01-主从搭建

一、安装Mysql

下载

https://downloads.mysql.com/archives/community/

安装

注意顺序

tar -xvf mysql-8.0.38-1.el9.x86_64.rpm-bundle.tar 
rpm -ivh mysql-community-common-8.0.38-1.el9.x86_64.rpm 
rpm -ivh mysql-community-client-plugins-8.0.38-1.el9.x86_64.rpm 
rpm -ivh mysql-community-libs-8.0.38-1.el9.x86_64.rpm 
rpm -ivh mysql-community-icu-data-files-8.0.38-1.el9.x86_64.rpmvi
rpm -ivh mysql-community-client-8.0.38-1.el9.x86_64.rpm rpm 
dnf install net-tools
dnf install perl
rpm -ivh mysql-community-server-8.0.38-1.el9.x86_64.rpm 

systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log #查看密码进行登录

配置

[root@di-mysql-1 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 服务ID
server-id=1
# binlog 配置 只要配置了log_bin地址 就会开启
log_bin = /var/lib/mysql/mysql_bin
binlog-ignore-db=sys,information_schema,performance_schema
# 日志存储天数 默认0 永久保存
# 如果数据库会定期归档，建议设置一个存储时间不需要一直存储binlog日志，理论上只需要存储归档之后的日志
expire_logs_days = 21
# binlog最大值
max_binlog_size = 2048M
# 规定binlog的格式，binlog有三种格式statement、row、mixad，默认使用statement，建议使用row格式
binlog_format = ROW
# 在提交n次事务后，进行binlog的落盘，0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件，如果是在线交易和账有关的数据建议设置成1，如果是其他数据可以保持为0即可
sync_binlog = 0
# 开启gtid（用于适配MASTER_AUTO_POSITION）
gtid-mode = ON
enforce-gtid-consistency = ON
default_authentication_plugin=mysql_native_password
slow_query_log=1
long_query_time=1 #慢查询时间 超过1秒则为慢查询
slow_query_log_file=/var/lib/mysql/mysql-slow.log
default-storage-engine=InnoDB #默认存储引擎

# 根据实际情况配置,测试直接用默认的
innodb_buffer_pool_size = 45875M 
innodb_buffer_pool_instances = 4 
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G

max_connections = 2000
max_connect_errors = 1000000
connect_timeout = 10
wait_timeout = 300
interactive_timeout = 300
back_log = 300
open_files_limit = 65535
table_open_cache = 16000

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

二、主库配置

创建用户

mysql> create user 'slave'@'%' identified by 'RepliCat*2024%';                                                                                                                                                                                                                                                                                                          
Query OK, 0 rows affected (0.01 sec)                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                        
mysql> grant replication slave on *.* to 'slave'@'%';                                                                                                                                                                                                                                                                                                                   
Query OK, 0 rows affected (0.00 sec)                                                                                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                                                                                                                        
mysql> flush privileges;                                                                                                                                                                                                                                                                                                                                                
Query OK, 0 rows affected (0.00 sec)

三、从库配置

[root@di-mysql-2 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
server-id=2 #服务id,保证集群唯一
read-only=1 #只读

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 开启gtid（用于适配MASTER_AUTO_POSITION）
gtid-mode = ON
enforce-gtid-consistency = ON

innodb_buffer_pool_size = 45875M
innodb_buffer_pool_instances = 4

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

开启复制,记得打开防火墙端口

change master to source_host='10.1.0.40',source_user='slave',source_password='RepliCat*2024%',master_port=3306,MASTER_AUTO_POSITION =1;
start slave;

如果出现异常可以在从库performance_schema里的replication_applier_status_by_worker这张表里面看到异常信息，处理后进行继续同步