MySQL主从的搭建
原理：在半同步复制中，主库写数据到二进制日志且sync,然后一直等待回复确认，当至少一个从库请求二进制日志后写入到中继日志并flush disk,就返回确认（不需要回放应用完日志）
优点：数据零丢失，性能好
缺点：会阻塞master session，非常依赖网络
代表：after sync，原生的半同步
环境
先在两台服务器分别搭建MySQL数据库
一、操作系统的一些配置
添加ip 主机名
echo "10.123.1.11 cwdb1" >> /etc/hosts
cat /etc/hosts
修改资源限制参数
echo "session required pam_limits.so" >> /etc/pam.d/login
cat /etc/pam.d/login
echo "* soft nproc 16384" >> /etc/security/limits.conf
echo "* hard nproc 16384" >> /etc/security/limits.conf
echo "* soft nofile 16384" >> /etc/security/limits.conf
echo "* hard nofile 65536" >> /etc/security/limits.conf
echo "* soft stack 16384" >> /etc/security/limits.conf
echo "* hard stack 32768" >> /etc/security/limits.conf
cat /etc/security/limits.conf
echo "* - nproc 16384" > /etc/security/limits.d/20-nproc.conf
cat /etc/security/limits.d/20-nproc.conf
echo "* soft memlock 3072000" >> /etc/security/limits.conf
echo "* hard memlock 3072000" >> /etc/security/limits.conf
echo "vm.nr_hugepages=300" >> /etc/sysctl.conf
echo "vm.swappiness=10" >> /etc/sysctl.conf
echo "vm.min_free_kbytes=51200" >> /etc/sysctl.conf
grep "net.ipv4.tcp_keepalive_time = 30" /etc/sysctl.conf
if [ $? != 0 ]
then
cat <<EOF>> /etc/sysctl.conf
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.ip_local_port_range = 1024 65000
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 1
net.core.somaxconn = 65000
net.core.netdev_max_backlog = 262144
net.ipv4.tcp_max_orphans = 262144
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_keepalive_time = 30
net.ipv4.tcp_keepalive_probes = 6
net.ipv4.tcp_keepalive_intvl = 5
net.ipv4.tcp_timestamps = 0
EOF
sed -i 's/net.bridge.bridge-nf-call-ip6tables =0/#net.bridge.bridge-nf-call-ip6tables = 0/g' /etc/sysctl.conf
sed -i 's/net.bridge.bridge-nf-call-iptables =0/#net.bridge.bridge-nf-call-iptables = 0/g' /etc/sysctl.conf
sed -i 's/net.bridge.bridge-nf-call-arptables =0/#net.bridge.bridge-nf-call-arptables = 0/g' /etc/sysctl.conf
fi
sysctl -p
设置系统安全策略
① SELinux
echo "SELINUX=disabled" > /etc/selinux/config
echo "SELINUXTYPE=targeted" >> /etc/selinux/config
cat /etc/selinux/config
setenforce 0
②关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
③关闭透明页（大内存开启的前提下）
echo "if test -f /sys/kernel/mm/transparent_hugepage/enabled; then">> /etc/rc.d/rc.local
echo "echo never > /sys/kernel/mm/transparent_hugepage/enabled">> /etc/rc.d/rc.local
echo "fi">> /etc/rc.d/rc.local
echo "if test -f /sys/kernel/mm/transparent_hugepage/defrag; then">> /etc/rc.d/rc.local
echo "echo never > /sys/kernel/mm/transparent_hugepage/defrag">> /etc/rc.d/rc.local
echo "fi">> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local
②、关闭NUMA功能
vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off"
然后执行：
grub2-mkconfig -o /etc/grub2.cfg
shutdown -r now
③配置字符界面
systemctl set-default multi-user.target
systemctl get-default
配置yum源 ------内网二进制安装可以不按照这样做
挂载光驱
mount /dev/cdrom /mnt
配置yum源
cd /etc/yum.repos.d
mkdir bk
mv CentOS* bk/
把后缀.repo的文件都放到bk里，新建00.repo
echo "[EL7]" >> 00.repo
echo "name = linux 7.9 dvd" >> 00.repo
echo "baseurl=file:///mnt" >> 00.repo
echo "gpgcheck=0" >> 00.repo
echo "enabled=1" >> 00.repo
二、开始安装MySQL8.0.29
1--环境检查（查看系统是否自带mariadb，并卸载）
rpm -qa |grep mariadb
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
2--创建用户、组、创建目录
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
mkdir -p /data01/mysql/app
mkdir -p /data01/mysql/data/33306/data
mkdir -p /data01/mysql/log/33306/binlog
mkdir -p /data01/mysql/log/33306/relaylog
mkdir -p /data01/mysql/backup/backup-db
mkdir -p /data01/mysql/backup/backup-tmp
mkdir -p /data01/mysql/backup/backup-binlog
chown -R mysql:mysql /data01/*
3--解压mysql安装包
cd /data01/mysql/app/
tar xvf mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-8.0.29-linux-glibc2.12-x86_64 mysql --做个软链接
--添加到环境变量
vi ~/.bash_profile
PATH=$PATH:/data01/mysql/app/mysql/bin:$HOME/bin
source ~/.bash_profile
4、编辑好my.cnf
主库配置文件10.123.1.11
cd /data01/mysql/data/33306/
vi my.cnf
[client]
port=33306
socket = /data01/mysql/data/33306/mysql.sock
[mysql]
no-beep
prompt="\u@mysqldb \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
server-id=161100
port=33306
user = mysql
bind_address=0.0.0.0
basedir=/data01/mysql/app/mysql
datadir=/data01/mysql/data/33306/data
socket = /data01/mysql/data/33306/mysql.sock
pid-file = /data01/mysql/data/33306/mysql.pid
character-set-server=utf8mb4
autocommit = 1
max_connections = 800
max_connect_errors = 10
default-storage-engine=INNODB
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 1024M
max_heap_table_size = 1024M
max_allowed_packet = 16M
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
table_open_cache=4096
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=240
open_files_limit=65536
table_definition_cache=1400
log-output=FILE
general_log = 0
general_log_file=/data01/mysql/log/33306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/data01/mysql/log/33306/mysql-query.err
long_query_time=10
min_examined_row_limit = 100
log-error=/data01/mysql/log/33306/mysql-error.err
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_bin=/data01/mysql/log/33306/binlog/mysql-binlog
log_bin_index=/data01/mysql/log/33306/binlog/mysql-binlog.index
binlog_format='ROW'
binlog_expire_logs_seconds=604800
binlog_rows_query_log_events=on
binlog_cache_size = 2M
#开启gtid
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery = 1
#无损同步（增强版半同步）
plugin_dir=/data01/mysql/app/mysql/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
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_thread_concurrency = 64
innodb_buffer_pool_size = 500M
innodb_stats_on_metadata=0
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 30
innodb_old_blocks_time=1000
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_open_files=65536
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
log_timestamps=system
default_authentication_plugin=caching_sha2_password
里面的目录位置需要根据自己实际情况修改
从库配置文件10.123.1.12
cd /data01/mysql/data/33306/
vi my.cnf
[client]
port=33306
socket = /data01/mysql/data/33306/mysql.sock
[mysql]
no-beep
prompt="\u@mysqldb \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
server-id=161200
port=33306
user = mysql
bind_address=0.0.0.0
basedir=/data01/mysql/app/mysql
datadir=/data01/mysql/data/33306/data
socket = /data01/mysql/data/33306/mysql.sock
pid-file = /data01/mysql/data/33306/mysql.pid
character-set-server=utf8mb4
autocommit = 1
max_connections = 800
max_connect_errors = 10
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 1024M
max_heap_table_size = 1024M
max_allowed_packet = 16M
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
table_open_cache=4096
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=240
open_files_limit=65536
table_definition_cache=1400
log-output=FILE
general_log = 0
general_log_file=/data01/mysql/log/33306/mysql-general.err
slow_query_log = ON
slow_query_log_file=/data01/mysql/log/33306/mysql-query.err
long_query_time=10
min_examined_row_limit = 100
log-error=/data01/mysql/log/33306/mysql-error.err
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_bin=/data01/mysql/log/33306/binlog/mysql-binlog
log_bin_index=/data01/mysql/log/33306/binlog/mysql-binlog.index
binlog_format='ROW'
binlog_expire_logs_seconds=604800
binlog_rows_query_log_events=on
binlog_cache_size = 2M
master_info_repository = TABLE
relay_log_info_repository = TABLE
#开启gtid
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
binlog_gtid_simple_recovery = 1
relay_log =/data01/mysql/log/33306/relaylog
read_only=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
slave_preserve_commit_order=1
#无损同步（增强版半同步）
plugin_dir=/data01/mysql/app/mysql/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
rpl_semi_sync_master_wait_point = AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_thread_concurrency = 64
innodb_buffer_pool_size = 500M
innodb_stats_on_metadata=0
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 30
innodb_old_blocks_time=1000
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_open_files=65536
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
default_authentication_plugin=caching_sha2_password
5、初始化my.cnf文件
/data01/mysql/app/mysql/bin/mysqld --defaults-file=/data01/mysql/data/33306/my.cnf --initialize --user=mysql --basedir=/data01/mysql/app/mysql --datadir=/data01/mysql/data/33306/data
# remove default my.cnf
rm -f /etc/my.cnf
rm -rf /etc/my.cnf.d
# set the default password
启动MySQL服务
/data01/mysql/app/mysql/bin/mysqld_safe --defaults-file=/data01/mysql/data/33306/my.cnf &
ln -s /data01/mysql/data/33306/my.cnf /etc/my.cnf
更改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootroot';
flush privileges;
三、10.123.1.12从节点同样如此设置
四、开始配置MySQL主从
主从库创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'rep1';
grant replication slave on *.* to 'repl'@'%';
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'rep1';
flush privileges;
从库配置，使slave与master建立连接，从而同步
change master to
master_host='10.123.1.11',
master_port=33306,
master_user='repl',
master_password='rep1',
master_auto_position=1;
start slave;
查看slave信息
root@mysqldb 21:29: [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.123.1.11
Master_User: repl
Master_Port: 33306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: relaylog.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 157
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 161100
Master_UUID: d438c184-f09a-11ec-b450-0800279577b4
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 82064af1-ee18-11ec-aa5b-0800270adb96:1-2,
d5f66f96-f09b-11ec-a8d0-0800270adb96:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
五、测试
主库插入数据：
create database 00db1;
use 00db1;
create table 00bak11 (id int,name varchar(40));
insert into 00bak11 values (1,'00111'),(2,'00112'),(3,'00113'),(4,'00114'),(5,'00115'),(6,'00111');
commit;
select * from 00bak11;
从库查看：
select * from 00db1.00bak11;
到此主从复制搭建完成，也可以去csdn看下，csdn当时发的时候是截图+操作，链接：blog.csdn.net/nzy15854627…
