Mysql5.7在Linux7下搭建主从架构配置文档

1. 环境介绍

主Ip：10.69.103.55    从ip：10.69.103.56    
mysql包：mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

2. 环境配置

2.1 修改hosts文件

2.2 关闭firewalld

Systemctl stop firewalld.service
Systemctl disable firewalld.service

2.3 关闭numa

修改/etc/default/grup，在GRUB_CMDLINE_LINUX末尾添加numa=off
grub2-mkconfig -o /etc/grub2.cfg

2.4 关闭selinux

3. mysql安装配置

3.1 上传安装包并解压

说明：主从库都执行
tar xvfz mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
cd /usr/local
mv mysql-5.7.28-linux-glibc2.12-x86_64/ mysql

3.2 新建mysql用户

说明：主从库都执行
groupadd mysql
useradd -g mysql -d /usr/local/mysql -s /sbin/nologin -MN mysql

3.3 创建目录并授权

说明：主从库都执行
mkdir -p /data/mysql/{data,logs,tmp}
chown -R mysql.mysql /data

3.4 主库配置文件

/etc/my.cnf
[client]
port            = 3306

[mysql]
auto-rehash
prompt="\\u@\\h [\\d]>"

[mysqld]
####: for global
user                                =mysql                         
basedir                             =/usr/local/mysql/            
datadir                             =/data/mysql/data    
server_id                           =1                        
port                                =3306                          
character_set_server                =utf8                          
explicit_defaults_for_timestamp     =off                           
log_timestamps                      =system
default_time_zone                    ='+8:00'                   
socket                              =/data/mysql/tmp/mysql.sock         
#skip_name_resolve                   =off                        
auto_increment_increment            =1                           
auto_increment_offset               =1                           
lower_case_table_names              =1                           
secure_file_priv                    =  /data/mysql/tmp/                     
open_files_limit                    =65536                       
max_connections                     =1000                         
thread_cache_size                   =64                          
table_open_cache                    =81920                       
table_definition_cache              =4096                        
table_open_cache_instances          =64                          
max_prepared_stmt_count             =1048576                     
log_bin_trust_function_creators     =1
group_concat_max_len                =1024000
####: for binlog
binlog_format                       =row                          
log_bin                             =/data/mysql/logs/mysql-bin    

binlog_rows_query_log_events        =on                     
log_slave_updates                   =on                     
expire_logs_days                    =7                      
binlog_cache_size                   =65536                  
binlog_checksum                     =none                   
sync_binlog                         =1                      
slave-preserve-commit-order         =ON                     

####: for error-log
log_error                           =/data/mysql/logs/error.log              

general_log                         =off                    
general_log_file                    =/data/mysql/logs/general.log            

####: for slow query log
slow_query_log                      =on                     
slow_query_log_file                 =/data/mysql/logs/slow.log               
#log_queries_not_using_indexes       =on                    
long_query_time                     =1.000000      

####: for innodb
innodb_data_file_path                           =ibdata1:100M:autoextend  
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend    
innodb_buffer_pool_filename                     =ib_buffer_pool           
innodb_log_files_in_group                       =3                        
innodb_log_file_size                            =100M
innodb_file_per_table                           =on                       
innodb_online_alter_log_max_size                =128M                     
innodb_open_files                               =65535                    
innodb_page_size                                =16k                      
innodb_thread_concurrency                       =0                        
innodb_read_io_threads                          =4                        
innodb_write_io_threads                         =4                        
innodb_purge_threads                            =4                        
innodb_page_cleaners                            =4                        
innodb_print_all_deadlocks                      =on                       
innodb_deadlock_detect                          =on                       
innodb_lock_wait_timeout                        =20                       
innodb_spin_wait_delay                          =128                      
innodb_autoinc_lock_mode                        =2                        
innodb_io_capacity                              =200                      
innodb_io_capacity_max                          =2000                     
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                       
innodb_stats_persistent                         =on                       
innodb_stats_persistent_sample_pages            =20                       

innodb_change_buffer_max_size                   =25                       
innodb_flush_neighbors                          =1                        
#innodb_flush_method                             =                        
innodb_doublewrite                              =on                       
innodb_log_buffer_size                          =128M                     
innodb_flush_log_at_timeout                     =1                        
innodb_flush_log_at_trx_commit                  =1                        
innodb_buffer_pool_size                         =8598323200               
innodb_buffer_pool_instances                    =4
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                       
innodb_old_blocks_time                          =1000                     
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                       
innodb_random_read_ahead                        =OFF                      
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                       
innodb_buffer_pool_dump_at_shutdown             =ON                       
innodb_buffer_pool_load_at_startup              =ON                       
innodb_flush_method                = O_DIRECT

server-id=55
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4

#GTID
gtid-mode=on
enforce-gtid-consistency=1
log-slave-updates=1

#relay log
skip_slave_start=1

3.5 从库配置文件

与主库配置文件唯一区别是server-id
server-id=56

3.6 数据库初始化

说明：主从库都执行
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql
查看error.log日志是否有错误（warning错误忽略）

3.7 主从库启动数据库

说明：主从库都执行
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &

3.8 登录主库

/usr/local/mysql/bin/mysql -uroot -p -S /data/mysql/tmp/mysql.sock

3.9 主库创建复制账号

grant replication slave on *.* to 'repl'@'10.69.103.56' identified by 'xxxx';
flush privileges;
从库测试连接复制账号是否正常
/usr/local/mysql/bin/mysql -urepl -p'xxxx' -h10.69.103.55

3.10 从库执行

change master to master_host='10.69.103.55',master_user='repl',master_password='xxx',master_port=3306,master_auto_position=1;   

start slave;