MySQL Forums
Forum List  »  Utilities

mysqlreplicate adds user during setup; replication fails.
Posted by: Jerry Richart
Date: March 11, 2016 01:29PM

I am attempting to set up replication using the mysqlreplicate utility. When I execute it, the utility adds a user during execute. The utility completes successfully but when I check the slave status, the new user fails on the slave. I've had it working before on these servers, just not since the last MySQL patch when we went from 5.6.27 to 5.6.29.

Can anyone shed some light on what I'm doing incorrectly? Or is this a bug?

Below are the steps I've taken:

Environment
Operating System: Both the master and slave are on WINDOWS 2008R2 Server (Version 6.1.7601)
MySQL Server Version: 5.6.29-log MySQL Community Server (GPL)
MySQL Utilities: MySQL Utilities mysqlreplicate version 1.5.6 License type: GPLv2

On PTWMYS03 (master in replication configuration)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+----------------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------------+----------+--------------+------------------+-------------------+
| PTWMYS03_MySQLtrnx_binlog.000001 | 151 | | | |
+----------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Now I'm backing up all databases to one file to restore all databases on the slave in order to sync the slave with the master.

Copying the backup from the master to the slave.

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

On PTWMYS04 (slave)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> set global slow_query_log = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log = 0;
Query OK, 0 rows affected (0.00 sec)

Restoring backup from master to slave.

O:\>mysql -u******** -p********* < F:\MySQL\Backups\PTWMYS03_Full_AllDBs_20160311102608.sql
Warning: Using a password on the command line interface can be insecure.

O:\>

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global general_log = 1;
Query OK, 0 rows affected (0.00 sec)

Prior to setting up replication on the master
mysql> select user, host from mysql.user where user = 'repl_slave_user';
+-----------------+-----------+
| user | host |
+-----------------+-----------+
| repl_slave_user | % |
| repl_slave_user | localhost |
+-----------------+-----------+
2 rows in set (0.00 sec)

Prior to setting up replication on the slave
mysql> select user, host from mysql.user where user = 'repl_slave_user';
+-----------------+-----------+
| user | host |
+-----------------+-----------+
| repl_slave_user | % |
| repl_slave_user | localhost |
+-----------------+-----------+
2 rows in set (0.00 sec)

Setting up Replication between PTWMYS03 (master) and PTWMYS04 (slave)
mysqlreplicate --master=********:**********@PTWMYS03:3306 --slave=*********:**********@PTWMYS04:3306 --rpl-user=repl_slave_user:************

WARNING: Using a password on the command line interface can be insecure.
# master on PTWMYS03: ... connected.
# slave on PTWMYS04: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.


After setting up replication on the master
mysql> select user, host from mysql.user where user = 'repl_slave_user';
+-----------------+-----------+
| user | host |
+-----------------+-----------+
| repl_slave_user | % |
| repl_slave_user | localhost |
| repl_slave_user | ptwmys04 |
+-----------------+-----------+
3 rows in set (0.02 sec)

Checking Slave Status
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: PTWMYS03
Master_User: repl_slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: PTWMYS03_MySQLtrnx_binlog.000003
Read_Master_Log_Pos: 24734
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 13629
Relay_Master_Log_File: PTWMYS03_MySQLtrnx_binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Worker 3 failed executing transaction '' at master log PTWMYS03_MySQLtrnx_binlog.000003, end_log_pos 24734; Error 'Operation CREATE USER failed for 'repl_slave_user'@'pt
wmys04'' on query. Default database: ''. Query: 'CREATE USER 'repl_slave_user'@'ptwmys04' IDENTIFIED BY PASSWORD '*D995B6164605FDBA622EE64B512430BC296CECC4''
Skip_Counter: 0
Exec_Master_Log_Pos: 13387
Relay_Log_Space: 25181
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL

Jerry Richart
Database Administrator for Microsoft SQL Server and MySQL.

Stand Out! Be a beacon for others to follow.

Options: ReplyQuote


Subject
Views
Written By
Posted
mysqlreplicate adds user during setup; replication fails.
1628
March 11, 2016 01:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.