MySQL主从复制详解

短命女 2022-05-23 13:17 162阅读 0赞

**MySQL复制原理**

MySQL主库在事务提交时会把数据变更作为事件Events记录在Binlog中，主库上的sync\_binlog参数控制Binlog日志刷新到磁盘。MySQL复制就是将Binlog日志传到从库上，在从库重做一遍，从而使得从库和主库保持数据的同步的机制。MySQL通过3个线程来实现这一机制，其中Binlog Dump线程运行在主库上，I/O线程和SQL线程运行在从库上。主从复制开始时，主库会推送Binlog中的事件到从库的Relay Log，之后从库根据Relay Log进行重做，通过逻辑复制来达到主从库的数据一致。

**MySQL复制过程**

主服务器DDL、DML操作记录到binlog——>start slave从库创建I/O线程连接主库请求同步数据——>主库随后创建Binlog Dump线程读取数据库事件binlog并发送给I/O线程——>I/O线程获取到事件数据后更新到从库的Relay Log中——>从库上的SQL线程读取Relay Log中更新的数据库事件并应用——>完成从库复制（是否删除relaylog），同时I/O线程将读取到的二进制日志当前位置信息写在master.info中,SQL线程将读取到的当前relay log位置信息写在realy.info文件中

**MySQL链式复制**

从一台主库同时向多台从库进行复制，从库同时也可以作为其他从库的主库，同时向其它从库进行复制。

**案例演示：**

配置为innodb共享表空间模式，修改配置文件后重启服务器。

修改主服务器数据，查看MySQL主从复制过程

\[root@test~\]\# vim /etc/my.cnf

innodb\_file\_per\_table=0

\#innodb\_file\_per\_table=1

\[root@wwwetc\]\# vim my.cnf

innodb\_file\_per\_table=0

\#innodb\_file\_per\_table=1

\[root@test~\]\# service mysqld restart ;ssh www "service mysqldrestart"

停止 mysqld：                                             \[确定\]

正在启动 mysqld：                                         \[确定\]

停止 mysqld：\[确定\]

正在启动 mysqld：\[确定\]

**1、master服务器**

**1）、直连服务器**

\[root@test~\]\# mysql -u root

mysql> show variables like'innodb\_file\_per\_table';

\+-----------------------+-------+

|Variable\_name        | Value |

\+-----------------------+-------+

|innodb\_file\_per\_table | OFF  |

\+-----------------------+-------+

<--可以看到，重启后已关闭独占表空间，配置为共享表空间

1 row inset (0.00 sec)

<--新建一个数据库ibdatax，并建表，观察主从复制

mysql>create database ibdatax;

Query OK,1 row affected (0.04 sec)

mysql>use ibdatax

Databasechanged

mysql>create table tb\_dept( Id int primary key auto\_increment,Namevarchar(18),description varchar(100));

Query OK,0 rows affected (0.02 sec)

mysql>insert into tb\_dept value('1','hr','recruit employee');

Query OK,1 row affected (0.01 sec)

mysql>insert into tb\_dept value('','SN','Service Network');

Query OK,1 row affected, 1 warning (0.00 sec)

mysql>select \* from tb\_dept;

\+----+------+------------------+

| Id |Name | description     |

\+----+------+------------------+

|  1| hr   |recruit employee |

|  2| SN   |Service Network  |

\+----+------+------------------+

2 rows inset (0.00 sec)

mysql> show full processlist;

\+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

\+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

\+----+----------+----------------------+---------+-------------+------+----------------------------------------------------------------+-----------------------+

查看进程状态，可以发现复制账户repluser状态为：Has sent all binlog to slave; waitingfor binlog to be updated

意即二进制日志文件以通过Binlog Dump线程传递给从服务器的I/O线程，等待数据库操作后更新binlog

2 rows inset (0.00 sec)

mysql>create table tb\_emp(id int primary key auto\_increment,Namevarchar(18),sex varchar(2),age int,address varchar(200),emailvarchar(100));

Query OK,0 rows affected (0.00 sec)

mysql>insert into tb\_empvalue('1','Jones','Man','31','GZ','jone@163.com');

Query OK,1 row affected, 1 warning (0.00 sec)

mysql>insert into tb\_empvalue('','Will','Man','28','GZ','will@126.com');

Query OK,1 row affected, 2 warnings (0.00 sec)

mysql>insert into tb\_empvalue('3','Kin','Man','35','FS','kin@126.com');

Query OK,1 row affected, 1 warning (0.00 sec)

mysql>select \* from tb\_emp;

\+----+-------+------+------+---------+--------------+

| id |Name  |sex  |age  |address | email       |

\+----+-------+------+------+---------+--------------+

|  1| Jones | Ma  |   31 |GZ     | jone@163.com |

|  2| Will  |Ma  |   28 |GZ     | will@126.com |

|  3| Kin   |Ma  |   35 |FS     | kin@126.com  |

\+----+-------+------+------+---------+--------------+

3 rows inset (0.00 sec)

mysql>SHOW MASTER STATUS;

\+-------------------+----------+--------------+------------------+

\+-------------------+----------+--------------+------------------+

|master-bin.000010 |    1740 |             |                 |

\+-------------------+----------+--------------+------------------+

1 row inset (0.00 sec)

**2）、查看数据存储目录**

进入目录，可以看到，主服务器已经生产表文件。包括：

记录表结构文件：tb\_dept.frm和tb\_emp.frm

共享表空间文件：ibdata1，大小为10M的文件中。

二进制日志文件：master-bin.000010

二进制日志索引文件：master-bin.index

\[root@wwwmysql\]\# pwd

/var/lib/mysql

\[root@wwwmysql\]\# ls –lrt|tail –n 8

drwx------. 2 mysql mysql    4096 6月   818:36 mydb

\-rw-rw----. 1 mysql mysql    2893 6月   822:21 master-bin.000009

srwxrwxrwx. 1 mysql mysql       0 6月   822:21 mysql.sock

\-rw-rw----. 1 mysql mysql     200 6月   822:21 master-bin.index

drwx------. 2 mysql mysql    4096 6月   822:36 ibdatax

\-rw-rw----. 1 mysql mysql    1740 6月   822:39 master-bin.000010

\-rw-rw----. 1 mysql mysql  52428806月   822:39 ib\_logfile0

\-rw-rw----. 1 mysql mysql 10485760 6月   822:39 ibdata1

\[root@wwwmysql\]\# cat master-bin.index

./master-bin.000001

…

./master-bin.000010

\[root@wwwmysql\]\# cd ibdatax

\[root@wwwibdatax\]\# ls -lrt

总用量 28

\-rw-rw----. 1 mysql mysql   656月   822:23 db.opt

\-rw-rw----. 1 mysql mysql 8630 6月   822:26 tb\_dept.frm

\-rw-rw----. 1 mysql mysql 8710 6月   822:36 tb\_emp.frm

\[root@wwwibdatax\]\# cat db.opt

default-character-set=latin1

default-collation=latin1\_swedish\_ci

\[root@wwwmysql\]\# cd ..

\[root@wwwmysql\]\# ls -lh|grep ibdata

\-rw-rw----. 1 mysql mysql  10M6月   822:39 ibdata1

drwx------. 2 mysql mysql 4.0K 6月   822:36 ibdatax

**2、slave从服务器**

**1）、直连服务器**

mysql> show slave status\\G

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Slave\_IO\_State: Waiting for master to send event

Master\_Host: 192.168.88.131

Master\_User: repluser

Master\_Port: 3306

Connect\_Retry: 60

Master\_Log\_File: master-bin.000010

Read\_Master\_Log\_Pos:1740 <--查看从服务器状态，已经同步完成

Relay\_Log\_File: relay-log.000046

Relay\_Log\_Pos: 1886

Relay\_Master\_Log\_File: master-bin.000010

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: 1740

Relay\_Log\_Space: 15846

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:

1 row inset (0.00 sec)

mysql> show full processlist;

\+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

\+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

\+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+-----------------------+

3 rows inset (0.00 sec)

**查看进程状态，可以发现**

从服务器system user状态一为：Waiting for master to sendevent

意即等待主服务器传递事件，即等待主服务器传递binlog，然后经由I/O线程更新到relay log中；

另一个system user状态为：Has read all relay log; waiting forthe slave I/O thread to update it

意即二进制日志文件relay log中事件已重做完毕，等待I/O线程接收主服务器binlog后更新relay log

**2）、查看数据存储目录**

进入数据储存目录，可以看到，从服务器已经生产表文件。包括：

记录表结构文件：tb\_dept.frm和tb\_emp.frm

共享表空间文件：ibdata1，大小为10M的文件中。

二进制日志文件：relay-log.000046

记录主从最后一次同步信息的日志文件：master.info、relay-log.info

注意：该文件记录下一次复制开始位置，尤为重要

从服务器二进制日志索引文件：relay\_log.index

\[root@test~\]\# cd /var/lib/mysql/

\[root@testmysql\]\# ls -lrt

总用量 20748

\-rw-rw----1 mysql mysql     295 6月   817:35 relay-log.000040

\-rw-rw----1 mysql mysql     149 6月   817:35 relay-log.000039

drwx------2 mysql mysql    4096 6月   818:36 mydb

\-rw-rw----1 mysql mysql    1220 6月   821:10 relay-log.000041

\-rw-rw----1 mysql mysql    2114 6月   822:20 relay-log.000042

\-rw-rw----1 mysql mysql     244 6月   822:20 master-bin.000005

\-rw-rw----1 mysql mysql     149 6月   822:21 relay-log.000043

srwxrwxrwx1 mysql mysql       0 6月   822:21 mysql.sock

\-rw-rw----1 mysql mysql      1206月   822:21 master-bin.index

\-rw-rw----1 mysql mysql     106 6月   822:21 master-bin.000006

\-rw-rw----1 mysql mysql     779 6月   822:22 relay\_log.index

\-rw-rw----1 mysql mysql     295 6月   822:22 relay-log.000045

\-rw-rw----1 mysql mysql      2956月   822:22 relay-log.000044

drwx------2 mysql mysql    4096 6月   822:36 ibdatax

\-rw-rw----1 mysql mysql      47 6月   822:39 relay-log.info

\-rw-rw----1 mysql mysql    1886 6月   822:39 relay-log.000046

\-rw-rw----1 mysql mysql      77 6月  822:39 master.info

\-rw-rw----1 mysql mysql  52428806月   822:39 ib\_logfile0

\-rw-rw----1 mysql mysql 10485760 6月   822:39 ibdata1

\[root@testmysql\]\# cat master.info

master-bin.000010   <-- 主服务器binlog dump线程传递的给从服务器binlog文件

1740             <-- 主服务器Position

192.168.88.131    <--主服务地址

repluser          <--提供复制的账户

replpass          <--提供复制的账户密码

3306             <-- 主服务地址端口

\[root@testmysql\]\# cat relay-log.info

./relay-log.000046     <--从服务器I/O线程更新event的从服务器relay-log文件

1886               <-- 从服务器Relay\_Log\_Pos

master-bin.000010    <--从服务器I/O线程传递的主服务器binlog文件

1740               <-- 主服务器Position

\[root@test mysql\]\# cat relay\_log.index

./relay-log.000006

./relay-log.000007

….

./relay-log.000051

./relay-log.000052

\[root@testmysql\]\#

\[root@testmysql\]\# cd ibdatax

\[root@testibdatax\]\# ls -lrt

总用量 28

\-rw-rw----1 mysql mysql   65 6月   822:23 db.opt

\-rw-rw----1 mysql mysql 8630 6月   822:26 tb\_dept.frm

\-rw-rw----1 mysql mysql 8710 6月   822:36 tb\_emp.frm

MySQL主从复制详解

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL主从复制详解

相关 Mysql数据库主从复制部署详解

相关 MySQL 主从复制详解（详细）

相关 MySQL主从复制详解

相关 mysql主从复制

相关 mysql主从复制

相关 MySQL实现主从复制过程详解

相关 Mysql 主从复制

相关 Mysql 主从复制

相关 MySQL主从复制原理详解