mysql主从复制原理及应用

一、主从复制简介

MySQL主从复制是一种异步、基于日志的、单向的数据库复制技术，它通过在主服务器上启用二进制日志并将其发送给一个或多个从服务器，实现了从服务器与主服务器之间的数据同步。主服务器将所有的数据库操作记录到二进制日志中，从服务器会定期或实时地检查主服务器的二进制日志，并将其中的操作应用到本地的MySQL实例中。复制过程可以基于语句、基于行或混合方式实现，并且可以通过多种手段来监测和管理复制延迟，以提高数据的可用性和可靠性。

原理如下：

1. 配置主服务器：在主服务器上启用二进制日志，并记录所有的数据库变更操作。每个二进制日志文件都包含一系列数据库操作的记录，这些记录按照时间顺序排列。
2. 配置从服务器：在从服务器上启动一个复制进程，该进程连接到主服务器并请求复制数据。从服务器会根据主服务器的配置信息，自动下载并解析主服务器的二进制日志文件，然后将其中的操作应用到本地的MySQL数据库中。
3. 复制过程：当主服务器上的数据库发生变化时，这些变化会被记录到二进制日志中。复制进程会定期或实时地检查主服务器的二进制日志，并将其中的新操作复制到从服务器的本地MySQL数据库中。复制过程可以通过多种方式实现，例如：
   • 基于语句的复制：复制进程将主服务器上的SQL语句复制到从服务器上执行。
   • 基于行的复制：复制进程将主服务器上的数据库行变更操作复制到从服务器上执行。
   • 混合复制：复制进程根据操作类型和情况选择使用基于语句的或基于行的复制方式。
4. 复制延迟：由于网络延迟、从服务器的负载等原因，从服务器上的数据可能会滞后于主服务器上的数据。这种滞后被称为复制延迟，它可能会对应用程序产生影响。因此，需要监测复制延迟并及时处理。
5. 故障转移：如果主服务器发生故障，需要将复制切换到另一个MySQL服务器上。可以通过使用MySQL提供的工具或第三方工具来实现自动或手动的故障转移。

总的来说，MySQL主从复制技术通过将数据从主服务器复制到多个从服务器中，提高了数据的可用性和可靠性。复制过程可以通过多种方式实现，并且可以根据实际情况进行调整和优化，以提高性能和稳定性。

二、主服务器

1.启用二进制日志(binlog)和设置server_id

[root@node1 ~]# vim /etc/my.cnf
log_bin=mysql-bin
server_id=10
# server_id要唯一

2.重启mysql服务

[root@node1 ~]# systemctl restart mysqld

3.创建一个mysql用户，并授予该用户复制权限

mysql> grant replication slave on *.* to 'rep'@'192.168.136.%' identified by '123456';
# 192.168.136.%，表示任何以192.168.136.开头的IP地址都可以连接到MySQL服务器并使用该用户进行复制操作。mysql> show grants for 'rep'@'192.168.136.%';  # 查看用户的授权

4.查看主库状态

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

5.备份数据库

锁表设置只读：为后面备份准备，注意生产环境要提前申请停机时间。

mysql> flush tables with read lock;

备份数据库的话因为我这个是空的所以就不做备份省略，做备份的话要把主服务器数据上传到从库

三、从服务器

1.启用二进制日志(binlog)和设置server_id

[root@node2 ~]# vim /etc/my.cnf
log_bin=msyql_bin
server_id = 11

2.重启mysql服务

[root@node2 ~]# systemctl restart mysqld

3.设定主库同步

mysql> change master to->  master_host='192.168.136.161',->  master_user='rep',->  master_password='123456',->  master_log_file='mysql-bin.000001',->  master_log_pos=440;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

4.启动从库同步并检查状态

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G;# 查看这两参数是否是yes，如果不是查看mysql日志
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

三、测试同步

1.创建一张表，并插入数据

mysql> CREATE  TABLE  student (-> id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  ,-> name  VARCHAR(20)  NOT NULL ,-> sex  VARCHAR(4)  ,-> birth  YEAR,-> department  VARCHAR(20) ,-> address  VARCHAR(50) -> );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
Query OK, 1 row affected (0.01 sec)

2.分别在主库和从库查看数据是否同步

# 主库：
mysql> select * from student;
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系      | 北京市海淀区        |
| 902 | 张老二    | 男   |  1986 | 中文系        | 北京市昌平区        |
| 903 | 张三      | 女   |  1990 | 中文系        | 湖南省永州市        |
| 904 | 李四      | 男   |  1990 | 英语系        | 辽宁省阜新市        |
| 905 | 王五      | 女   |  1991 | 英语系        | 福建省厦门市        |
| 906 | 王六      | 男   |  1988 | 计算机系      | 湖南省衡阳市        |
+-----+-----------+------+-------+--------------+--------------------+
6 rows in set (0.00 sec)
# 从库
mysql> select * from student;
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系      | 北京市海淀区        |
| 902 | 张老二    | 男   |  1986 | 中文系        | 北京市昌平区        |
| 903 | 张三      | 女   |  1990 | 中文系        | 湖南省永州市        |
| 904 | 李四      | 男   |  1990 | 英语系        | 辽宁省阜新市        |
| 905 | 王五      | 女   |  1991 | 英语系        | 福建省厦门市        |
| 906 | 王六      | 男   |  1988 | 计算机系      | 湖南省衡阳市        |
+-----+-----------+------+-------+--------------+--------------------+
6 rows in set (0.00 sec)
# 主从同步成功