MySQL master-slave replication configuration-windows single-host environment _ MySQL

MySQL master-slave replication configuration-windows single-host environment bitsCN. in the comwindows8.1 system, configure mysql master-slave replication. test Environment Overview win8os, 64-bit OS, memory, download mysql5.6.16-winx64.zip package on the local disk decompress two mysql, ports 3310 and 3311, namely: localhost: 3310 -- master server localhost: 3311 -- slave server
2. configure the master to decompress the compressed package: C:/software/mysql-5.6.16-winx64-3310 create a tmp folder under this directory add my. ini file under this directory, the content is as follows:

# 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 = 128 M # 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 = 128 M # sort_buffer_size = 2 M # Second = 2Msql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES [client] port = 3310 # default-character-set = utf8

The server-id variable indicates the unique representation of the node in the cluster. each node must use a different server-id to distinguish basedir, datadir, tmpdir indicates the storage path of data files or temporary files, because we create two mysql database services on one machine to avoid data interference between two instances, in the configuration file, we specify the port location: this parameter is the listening port of the mysql service. Here we set 3310log-bin: to set the server to the master, make sure that the server has an active binary log, which retains all changes on the server and can be re-executed on the slave. start the master Command line switch to C:/software/mysql-5.6.16-winx64-3310/bin (master extract directory), execute mysqld -- console Command, the following information, the master is started:

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 ). 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 '::'; 22:20:49 7204 [Note] Server socket created on IP :'::'. 22:20:50 7204 [Note] Event schedld: 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)

4. on the master node, create a copy User 4.1 to modify the default root password. if it is an existing database, this step will be skipped in the change password Section C: /software/mysql-5.6.16-winx64-3310/mysqladmin-uroot password 111111 through the above command, we give the default root account a password 1111114.2 add copy user and give permissions: C: /software/mysql-5.6.16-winx64-3310/mysql-uroot-p111111 go to mysql database

Mysql> create user repl_user; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *. * to repl_user identified by '20140901'; Query OK, 0 rows affected (111111 sec) mysql>

Replication slave has no special permissions, but this user can obtain binary log dump data from the master and Grant replication slave permissions to a regular account, however, it is better to separate the replication slave permission from other user permissions. in this way, if you want to disable some slave links later, you only need to delete this user. configure slave to decompress the compressed package: C:/software/mysql-5.6.16-winx64-3311 create tmp folder under this directory add my. the content of the INI file is as follows:

# 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 = 128 M # 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 = 128 M # sort_buffer_size = 2 M # Second = 2Msql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES [client] port = 3311 # default-character-set = utf8

Like the master, slave also needs to configure a unique server id. relay-log and relay-log-index indicate the relay log file and relay log index file 6. start the slave command line to switch to C:/software/mysql-5.6.16-winx64-3311/bin (slave extract directory), execute the mysqld -- console Command, the following information is required, the slave is started:

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 ). 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 '::'; 22:21:20 6916 [Note] Server socket created on IP :'::'. 22:21:21 6916 [Note] Event schedld: 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)

7. connecting slave to the master now only has the last step to create a basic copy: point the slave to the master to let it know where to copy. Therefore, you need to know four information about the master: host name, host port number, host database user, host database account password. because a replication user has been created when the master is created, execute the following command on the slave node:

Mysql> change master to master_host = 'localhost', master_port = 3310, master_user = 'repl _ user', master_password = '000000'; Query OK, 0 rows affected, 2 warnings (0.45 sec) mysql>

Mysql> start slave; Query OK, 0 rows affected (0.05 sec) mysql>

8. after troubleshooting and executing the preceding command, the following information is output: 23:11:23 6916 [ERROR] Slave I/O: error connecting to master 'repl _ user @ localhost: 3310 '-retry-time: 60 retries: 1, Error_code: 1045 indicates that the link between the slave and the master is not established successfully. in this case, after the repl_user user is added, if the permission does not take effect, execute flush privileges on the master to take the new user and permissions into effect. bitsCN.com