一、架构说明
MySQL 主从架构是一种常见的数据库部署方案，用于实现数据的自动同步和冗余备份。在该架构中，主服务器负责处理事务的写入操作，从服务器则负责复制主服务器上的数据，并可以用于读取操作，以减轻主服务器的负担。
以下是MySQL主从架构的基本原理和组件：
主服务器（Master）：
主服务器负责处理所有的写入操作和更新操作，包括INSERT、UPDATE、DELETE等。
主服务器上的数据更改被记录到二进制日志（Binary Log）中。
主服务器为从服务器提供数据复制并监控从服务器的连接。
从服务器（Slave）：
从服务器负责从主服务器复制数据，并可以用于处理读取操作，以减轻主服务器的负载。
从服务器连接到主服务器并请求复制主服务器上的二进制日志，然后应用这些日志来保持数据的一致性。
复制流程（Replication Process）：
主服务器将所有更改记录到二进制日志中。
从服务器连接到主服务器并请求从指定位置开始复制二进制日志。
从服务器将从主服务器接收到的二进制日志数据应用到本地数据中，以保持与主服务器的数据一致性。
自动故障切换和负载分担：
当主服务器发生故障或不可用时，可以将一个从服务器提升为新的主服务器，以继续处理写入操作。
通过在不同的从服务器上处理读取操作，可以在多个从服务器之间分担读取负载，提高系统的并发处理能力。
监控和管理：
需要对主从复制状态进行监控，确保数据同步正常运行，及时处理任何复制延迟或错误。
执行定期的备份和恢复策略，确保数据库的可靠性和恢复性。
主从架构提供了一种成本效益高、可靠性好的数据库解决方案，用于提高系统的可用性、性能和数据冗余备份能力。
| 架构 | 操作系统 | IP | 主机名 | 数据库版本 | 端口 | 磁盘空间 | 内存 | CPU |
|---|---|---|---|---|---|---|---|---|
| 主从复制（repl） | centos7.9 | 192.168.111.34 | replserver01 | 8.0.35 | 3307 | 100G | 8G | 4C |
| - | centos7.9 | 192.168.111.35 | replserver02 | 8.0.35 | 3307 | 100G | 8G | 4C |
二、环境准备
两个节点都需要操作。
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 软件包下载
[root@replserver01 soft]# cd /data/soft
[root@replserver01 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 17s
2024-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
[root@replserver01 ~]# 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
[root@replserver01 ~]# 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
[root@replserver01 ~]# 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 replserver02
2.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 依赖包安装
[root@replserver01 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 安装jemalloc、python-3.6.8
我们在日常运维工作中，发现测试环境的mysql服务端偶尔出现内存使用率非常高的情况。
系统中查看mysqld已用内存量远远超出了innodb_buffer_pool_size配置的数倍之多。
另外在问题发生时，查看mysqld中的连接数仅500多，且大部分连接为sleep状态。
也就是说，虽然系统分配了大量的内存给mysqld，但其实际上并不需要用到那么多内存，内存可能产生了碎片，内存没有及时GC。
所以，尝试将mysqld的内存管理器改为使用 jemalloc 来提升内存效能。
jemalloc 是由Jason Evans 在FreeBSD 项目中引入的新一代内存分配器。
它是一个通用的malloc 实现，侧重于减少内存碎片和提升高并发场景下内存的分配效率，其目标是能够替代malloc。
redis目前默认使用的内存管理器就是 jemalloc 。
[root@mgrserver01]# cd /data/soft
[root@mgrserver01 soft]# wget https://github.com/jemalloc/jemalloc/archive/refs/tags/5.3.0.tar.gz
[root@mgrserver01 soft]# tar -zvxf jemalloc-5.3.0.tar.gz -C /data/soft/
[root@mgrserver01 soft]# cd jemalloc-5.3.0
[root@mgrserver01 jemalloc-5.3.0]# ls
autogen.sh config.stamp COPYING jemalloc.pc Makefile.in src
bin config.stamp.in doc jemalloc.pc.in msvc test
build-aux config.status doc_internal lib README TUNING.md
ChangeLog configure include m4 run_tests.sh VERSION
config.log configure.ac INSTALL.md Makefile scripts
[root@mgrserver01 jemalloc-5.3.0]# ./autogen.sh
autoconf
./autogen.sh:行5: autoconf: 未找到命令
Error 0 in autoconf
[root@mgrserver01 jemalloc-5.3.0]# yum -y install autoconf
[root@mgrserver01 jemalloc-5.3.0]# ./configure --prefix=/usr/local/jemalloc --libdir=/usr/local/lib
[root@mgrserver01 jemalloc-5.3.0]# make && make install
[root@mgrserver01 jemalloc-5.3.0]# echo "export LD_PRELOAD=/usr/local/lib/libjemalloc.so" >>/root/.bash_profile
3.3 解压软件包
[root@replserver01 soft]# tar -Jxvf /data1/soft/mysql-8.0.35-linux-glibc2.12-x86_64.tar.xz -C /data1/mysql8.0.35/install/
[root@replserver01 soft]# cd /data1/mysql8.0.35/install/
[root@replserver01 install]# ll
总用量 0
drwxr-xr-x. 9 root root 129 1月 26 16:50 mysql-8.0.35-linux-glibc2.12-x86_64
[root@replserver01 install]# mv mysql-8.0.35-linux-glibc2.12-x86_64/ mysql-8.0.35
[root@replserver01 install]# ls
mysql-8.0.35
3.4 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.sock
character-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-bin
slow_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 = 1
binlog_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 = 7
skip_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 = 1
gtid_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_master=semisync_master.so;rpl_semi_sync_slave=semisync_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-instrument='stage/%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON
performance-schema-consumer-events-transactions-history-long=ON
###### INNODB MONITOR ###### 监控
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
###### 基本参数 ######
#基本参数
#port=
#server_id=
#innodb_buffer_pool_size=
#read_buffer_size=
#read_rnd_buffer_size=
#sort_buffer_size=
#join_buffer_size=
#tmp_table_size=
3.5 初始化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 --user=mysql --log_error_verbosity --explicit_defaults_for_timestamp
3.6 命令执行完毕输出无误，启动数据库
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 &
编写启库脚本
vi /home/mysql/mysql_start.sh
#!/bin/bash
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 &
ln -s /home/mysql/mysql_stop.sh /usr/local/bin/mysql_start
chmod u+x /usr/local/bin/mysql_start
3.6.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 shutdown
编写停库脚本
vi /home/mysql/mysql_stop.sh
#!/bin/bash
/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 shutdown
ln -s /home/mysql/mysql_stop.sh /usr/local/bin/mysql_stop
chmod u+x /usr/local/bin/mysql_stop
3.7 登录数据库
/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
编写登录数据库脚本
vi /home/mysql/mysql_login.sh
#!/bin/bash
/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
ln -s /home/mysql/mysql_login.sh /usr/local/bin/mysql_login
chmod u+x /usr/local/bin/mysql_login
登录密码在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 root@localhost: y-yxoqupe33J
3.8 修改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 -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.35
Copyright (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.
root@localhost: 08:29: [(none)]> alter user 'root'@'localhost' identified by "y-yxoqupe33Z";
Query OK, 0 rows affected (0.01 sec)
四、主从配置
4.1 在主节点中创建主从管理账号
CREATE USER 'repl'@'%' IDENTIFIED with mysql_native_password by 'myttrepl@2222#TO';
GRANT replication slave ON *.* TO 'repl'@'%';
flush privileges;
4.2 在从节点中配置主从同步信息
change master to
master_host='主库IP',
master_port=3307,
master_user='repl',
master_password='myttrepl@2222#TO',
master_auto_position=1;
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; //查看当前的从库状态
4.3 将从库修改为只读模式
MySQL的从库read-only配置非常重要，它能够保证从库的数据一致性和安全性。只读模式下的从库具有一些限制，不允许对数据进行修改操作，但仍然可以执行SELECT查询等读操作。通过合理配置和优化，可以解决从库延迟问题，提高数据库的性能和可用性。
4.3.1 只读模式的限制
在只读模式下，MySQL数据库具有以下限制：
- 不允许执行任何会修改数据的操作，包括INSERT、UPDATE、DELETE等。
- 不允许创建、修改、删除表和索引。
- 不允许创建、修改、删除存储过程和函数。
- 不允许更改表的结构和定义。
- 不允许执行数据导入和导出操作。
可以执行的操作
虽然只读模式下禁止了对数据进行修改的操作，但仍然可以执行以下操作：
- 执行SELECT查询操作。
- 执行SHOW语句和DESCRIBE语句。
- 执行事务的开始和提交操作。
- 执行读取数据的存储过程和函数。
- 执行分析和优化查询语句的操作。
检查MySQL数据库是否处于只读模式或读写模式
SHOW VARIABLES LIKE 'read_only';
这将显示read_only变量的值。如果read_only的值为ON或1，则数据库处于只读模式。如果read_only的值为OFF或0，则数据库处于读写模式。
在MySQL数据库中，read_only 和 super_read_only 是两个系统变量，用于控制数据库的不同读取权限。
-
read_only:
当设置为 ON 时，它阻止了对数据库的所有写操作（例如，INSERT、UPDATE、DELETE 等），但允许 SELECT 读取操作。
此外，read_only 还允许超级用户（例如，拥有 SUPER 权限的用户）执行写操作。
这个设置通常用于维护期间，当你想要阻止常规用户写入数据库，但仍然可以通过超级用户进行管理操作时。 -
super_read_only:
当设置为 ON 时，它阻止了对数据库的所有写操作，并且即使是超级用户也不能执行写操作。
这个设置比 read_only 更严格，因为它完全禁止了所有用户的写操作，包括拥有 SUPER 权限的用户。
super_read_only 通常用于那些需要确保数据库完全不被修改的场景，例如，当将数据库设置为只读模式，并且不希望有任何写操作（包括管理或维护任务）时。
4.3.2 设置为只读模式
要将MySQL数据库设置为只读模式，通过以下不同的方法来实现。以下是两种常见的方法：
方法1：使用SET GLOBAL命令
通过执行以下SQL命令将MySQL服务器设置为只读模式：
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only=ON;
这将立即将read_only系统变量设置为ON，使得数据库进入只读模式。请注意，您需要具有SUPER权限才能执行此命令。
方法2：修改配置文件
另一种方法是修改MySQL的配置文件（通常是my.cnf或my.ini，取决于操作系统和MySQL版本）。在[mysqld]部分下添加或修改read_only选项：
[mysqld]
read_only = ON
保存并关闭配置文件后，您需要重启MySQL服务以使更改生效。
扫一扫
612
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
