聲明:我配置出來的slave_io_running和slave_sql_running都是yes。但是資料并沒有同步!
希望有遇到相同問題的朋友,能夠告訴我一下解決方案?
首先,如何在同一個伺服器安裝兩個MySQL
接着我們配置兩個mysql同步資料
一 先看mysql1的my.ini
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# 以下内容手動添加
[client]
port=3307
default-character-set=utf8
[mysqld]
#skip-grant-tables
#從庫配置
server_id=1
#需要同步的表,不寫則預設整個庫同步
binlog-do-db=testdb
slave_parallel_type='logical_clock'
slave_parallel_workers=4
log_bin=mysql-bin
log_bin-index=mysql-bin.index
#端口
port=3306
character_set_server=utf8
#我的data和bin目錄分成了連個,是以路徑有些不一樣,大家按照自己的來就行
#解壓目錄
basedir=C:\ProgramData\MySQL\MySQL Server 5.7
#解壓目錄下data目錄
datadir=C:\ProgramData\MySQL\MySQL Server 5.7\data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[WinMySQLAdmin]
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe
二 在看mysql2的my.ini
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
# 以下内容手動添加
[client]
port=3307
default-character-set=utf8
[mysqld]
#從庫配置
server_id=2
#這個是你需要覆寫的庫
replicate-do-db=kps_common
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
#端口
port=3307
character_set_server=utf8
#我的data和bin目錄分成了連個,是以路徑有些不一樣,大家按照自己的來就行
#解壓目錄
basedir=C:\ProgramData\MySQL1\MySQL Server 5.7
#解壓目錄下data目錄
datadir=C:\ProgramData\MySQL1\MySQL Server 5.7\data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[WinMySQLAdmin]
C:\Program Files\MySQL1\MySQL Server 5.7\bin\mysqld.exe
三 指令配置mysql1
接着,先配置mysql1
打開cmd,然後轉到mysql1的bin目錄
1 cd\
2 cd C:\Program Files\MySQL\MySQL Server 5.7\bin
3 輸入 mysql -uroot -p
4 輸入密碼 登入成功
5 show master status;
第5步,完成後,出現下圖:(圖檔是拷貝的,主要記住File和Position)
最後使用語句
PS:這裡必須要填192.168.1.88的ip,而不是127.0.0.1。不然之後會Slave_IO_Running:Connecting
這個問題困擾了我2天,後來偶然的使用ip來指派使用者權限,才正确!
create user slave;
grant replication slave on *.* to 'slave'@'192.168.1.88'identified by '123';
flush privileges;
四 指令配置mysql2
同上面一樣,先打開cmd,然後轉到mysql2的bin目錄
然後登陸。
接着運作
CHANGE MASTER TO
master_host = '192.168.1.88',
master_user = 'slave',
master_password = '123',
master_log_file = 'bin_log.000002',
master_log_pos = 631;
然後運作
start slave;
show slave status \G;
出現這個就正确了
參考: