**前置文章:[MySQL YUM安装教程](https://www.xiaoleizhang.com/index.php/archives/70/ "MySQL YUM安装教程")** **环境配置** |服务器|IP地址| | :------------: | :------------: | |Master数据库|10.60.60.11| |Slave数据库|10.60.60.12| ------------ ------------ # 一、MySQL主从复制作用及原理 ## 1、MySQL主从复制用途 - 实时灾备,故障切换。当主库发生故障时,可以快速切到某个从库提升为主库,因为正常情况下数据一样,不会影响系统运行。 - 架构扩展,读写分离。当 MySQL 主库服务器请求压力大时,将读请求流量切到各个从库,降低主库压力,形成读写分离架构。 ## 2、主从部署必要条件 - 主库开启 binlog 日志 - 主从 server_id 不同 - 从库服务器能连通主库 - 主从版本一致 ## 3、Mysql支持哪些复制 其实MySQL的复制模式和binlog的录入格式是一样的,主要分为以下三种 - statement-based(基于语句的复制) 当使用 statement-based 方式,Master 将修改数据的 SQL 语句写入 binlog 中,slave 获取binlog 文件后执行 SQL 语句持久化; **优点:** 1)binlog 日志文件相对较小。当 update or delete 时,存入的是 SQL 语句,不是更新 或删除的逐行数据。日志文件小,从库获取 binlog I/O 读取效率高,同时使用 binlog 恢复数据会更快。 2)binlog 含有所有的修改 SQL 语句,可以做 SQL 审计; **缺点:** 有些 DML 语句操作不确定,不能使用 statement-based 方式记录。比如 UUID(),SYSDATE() 等,从库执行 binlog时,跟主库的值不一致,导致主库和从库数据不一致。 可以参考官网记录,哪些不能使用 statement-based 记录格式。 - row-based(基于行的复制) 当使用 row-based 方式,Master 会将表中每一行的修改记录写入到 binlog 文件中, slave 获取 binlog 文件后逐行执行; **优点:** 1)所有的改变都可以写入到日志,是最安全的方式; 2)对于任何 INSERT/UPDATE/DELETE操作,row-based 方式需要更少的行锁; **缺点:** 1)记录更多的日志文件,同时 binlog 日志写入会被锁住,如果数据量太大,会导致性能问题; 2)日志量大,主从复制 I/O 慢,恢复数据,花费更多时间; - mixed(混合类型的复制) MySQL 可以混合使用 statement-based 和 row-based 两种方式(MIXED FORMAT), 在记录日志时,MySQL 会选择最合适的方式记录日志,比如,alter table,采用 statement-based,update 采用 row-based,由具体的存储引擎和执行语句决定。 ## 4、MySQL主从复制原理 1. master服务器将数据的改变都记录到二进制binlog日志中,只要master上的数据发生改变,则将其改变写入二进制日志 2. salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/O Thread请求master二进制事件 3. 同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中 4. 从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致 5. 最后I/O Thread和SQL Thread将进入睡眠状态,等待下一次被唤醒 需要理解: 1)从库会生成两个线程,一个I/O线程,一个SQL线程; 2)I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中; 3)主库会生成一个log dump线程,用来给从库I/O线程传binlog; 4)SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行; ## 5、MySQL主从复制流程图 1. master将操作语句记录到binlog日志中 1. salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变 1. salave开启两个线程:IO线程和SQL线程 1)*IO线程:*负责读取master的binlog内容到中继日志relay log里; 2)*SQL线程:*负责从relay log日志里读出binlog内容,并更新到slave的数据库里(保证数据一致) ![MySQL主从复制流程图.png](https://www.xiaoleizhang.com/usr/uploads/2021/12/1626087159.png) # 二、MySQL主从搭建 搭建之前确保安装的MySQL版本一致3306端口可达,最好selinux关闭。 ## 1、主库操作 使用命令对主库进行配置 ```shell vim /etc/my.cnf ``` 添加如下配置 ```shell server-id=1 #服务器id (主从必须不一样),爱写啥写啥,别太离谱就行 log-bin=/data/xiaolei-binlog #打开日志(主库需要打开),这个xiaolei-binlog也可以自定义,这里也可以加上其它路径作为主库的配置 #binlog-do-db=xiaolei #要给从库同步的库 #binlog-ignore-db=mysql #不给从库同步的库(多个写多行) #binlog-ignore-db=information_schema #binlog-ignore-db=performance_schema #binlog-ignore-db=sys #expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改 ``` **注意:**对于想将binlog放到别的目录下的情况,注意在目标目录一定要用户和用户组是mysql,如果开着selinux确保这个目录的安全上下文是mysqld_db_t具体命令如下所示(我这边是放到到/data下) ```shell yum install -y setroubleshoot-server #安装semanage chown -R mysql:mysql /data chcon -t mysqld_db_t /data #修改/data的安全上下文 semanage fcontext -a -t mysqld_db_t "/data(/.*)?" #修改/data下文件的安全上下文,写入内核的永久修改 ``` 接着配置文件改完后需要重启下MySQL ```shell systemctl restart mysqld.service ``` 测试binlog是否成功开启 登录mysql 执行 ```shell show variables like '%log_bin%'; ``` 然后会看到如下信息 ```shell mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------+ | Variable_name | Value | +---------------------------------+----------------------------+ | log_bin | ON | | log_bin_basename | /data/xiaolei-binlog | | log_bin_index | /data/xiaolei-binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------------+ 6 rows in set (0.00 sec) mysql> ``` 然后在主库中建立备份账号:backup为用户名,我这里使用从库的地址10.60.60.12(%表示任何远程地址),如下表示只有从10.60.60.12的主机上远程过来,并且密码为1qaz@WSX的backup可以连接主库。 ```shell grant replication slave on *.* to 'backup'@'10.60.60.12' identified by '1qaz@WSX'; ``` 可以进入mysql库查看到用户 ```shell mysql> grant replication slave on *.* to 'backup'@'10.60.60.12' identified by '1qaz@WSX'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select user,authentication_string,host from user; +---------------+-------------------------------------------+-------------+ | user | authentication_string | host | +---------------+-------------------------------------------+-------------+ | root | *58319282EAB9E38D49CA25844B73DA62C80C2ABC | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | backup | *58319282EAB9E38D49CA25844B73DA62C80C2ABC | 10.60.60.12 | +---------------+-------------------------------------------+-------------+ 4 rows in set (0.00 sec) mysql> ``` 查看主服务器上当前的二进制日志名和偏移量值这里的file 和position。 ```shell show master status\G ``` 我的输出如下所示 ```shell mysql> show master status\G *************************** 1. row *************************** File: xiaolei-binlog.000001 Position: 449 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) mysql> ``` 这个参数你要有记录,一会设置从库的时候需要里面的参数。 接着我们在主库中创建数据库和一张表,数据库中执行命令如下 ```shell create database xiaolei; use xiaolei; create table `test` ( `test1` int(11) NOT NULL, `test2` int(11) NOT NULL, PRIMARY KEY (`test1`) ) engine=InnoDB ; ``` 我们可以在主库中看到有这个库和表了 ```shell mysql> create database xiaolei; Query OK, 1 row affected (0.00 sec) mysql> use xiaolei; Database changed mysql> mysql> create table `test` ( -> `test1` int(11) NOT NULL, -> `test2` int(11) NOT NULL, -> PRIMARY KEY (`test1`) -> ) engine=InnoDB ; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | xiaolei | +--------------------+ 5 rows in set (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_xiaolei | +-------------------+ | test | +-------------------+ 1 row in set (0.00 sec) mysql> ``` ## 2、从库操作 登录从库,同样打开配置文件 ```shell vim /etc/my.cnf ``` 添加下面几行 ```shell server_id=2 master_info_repository=TABLE relay_log_info_repository=TABLE ``` 保存退出后,重启MySQL服务 ```shell systemctl restart mysqld.service ``` 登录从库的MySQL,然后执行,下面的参数自己去找主库我刚刚让你记录下来的数值填写。 ```shell mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to -> master_host='10.60.60.11', -> master_user='backup', -> master_password='1qaz@WSX', -> master_log_file='xiaolei-binlog.000001', -> master_log_pos=449; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> ``` 查看slave从库的状态 ```shell mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.60.60.11 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: xiaolei-binlog.000001 Read_Master_Log_Pos: 899 Relay_Log_File: 10-60-60-12-relay-bin.000002 Relay_Log_Pos: 775 Relay_Master_Log_File: xiaolei-binlog.000001 Slave_IO_Running: Yes #看到这两个yes就表示已经主从同步了 Slave_SQL_Running: Yes #看到这两个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: 899 Relay_Log_Space: 988 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: 4ed7fdb8-5680-11ec-8971-000c2943a14f Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql> ``` ## 3、测试一下 我们在主库插入一条数据,然后在从库读出来 主库执行如下 ```shell mysql> use xiaolei; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into test values(1,1); Query OK, 1 row affected (0.00 sec) mysql> select * from test; +-------+-------+ | test1 | test2 | +-------+-------+ | 1 | 1 | +-------+-------+ 1 row in set (0.00 sec) mysql> ``` 接着我们去从库读取看下 ```shell mysql> use xiaolei; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; +-------+-------+ | test1 | test2 | +-------+-------+ | 1 | 1 | +-------+-------+ 1 row in set (0.00 sec) mysql> ``` 发现可以在从库上读取到数据,主从搭建成功! 最后修改:2022 年 01 月 08 日 © 允许规范转载 赞 0 如果觉得我的文章对你有用,请随意赞赏