MySQL Replication on Windows Worked Example



Introduction...

This is a worked example showing how MySQL replication can be set up on the same Windows 10 host.

The master is on port 3306 (version MySQL 5.6.33)
The slave is on port 3307 (version MySQL 5.7)

During the description below the two database instances will be referred to as master and slave.

To access the slave database instance using windows command specified the port to use:-

mysql -uroot -p --port=3307

Step 1 - Create Directories for master and slave replication files

Although this is a windows host, for this example I've created and set similar directory names to linux:-

C:\var\lib\master
C:\var\lib\slave

NB Without separate directories for master and slave there are likely to be file access error problems
(due to this example having two database instances being run on the same host)

Step 2 - Add settings to master my.ini file

Added below [mysqld] in file:

server-id=56
binlog-do-db=repl_test
log-bin = "c:/var/lib/master/mysql-bin"

Step 3 - Add settings to slave my.ini file

Added below [mysqld] in file:

server-id=57
replicate-do-db=repl_test
relay-log = "c:/var/lib/slave/mysql-relay-bin"
relay-log-index = "c:/var/lib/slave/mysql-relay-bin.index"
master-info-file = "c:/var/lib/slave/mysql-master.info"
relay-log-info-file = "c:/var/lib/slave/mysql-relay-log.info"
log-bin = "c:/var/lib/slave/mysql-bin"


NB: Make sure there is only one line with server-id in the my.ini files.

Step 4 - Set up repl_test database and table in master

create database repl_test;
use repl_test;

CREATE TABLE repl_time (
id INT NOT NULL AUTO_INCREMENT,
ref VARCHAR(30) NOT NULL,
time_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
 );

insert repl_time ( ref ) values ( "test1");
insert repl_time ( ref ) values ( "test2");
insert repl_time ( ref ) values ( "test3");


Step 5 - Commands on master

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slavepass';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES;
mysql> SHOW MASTER STATUS;

The output showing the file name (mysql-bin.000001) position (1693) is used later:

File	                Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	1693	        repl_test		

Note the corresponding settings for your system and replace in the commands below.

Step 6 - Restart MySQL master

Re-start the master database instance.

select @@server_id;
The output should show @@server_id as 56.

Step 7 - Dump database repl_test on master

mysqldump -uroot -p repl_test > repl_test.sql

Step 8 - create repl_test database on the slave

mysql -uroot -p --port=3307

create database repl_test;

Step 9 - Load database repl_test on the slave

mysql -uroot -p repl_test --port=3307 < repl_test.sql

Step 10 - Re-start MySQL on slave

Re-start the slave database instance.

select @@server_id;
The output should show @@server_id as 57.

Step 11 - commands on slave

(using values of mysql-bin.000001 and 1693 from above)

Run change master command:
(values will be different for your system and will need to be changed)
mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='slave_user', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1693;


The error log then shows the entry:
2016-11-03T17:07:26.933162Z 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='localhost', master_port= 3306, master_log_file='mysql-bin.000001', master_log_pos= 1693, master_bind=''.


Show slave status will confirm the settings for the replication:
mysql> SHOW SLAVE STATUS\G;


Start slave:
mysql> START SLAVE;


Show slave status will now show "Slave_IO_State: Waiting for master to send event":
mysql> SHOW SLAVE STATUS\G;
mysql replication



























As the master has been re-started, the current master log file shown above has moved to mysql-bin.000002

Step 12 - Test with inserts into master

insert repl_time ( ref ) values ( "test4");
insert repl_time ( ref ) values ( "test5");

If these values show in the slave database instance then things are going well.
Assuming there is no other activity on the system the update to the slave will be instantaneous.

Error in show slave status

In the event of having the connection error below it might be that the passwords are not correct at both ends:

In SHOW SLAVE STATUS\G;
Last_IO_Error: error connecting to master 'slave_user@localhost:3306

Run CHANGE MASTER command again on slave (changing the various settings for your system)
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='slave_user', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;

The same error will show if the MASTER_LOG_FILE and MASTER_LOG_POS values are incorrect.

To check the correct values use the binary mysqlbinlog.exe:

mysqlbinlog filename_filepath

Signs that things are going ok...

In show slave status:
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Versions used

master
MySQL:
version 5.6.33
version_comment MySQL Community Server (GPL)
version_compile_machine x86_64
version_compile_os Win64
port 3306

slave
MySQL:
version 5.7.15
version_comment MySQL Community Server (GPL)
version_compile_machine x86_64
version_compile_os Win64
port 3307

Note: on linux systems the my.ini is called my.cnf.

server-id and server_id confusion

It seems that both of the following are accepted ie work in the my.ini:

with MINUS
server-id = 56
or UNDERSCORE
server_id = 56

The test to check that the my.ini change has worked:
mysql> select @@server_id;

Changing slave user password on master and slave

To change the password on the master:
log in as root, then:

SET PASSWORD FOR slave_user = PASSWORD('slavepass_new');


To change the password on the slave:
log in as root, then:

STOP SLAVE;
CHANGE MASTER TO MASTER_PASSWORD='slavepass_new';
START SLAVE;
The slave password is stored in the mysql-master.info file.

Skipping statements

There might be a reason for wanting to skip a transaction.
To test this procedure out use the following on the slave:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

For example the slave could be stopped, two new rows added to repl_time on master. Using these command above it can be shown that the first row is not added to the slave.

Other commands used along the way

Cleared errors on one occasion
mysql> RESET SLAVE;

From the master when server_id was not being recognised:-
mysql> SET GLOBAL server_id = 56;
This will set the server_id while the master database instance is running.
If the value is not changed in the my.ini file the server_id value will be changed back to the previous value.

Conclusion on the MySQL replication software

Coming from a Sybase DBA background it seems very helpful that MySQL replication should be available included and built into the base software installation.

In comparison I contacted Sybase / SAP to try to obtain a developer's version of ASE Replication Server and apparently it was not possible to download the software even if I offered to pay for a developers licence.

Disclaimer

This page was created due to few step by step illustrations being available of how to set up MySQL replication on Windows using the same host for two database instances.
It is intended for assistance in learning, test or development environments only.

No liability will be accepted as a result of adverse effects of using information on this webpage in commercial environments.

Page created on 3rd Nov 2016

Error Messages experienced when wrongly using same replication directories for both master and slave

Initially I had the messages below, then set up separate directories for master and slave as both database instances are on the same host.

2016-11-02T17:03:49.594039Z 3 [ERROR] While rebuilding index file c:\var\lib\mysql\mysql-relay-bin.index: Failed to rename the new index file to the existing index file.
2016-11-02T17:03:49.595039Z 3 [ERROR] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe: Binary logging not possible. Message: MySQL server failed to update the binlog.index file's content properly. It might not be in sync with available binlogs and the binlog.index file state is in unrecoverable state. Aborting the server.
17:03:49 UTC - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=65536
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 58347 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x1e0f367d7a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff742a6e262    mysqld.exe!my_errno()
7ff742e18249    mysqld.exe!my_wildcmp_mb()
7ff742e17140    mysqld.exe!my_wildcmp_mb()
7ff742a1d507    mysqld.exe!?erase@?$list@V?$basic_string@DU?$char_traits@D@std@@V?$allocator@D@2@@std@@V?$allocator@V?$basic_string@DU?$char_traits@D@std@@V?$allocator@D@2@@std@@@2@@std@@QEAA?AV?$_List_iterator@V?$_List_val@U?$_List_simple_types@V?$basic_string@DU?$char_traits@D@std@@V?$allocator@D@2@@std@@@std@@@std@@@2@V?$_List_const_iterator@V?$_List_val@U?$_List_simple_types@V?$basic_string@DU?$char_traits@D@std@@V?$allocator@D@2@@std@@@std@@@std@@@2@@Z()
7ff742a21a1e    mysqld.exe!?move_crash_safe_index_file_to_index_file@MYSQL_BIN_LOG@@QEAAH_N@Z()
7ff742a18f62    mysqld.exe!?add_log_to_index@MYSQL_BIN_LOG@@QEAAHPEAE_K_N@Z()
7ff742a22fdf    mysqld.exe!?open_binlog@MYSQL_BIN_LOG@@QEAA_NPEBD0K_N11PEAVFormat_description_log_event@@@Z()
7ff742a56471    mysqld.exe!?rli_init_info@Relay_log_info@@QEAAHXZ()
7ff742a3e8f2    mysqld.exe!?global_init_info@@YAHPEAVMaster_info@@_NH@Z()
7ff742a3a2eb    mysqld.exe!?change_master@@YAHPEAVTHD@@PEAVMaster_info@@PEAUst_lex_master_info@@_N@Z()
7ff742a3a93e    mysqld.exe!?change_master_cmd@@YA_NPEAVTHD@@@Z()
7ff742365ed2    mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z()
7ff742369583    mysqld.exe!?mysql_parse@@YAXPEAVTHD@@PEAVParser_state@@@Z()
7ff74236274d    mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z()
7ff74236375a    mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z()
7ff74232a10c    mysqld.exe!handle_connection()
7ff742d559f2    mysqld.exe!?reserve@?$vector@EV?$allocator@E@std@@@std@@QEAAX_K@Z()
7ff742a6e0cb    mysqld.exe!my_thread_once()
7ff742e17cbf    mysqld.exe!my_wildcmp_mb()
7ff742e17f0a    mysqld.exe!my_wildcmp_mb()
7ffa7b5c8364    KERNEL32.DLL!BaseThreadInitThunk()
7ffa7dcc5e91    ntdll.dll!RtlUserThreadStart()

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (1e0f36c2930): CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='slave_user', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=403
Connection ID (thread ID): 3
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2016-11-02T17:44:13.850040Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-11-02T17:44:13.850040Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
Push Symbol. Push Symbol. Door logos