0

So I have a CentOS server that I have started to configure replication on my local machine. When I take a look at the master server with show master status;, I get the following:

+------------------+----------+---------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB        | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------------+------------------+-------------------+
| mysql-bin.000001 |   713473 | some_database       |                  |                   |
+------------------+----------+---------------------+------------------+-------------------+

Then on my slave (which is actually MAMP PRO) I connect it to the master via the following:

CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xx', MASTER_USER='my_slave', MASTER_PASSWORD='my_slave_password!', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=713473;

And when I look at the log from MAMP PRO, I get the following:

170529  2:14:00 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='xxx.xxx.xxx.xx', master_port='3306', master_log_file='mysql-bin.000001', master_log_pos='713473'.
170529  2:14:42 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 713473, relay log './mysql-relay-bin.000001' position: 4
170529  2:14:42 [ERROR] Slave I/O: error connecting to master 'my_slave@xxx.xxx.xxx.xx:3306' - retry-time: 60  retries: 86400, Error_code: 1130

Before trying this method, I tried editing the my.cnf file for MAMP PRO and it didn't work. Since then, I deleted the master.info, which was empty anyways, and tried this method. What else can I try? The log doesn't seem to give me any actual information, is there anywhere else I can look for info?

0

first of all You need to test connection from slave to master You can do this from command line (CMD windows)

mysql --host=xxx.xxx.xxx.xx --port=3306 --user=my_slave --password=my_slave_password!

if it will be successful You will have normal mysql console

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

if not, You must check:

  • network connection
  • my_slave user credentials, including allowed host, like my_slave%yyy.yyy.yyy.yyy where yyy.yyy.yyy.yyy - Your real ip address (in case of MAMP it could be ip of router). this is one of the frequent reasons for Error_code: 1130

if all ok, You can make dump direct from slave:

mysqldump --host=xxx.xxx.xxx.xx --port=3306 --user=my_slave --password=my_slave_password! --single-transaction --master-data=1 --all-databases > c:\dumps\all_db.sql

restore dump on slave:

mysql --host=xxx.xxx.xxx.xx --port=3306 --user=root --password=slave_root_password < c:\dumps\all_db.sql

login to mysql console and start slave:

mysql> start slave;

generally - that all

  • So in your answer, you are making the entire collection of databases slave to those on another server, correct? I really only want to set it up for one database, is that possible? And how? – David Rhoderick Jun 2 '17 at 14:06
  • in this case --all-databases must be changed to --databases db_name , and in my.cnf (my.ini) defined - filter for single database , like -dev.mysql.com/doc/refman/5.6/en/… – a_vlad Jun 3 '17 at 4:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.