5

I was trying to implement Master-Slave Database Replication on two systems.
I followed the steps from the following links
https://www.youtube.com/watch?v=DmQWcU2INqQ
and
Master Slave Replication

But in both cases, I ended up with

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

I've tried to check the network connection between the machines and they're able to ping each other successfully.
Disabled the antiviruses and Firewalls on both systems
Added an alias for the slave to access the master machine, Working.
Both machines using 3306 port for mysql.

Last_IO_error: error connecting to master 'user01@192.168.2.46:3306' -- retry-time 60 retries: 1

Community
  • 1
Eric Bana
  • 151
  • 1
  • 1
  • 3

10 Answers10

6

I faced a similar error some days ago. I was able to solve that error based on some Google search results. Here are some of the suggestions that might hopefully help you too:

  1. Check whether you created the replication user with required privileges. If not, create a new user on master with the following command:

    Create user ‘repl’@’192.168.2.46’ identified by ‘replpwd’;
Grant replication slave on *.* to ‘repl’@’192.168.2.46’;

  2. If you already created the required replication user, check the privileges of the replication user. You can check the privileges by using the following command:

    show grants for repl;

  3. Usually, this issue occurs because of the incorrect password. In that case you can reset the password on the slave by using the following command:

    change master to master_password = 'rplpassword';

  4. Also, check the for any mistakes in the given replication user and host IP address.

Since, you are able to ping the master server, it is most probably because of the above mentioned reason.

Andriy M
  • 23,261
  • 6
  • 60
  • 103
Rathish Kumar B
  • 2,320
  • 5
  • 24
  • 36
1

You should also check that these two lines are commented on the Master side (for networking issues) as shown below.

# skip-networking  
# bind-address = 127.0.0.1

For complete details on master slave replication, follow this link.

Andriy M
  • 23,261
  • 6
  • 60
  • 103
Gaurang Agarwal
  • 11
  • 1
0

I faced the same issue with the MySQL replication.
I had given the GRANT ALL privileges, which solved my issue, as follows:

GRANT ALL ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

You may need to flush your privileges after you set them with FLUSH PRIVILEGES; FLUSH TABLES WITH READ LOCK;

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Roopa
  • 101
  • 1
0

Try the following:

  1. Stop slave
  2. Start slave

It solved the problem for me today.

Anthony Genovese
  • 2,067
  • 3
  • 22
  • 34
Richardhe2007
  • 119
  • 1
0

I had to restart mysql service on slave (once the import is finished) after that it started working again. Here are the steps

  1. sudo service mysql restart;
  2. mysql -uroot
  3. start slave;
  4. show slave status \G;
mustaccio
  • 28,207
  • 24
  • 60
  • 76
user3593468
  • 1
0

I created a DB in AWS RDS and it showed the same status -

Slave_IO_Running: connecting
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

I needed to allow the egress from RDS to my external master.
You can modify the security group attached to your RDS, or in my case terraform - [you might want to limit the source IPs that can connect to/from].

resource "aws_security_group" "allow_db" {
  name = "${var.customer_name}-${var.environment}-wpdb"
  description = "Security for DB Connections"
  vpc_id      = "${var.vpc_id}"

ingress {
    description = "DB from all"
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
  egress {
    description = "DB to master"
    from_port   = 3306
    to_port     = 3306
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
}
Jeremy
  • 101
  • 2
0

I encountered a similar issue and after searching for one hour, I saw that port 3306 was closed between my master and slave. Be sure it's open and then start slave;

John K. N.
  • 18,854
  • 14
  • 56
  • 117
hn_tired
  • 101
  • 1
0

Try this on master:

GRANT ALL ON *.* to 'username'@'ip' WITH GRANT OPTION;

Then:

FLUSH TABLES WITH READ LOCK;
FLUSH PRIVILEGES

Quit mysql

Restart Mysql: sudo systemctl restart mysql

Re-enter mysql and run:

SHOW MASTER STATUS \G

SHOW MASTER STATUS \G <- command's output will help you to set master from slave server

Do this in slave:

  1. Stop slave if it's already running.
  2. Set the master again with all the paramas.
  3. Start slave
  4. SHOW SLAVE STATUS \G

Hopefully this will resolve your error :)

Naishar Shah
  • 1
0

Ping is not right way to test. Telnet the 3306 on the Master port. Also if you are connecting cross regional or cross datacenter - having intermittent issue of "Connecting" is to be expected.

Phani Kiran Mullapudi
  • 36
  • 2
0

if all other settings and configurations and commands betwen the machines and their IP addresses are ok then try sudo ufw allow 3306 the port 3306 could be blocked by the ubuntu 20 firewall by default run this on the master

lisawebcoder
  • 1