1. 實(shí)驗(yàn)環(huán)境

System

IP

Host

CentOS 7.4.1708

192.168.100.101

master

CentOS 7.4.1708

192.168.100.102

slave

2. 安裝MySQL8

3. 配置主從復(fù)制

要想將主節(jié)點(diǎn)配置為使用基于二進(jìn)制日志的復(fù)制，必須確保啟用了二進(jìn)制日志記錄，并建立唯一的服務(wù)器ID

[root@master ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql # 這是數(shù)據(jù)存放的路徑

socket=/var/lib/mysql/mysql.sock # 這是監(jiān)聽(tīng)的套接字

log-error=/var/log/mysqld.log # 日志輸出的路徑

pid-file=/var/run/mysqld/mysqld.pid # pid存放的路徑

log-bin=on # 開(kāi)啟二進(jìn)制日志

server-id=1 # 服務(wù)器id為1

[root@slave ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

server-id=2

主節(jié)點(diǎn)server-id為0：拒絕來(lái)自從節(jié)點(diǎn)的任何連接

從節(jié)點(diǎn)server-id為0：拒絕連接到主節(jié)點(diǎn)

4. 配置復(fù)制用戶

創(chuàng)建一個(gè)只能復(fù)制的用戶。

mysql> CREATE USER 'repl'@'192.168.100.101' IDENTIFIED BY 'MyNewPass4!';

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.101';

Query OK, 0 rows affected (0.06 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.01 sec)

5. 數(shù)據(jù)的同步

在配置之前，要注意的是，主節(jié)點(diǎn)現(xiàn)在的數(shù)據(jù)與從節(jié)點(diǎn)是不同步的，這可能導(dǎo)致復(fù)制失敗，所以你可能需要將主節(jié)點(diǎn)的數(shù)據(jù)手動(dòng)導(dǎo)入到從節(jié)點(diǎn)。在此你必須保證數(shù)據(jù)不被寫(xiě)入，所以你需要阻止寫(xiě)入。

# 這條命令會(huì)阻止對(duì)所有表的寫(xiě)入操作，但當(dāng)前客戶端斷開(kāi)，則會(huì)釋放鎖定

mysql> FLUSH TABLES WITH READ LOCK;

# 這條命令是確定當(dāng)前二進(jìn)制日志文件的名稱和位置。

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000002 | 866 | | | |

+---------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

6. 配置從節(jié)點(diǎn)

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.100.101', # 主節(jié)點(diǎn)地址

MASTER_USER='repl', # 主節(jié)點(diǎn)用戶

MASTER_PASSWORD='MyNewPass4!', # 主節(jié)點(diǎn)密碼

MASTER_LOG_FILE='binlog.000002', # 二進(jìn)制日志文件

MASTER_LOG_POS=866; # 二進(jìn)制日志位置

Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> START SLAVE;

Query OK, 0 rows affected (0.04 sec)

mysql> SHOW SLAVE STATUS\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.100.101

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 866

Relay_Log_File: slave-relay-bin.000003

Relay_Log_Pos: 319

Relay_Master_Log_File: binlog.000002

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

Relay_Log_Space: 691

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: aa8d6cc4-5f26-11e9-b7d7-000c29999aa1

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:

Master_public_key_path:

Get_master_public_key: 0

1 row in set (0.00 sec)

7. 測(cè)試主從復(fù)制

# 主節(jié)點(diǎn)

mysql> CREATE DATABASE REPL;

Query OK, 1 row affected (0.04 sec)

# 從節(jié)點(diǎn)

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| REPL |

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.01 sec)

總結(jié)

以上是生活随笔為你收集整理的mysql 主从二进制日志_Mysql-8 配置主从复制（基于二进制日志）的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。