windows8.1系統下配置mysql主從復制
1.測試環境概述
本地win8操作系統，64位操作系統，3G內存，下載的是mysql5.6.16-winx64.zip包
在本地磁盤上解壓兩個mysql，端口分別是3310和3311，即：
localhost:3310 --master服務器
localhost:3311 --slave服務器
2.配置master
將壓縮包解壓： C:/software/mysql-5.6.16-winx64-3310
在這個目錄下建立tmp文件夾
在該目錄下新增my.ini文件，其內容如下：
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/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] server-id=1 #replicate-same-server-id=0 # 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 = C:/software/mysql-5.6.16-winx64-3310 datadir = C:/software/mysql-5.6.16-winx64-3310/data tmpdir = C:/software/mysql-5.6.16-winx64-3310/tmp port = 3310 log-bin=master-bin log-bin-index=master-bin.index #default-character-set = utf8 # 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 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [client] port = 3310 #default-character-set = utf8其中，server-id變量表示該節點在集群內的唯一表示，每個節點必須使用不同的server-id來區分 basedir,datadir,tmpdir這些表示的是數據文件或臨時文件的存放路徑，因為我們在一台機器上創建2個mysql數據庫服務，為了兩個實例之間的數據不發生干擾，我們在配置文件中明確指定其位置 port:這個參數就是mysql服務的監聽端口，這裡我們設置的是3310 log-bin:要將服務器設置為master，需要確保該服務器有一個活動的二進制日志（binary log）,該日志上保留了服務器上的所有改變，並且這些改變可以在slave上被重新執行 3.啟動master 命令行切換到 C:\software\mysql-5.6.16-winx64-3310\bin（master解壓目錄），執行mysqld --console命令，得如下信息，則master啟動完畢：
C:\software\mysql-5.6.16-winx64-3310\bin>mysqld --console 2014-03-26 22:20:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-03-26 22:20:48 7204 [Note] Plugin 'FEDERATED' is disabled. 2014-03-26 22:20:48 7204 [Note] InnoDB: Using atomics to ref count buffer pool pages 2014-03-26 22:20:48 7204 [Note] InnoDB: The InnoDB memory heap is disabled 2014-03-26 22:20:48 7204 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2014-03-26 22:20:48 7204 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-03-26 22:20:48 7204 [Note] InnoDB: Not using CPU crc32 instructions 2014-03-26 22:20:48 7204 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2014-03-26 22:20:48 7204 [Note] InnoDB: Completed initialization of buffer pool 2014-03-26 22:20:48 7204 [Note] InnoDB: Highest supported file format is Barracuda. 2014-03-26 22:20:48 7204 [Note] InnoDB: 128 rollback segment(s) are active. 2014-03-26 22:20:48 7204 [Note] InnoDB: Waiting for purge to start 2014-03-26 22:20:48 7204 [Note] InnoDB: 5.6.16 started; log sequence number 1625977 2014-03-26 22:20:49 7204 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d440636c-b4f1-11e3-9ec4-904ce5e379cc. 2014-03-26 22:20:49 7204 [Note] Server hostname (bind-address): '*'; port: 3310 2014-03-26 22:20:49 7204 [Note] IPv6 is available. 2014-03-26 22:20:49 7204 [Note] - '::' resolves to '::'; 2014-03-26 22:20:49 7204 [Note] Server socket created on IP: '::'. 2014-03-26 22:20:50 7204 [Note] Event Scheduler: Loaded 0 events 2014-03-26 22:20:50 7204 [Note] mysqld: ready for connections. Version: '5.6.16-log' socket: '' port: 3310 MySQL Community Server (GPL)4.在master上創建一個復制用戶 4.1修改默認的root密碼 如果是已有的數據庫則該步驟，則修改密碼部分省略 C:/software/mysql-5.6.16-winx64-3310\mysqladmin -uroot password 111111 通過如上命令，我們給默認的root帳號一個密碼111111 4.2添加復制用戶並給予權限： C:/software/mysql-5.6.16-winx64-3310 \mysql -uroot -p111111 進入到mysql數據庫中
mysql> create user repl_user; Query OK, 0 rows affected (0.00 sec) mysql> mysql> grant replication slave on *.* to repl_user identified by '111111'; Query OK, 0 rows affected (0.00 sec) mysql>replication slave這個權限沒有什麼特別之處，只是這個用戶可以從master上取得二進制的日志轉儲數據，完全可以給一個常規帳號賦予replication slave權限，但是最好還是將replication slave權限和其他用戶權限分開，這樣的話，如果後期想禁止某些slave的鏈接，只要刪除該用戶就可以了 5.配置slave 將壓縮包解壓： C:/software/mysql-5.6.16-winx64-3311 在這個目錄下建立tmp文件夾 在該目錄下新增my.ini文件，其內容如下：
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/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] server-id=2 #replicate-same-server-id=0 # 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 = C:/software/mysql-5.6.16-winx64-3311 datadir = C:/software/mysql-5.6.16-winx64-3311/data tmpdir = C:/software/mysql-5.6.16-winx64-3311/tmp port = 3311 relay-log = slave-relay-bin relay-log-index = slave-relay-bin.index #default-character-set = utf8 # 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 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [client] port = 3311 #default-character-set = utf8與master一樣，slave也要配置一個唯一的服務器id,relay-log和relay-log-index表示中繼日志文件和中繼日志索引文件 6.啟動slave 命令行切換到 C:\software\mysql-5.6.16-winx64-3311\bin（slave解壓目錄），執行mysqld --console命令，得如下信息，則slave啟動完畢：
C:\software\mysql-5.6.16-winx64-3311\bin>mysqld --console 2014-03-26 22:21:19 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-03-26 22:21:19 6916 [Note] Plugin 'FEDERATED' is disabled. 2014-03-26 22:21:19 6916 [Note] InnoDB: Using atomics to ref count buffer pool pages 2014-03-26 22:21:19 6916 [Note] InnoDB: The InnoDB memory heap is disabled 2014-03-26 22:21:19 6916 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions 2014-03-26 22:21:19 6916 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-03-26 22:21:19 6916 [Note] InnoDB: Not using CPU crc32 instructions 2014-03-26 22:21:19 6916 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2014-03-26 22:21:19 6916 [Note] InnoDB: Completed initialization of buffer pool 2014-03-26 22:21:19 6916 [Note] InnoDB: Highest supported file format is Barracuda. 2014-03-26 22:21:20 6916 [Note] InnoDB: 128 rollback segment(s) are active. 2014-03-26 22:21:20 6916 [Note] InnoDB: Waiting for purge to start 2014-03-26 22:21:20 6916 [Note] InnoDB: 5.6.16 started; log sequence number 1625977 2014-03-26 22:21:20 6916 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e6f98904-b4f1-11e3-9ec5-904ce5e379cc. 2014-03-26 22:21:20 6916 [Note] Server hostname (bind-address): '*'; port: 3311 2014-03-26 22:21:20 6916 [Note] IPv6 is available. 2014-03-26 22:21:20 6916 [Note] - '::' resolves to '::'; 2014-03-26 22:21:20 6916 [Note] Server socket created on IP: '::'. 2014-03-26 22:21:21 6916 [Note] Event Scheduler: Loaded 0 events 2014-03-26 22:21:21 6916 [Note] mysqld: ready for connections. Version: '5.6.16' socket: '' port: 3311 MySQL Community Server (GPL)7.將slave連接到master上 現在創建基本的復制只剩最後一步了：將slave指向master，讓它知道從哪裡進行復制，為此你需要知道master的4個信息：主機名，主機端口號，主機數據庫用戶，主機數據庫帳號密碼，因為創建master的時候已經創建了一個復制用戶，因此在slave節點上執行如下指令：
mysql> change master to master_host='localhost',master_port=3310,master_user='repl_user',master_password='111111'; Query OK, 0 rows affected, 2 warnings (0.45 sec) mysql>
mysql> start slave; Query OK, 0 rows affected (0.05 sec) mysql>8.常見問題排查 執行如上命令後，輸出如下信息： 2014-03-26 23:11:23 6916 [ERROR] Slave I/O: error connecting to master 'repl_user@localhost:3310' - retry-time: 60 retries: 1, Error_code: 1045 這個說明slave和master之間的鏈接沒有建立成功，這個時候多半是添加了新用戶repl_user後，沒有生效導致 在master上執行flush privileges;來將新建的用戶及權限生效
