MySQL5.7 Replication主從復制配置教程 - langtianya－IT工程師數位筆記本

文章出處

最近配置mysql5.7主從復制的時候碰到了些問題，老老實實按老版本的步驟配置會有錯誤，后來自己查看了官方文檔，才解決了問題，在這里總結一下5.7的配置步驟，

大體步驟跟老版本的還是一樣的，只是有一些小區別，下面進行具體介紹。

官方文檔：http://dev.mysql.com/doc/refman/5.7/en/replication.html

系統環境：win7

我只安裝了mysql server，其他配套工具沒安裝。安裝后發現根目錄下有一個my-default.ini文件，請把該文件改名成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
# server_id = .....
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# bin log file name of the log to create
# log_bin= .....
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....

# 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 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

把如下幾個屬性改動一下，

server_id = 101 注意這個101是唯一標識你這個mysql服務器的，一般取你電腦ip的最后幾位，以便好區分。
log_bin=mysql-bin 注意mysql-bin是一個存放二進制log文件的路徑，我這里指定了一個mysql當前安裝目錄的mysql-bin文件夾

port=3306     3306是你設定的端口

改完之后在mysql command line client（mysql命令行客戶端）執行如下兩個命令中一個,初始化data目錄

在window系統：

mysqld --initialize-insecure; 解釋：初始化data目錄的同時，會自動生成無密碼的root用戶，
mysqld --initialize; 解釋：初始化data目錄的同時，會自動生成帶隨機密碼的root用戶，

在 Unix 或者Unix相關，相似的系統：

mysqld --initialize --user=mysql
bin/mysqld --initialize-insecure --user=mysql

如上操作完成后，需要用上面生成的用戶名登錄服務，如下：

如果你用 --initialize 初始化data目錄，請用如下命令登錄服務
```
 mysql -u root -p
執行上面命令，會提示輸入密碼，輸入隨機生成的密碼即可。
```
如果你不知道這個隨機密碼，請查看error log文件查找這個隨機密碼。
如果用 --initialize-insecure 初始化data目錄，請用root用登錄，并不需要輸入密碼就可以登錄，如下命令:
```
 mysql -u root --skip-password
```

用沒有密碼的root用戶登錄后可以給這個用戶設置密碼，設置方法如下命令：

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

另外，如果 mysqld 無法識別你的mysql安裝目錄和mysql數據存放目錄，請手工指定：方法有二

第一種：即在輸入命令的時候多加兩個參數--basedir和--datadir，具體如下

mysqld --initialize --user=mysql
         --basedir=your mysql install dir e.g：/opt/mysql/mysql

        --datadir=your mysql data dir e.g：/opt/mysql/mysql/data

第二種：在你的my.ini文件增加兩個屬性：

basedir= you mysql install dir e.g：/opt/mysql/mysql

datadir=you mysql data dir e.g：/opt/mysql/mysql/data

詳細請參考：http://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html

配置完后你可以重啟服務了，命令：mysqld restart --console;或者手動關掉 mysqld進程后執行命令：mysqld --console;。

請用同樣的方法設置 master和slave但是兩者的端口要不同。

至此，剩下的操作跟老版本一樣了，剩下的操作請參考其他文章，例如[z]如何在一臺windows主機上搭建mysql主從復制這篇文件，從如下文字開始：

3、在主庫添加一個用戶 repl 并指定replication權限
create user 'repl'@'127.0.0.1' identified by 'asdf';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --這里我指定數據庫（test.*）時報錯，而指定全庫（*.*）時會成功。

4、保持主從mysql的test數據庫初始狀態一致。

.....................

===完====

下面的附加信息也許你會用到，或者碰到這些問題：

1、用 show variables like '%log_bin%';查看狀態：

配置失敗的狀態如下:

+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set, 1 warning (0.01 sec)

配置成功的類似是如下：

+---------------------------------+---------------------------------------------
-----------------+
| Variable_name | Value
|
+---------------------------------+---------------------------------------------
-----------------+
| log_bin | ON
|
| log_bin_basename | C:\Program Files\MySQL\MySQL Server 5.7\data
\mysql-bin |
| log_bin_index | C:\Program Files\MySQL\MySQL Server 5.7\data
\mysql-bin.index |
| log_bin_trust_function_creators | OFF
|
| log_bin_use_v1_row_events | OFF
|
| sql_log_bin | ON
|
+---------------------------------+---------------------------------------------
-----------------+
6 rows in set, 1 warning (0.01 sec)

2、如果執行mysqld --console，報類似如下錯，手動停掉mysqld進程后再執行。

2016-02-06T13:23:13.951203Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is
deprecated. Please use --explicit_defaults_for_timestamp server option (see doc
umentation for more details).
2016-02-06T13:23:13.951203Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'E
RROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will
be merged with strict mode in a future release.
2016-02-06T13:23:13.951203Z 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not s
et.
2016-02-06T13:23:13.951203Z 0 [Warning] Insecure configuration for --secure-file
-priv: Current value does not restrict location of generated files. Consider set
ting it to a valid, non-empty path.
2016-02-06T13:23:13.952203Z 0 [Note] mysqld (mysqld 5.7.10-log) starting as proc
ess 38432 ...
2016-02-06T13:23:13.976205Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows in
terlocked functions
2016-02-06T13:23:13.976205Z 0 [Note] InnoDB: Uses event mutexes
2016-02-06T13:23:13.977205Z 0 [Note] InnoDB: Memory barrier is not used
2016-02-06T13:23:13.977205Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-02-06T13:23:13.977205Z 0 [Note] InnoDB: Number of pools: 1
2016-02-06T13:23:13.978205Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2016-02-06T13:23:14.005206Z 0 [Note] InnoDB: Initializing buffer pool, total siz
e = 128M, instances = 1, chunk size = 128M
2016-02-06T13:23:14.019207Z 0 [Note] InnoDB: Completed initialization of buffer
pool
2016-02-06T13:23:14.092211Z 0 [Note] InnoDB: Highest supported file format is Ba
rracuda.
2016-02-06T13:23:14.239220Z 0 [Note] InnoDB: Creating shared tablespace for temp
orary tables
2016-02-06T13:23:14.240220Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12
MB. Physically writing the file full; Please wait ...
2016-02-06T13:23:14.450232Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2016-02-06T13:23:14.453232Z 0 [Note] InnoDB: 96 redo rollback segment(s) found.
96 redo rollback segment(s) are active.
2016-02-06T13:23:14.453232Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are
active.
2016-02-06T13:23:14.463233Z 0 [Note] InnoDB: 5.7.10 started; log sequence number
1210265
2016-02-06T13:23:14.464233Z 0 [Note] InnoDB: Loading buffer pool(s) from C:\Prog
ram Files\MySQL\MySQL Server 5.7\data\ib_buffer_pool
2016-02-06T13:23:14.464233Z 0 [Note] Plugin 'FEDERATED' is disabled.
2016-02-06T13:23:14.464233Z 0 [Note] InnoDB: not started
mysqld: Table 'mysql.plugin' doesn't exist
2016-02-06T13:23:14.465233Z 0 [ERROR] Can't open the mysql.plugin table. Please
run mysql_upgrade to create it.
2016-02-06T13:23:14.478233Z 0 [Note] InnoDB: Buffer pool(s) load completed at 16
0206 21:23:14
2016-02-06T13:23:14.586240Z 0 [Warning] Gtid table is not ready to be used. Tabl
e 'mysql.gtid_executed' cannot be opened.
2016-02-06T13:23:14.609241Z 0 [Warning] Failed to set up SSL because of the foll
owing SSL library error: SSL context is not usable without certificate and priva
te key
2016-02-06T13:23:14.609241Z 0 [Note] Server hostname (bind-address): '*'; port:
3306
2016-02-06T13:23:14.611241Z 0 [Note] IPv6 is available.
2016-02-06T13:23:14.612241Z 0 [Note] - '::' resolves to '::';
2016-02-06T13:23:14.612241Z 0 [Note] Server socket created on IP: '::'.
2016-02-06T13:23:14.615241Z 0 [Warning] Failed to open optimizer cost constant t
ables

2016-02-06T13:23:14.617241Z 0 [ERROR] Fatal error: Can't open and lock privilege
tables: Table 'mysql.user' doesn't exist
2016-02-06T13:23:14.619242Z 0 [ERROR] Aborting

2016-02-06T13:23:14.619242Z 0 [Note] Binlog end
2016-02-06T13:23:14.640243Z 0 [Note] Shutting down plugin 'ngram'
2016-02-06T13:23:14.640243Z 0 [Note] Shutting down plugin 'partition'
2016-02-06T13:23:14.640243Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2016-02-06T13:23:14.641243Z 0 [Note] Shutting down plugin 'ARCHIVE'
2016-02-06T13:23:14.641243Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2016-02-06T13:23:14.641243Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2016-02-06T13:23:14.641243Z 0 [Note] Shutting down plugin 'MyISAM'
2016-02-06T13:23:14.641243Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2016-02-06T13:23:14.642243Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'

2016-02-06T13:23:14.642243Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACE
S'
2016-02-06T13:23:14.643243Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_CO
LS'
2016-02-06T13:23:14.643243Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2016-02-06T13:23:14.643243Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2016-02-06T13:23:14.643243Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2016-02-06T13:23:14.643243Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2016-02-06T13:23:14.644243Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS
'
2016-02-06T13:23:14.644243Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2016-02-06T13:23:14.644243Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE
'
2016-02-06T13:23:14.644243Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE
'
2016-02-06T13:23:14.644243Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2016-02-06T13:23:14.645243Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELET
ED'
2016-02-06T13:23:14.645243Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2016-02-06T13:23:14.645243Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STO
PWORD'
2016-02-06T13:23:14.645243Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2016-02-06T13:23:14.645243Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INF
O'
2016-02-06T13:23:14.646243Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_ST
ATS'
2016-02-06T13:23:14.646243Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LR
U'
2016-02-06T13:23:14.646243Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2016-02-06T13:23:14.646243Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_
RESET'
2016-02-06T13:23:14.646243Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'

2016-02-06T13:23:14.647243Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2016-02-06T13:23:14.647243Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2016-02-06T13:23:14.647243Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2016-02-06T13:23:14.647243Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2016-02-06T13:23:14.647243Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2016-02-06T13:23:14.648243Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2016-02-06T13:23:14.648243Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2016-02-06T13:23:14.648243Z 0 [Note] Shutting down plugin 'InnoDB'
2016-02-06T13:23:14.648243Z 0 [Note] InnoDB: FTS optimize thread exiting.
2016-02-06T13:23:14.648243Z 0 [Note] InnoDB: Starting shutdown...
2016-02-06T13:23:14.748249Z 0 [Note] InnoDB: Dumping buffer pool(s) to C:\Progra
m Files\MySQL\MySQL Server 5.7\data\ib_buffer_pool
2016-02-06T13:23:14.751249Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 16
0206 21:23:14
2016-02-06T13:23:16.137328Z 0 [Note] InnoDB: Shutdown completed; log sequence nu
mber 1210284
2016-02-06T13:23:16.138328Z 0 [Note] InnoDB: Removed temporary tablespace data f
ile: "ibtmp1"
2016-02-06T13:23:16.138328Z 0 [Note] Shutting down plugin 'MEMORY'
2016-02-06T13:23:16.138328Z 0 [Note] Shutting down plugin 'CSV'
2016-02-06T13:23:16.139328Z 0 [Note] Shutting down plugin 'sha256_password'
2016-02-06T13:23:16.139328Z 0 [Note] Shutting down plugin 'mysql_native_password
'
2016-02-06T13:23:16.139328Z 0 [Note] Shutting down plugin 'binlog'
2016-02-06T13:23:16.139328Z 0 [Note] mysqld: Shutdown complete

原創文章，轉載請注明:http://www.cnblogs.com/langtianya/p/5185577.html

相關:

centos下mysql集群配置例子

最后簡單介紹一下主從復制和讀寫分離

1 主從復制，是用來建立一個和主數據庫完全一樣的數據庫環境，稱為從數據庫；主數據庫一般是實時的業務數據庫，從數據庫的作用和使用場合一般有幾個：一是作為后備數據庫，主數據庫服務器故障后，可切換到從數據庫繼續工作；二是可在從數據庫作備份、數據統計等工作，這樣不影響主數據庫的性能；

2 讀寫分離，是指讀與寫分別使用不同的數據庫，當然一般是在不同服務器上的；在同一臺服務器上的讀寫環境，估計只是用來測試吧。一般讀寫的數據庫環境配置為，一個寫入的數據庫，一個或多個讀的數據庫，各個數據庫分別位于不同的服務器上，充分利用服務器性能和數據庫性能；當然，其中會涉及到如何保證讀寫數據庫的數據一致，這個就可以利用主從復制技術來完成。一般應用場合為：業務吞吐量很大

相關：MYSQL主主切換(主庫宕機)

mysql一主多從宕機從庫切換主繼續和從同步教程

文章列表