mysql 主从配置

MySQL 主从复制是指在 MySQL 数据库系统中，主服务器（Master）将数据更新操作（如 INSERT、UPDATE、DELETE）复制到从服务器（Slave）。主从复制实现了数据的同步复制，使得从服务器可以保持与主服务器的数据一致。

主从复制的原理

主服务器

主服务器上启用二进制日志（binlog），记录所有修改数据库的操作（如 DML 操作）。
当主服务器有数据更新操作时，相关的操作信息会被记录在 binlog 文件中，文件名一般为 mysql-bin.xxxxxx。

从服务器

从服务器上启用中继日志（relay log）。从服务器从主服务器获取二进制日志。
从服务器通过 IO线程（IO_THREAD）从主服务器读取 binlog 文件并将其存储到中继日志（Relay Log）中。
然后，从服务器的 SQL线程（SQL_THREAD）从中继日志中读取操作并在从服务器上执行这些操作，保持数据同步。
同步方式：

异步复制：主服务器将操作写入 binlog 后立即返回，不等待从服务器确认。
半同步复制：主服务器将操作写入 binlog，然后至少等待一个从服务器确认接收到数据。
同步复制：主服务器将操作写入 binlog 后，所有从服务器执行完成后才返回。

主从复制的工作过程

配置主服务器：

启用 binlog 日志。
创建用于复制的专用账户，给予 REPLICATION SLAVE 权限。
获取主服务器当前的二进制日志位置，以便从服务器从正确的位置开始复制。

配置从服务器：

在从服务器上设置主服务器的连接信息，包含主服务器的地址、端口、复制账号等。
启动从服务器的 IO_THREAD，从主服务器拉取 binlog。
启动 SQL_THREAD，在从服务器上执行中继日志中的 SQL 语句。

1. 配置主服务器

(1)编辑 MySQL 配置文件

[root@localhost ~]# /etc/my.cnf[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysqld.log
binlog-do-db = testdb

(2)创建用于复制的账户

create user rep@'192.168.1.%' identified with mysql_native_password by '123456';
GRANT REPLICATION SLAVE ON *.* TO rep@'192.168.1.%';   
FLUSH PRIVILEGES;
SHOW MASTER STATUS; 
mysql> CREATE USER rep@'192.168.1.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rep@'192.168.1.%';  
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      875 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

（3）重启服务

 [root@localhost ~]# systemctl restart mysqld

2. 配置从服务器

（1）编辑从服务器的 MySQL 配置文件

[root@localhost ~]# /etc/my.cnf [mysqld]
server-id = 2  
relay-log = /var/log/mysql/mysql-relay-bin.log
log-bin = /var/log/mysql/mysql-bin.log

（2）配置从服务器连接主服务器

mysql> CHANGE MASTER TO  MASTER_HOST = '192.168.1.15',-> MASTER_USER = 'rep',-> MASTER_PASSWORD = '123456',-> MASTER_LOG_FILE = 'mysql-bin.000001',-> MASTER_LOG_POS = 12345;
Query OK, 0 rows affected, 8 warnings (0.01 sec)mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.15 sec)

（3）重启 MySQL 服务

[root@localhost ~]# systemctl restart mysqld

（4）验证复制是否成功

//如果 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，说明复制正常

3. 配置第二个从服务器

（1）编辑配置文件

[root@localhost ~]# /etc/my.cnf [mysqld]
server-id = 3 
relay-log = /var/log/mysql/mysql-relay-bin.log
log-bin = /var/log/mysql/mysql-bin.log

（2）配置主服务器信息

mysql> CHANGE MASTER TO  MASTER_HOST = '192.168.1.15',-> MASTER_USER = 'rep',-> MASTER_PASSWORD = '123456',-> MASTER_LOG_FILE = 'mysql-bin.000001',-> MASTER_LOG_POS = 12345;
Query OK, 0 rows affected, 8 warnings (0.01 sec)mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.15 sec)

（3）重启 MySQL 服务

[root@localhost ~]# systemctl restart mysqld

（4）验证复制是否成功

4.验证主从复制是否生效

(1). 在主服务器上执行一些数据操作

mysql> create table test(-> id INT,-> name VARCHAR(255));
Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO test (id, name) VALUES (1, 'test');
Query OK, 1 row affected (0.08 sec)