MySQL blank users preventing slave I/O connection?

Hi,
Recently I have encountered a weird simple issue with starting Slave server.

So created user as tried to run change master:

2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
'select @@port'
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
"CREATE USER 'repl'@'%' IDENTIFIED BY 'Baku12345'"
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 
"GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'"
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/sock0.sock --force test -e 
"CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='Baku12345', MASTER_PORT=10023, MASTER_AUTO_POSITION=1"
2017-10-30 14:03:59 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/sock0.sock --force test -e 
'start slave'

After that got the error:

2017-10-30 13:22:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 11, Error_code: 2003
2017-10-30 13:22:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 106, Error_code: 2003
2017-10-30 13:22:57 28984 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 1316, Error_code: 2003
2017-10-30 13:23:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 12, Error_code: 2003
2017-10-30 13:23:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 107, Error_code: 2003
2017-10-30 13:23:57 28984 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 1317, Error_code: 1045
2017-10-30 13:24:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 13, Error_code: 1045
2017-10-30 13:24:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60  retries: 108, Error_code: 1045

I got the same result with MySQL 5.5 today and remember previous case with MySQL 5.6

2017-11-24 10:14:15 DEBUG    Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231117-5.5.58-38.10-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231117-5.5.58-38.10-linux-x86_64-debug/sock1.sock --force test -e 'show slave status\G'
2017-11-24 10:14:15 DEBUG    <pid.PidFile object at 0x7fbe55751cc8> closing pidfile: /tmp/MySQL-AutoXtraBackup/autoxtrabackup.pid
Traceback (most recent call last):
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/bin/autoxtrabackup", line 11, in <module>
    load_entry_point('mysql-autoxtrabackup==1.5.0', 'console_scripts', 'autoxtrabackup')()
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/autoxtrabackup.py", line 209, in all_procedure
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 464, in wipe_backup_prepare_copyback
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 378, in run_change_master
  File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 152, in check_slave_status
RuntimeError: Slave_IO_Running is not Yes
2017-11-24 10:14:15 DEBUG    <pid.PidFile object at 0x7fbe55751cc8> closing pidfile: /tmp/MySQL-AutoXtraBackup/autoxtrabackup.pid

So after that, I tried to connect manually via replication user:

$ /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -urepl --password='Baku12345' --port=10023 --socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES)

Removed blank users from master:

5.6.37>drop user ''@'localhost';
Query OK, 0 rows affected (0.01 sec)

5.6.37>drop user ''@'qaserver-02.ci.percona.com';
Query OK, 0 rows affected (0.00 sec)

Now reconnecting:

$ /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -urepl --password='Baku12345' --port=10023 --socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 623
Server version: 5.6.37-82.2-debug-log MySQL Community Server (GPL)

So it turned out, the blank users were preventing slave connection to master server 🙂

Also there is a nice bug report Slave can’t reconnect after 2003 error when sha256_password is used

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

6 thoughts on “MySQL blank users preventing slave I/O connection?”

  1. Empty users are a security liability and usability burden, because they hijack the connection from intended users. They are removed by default in 5.7.
    In previous versions, you should run mysql_secure_installation, or just remove the blank users manually as in your example.

    Like

    1. I was trying to automate the process of starting slave from physical backup for my tests.
      Created a replication user, grabbed gtid position etc.
      Then I took a backup, prepare, copied-back and started new server.
      Run the change master and start slave. Whoops. It could not connect to master server, then tried to figure out what was the reason, at first I thought something was wrong with my script but then suddenly it turned out to be the blank user issue. That was quite funny 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s