Centos7配置MySQL主从复制

浅浅的花香味﹌ 2022-05-22 02:24 255阅读 0赞

有两台MySQL数据库服务器master和slave，master为主服务器，slave为从服务器，初始状态时两台服务器数据相同，当master数据变化时slave中的数据同步变化，达到备份的目的。

Mysql 之间数据复制的基础是二进制日志文件(bin log file) Slave 数据库作为slave通过一个 I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现，master二进制文件发生变化，则会把变化复制到自己的日志中，然后SQL线程会把相关的数据库操作事件执行到自己的数据库中，依次实现从数据库和主数据库的一致性，也就是实现了主从复制。

注意事项：

*  在主服务器必须启动二进制日志记录并配置唯一的服务器标识
 *  在要连接到主站的每个从站上，必须配置一个唯一的服务器ID。
 *  在启动复制过程之前需记录主服务器上二进制日志的位置，配置从站时需要这些信息，以便从站知道二进制日志中哪个位置开始执行事件。
 *  如果主服务器已有数据并希望同步到从服务器，需创建数据快照。eg: mysqldump
 *  使用连接到主站的设置来配置从站，例如主机名，登陆凭证和二进制日志文件的名称和位置。

## 开始配置 ##

### 设置主服务器配置 ###

在主服务器上，必须启用二进制日志记录并建立唯一的服务器其标识。因为二进制日志是将主服务器的更改复制到从服务器的基础。
    编辑/etc/my.cnf，在[mysqld]中配置，添加log-bin和server-id

1
    2

log-bin=/usr/local/mysql/mysql-bin
    server-id=1

`保存并重启mysql服务。`

### 设置从服务器配置 ###

同样编辑 /etc/my.cnf

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12

[mysqld]
    #配置从slave
    server-id=2 #必须唯一
    log-bin=/usr/local/mysql/mysql-bin
    log_slave_updates
    replicate-do-db=xfdlx #需复制的库
    replicate-ignore-db = mysql #忽略的库
    replicate-ignore-db = information_schema
    replicate-ignore-db = performance_schema
    replicate-ignore-db = test
    slave-skip-errors=all
    slave-net-timeout=60

`保存并重启mysql服务。`

### 创建复制用户 ###

每个从设备使用用户名和密码连接到主设备，因此主设备必须有一个用户账户以便从设备连接。该账号需被授权 REPLICATION SLAVE.可以只创建一个仅具有复制过程权限的单独帐户，以最大限度地减少对其他帐户造成危害的可能性。
    
    要创建一个新帐户，请使用CREATE USER。要授予此帐户复制所需的权限，请使用该GRANT 语句。如果您仅为复制目的创建帐户，则该帐户只需要 REPLICATION SLAVE特权。
    
    赋予从库权限账号，允许用户在主库读取日志。

1
    2
    3

GRANT FILE ON *.* TO 'root'@'10.120.114.183' IDENTIFIED BY 'root'
    GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.120.114.183' IDENTIFIED BY 'root'
    FLUSH PRIVILEGES

`这里使用的仍是root用户作为同步的时候使用的用户，可以自己设定。`

### 查看主服务器状态。 ###

在主服务器查看状态并记录file和position的值

1
    2
    3
    4
    5
    6

mysql> SHOW MASTER STATUS\G
    File: mysql-bin.000002 #文件 
    Position: 120 #位置
    Binlog_Do_DB: 
    Binlog_Ignore_DB: 
    Executed_Gtid_Set:

### 在从站上设置主站配置 ###

MySQL5.6之后版本无法在配置文件里配置主站的信息，需要通过语句来执行。

1
    2
    3
    4
    5
    6
    7
    8
    9

mysql> CHANGE MASTER TO
     -> MASTER_HOST='10.120.114.182',
     -> MASTER_USER='root',
     -> MASTER_PASSWORD='root',
     -> MASTER_LOG_FILE='mysql-bin.000002', #第四步中的文件
     -> MASTER_LOG_POS=120; #第四步中的位置
     
    #开启slave
    mysql> START SLAVE;

### 在从服务器查看状态 ###

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57

mysql> SHOW SLAVE STATUS \G
    *************************** 1. row ***************************
     Slave_IO_State: Waiting for master to send event
     Master_Host: 10.120.114.182
     Master_User: root
     Master_Port: 3306
     Connect_Retry: 60
     Master_Log_File: mysql-bin.000002
     Read_Master_Log_Pos: 572
     Relay_Log_File: customer-1-relay-bin.000002
     Relay_Log_Pos: 735
     Relay_Master_Log_File: mysql-bin.000002
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
     Replicate_Do_DB: xfdlx
     Replicate_Ignore_DB: mysql,information_schema,performance_schema,test
     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: 572
     Relay_Log_Space: 913
     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: 141110e6-48f9-11e8-b95e-fa163e3a3a85
     Master_Info_File: /usr/local/mysql/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)

### 自己在navicat测试下。 ###