測試環境

系統：WIN 10

MySQL版本：mysql-5.7.23

安裝配置

解壓文件

使用mysql-5.7.23-winx64.zip包解壓縮安裝

路徑分別為：

D:\mysql\mysql-5.7.23-winx64

D:\mysql\mysql-5.7.23-winx64-3307

解壓安裝路徑隨意指定，注意需要與my.ini文件中的路徑一致

my.ini配置文件

分別在兩個目錄中創建my.ini 文件

在同一臺機器上測試，mysql實例使用不同端口

主數據庫：3306 (默認)

從數據庫：3307

內容如下：

主庫my.ini

[mysqld]

# set basedir to your installation path

basedir=D:/mysql/mysql-5.7.23-winx64

# set datadir to the location of your data directory

datadir=D:/mysql/mysql-5.7.23-winx64/data

# 唯一標識

server-id=1

# binlog文件名(可以不指定)

log-bin=mysql-binlog

# 要寫binlog的數據庫，要同步多個數據庫，就多加幾個binlog-do-db=數據庫名

binlog-do-db=mstest

binlog-do-db=test

# 要忽略的數據庫

binlog-ignore-db=mysql

從庫my.ini

[mysqld]

# 指定端口

port=3307

# set basedir to your installation path

basedir=D:/mysql/mysql-5.7.23-winx64-3307

# set datadir to the location of your data directory

datadir=D:/mysql/mysql-5.7.23-winx64-3307/data

# 唯一標識

server-id=2

# 要復制多個數據庫，就多加幾個replicate-do-db=數據庫名

replicate-do-db=mstest

replicate-do-db=test

# 要忽略的數據庫

replicate-ignore-db=mysql

初始化并啟動數據庫

分別初始化、啟動兩個數據庫，并修改root密碼

mysqld --initialize --console

mysqld.exe --console

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

具體見安裝文檔

數據庫配置

主庫配置

登錄主庫

>mysql -uroot -p

主數據庫創建用于同步的用戶

GRANT REPLICATION SLAVE ON *.* TO 'mstest'@'%' IDENTIFIED BY '123456';

從數據庫配置

登錄從庫，指定端口3307

>mysql -uroot -p -P3307

配置從庫連接到主庫

change master to master_host='127.0.0.1',master_port=3306,

master_user='mstest',master_password='123456';

start slave;

查看狀態

主庫

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-binlog.000002 | 313 | mstest,test | mysql | |

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

1 row in set (0.00 sec)

從庫

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 127.0.0.1

Master_User: mstest

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-binlog.000002

Read_Master_Log_Pos: 1306

Relay_Log_File: wwh-relay-bin.000005

Relay_Log_Pos: 323

Relay_Master_Log_File: mysql-binlog.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: mstest,test

Replicate_Ignore_DB: mysql

......

......

......

Master_Server_Id: 1

Master_UUID: ea65d565-a8a0-11e8-807e-0a002700000a

Master_Info_File: D:\mysql\mysql-5.7.23-winx64-3307\data\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

......

......

1 row in set (0.00 sec)

同步測試

創建數據庫

主庫創建表，從庫查看

主庫插入記錄，從庫查看

mysql> create database test;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

| wwh |

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

6 rows in set (0.00 sec)

mysql> status;

--------------

mysql Ver 14.14 Distrib 5.7.23, for Win64 (x86_64)

Connection id: 2

Current database:

Current user: root@localhost

SSL: Not in use

Using delimiter: ;

Server version: 5.7.23 MySQL Community Server (GPL)

Protocol version: 10

Connection: localhost via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: gbk

Conn. characterset: gbk

TCP port: 3307

Uptime: 7 min 37 sec

show binlog events\G

show master status\G

查詢命令

在master上查看當前有多少個從節點

select * from information_schema.processlist as p where p.command = 'Binlog Dump';

總結

以上是生活随笔為你收集整理的mysql主从同步测试_mysql 主从同步测试的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。