导航：首页 > 数据库 >

MySQL设置主从复制

发表于：2024-09-23 作者：千家信息网编辑

千家信息网最后更新 2024年09月23日，主从复制：单向，双向，环形，级联，一主多从双机复制的5种情形1、异步主从（默认常规）2、双写（前段程序对两个数据库同时写，必须两边都落实，程序才返回成功）3、利用外挂软件实现实时主库Binlog日志抓

千家信息网最后更新 2024年09月23日MySQL设置主从复制

主从复制：

单向，双向，环形，级联，一主多从

双机复制的5种情形
1、异步主从（默认常规）
2、双写（前段程序对两个数据库同时写，必须两边都落实，程序才返回成功）
3、利用外挂软件实现实时主库Binlog日志抓取，从而可以在当机的时候补全从库
4、谷歌开发的半同步插件
5、DRBD

主从读写分离
1、通过程序实现（性能，效率最佳，推荐）
php,java等程序可以通过设置多个连接文件轻松实现主从读写分离。
2、通过软件实现读写分离
MySQL-proxy, Amoeba等代理软件也可以实现读写分离功能，但是最好还是程序实现。
3、开发dbproxy
读写分离逻辑图展示：

主从同步原理：
实际上是异步的，即总是主库写完日志，日志才可能被从库应用。
master slave
线程：IO 线程：IO/SQL
开启log-bin 需要设置的启动参数：CHANGE MASTER TO
怎删改会去写log-bin MASTER_HOST='192.168.1.111'
MASTER_PORT=3308
MASTER_USER='alrinrep'
MASTER_PASSWORD='password123'
MASTER_LOG_FILE='mysql-bin.000047'
MASTER_LOG_POS='5632'
开启同步方式：start slave

                从库IO向主库发起复制请求，主库开始对从库认证，通过后主库IO根据从库                日志要求开始发送bin-log到从库relay-log。一个周期后，从库更新master.info                ，并重新向主库发起请求。                SQL线程从relay-log发现新数据，并写到slave的数据文件和日志系统。                相关语句：--master-data=1,                CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=1191;建立相关的账号，即alrinrep并授予应有的权限

MySQL主从复制原理过程
1、Slave服务器上执行start slave，开启主从复制开关
2、Slave服务器的IO线程会通过在Master上授权的复制用户权限请求连接Master服务器，并请求从指定的bin-log日志文件指定位置
（change master to 命令相关参数）之后发送binlog日志
3、Master服务器接受到来自Slave服务器的IO线程的请求后，Master服务器上负责复制IO线程根据Slave服务器的IO线程请求的信息
读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志的内容外，
还有本次返回日志内容后在Master服务器端的新的binlog文件名称以及在binlog中的下一个指定更新位置
4、当Slave服务器的IO线程获取到来自Master服务器上的IO线程发送的日志以及日志文件及位置点后，将binlog日志内容一次写入到
slave端的Relay log（中继日志）文件（MySQL-relay-bin.xxxxxx）的最后端，并将新的binlog文件名和位置记录到master-info文件
中，以便下一次读取Master端新Binlog日志时能够告诉Master服务器需要从新binlog日志的那个文件哪个文件开始新的binlog内容。
5、Slave服务器端的SQL线程会实时的检测本地relay log中新增的日志内容，然后及时把LOG文件中的内容解析成MASTER端曾经执行过
的SQL语句，并在自身SLAVE服务器上安顺序执行这些SQL。应用玩后清理应用过的日志。
6，经过了上述过程，就可以确保MASTER和SLAVE执行了同样的SQL。在复制正常的情况下，MASTER和SLAVE数据是完全一致的。

主从复制上机实验：

0，环境：3308/3309同机，IP为192.168.199.151
1，修改my.cnf，检查server-id和log-bin，主从server-id必须不同。主库必须开启bin-log
2，添加复制账号和对应的 replication slave 权限

    mysql> select user,host,password from user;    +-------+-----------+-------------------------------------------+    | user  | host      | password                                  |    +-------+-----------+-------------------------------------------+    | root  | localhost | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |    | alrin | %         | *A0F874BC7F54EE086FCE60A37CE7887D8B31086B |    +-------+-----------+-------------------------------------------+    2 rows in set (0.00 sec)

    mysql> grant replication slave on *.* to alrinrep@'192.168.199.%' identified by 'password123';    Query OK, 0 rows affected (0.00 sec)    mysql> flush privileges;    Query OK, 0 rows affected (0.00 sec)    mysql> show variables like 'log_bin';    +---------------+-------+    | Variable_name | Value |    +---------------+-------+    | log_bin       | ON    |    +---------------+-------+    1 row in set (0.00 sec)    mysql>

3，主库上做备份。先锁表，再备份

    mysql> flush table with read lock;    Query OK, 0 rows affected (0.03 sec    mysql> show master status;    +------------------+----------+--------------+------------------+-------------------+    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |    +------------------+----------+--------------+------------------+-------------------+    | mysql-bin.000004 |      423 |              |                  |                   |    +------------------+----------+--------------+------------------+-------------------+    1 row in set (0.00 sec)

新开session窗口后执行备份：

[root@localhost backup]# mysqldump -uroot -ppassword123 -S /data/mysqldata/3308/mysql.sock -A -B --events --master-data=2| gzip > /data/mysqldata/backup/rep.sql.gz

备份后检查：

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 |      423 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

解锁表：

mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)mysql>

4，从库操作

    mysql> change master to         -> MASTER_HOST='192.168.199.151',        -> MASTER_PORT=3308,        -> MASTER_USER='alrinrep',         -> MASTER_PASSWORD='password123',         -> MASTER_LOG_FILE='mysql-bin.000004',         -> MASTER_LOG_POS=423;    Query OK, 0 rows affected, 2 warnings (0.05 sec)

5,开启复制

    mysql> start slave;    Query OK, 0 rows affected (0.25 sec)

6，检查slave状态：

mysql> show slave status\G    *************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event              Master_Host: 192.168.199.151              Master_User: alrinrep              Master_Port: 3308            Connect_Retry: 60              Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 423               Relay_Log_File: mysql-relay-bin.000002            Relay_Log_Pos: 283        Relay_Master_Log_File: mysql-bin.000004             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:            Replicate_Ignore_Table:           Replicate_Wild_Do_Table:       Replicate_Wild_Ignore_Table:                Last_Errno: 0               Last_Error:              Skip_Counter: 0          Exec_Master_Log_Pos: 423              Relay_Log_Space: 456              Until_Condition: None               Until_Log_File:             Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0    Master_SSL_Verify_Server_Cert: No            Last_IO_Errno: 0            Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:       Replicate_Ignore_Server_Ids:              Master_Server_Id: 1              Master_UUID: 16b758c5-2455-11e9-8fc6-080027339667             Master_Info_File: /data/mysqldata/3309/data/master.info                SQL_Delay: 0          SQL_Remaining_Delay: NULL          Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400              Master_Bind:           Last_IO_Error_Timestamp:          Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:             Auto_Position: 0    1 row in set (0.00 sec)    mysql>

7，尝试在主库DDL，检查复制状态
主库：

    mysql> create database alrin;    Query OK, 1 row affected (0.00 sec)    从库：    mysql> show databases;    +--------------------+    | Database           |    +--------------------+    | information_schema |    | alrin              |    | mysql              |    | performance_schema |    | test               |    +--------------------+    5 rows in set (0.00 sec)

命令汇总：
1，grant replication slave on . to alrinrep@'192.168.199.%' identified by 'password123';
2，flush table with read lock;
3，show master status;
4，unlock tables;
5，change master to
MASTER_HOST='192.168.199.151',
MASTER_PORT=3308,
MASTER_USER='alrinrep',
MASTER_PASSWORD='password123',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=423;
6，start slave;
7，show slave status\G

8, 关于log_slave_updates：
默认情况下，从库的SLAVE SQL读了replay_bin日志并应用后，数据库就有了内容，但是并没有去写自己的BINLOG，也就是说，如果slave要再去拖一个slave，所谓的级联复制，是会失败的，因为第二个slave会去读第一个slave的binlog。那么有什么办法让第一个SLAVE写binlog呢，办法就是开启log_slave_updates。

很赞哦！