mysql主从搭建_mysql8.0.37安装及配置超详细教程

1、下载mysql安装包（都要）

https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.37-linux-glibc2.12-x86_64.tar.xz

mysql-8.0.37-linux-glibc2.12-x86_64.tar.xz

2、执行安装（都要）

tar xvJf mysql-8.0.37-linux-glibc2.12-x86_64.tar.xz
cp -r mysql-8.0.37-linux-glibc2.12-x86_64 /usr/local/ 
cd /usr/local/
mv mysql-8.0.37-linux-glibc2.12-x86_64 mysql
groupadd mysql 
useradd -g mysql -s /sbin/nologin mysql 
chown -R mysql.mysql /usr/local/mysql
mkdir /data
cd /data
mkdir -p /data/{mysqldata,mysqllog,mysqltmp}
chown -R mysql.mysql /data/{mysqldata,mysqllog,mysqltmp}

3、在/data目录，创建my.cnf文件，写入以下内容：（都要，除server-id以外，其他都先不动，server-id需要每台机器不一样）

[mysqld]
datadir=/data/mysqldata/
secure-file-priv=/data/mysqltmp
tmpdir=/data/mysqltmp
log-error=/data/mysqllog/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
#read_only = 1
log_timestamps = SYSTEM
slow_query_log=1
long_query_time=5
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
explicit_defaults_for_timestamp
skip-name-resolve
skip-character-set-client-handshake
skip-host-cache
skip-external-locking
skip-slave-start
innodb_buffer_pool_size = 10240M
innodb_sort_buffer_size = 2048M
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 128M
innodb_log_files_in_group = 4
#innodb_lock_wait_timeout = 200
#innodb_flush_method=O_DIRECT
innodb_adaptive_flushing=ON
innodb_max_dirty_pages_pct=80
innodb_io_capacity=1000
lower_case_table_names=1
wait_timeout=28800
interactive_timeout=28800
character-set-server=utf8
collation-server=utf8_general_ci
log-bin-trust-function-creators=1
max_connections=1024
max_allowed_packet=512M
back_log=500
event_scheduler=on
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G  
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
gtid_mode=ON
enforce_gtid_consistency=TRUE
log-bin=mysql-bin-A
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=1
expire_logs_days=4
binlog_format=row
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
master_info_repository = TABLE
relay_log_info_repository = TABLE
#federated
#slave-skip-errors=1061,1062,1305,1032
 
[mysqldump]
quick
max_allowed_packet            = 1G
[mysql]
no-auto-rehash
socket                        = /usr/local/mysql/mysql.sock
 
[myisamchk]
key_buffer_size               = 20M
sort_buffer_size              = 20M
read_buffer                   = 2M
write_buffer                  = 2M
 
[mysqlhotcopy]
interactive-timeout
mkdir -p /data/{mysqldata,mysqllog,mysqltmp}
chown -R mysql.mysql /data/{mysqldata,mysqllog,mysqltmp}

4、初始化mysql，启动mysql（都要）

# 初始化 如果报错可能是系统和mysql版本不兼容，我这里是centos7最小安装
/usr/local/mysql/bin/mysqld --defaults-file=/data/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysqldata
 
 
# 启动
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/my.cnf --pid-file=/usr/local/mysql/mysqld.pid --datadir=/data/mysqldata &

5、查看密码（都要）

执行如下命令查看日志，找到图中类似位置，为root密码

more /data/mysqllog/mysqld.log

6、登录mysql（都要）

执行如下命令，并输入密码即可

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

7、修改管理员密码（都要）

alter user 'root'@'localhost' identified by '你的管理员密码';

以上是mysql正常搭建步骤，下面开始主从配置

1、在主库上创建一个有权限的用户，用于从库登录主库进行复制

# 在主库上：
create user 'repl'@'192.168.137.%' identified with mysql_native_password by 'Repl@001';
grant replication slave on *.* to 'repl'@'192.168.137.%';
flush privileges;
 
# 查看主库日志名称和读写位置
show master status;
+--------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                        |
+--------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| mysql-bin-A.000002 |     2053 |              | mysql,information_schema,performance_schema,sys | 34bfd5a9-0dae-11ef-ad2d-00155d24fe02:1-8 |
+--------------------+----------+--------------+-------------------------------------------------+------------------------------------------+

2、配置从库的用户信息

# 配置主库地址，账号，日志名，位置
change master to master_host='192.168.137.8',master_user='repl',master_password='Repl@001',master_log_file='mysql-bin-A.000002',master_log_pos=2053,get_master_public_key=1;
 
 
# 启动主从同步
start slave;
 
# 查看主从同步状态，两个yes，文件名和主库一致，然后数据位置一样即可
show slave status;
 

完成