[mysqld]
server-id = 1                # 唯一服務(wù)器ID，主從不能相同
log_bin = mysql-bin          # 啟用二進(jìn)制日志
binlog_format = ROW          # 推薦使用ROW格式
binlog_do_db = your_database # 可選，指定要復(fù)制的數(shù)據(jù)庫(kù)
# 如果需要復(fù)制所有數(shù)據(jù)庫(kù)，可以省略 binlog_do_db

systemctl restart mysqld

CREATE USER 'repl'@'%' IDENTIFIED BY 'iflytek';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

SHOW MASTER STATUS;

[mysqld]
server-id = 2                # 不同于主服務(wù)器的唯一ID
relay-log = mysql-relay-bin  # 啟用中繼日志
read_only = 1                # 從服務(wù)器設(shè)為只讀（可選）

CHANGE MASTER TO
MASTER_HOST='192.168.56.101',
MASTER_USER='repl',
MASTER_PASSWORD='iflytek',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=747;

CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='記錄的文件名',
MASTER_LOG_POS=記錄的位置值;

systemctl restart mysqld

CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='記錄的文件名',
MASTER_LOG_POS=記錄的位置值;

START SLAVE;

SHOW SLAVE STATUS\G

-- 創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;
-- 創(chuàng)建老師表
CREATE TABLE teachers (
    teacher_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    hire_date DATE
);
-- 插入老師數(shù)據(jù)
INSERT INTO teachers (name, subject, hire_date) VALUES
('張老師', '數(shù)學(xué)', '2018-03-15'),
('李老師', '語(yǔ)文', '2016-09-01'),
('王老師', '英語(yǔ)', '2020-11-22');
-- 創(chuàng)建班級(jí)表
CREATE TABLE classes (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(50) NOT NULL,
    grade_level VARCHAR(20) NOT NULL,
    head_teacher_id INT,
    FOREIGN KEY (head_teacher_id) REFERENCES teachers(teacher_id)
);
-- 插入班級(jí)數(shù)據(jù)
INSERT INTO classes (class_name, grade_level, head_teacher_id) VALUES
('高一(1)班', '高一', 1),
('高二(3)班', '高二', 2),
('高三(2)班', '高三', 3);
-- 創(chuàng)建學(xué)生表
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    birth_date DATE,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);
-- 插入學(xué)生數(shù)據(jù)
INSERT INTO students (name, gender, birth_date, class_id) VALUES
('張三', '男', '2007-05-12', 1),
('李四', '男', '2006-11-30', 1),
('王小紅', '女', '2007-02-18', 1),
('趙明', '男', '2006-08-22', 2),
('錢芳', '女', '2006-04-05', 2),
('孫莉', '女', '2006-09-15', 2),
('周杰', '男', '2005-12-03', 3),
('吳婷', '女', '2005-07-19', 3),
('鄭浩', '男', '2005-10-28', 3);

SELECT * FROM teachers;

SELECT c.class_id, c.class_name, c.grade_level, t.name AS head_teacher
FROM classes c
JOIN teachers t ON c.head_teacher_id = t.teacher_id;

SELECT s.student_id, s.name, s.gender, s.birth_date
FROM students s
JOIN classes c ON s.class_id = c.class_id
WHERE c.class_name = '高一(1)班';

SELECT c.class_name, COUNT(s.student_id) AS student_count
FROM classes c
LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id;