优秀网站建设报价,wordpress图片存储方案,房地产行情最新消息,网页搜索关键词一、架构说明
MySQL 主从架构是一种常见的数据库部署方案#xff0c;用于实现数据的自动同步和冗余备份。在该架构中#xff0c;主服务器负责处理事务的写入操作#xff0c;从服务器则负责复制主服务器上的数据#xff0c;并可以用于读取操作#xff0c;以减轻主服务器的…一、架构说明
MySQL 主从架构是一种常见的数据库部署方案用于实现数据的自动同步和冗余备份。在该架构中主服务器负责处理事务的写入操作从服务器则负责复制主服务器上的数据并可以用于读取操作以减轻主服务器的负担。 以下是MySQL主从架构的基本原理和组件
主服务器Master
主服务器负责处理所有的写入操作和更新操作包括INSERT、UPDATE、DELETE等。 主服务器上的数据更改被记录到二进制日志Binary Log中。 主服务器为从服务器提供数据复制并监控从服务器的连接。
从服务器Slave 从服务器负责从主服务器复制数据并可以用于处理读取操作以减轻主服务器的负载。 从服务器连接到主服务器并请求复制主服务器上的二进制日志然后应用这些日志来保持数据的一致性。
复制流程Replication Process 主服务器将所有更改记录到二进制日志中。 从服务器连接到主服务器并请求从指定位置开始复制二进制日志。 从服务器将从主服务器接收到的二进制日志数据应用到本地数据中以保持与主服务器的数据一致性。
自动故障切换和负载分担 当主服务器发生故障或不可用时可以将一个从服务器提升为新的主服务器以继续处理写入操作。 通过在不同的从服务器上处理读取操作可以在多个从服务器之间分担读取负载提高系统的并发处理能力。
监控和管理 需要对主从复制状态进行监控确保数据同步正常运行及时处理任何复制延迟或错误。 执行定期的备份和恢复策略确保数据库的可靠性和恢复性。
主从架构提供了一种成本效益高、可靠性好的数据库解决方案用于提高系统的可用性、性能和数据冗余备份能力。
架构操作系统IP主机名数据库版本端口磁盘空间内存CPU主从复制replcentos7.9192.168.111.34replserver018.0.353307100G8G4C-centos7.9192.168.111.35replserver028.0.353307100G8G4C
二、环境准备
两个节点都需要操作。
2.1 在data目录下创建数据库相应目录
mkdir -p /data1/soft
mkdir -p /data1/mysql8.0.35/3307
mkdir -p /data1/mysql8.0.35/install
cd /data1/mysql8.0.35/3307
mkdir {data,binlog,logs,conf,relaylog,tmp}2.2 软件包下载
[rootreplserver01 soft]# cd /data/soft
[rootreplserver01 soft]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz
--2024-01-25 16:59:42-- https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz
正在解析主机 downloads.mysql.com (downloads.mysql.com)... 23.64.178.143, 2600:140b:2:5ad::2e31
正在连接 downloads.mysql.com (downloads.mysql.com)|23.64.178.143|:443... 已连接。
已发出 HTTP 请求正在等待回应... 302 Moved Temporarily
位置https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz [跟随至新的 URL]
--2024-01-25 16:59:43-- https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 96.7.189.131, 2600:140b:2:593::1d68, 2600:140b:2:58f::1d68
正在连接 cdn.mysql.com (cdn.mysql.com)|96.7.189.131|:443... 已连接。
已发出 HTTP 请求正在等待回应... 200 OK
长度617552732 (589M) [text/plain]
正在保存至: “mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz”100%[] 617,552,732 4.28MB/s 用时 2m 17s2024-01-25 17:02:02 (4.30 MB/s) - 已保存 “mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz” [617552732/617552732])2.3 设置/etc/security/limits.conf、 /etc/sysctl.conf、/etc/hosts [rootreplserver01 ~]# cat /etc/security/limits.conf
...
mysql soft nproc 16384
mysql hard nproc 16384
mysql soft nofile 65536
mysql hard nofile 65536
mysql soft stack 1024
mysql hard stack 1024[rootreplserver01 ~]# cat /etc/sysctl.conf....
kernel.sysrq 1
#basic setting
#net.ipv6.conf.all.disable_ipv6 1
#net.ipv6.conf.default.disable_ipv6 1
kernel.pid_max 524288
fs.file-max 6815744
fs.aio-max-nr 1048576
kernel.sem 500 256000 250 8192
net.ipv4.ip_local_port_range 9000 65000
net.ipv4.tcp_syncookies 1
net.ipv4.tcp_tw_reuse 1
#net.ipv4.tcp_tw_recycle 1
net.ipv4.tcp_fin_timeout 30
net.ipv4.tcp_keepalive_time 1800
net.ipv4.tcp_retries2 5
net.core.rmem_default 262144
net.core.rmem_max 16777216
net.core.wmem_default 262144
net.core.wmem_max 16777216
net.ipv4.tcp_rmem 4096 87380 16777216
net.ipv4.tcp_wmem 4096 65536 16777216
net.ipv4.tcp_max_syn_backlog 8192
net.core.somaxconn 4096
net.core.netdev_max_backlog 3000
vm.swappiness 0
net.ipv6.conf.eth0.disable_ipv6 1
vm.swappiness 1[rootreplserver01 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.111.33 replserver01
192.168.111.34 replserver022.4 创建mysql用户
groupadd mysql
useradd -g mysql -d /home/mysql mysql
echo 777dba_test | passwd --stdin mysql
chown -R mysql:mysql /data1
chmod -R 775 /data1三、搭建基础库
两个节点都操作。
3.1 依赖包安装
[rootreplserver01 soft]# yum -y install make cmake libaio numactl.x86_64 gcc openssl-devel bzip2-devel expat-devel gdbm-devel readline-devel sqlite-devel zlib* 3.2 解压软件包
[rootreplserver01 soft]# tar -Jxvf /data1/soft/mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz -C /data1/mysql8.0.35/install/
[rootreplserver01 soft]# cd /data1/mysql8.0.35/install/
[rootreplserver01 install]# ll
总用量 0
drwxr-xr-x. 9 root root 129 1月 26 16:50 mysql-8.0.35-linux-glibc2.12-x86_64
[rootreplserver01 install]# mv mysql-8.0.35-linux-glibc2.12-x86_64/ mysql-8.0.35
[rootreplserver01 install]# ls
mysql-8.0.353.3 my.cnf文件配置
注意两节点的server-id是不一致的。
view /data1/mysql8.0.35/3307/conf/my.cnf
[client]
port 3307
socket /data1/mysql8.0.35/3307/tmp/mysql.sock[mysqldump]
quick
max_allowed_packet 32M[mysql]
prompt\u\h: \R:\m:\s [\d]
no-auto-rehash[mysqld]
###### BASIC SETTINGS ######
server-id 343307
user mysql
port 3307
basedir /data1/mysql8.0.35/install
tmpdir /data1/mysql8.0.35/3307/tmp
datadir /data1/mysql8.0.35/3307/data
pid-file /data1/mysql8.0.35/3307/tmp/mysql.pid
socket /data1/mysql8.0.35/3307/tmp/mysql.sockcharacter-set-server utf8mb4
transaction_isolation READ-COMMITTED
explicit_defaults_for_timestamp 1
max_allowed_packet 32M
open_files_limit 65535
sql_mode NO_ENGINE_SUBSTITUTION
group_concat_max_len 1024000
lock_wait_timeout 120
skip_name_resolve 1
#不区分大小写
lower-case-table-names 1
default_time_zone 8:00 #沿用5.7的密码认证
default_authentication_plugin mysql_native_password
log_bin_trust_function_creators 1###### CACHES AND CONNECTS SESSION ######
table_definition_cache 2048
table_open_cache 10240
table_open_cache_instances 16
read_buffer_size 4M
read_rnd_buffer_size 8M
sort_buffer_size 8M
join_buffer_size 8M
tmp_table_size 16M
max_heap_table_size 64M
thread_cache_size 128
thread_stack 512K
key_buffer_size 4M
max_length_for_sort_data 8096
bulk_insert_buffer_size 4M
###### CONNECTION SETTINGS ######
interactive_timeout 300
wait_timeout 300
max_connections 3000
max_user_connections 0
max_connect_errors 1000
back_log 2048###### LOG SETTINGS ######
log-error /data1/mysql8.0.35/3307/logs/error.log
slow_query_log_file /data1/mysql8.0.35/3307/logs/slow.log
log-bin /data1/mysql8.0.35/3307/binlog/mysql-bin
relay-log /data1/mysql8.0.35/3307/relaylog/mysql-relay-binslow_query_log 1
long_query_time 1
log_queries_not_using_indexes 1
log_throttle_queries_not_using_indexes 60
min_examined_row_limit 1000
log_slow_admin_statements 1
log_slow_slave_statements 1binlog_format row
binlog_row_image full
binlog_cache_size 4M
max_binlog_cache_size 2G
max_binlog_size 1G
# binlog过期时间8.0已废弃
# expire_logs_days 7skip_slave_start 1
max_relay_log_size 500M
relay_log_recovery 1
relay-log-purge 1
master_info_repository TABLE
relay_log_info_repository TABLE
log_timestamps SYSTEM
binlog_checksum 1gtid_mode on
log_slave_updates 1
enforce_gtid_consistency 1
binlog_gtid_simple_recovery 1#log security
sync_binlog 1
innodb_flush_log_at_trx_commit 1
#sync_binlog 1000
#innodb_flush_log_at_trx_commit 2
###### REPLICATION SETTINGS ###### 并行复制
slave_parallel_type LOGICAL_CLOCK
slave_parallel_workers 4
slave_preserve_commit_order on
#binlog-transaction-dependency-tracking commit_order
#如果在同一台主机部署多个实例并启动增强半同步需指定以下参数
mysqlx_socket/data1/mysql8.0.35/3307/tmp/mysqlx.sock
mysqlx_port 13307###### semi sync replication settings ###### 增强半同步复制
plugin_dir/data1/mysql8.0.35/install/lib/plugin
plugin_load rpl_semi_sync_mastersemisync_master.so;rpl_semi_sync_slavesemisync_slave.so
loose_rpl_semi_sync_master_enabled 1
loose_rpl_semi_sync_slave_enabled 1
loose_rpl_semi_sync_master_timeout 5000
###### INNODB SETTINGS ######
#企业级部署可以直接初始化10G
innodb_buffer_pool_size 7G
innodb_buffer_pool_instances 8
innodb_buffer_pool_load_at_startup 1
innodb_buffer_pool_dump_at_shutdown 1
#innodb_data_file_path ibdata1:128M;ibdata2:128M:autoextend
innodb_lru_scan_depth 4096
innodb_lock_wait_timeout 50
innodb_io_capacity 10000
innodb_io_capacity_max 15000
innodb_flush_method O_DIRECT
#innodb_undo_tablespaces已废弃innodb默认创建2个undo表空间如果需要更多后期使用CREATE UNDO TABLESPACE命令进行添加
#innodb_undo_tablespaces 127
innodb_max_undo_log_size 4G
innodb_undo_log_truncate 1
innodb_flush_neighbors 0
innodb_log_file_size 2G
innodb_log_files_in_group 5
innodb_log_buffer_size 32M
#在mysql8.0已废弃innodb_large_prefix
#innodb_large_prefix 1
innodb_thread_concurrency 0
innodb_print_all_deadlocks 1
innodb_strict_mode 1
innodb_purge_threads 4
innodb_write_io_threads 8
innodb_read_io_threads 8
innodb_page_cleaners 8
innodb_sort_buffer_size 32M
innodb_file_per_table 1
innodb_stats_persistent_sample_pages 64
innodb_autoinc_lock_mode 2
innodb_online_alter_log_max_size 4G
innodb_open_files 65535
innodb_checksum_algorithm crc32
innodb_rollback_on_timeout 1
#内部临时表的存储引擎在mysql8.0.16中已废弃
#internal_tmp_disk_storage_engine InnoDB
innodb_status_file 1
innodb_status_output 0
innodb_status_output_locks 0
innodb_sync_spin_loops 100
innodb_spin_wait_delay 30
innodb_flush_sync 0
innodb_max_dirty_pages_pct 50
innodb_stats_on_metadata 0
###### some var for MySQL 8 ######
log_error_verbosity 3
innodb_print_ddl_logs 1
#binlog过期时间604800秒 即7天
binlog_expire_logs_seconds 604800
innodb_dedicated_server OFF###### PERFORMANCE_SCHEMA SETTINGS ######
performance_schema 1
performance_schema_instrument %on
performance_schema_digests_size 40000
performance_schema_max_table_instances 40000
performance_schema_max_sql_text_length 4096
performance_schema_max_digest_length 4096
performance-schema-instrumentstage/%ON
performance-schema-consumer-events-stages-currentON
performance-schema-consumer-events-stages-historyON
performance-schema-consumer-events-stages-history-longON
performance-schema-consumer-events-transactions-history-longON###### INNODB MONITOR ###### 监控
innodb_monitor_enablemodule_innodb
innodb_monitor_enablemodule_server
innodb_monitor_enablemodule_dml
innodb_monitor_enablemodule_ddl
innodb_monitor_enablemodule_trx
innodb_monitor_enablemodule_os
innodb_monitor_enablemodule_purge
innodb_monitor_enablemodule_log
innodb_monitor_enablemodule_lock
innodb_monitor_enablemodule_buffer
innodb_monitor_enablemodule_index
innodb_monitor_enablemodule_ibuf_system
innodb_monitor_enablemodule_buffer_page
innodb_monitor_enablemodule_adaptive_hash###### 基本参数 ######
#基本参数
#port
#server_id#innodb_buffer_pool_size
#read_buffer_size
#read_rnd_buffer_size
#sort_buffer_size
#join_buffer_size
#tmp_table_size3.4 初始化mysql数据库
/data1/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqld --defaults-file/data1/mysql8.0.35/3307/conf/my.cnf --initialize --usermysql --log_error_verbosity --explicit_defaults_for_timestamp3.5 命令执行完毕输出无误启动数据库 nohup /data1/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqld_safe --defaults-file/data1/mysql8.0.35/3307/conf/my.cnf 3.5.1 停库命令
/data1/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqladmin --defaults-file/data1/mysql8.0.35/3307/conf/my.cnf -uroot -p shutdown3.6 登录数据库 /data1/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysql --defaults-file/data1/mysql8.0.35/3307/conf/my.cnf -uroot -p登录密码在error.log里
grep password /data1/mysql8.0.35/3307/logs/error.log 2024-01-30T07:44:10.157776-00:00 6 [Note] [MY-010454] [Server] A temporary password is generated for rootlocalhost: y-yxoqupe33J3.7 修改Mysql的root用户密码以及打开远程连接 /data1/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysql --defaults-file/data1/mysql8.0.35/3307/conf/my.cnf -uroot -pWelcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35Copyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.rootlocalhost: 08:29: [(none)] alter user rootlocalhost identified by y-yxoqupe33Z;
Query OK, 0 rows affected (0.01 sec)四、主从配置
4.1 在主节点中创建主从管理账号
CREATE USER repl% IDENTIFIED with mysql_native_password by myttrepl2222#TO;
GRANT replication slave ON *.* TO repl%;flush privileges;4.2 在从节点中配置主从同步信息
change master to
master_host主库IP,
master_port3307,
master_userrepl,
master_passwordmyttrepl2222#TO,
master_auto_position1;start slave; //启动slave进程--查看slave状态
确认IO线程、SQL线程都以运行
mysql show slave status\G; //查看当前的从库状态Slave_IO_Running: Yes //IO线程已运行Slave_SQL_Running: Yes //SQL线程已运行mysql show slave status\G; //查看当前的从库状态
