MySQL主从复制配置-windows单机环境
其中,server-id变量表示该节点在集群内的唯一表示,每个节点必须使用不同的server-id来区分# 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-3310datadir = C:/software/mysql-5.6.16-winx64-3310/datatmpdir = C:/software/mysql-5.6.16-winx64-3310/tmpport = 3310log-bin=master-binlog-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 = 2Msql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[client]port = 3310#default-character-set = utf8
4.在master上创建一个复制用户C:\software\mysql-5.6.16-winx64-3310\bin>mysqld --console2014-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 pages2014-03-26 22:20:48 7204 [Note] InnoDB: The InnoDB memory heap is disabled2014-03-26 22:20:48 7204 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions2014-03-26 22:20:48 7204 [Note] InnoDB: Compressed tables use zlib 1.2.32014-03-26 22:20:48 7204 [Note] InnoDB: Not using CPU crc32 instructions2014-03-26 22:20:48 7204 [Note] InnoDB: Initializing buffer pool, size = 128.0M2014-03-26 22:20:48 7204 [Note] InnoDB: Completed initialization of buffer pool2014-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 start2014-03-26 22:20:48 7204 [Note] InnoDB: 5.6.16 started; log sequence number 16259772014-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: 33102014-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 events2014-03-26 22:20:50 7204 [Note] mysqld: ready for connections.Version: ‘5.6.16-log‘ socket: ‘‘ port: 3310 MySQL Community Server (GPL)
replication slave这个权限没有什么特别之处,只是这个用户可以从master上取得二进制的日志转储数据,完全可以给一个常规帐号赋予replication slave权限,但是最好还是将replication slave权限和其他用户权限分开,这样的话,如果后期想禁止某些slave的链接,只要删除该用户就可以了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>
与master一样,slave也要配置一个唯一的服务器id,relay-log和relay-log-index表示中继日志文件和中继日志索引文件# 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-3311datadir = C:/software/mysql-5.6.16-winx64-3311/datatmpdir = C:/software/mysql-5.6.16-winx64-3311/tmpport = 3311relay-log = slave-relay-binrelay-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 = 2Msql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[client]port = 3311#default-character-set = utf8
C:\software\mysql-5.6.16-winx64-3311\bin>mysqld --console2014-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 pages2014-03-26 22:21:19 6916 [Note] InnoDB: The InnoDB memory heap is disabled2014-03-26 22:21:19 6916 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions2014-03-26 22:21:19 6916 [Note] InnoDB: Compressed tables use zlib 1.2.32014-03-26 22:21:19 6916 [Note] InnoDB: Not using CPU crc32 instructions2014-03-26 22:21:19 6916 [Note] InnoDB: Initializing buffer pool, size = 128.0M2014-03-26 22:21:19 6916 [Note] InnoDB: Completed initialization of buffer pool2014-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 start2014-03-26 22:21:20 6916 [Note] InnoDB: 5.6.16 started; log sequence number 16259772014-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: 33112014-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 events2014-03-26 22:21:21 6916 [Note] mysqld: ready for connections.Version: ‘5.6.16‘ socket: ‘‘ port: 3311 MySQL Community Server (GPL)
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>
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。