MySQL主从复制配置-windows单机环境

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;来将新建的用户及权限生效

MySQL主从复制配置-windows单机环境,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。