MySQL主从复制原理:MySQL主从复制是一种常用的数据同步技术，它通过将一个MySQL数据库服务器（主服务器）的数据实时复制到一个或多个从服务器，从而实现数据的备份、读写分离以及高可用性等目标.
基于binlog的主从同步
#主服务器配置
[root@localhost ~]# cat /etc/my.cnf | grep server_id
server_id=1mysql> create user rep@'192.168.238.%' identified with mysql_native_password by 'MySQL@123';
Query OK, 0 rows affected (0.00 sec)mysql> grant replication slave on *.* to rep@'192.168.238.%';
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 | 1002 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)#两台从服务器配置
[root@localhost ~]# cat /etc/my.cnf | grep server_id
server_id=2
#[root@localhost ~]# cat /etc/my.cnf | grep server_id
#server_id=3mysql> change master to-> master_host='192.168.238.135',-> master_user='rep',-> master_password='MySQL@123',-> master_log_file='binlog.000001',-> master_log_pos=1002;
Query OK, 0 rows affected, 8 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> show slave status \G
*************************** 1. row ***************************Slave_IO_State: Master_Host: 192.168.238.135Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000001Read_Master_Log_Pos: 1002Relay_Log_File: localhost-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: binlog.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1002Relay_Log_Space: 157Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 13117Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it).Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: 250211 15:50:38Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)
#验证#主服务器创建表
mysql> create database test;
Query OK, 1 row affected (0.01 sec)#从服务器验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| KW |
| test |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)