1. 環境準備
1.)修改主機名
[root@MySQL-master ~]# hostnamectl set-hostname mysql-master
[root@mysql-master ~]# hostnamectl set-hostname mysql-slave01
[root@mysql-master ~]# hostnamectl set-hostname mysql-slave02
2.)修改hosts文件
[root@mysql-master ~]# vim /etc/hosts
192.168.10.231 mysql-master
192.168.10.232 mysql-slave01
192.168.10.233 mysql-slave02
3.)系統準備
[root@mysql-master ~]# yum -y install wget vim lrzsz net-tools ntp
#修改yum源
[root@mysql-master ~]# mv /etc/yum.repos.d/centos-Base.repo /etc/yum.repos.d/CentOS-Base.repo_bak
[root@mysql-master ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
[root@mysql-master ~]# yum clean all
[root@mysql-master ~]# yum -y update
[root@mysql-master ~]# yum -y install gcc
# 關閉 防火墻
[root@mysql-master ~]# systemctl stop firewalld
[root@mysql-master ~]# systemctl disable firewalld
# 關閉 Selinux
[root@mysql-master ~]# setenforce 0
[root@mysql-master ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
3.)準備目錄【將最大的磁盤掛載到/data目錄】
[root@mysql-master ~]# mkdir /data/mysql -p
[root@mysql-master ~]# mkdir /data/log/mysql/ -pv
2.mysql5.7安裝
1). 安裝
[root@mysql-master ~]# yum remove mysql-libs
[root@mysql-master ~]# tar -xf mysql-5.7.25-1.el7.x86_64.rpm-bundle.tar
[root@mysql-master ~]# rpm -ivh mysql-community-common-5.7.25-1.el7.x86_64.rpm mysql-community-libs-5.7.25-1.el7.x86_64.rpm mysql-community-client-5.7.25-1.el7.x86_64.rpm mysql-community-server-5.7.25-1.el7.x86_64.rpm mysql-community-devel-5.7.25-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.25-1.el7.x86_64.rpm
[root@mysql-master ~]# chown -R mysql:mysql /data/mysql
[root@mysql-master ~]# chown -R mysql:mysql /data/log/
2). 替換配置文件/etc/my.cnf
備注：從庫請將server-id修改為大于1的整數。
[mysqld]
port = 3306
server-id = 1
datadir = /data/mysql
socket = /dev/shm/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
tmpdir = /dev/shm
innodb_buffer_pool_size = 1228M
sort_buffer_size = 16M
innodb_sort_buffer_size = 32M
join_buffer_size = 32M
key_buffer_size = 128M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
query_cache_type = 1
query_cache_size = 512M
query_cache_limit = 16M
query_cache_min_res_unit = 2k
thread_stack = 256K
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
binlog-format = ROW
# log
log-bin = mysql-master-bin
expire_logs_days = 7
slow_query_log = on
log-error = /data/log/mysql/mysqld.log
long_query_time = 2
log-queries-not-using-indexes = 1
slow_query_log_file = /data/log/mysql/mysqld01_slowquery.log
#GTID
gtid-mode = on
enforce_gtid_consistency = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay_log_recovery = on
sync-master-info = 1
#other
max_connections = 1000
table_open_cache = 4096
skip_name_resolve = 1
lower_case_table_names = 1
log_bin_trust_function_creators = 1
#mode
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#skip-grant-tables
3).設置開機自動啟動
[root@mysql-master ~]# systemctl enable mysqld.service
[root@mysql-master ~]# systemctl start mysqld.service
4). 首次root登錄
[root@mysql-master ~]# cat /data/log/mysql/mysqld.log|grep generated
[Note] A temporary password is generated for root@localhost: xxI07yii>mJt
#冒號后面后數據庫的初始化密碼
5). 給root修改密碼
[root@mysql-master ~]# mysql -S /dev/shm/mysql.sock -uroot -p
#方法一
mysql> SET PASSWORD = PASSWORD('Mayi123@');
mysql> flush privileges;
mysql> exit;
#方法二：
mysql> update user set authentication_string=password('Mayi123@') where user='root' and host='localhost';
mysql> flush privileges;
mysql> exit;
#方法三
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mayi123@' PASSWORD EXPIRE NEVER;
mysql> flush privileges;
mysql> exit;
6). 新密碼再次登錄
[root@mysql-master ~]# mysql -S /dev/shm/mysql.sock -uroot -pMayi123@
3.主從設置
1).創建用于主從同步的賬戶
#主庫執行
[root@mysql-master ~]# mysql -S /dev/shm/mysql.sock -uroot -pMayi123@
mysql> grant replication slave on *.* to 'slave'@'192.168.%.%' identified by 'Slave@0000';
mysql> flush privileges;
mysql> show master logs;
+-------------------------+-----------+
| Log_name | File_size |
+-------------------------+-----------+
| mysql-master-bin.000001 | 177 |
| mysql-master-bin.000002 | 995 |
+-------------------------+-----------+
mysql> show binlog events in 'mysql-master-bin.000002'; #查看二進制日志中的執行命令
2).從庫同步
[root@mysql-slave01 ~]# mysql -S /dev/shm/mysql.sock -uroot -pMayi123@
mysql> reset slave;
mysql> stop slave;
mysql> change master to master_host='192.168.10.231',master_port=3306,master_user='slave',master_password='Slave@0000',master_auto_position = 1;
mysql> start slave;
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.231
Master_User: sync
Master_Port: 4980
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000002
Read_Master_Log_Pos: 995
Relay_Log_File: mysql-slave2-relay-bin.000002
Relay_Log_Pos: 1222
Relay_Master_Log_File: mysql-master-bin.000002
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: 995
Relay_Log_Space: 1436
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: 1
Master_UUID: 95a3f56b-b6e4-11eb-8a6a-3a4c58bd3f13
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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: 95a3f56b-b6e4-11eb-8a6a-3a4c58bd3f13:1-4
Executed_Gtid_Set: 95a3f56b-b6e4-11eb-8a6a-3a4c58bd3f13:1-4,
a00b9b71-b6e4-11eb-8a2f-f6cc164fe846:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
2).查看集群狀態
mysql> show master status;
+-------------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+------------------------------------------+
| mysql-master-bin.000002 | 995 | | | 95a3f56b-b6e4-11eb-8a6a-3a4c58bd3f13:1-4 |
+-------------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 4980 | 1 | a00b9b71-b6e4-11eb-8a2f-f6cc164fe846 |
| 2 | | 4980 | 1 | 9ea77650-b6e4-11eb-8963-a6f44ead4ab1 |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
mysql> show global variables like '%gtid%';
+----------------------------------+---------------------------------------------+
| Variable_name | Value |
+----------------------------------+---------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 3b8e89ca-fe36-11ea-bbba-66ef29902e35:1-2057 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+---------------------------------------------+
8 rows in set (0.01 sec)
4.開機自啟
systemctl enable mysqld
systemctl start mysqld
