#!/bin/bash
echo "=====系統(tǒng)環(huán)境初始化腳本====="
sleep 3
echo "——>>> 關閉防火墻與SELinux <<<——"
sleep 3
systemctl stop firewalld
systemctl disable firewalld &> /dev/null
setenforce 0
sed -i '/SELINUX/{s/enforcing/disabled/}' /etc/selinux/config
 
echo "——>>> 創(chuàng)建阿里倉庫 <<<——"
sleep 3
rm -rf /etc/yum.repos.d/*
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo 
yum -y install wget
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo  
 
 
echo "——>>> 設置時區(qū)并同步時間 <<<——"
sleep 3
timedatectl set-timezone Asia/Shanghai
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
reboot

sudo yum remove mysql-server -y && sudo yum autoremove -y
sudo yum remove *mysql* -y
sudo rm -rf /var/lib/mysql/ 
sudo rm -rf /etc/mysql/ 

yum install -y yum-utils > /dev/null
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm > /dev/null
yum-config-manager --enable mysql80-community > /dev/null
yum-config-manager --disable mysql57-community > /dev/null
yum install -y mysql-server
systemctl start mysqld && systemctl enable mysqld
mysqladmin -p"`awk '/temporary password/{p=$NF}END{print p}' /var/log/mysqld.log`" password 'TianPFh@123'
mysql -p'TianPFh@123' -e "UNINSTALL COMPONENT 'file://component_validate_password'"
mysqladmin -p'TianPFh@123' password '1234'

# 追加如下配置 

server-id = 1            # 服務器唯一標識，每個服務器在復制環(huán)境中應有唯一的ID，用于標識不同的復制實例

log-bin = mysql-bin      # 啟用二進制日志，指定二進制日志文件的基名，MySQL會在此基名后添加數字和擴展名來創(chuàng)建日志文件

binlog-format = ROW      # 設置二進制日志格式為ROW，記錄每一行數據的變化，有助于減少數據不一致的風險，也便于從庫的并行復制

expire_logs_days = 10    # 二進制日志保留時間，設置二進制日志文件的自動清理周期，這里是10天，根據數據保留和備份策略調整

read-only=0             # 設置服務器是否為只讀模式，0表示關閉只讀模式，允許所有用戶進行寫操作，包括非超級用戶

# binlog-ignore-db =      # 指定不需要復制的數據庫，即不記錄這些數據庫的變更到二進制日志中，多個數據庫用逗號分隔

# binlog_do_db =          # 指定需要復制的數據庫，即只記錄這些數據庫的變更到二進制日志中，多個數據庫用逗號分隔
# 注意：binlog-ignore-db和binlog_do_db不要同時使用，以免產生沖突

systemctl restart mysqld

[root@master ~]# mysql -uroot -p1234  # 使用root用戶登錄MySQL，密碼是1234
mysql: [Warning] Using a password on the command line interface can be insecure.  # 警告：在命令行界面使用密碼可能不安全
Welcome to the MySQL monitor.  Commands end with ; or \g.  # 歡迎信息，命令以;或\g結束
Your MySQL connection id is 8  # MySQL連接ID為8
Server version: 8.0.39 MySQL Community Server - GPL  # 服務器版本信息

Copyright (c) 2000, 2024, 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.  # Oracle是Oracle公司及其附屬公司的注冊商標

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  # 輸入'help;'或'\h'獲取幫助，輸入'\c'清除當前輸入的命令

mysql> CREATE USER 'itit'@'%' IDENTIFIED WITH mysql_native_password BY '123456';  # 創(chuàng)建用戶'itit'，允許從任何主機訪問，并設置密碼為'123456'
Query OK, 0 rows affected (0.01 sec)  # 命令執(zhí)行成功，沒有行受到影響

mysql> GRANT REPLICATION SLAVE ON *.* TO 'itit'@'%';  # 授予用戶'itit'從任何主機進行復制的權限
Query OK, 0 rows affected (0.00 sec)  # 命令執(zhí)行成功，沒有行受到影響

mysql> show master status ;  # 顯示主服務器狀態(tài)，包括二進制日志文件名和位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      660 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)  # 輸出結果，顯示當前使用的二進制日志文件和位置

mysql> exit  # 退出MySQL命令行
Bye  # MySQL命令行退出信息

# 在文件中追加下述配置即可

# 從庫的唯一標識，與主庫和其他從庫不同
server-id = 2

# 啟用二進制日志，以便從庫可以作為其他從庫的主庫
log-bin = mysql-bin

# 設置二進制日志格式為ROW，有助于減少數據不一致的風險
binlog-format = ROW

# 設置從庫為只讀模式，防止在從庫上直接寫入數據導致的數據不一致
read-only = 1

# 設置服務器為只讀模式，超級管理員也無法執(zhí)行寫操作（可選操作）
#super-read-only=1

# 注：從庫開啟二進制文件是可以記錄從庫的數據操作的記錄，也是可選操作，
#如果不開，從庫就不會記錄自己這個庫的操作。

systemctl restart mysqld

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.226.100',
    SOURCE_USER='itit',
    SOURCE_PASSWORD='123456',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=660;

CHANGE MASTER TO 
    MASTER_HOST='192.168.226.100',
    MASTER_USER='itit', 
    MASTER_PASSWORD='123456', 
    MASTER_LOG_FILE='mysql-bin.000001', 
    MASTER_LOG_POS=660;

START REPLICA;

STOP REPLICA;

SHOW REPLICA STATUS\G;

SHOW PROCESSLIST;

START SLAVE;

STOP SLAVE;

SHOW SLAVE STATUS\G;

SHOW PROCESSLIST;

[root@master ~]# mysql -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.04 sec)

[root@slave ~]# mysql -p1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

-- 創(chuàng)建數據庫，如果數據庫不存在
CREATE DATABASE IF NOT EXISTS db01;

-- 切換到剛剛創(chuàng)建的數據庫
USE db01;

-- 創(chuàng)建用戶表
CREATE TABLE tb_user (
    id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL,
    sex VARCHAR(1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入數據
INSERT INTO tb_user (name, sex) VALUES
('Tom', '1'),
('Trigger', '0'),
('Dawn', '1');

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db01               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| tb_user        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from tb_user;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
|  1 | Tom     | 1    |
|  2 | Trigger | 0    |
|  3 | Dawn    | 1    |
+----+---------+------+
3 rows in set (0.00 sec)

server-id = 1
log-bin = mysql-bin
enforce-gtid-consistency = ON
gtid-mode = ON
binlog_format = ROW

sudo systemctl restart mysqld

CREATE USER 'replica'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
ALTER USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

server-id = 2
enforce-gtid-consistency = ON
gtid-mode = ON
log-bin = mysql-bin
relay-log = mysql-relay-bin

sudo systemctl restart mysqld

CHANGE MASTER TO
  MASTER_HOST='192.168.226.100',
  MASTER_USER='replica',
  MASTER_PASSWORD='123456',
  MASTER_AUTO_POSITION=1;

START SLAVE;

SHOW SLAVE STATUS\G

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE test_table (id INT PRIMARY KEY, value VARCHAR(255));
INSERT INTO test_table (id, value) VALUES (1, 'Test Value 1');
INSERT INTO test_table (id, value) VALUES (2, 'Test Value 2');
INSERT INTO test_table (id, value) VALUES (3, 'Test Value 3');
COMMIT;
SHOW MASTER STATUS;

SHOW DATABASES;
USE test_db;
SELECT * FROM test_table;
SHOW SLAVE STATUS\G;