mysql-5.7.40 主从部署

[root@bre03 opt]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz
[root@bre03 opt]# ls
jdk-8u161-linux-x64.tar.gz  mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz  rh
[root@bre03 opt]# tar -xf mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@bre03 opt]# useradd -M -s /sbin/nologin mysql
[root@bre03 opt]# mkdir /data/mysql/ -p
[root@bre03 opt]# chown -R mysql: /data/mysql
[root@bre03 opt]# cd /usr/local/
[root@bre03 local]# mv mysql-5.7.40-linux-glibc2.12-x86_64/ mysql
[root@bre03 local]# chown -R mysql: /usr/local/mysql/
[root@bre03 local]# vim /etc/my.cnf
[root@bre03 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/data/mysql
[root@bre03 local]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql
[root@bre03 local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@bre03 local]# vim /etc/init.d/mysqld

	basedir=/usr/local/mysql
	datadir=/data/mysql

[root@bre03 local]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 
[root@bre03 local]# grep password /data/mysql/error.log  
2023-02-01T01:46:11.774737Z 1 [Note] A temporary password is generated for root@localhost: #jhw_YlgW22y
[root@bre03 local]# mysql -uroot -p
.Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.40-log

Copyright (c) 2000, 2022, 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:(none) 10:00:11> set password=password('root');
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost:(none) 10:13:43> grant replication slave,replication client on *.* to slave@'192.168.248.%' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.04 sec)



root@localhost:(none) 10:00:16> flush privileges;
Query OK, 0 rows affected (0.04 sec)

root@localhost:(none) 10:00:22> \q
Bye

[root@bre03 ~]# cat /etc/my.cnf

[mysql]
prompt="\\u@\\h:\\d \\r:\\m:\\s> "
no_auto_rehash
default-character-set=utf8

[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
socket=/data/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql
pid-file =/data/mysql/mysql.pid
lower_case_table_names=1
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M
long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/mysql/slow.log
log_error= /data/mysql/error.log
log-bin=mysql-bin
binlog_cache_size=4M
max_binlog_cache_size=96M
max_binlog_size=1024M
binlog_format=row
expire_logs_days=7

gtid_mode = ON #开启gtid模式
enforce_gtid_consistency = ON  #强制gtid一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE

log_slave_updates = ON #从库binlog才会记录主库同步的操作日志
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M

server-id=91 #与从库不一致
innodb_buffer_pool_size=730M
innodb_data_file_path=ibdata1:10M:autoextend

innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M

[mysqld_safe]
open-files-limit=8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

[mysql]
prompt="\\u@\\h:\\d \\r:\\m:\\s> "
no_auto_rehash
default-character-set=utf8

[client]
port = 3306
socket = /data/mysql/mysql.sock
default-character-set=utf8

[mysqld]
port=3306
character-set-server=utf8
socket=/data/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql
pid-file =/data/mysql/mysql.pid
lower_case_table_names=1
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M
long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/mysql/slow.log
log_error= /data/mysql/error.log
log-bin=mysql-bin
binlog_cache_size=4M
max_binlog_cache_size=96M
max_binlog_size=1024M
binlog_format=row
expire_logs_days=7

gtid_mode = ON #开启gtid模式
enforce_gtid_consistency = ON #强制gtid一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE

log_slave_updates = ON #从库binlog才会记录主库同步的操作日志
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M

server-id=92 #与主库不一致
innodb_buffer_pool_size=730M
innodb_data_file_path=ibdata1:10M:autoextend

innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on

[mysqldump]
quick
max_allowed_packet=32M

[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M

[mysqld_safe]
open-files-limit=8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

从库操作
root@localhost:(none) 10:00:22 > change master to
    -> master_host='192.168.248.131' ,
    -> master_user='slave' ,
    -> master_password='slave@123' ,
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

root@localhost:(none) 10:00:22 > start slave ;
Query OK, 0 rows affected (0.00 sec)
root@localhost:(none) 10:00:39> show slave status\G
*************************** 1. row ***************************
···
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
···