1
[mysqld]
2
server-id = 153
3
# 启用二进制日志功能，这是复制的基础
4
log-bin                 = /var/log/mysql/mysql-bin.log
5
# (可选) 设置二进制日志的格式，建议使用ROW格式，可以更好地保证数据一致性
6
binlog_format           = ROW
7
binlog_ignore_db        = mysql

1
-- 创建远程用户
2
CREATE USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'remote';
3
-- 给予复制权限
4
 GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
5

6
-- 刷新权限
7
FLUSH PRIVILEGES;

1
-- 锁定所有表，防止新的数据写入，确保数据一致性
2
FLUSH TABLES WITH READ LOCK;
3

4
-- 查看主服务器状态
5
SHOW MASTER STATUS;

1
Host mysql-slave
2
    HostName 192.168.0.93
3
    User root
4
    Port 22
5
    IdentityFile ~/.ssh/id_rsa

1
Host mysql-master
2
    HostName 192.168.0.153
3
    User root
4
    Port 22
5
    IdentityFile ~/.ssh/id_rsa

1
root@ecs-f95f-0002:~# scp master_backup.sql mysql-slave:~
2
master_backup.sql

1
[mysqld]
2
server-id = 93
3
relay-log       = /var/log/mysql/mysql-relay-bin
4
read-only       = 1
5
# (可选但推荐) 记录从服务器的数据更改到自己的二进制日志，以便将来可以作为其他从服务器的主服务器
6
log-bin         = /var/log/mysql/mysql-bin.log

1
stop slave;
2
CHANGE MASTER TO
3
    MASTER_HOST = '192.168.0.153',
4
    MASTER_USER = 'repl_user',
5
    MASTER_PASSWORD = 'remote',
6
    MASTER_LOG_FILE = 'mysql-bin.000005',
7
    MASTER_LOG_POS = 157;
8
START SLAVE;

1
mysql> show slave status \G;
2
*************************** 1. row ***************************
3
               Slave_IO_State: Waiting for source to send event
4
                  Master_Host: 192.168.0.153
5
                  Master_User: repl_user
6
                  Master_Port: 3306
7
                Connect_Retry: 60
8
              Master_Log_File: mysql-bin.000005
9
          Read_Master_Log_Pos: 1826
10
               Relay_Log_File: mysql-relay-bin.000002
11
                Relay_Log_Pos: 326
12
        Relay_Master_Log_File: mysql-bin.000005
13
             Slave_IO_Running: Yes
14
            Slave_SQL_Running: Yes
15
              Replicate_Do_DB:
16
          Replicate_Ignore_DB:
17
           Replicate_Do_Table:
18
       Replicate_Ignore_Table:
19
      Replicate_Wild_Do_Table:
20
  Replicate_Wild_Ignore_Table:
21
                   Last_Errno: 0
22
                   Last_Error:
23
                 Skip_Counter: 0
24
          Exec_Master_Log_Pos: 1826
25
              Relay_Log_Space: 536
26
              Until_Condition: None
27
               Until_Log_File:
28
                Until_Log_Pos: 0
29
           Master_SSL_Allowed: No
30
           Master_SSL_CA_File:
31
           Master_SSL_CA_Path:
32
              Master_SSL_Cert:
33
            Master_SSL_Cipher:
34
               Master_SSL_Key:
35
        Seconds_Behind_Master: 0
36
Master_SSL_Verify_Server_Cert: No
37
                Last_IO_Errno: 0
38
                Last_IO_Error:
39
               Last_SQL_Errno: 0
40
               Last_SQL_Error:
41
  Replicate_Ignore_Server_Ids:
42
             Master_Server_Id: 153
43
                  Master_UUID: 014654cd-8d83-11f0-b940-fa163e8fe780
44
             Master_Info_File: mysql.slave_master_info
45
                    SQL_Delay: 0
46
          SQL_Remaining_Delay: NULL
47
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
48
           Master_Retry_Count: 86400
49
                  Master_Bind:
50
      Last_IO_Error_Timestamp:
51
     Last_SQL_Error_Timestamp:
52
               Master_SSL_Crl:
53
           Master_SSL_Crlpath:
54
           Retrieved_Gtid_Set:
55
            Executed_Gtid_Set:
56
                Auto_Position: 0
57
         Replicate_Rewrite_DB:
58
                 Channel_Name:
59
           Master_TLS_Version:
60
       Master_public_key_path:
61
        Get_master_public_key: 0
62
            Network_Namespace:
63
1 row in set, 1 warning (0.00 sec)
64

65
ERROR:
66
No query specified

1
-- 创建 UTF8MB4 数据库
2
CREATE DATABASE testdb
3
CHARACTER SET utf8mb4
4
COLLATE utf8mb4_general_ci;
5

6
use testdb;
7
CREATE TABLE user_data (
8
    id INT AUTO_INCREMENT PRIMARY KEY,        -- 主键，自动递增
9
    username VARCHAR(255) NOT NULL,           -- 用户名，最大长度255
10
    comment TEXT CHARACTER SET utf8mb4        -- 评论，支持存储表情符号和其他Unicode字符
11
) ENGINE=InnoDB CHARSET=utf8mb4;
12

13
-- 插入常规数据
14
INSERT INTO user_data (username, comment) VALUES ('Alice', 'Hello, world!');
15

16
-- 插入带表情符号的数据
17
INSERT INTO user_data (username, comment) VALUES ('Bob', 'This is fun! 😊');
18

19
-- 插入中文字符
20
INSERT INTO user_data (username, comment) VALUES ('Charlie', '你好，世界！');
21

22
-- 插入混合内容
23
INSERT INTO user_data (username, comment) VALUES ('David', '中文＋Emoji 🌟😄');