Home / ubuntu / Questions / 1320535
In Process
Hrish
Asked: 2021-03-03 06:28:30 +0800 CST

How to solve MYSQL Server declines remote connections after restoring the server from backup?

  • 772

Here are the details:

# cat /etc/release

    DISTRIB_ID=Ubuntu
    DISTRIB_RELEASE=20.04
    DISTRIB_CODENAME=focal
    DISTRIB_DESCRIPTION="Ubuntu 20.04.1 LTS"
    NAME="Ubuntu"
    VERSION="20.04.1 LTS (Focal Fossa)"
    ID=ubuntu
    ID_LIKE=debian
    PRETTY_NAME="Ubuntu 20.04.1 LTS"
    VERSION_ID="20.04"
    HOME_URL="https://www.ubuntu.com/"
    SUPPORT_URL="https://help.ubuntu.com/"
    BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
    PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
    VERSION_CODENAME=focal
    UBUNTU_CODENAME=focal

# mysqld --version


    /usr/sbin/mysqld  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

root@XXXXXXX:~# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep --color bind

bind-address            = 0.0.0.0

Mysql Service Status:

root@XXXXXXX:~# systemctl status mysql.service
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2021-03-02 12:54:00 UTC; 1h 12min ago
    Process: 31460 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 31484 (mysqld)
     Status: "Server is operational"
      Tasks: 40 (limit: 2372)
     Memory: 356.6M
     CGroup: /system.slice/mysql.service
             └─31484 /usr/sbin/mysqld

Mar 02 12:53:59 ip-192-168-1-115 systemd[1]: Starting MySQL Community Server...
Mar 02 12:54:00 ip-192-168-1-115 systemd[1]: Started MySQL Community Server.

root@XXXXXXX:~# tail -100 /var/log/mysql/mysql.log

/usr/sbin/mysqld, Version: 8.0.22-0ubuntu0.20.04.2 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2021-03-02T12:53:51.185036Z        12 Query     FLUSH PRIVILEGES
2021-03-02T12:53:54.040659Z        12 Quit

root@XXXXXXX:~# tail -25 /var/log/mysql/error.log

2021-03-02T11:48:38.852882Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-02T11:48:38.993865Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-02T11:48:39.083889Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-02T11:48:39.084070Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-02T11:48:39.110064Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22-0ubuntu0.20.04.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2021-03-02T12:16:18.763905Z 137 [ERROR] [MY-013134] [Server] Table './flash@002dfast@002dprod@002ddb/videos' is marked as crashed and should be repaired
2021-03-02T12:16:18.765400Z 137 [Warning] [MY-010756] [Server] Checking table:   './flash@002dfast@002dprod@002ddb/videos'
2021-03-02T12:38:29.704195Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.22-0ubuntu0.20.04.2).
2021-03-02T12:38:31.195350Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22-0ubuntu0.20.04.2)  (Ubuntu).
2021-03-02T12:38:31.627975Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-0ubuntu0.20.04.2) starting as process 31217
2021-03-02T12:38:31.637190Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-02T12:38:31.975618Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-02T12:38:32.112947Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-02T12:38:32.200447Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-02T12:38:32.200629Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-02T12:38:32.233977Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22-0ubuntu0.20.04.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).
2021-03-02T12:53:58.504425Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.22-0ubuntu0.20.04.2).
2021-03-02T12:53:59.610387Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.22-0ubuntu0.20.04.2)  (Ubuntu).
2021-03-02T12:54:00.048268Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.22-0ubuntu0.20.04.2) starting as process 31484
2021-03-02T12:54:00.058643Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-03-02T12:54:00.443835Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-03-02T12:54:00.581426Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-03-02T12:54:00.665735Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-03-02T12:54:00.665956Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-03-02T12:54:00.693806Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22-0ubuntu0.20.04.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).

Error when trying to connect from another host which is hashed out as ABCXYZ:

root@ABCXYZ:~# mysql -u'phpmyadmin' -p -h192.168.1.115 -P3306
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.115' (110)
root@ABCXYZ:~#

Using exactly the same command from the mysql server (i.e. in this hashed out as XXXXXXX) & it works fine and we are able to connect:

root@XXXXXXX:~# mysql -u'phpmyadmin' -p -h192.168.1.115 -P3306

I also did packet capture on the "" to ensure that there is no connectivity issue from ""
root@ip-172-31-32-115:~# tcpdump -n host 192.168.1.188
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 262144 bytes



12:43:19.631926 IP 192.168.1.188.47250 > 192.168.1.115.3306: Flags [S], seq 945902876, win 62727, options [mss 8961,sackOK,TS val 1425290709 ecr 0,nop,wscale 7], length 0
12:43:20.649648 IP 192.168.1.188.47250 > 192.168.1.115.3306: Flags [S], seq 945902876, win 62727, options [mss 8961,sackOK,TS val 1425291727 ecr 0,nop,wscale 7], length 0
12:43:22.665634 IP 192.168.1.188.47250 > 192.168.1.115.3306: Flags [S], seq 945902876, win 62727, options [mss 8961,sackOK,TS val 1425293743 ecr 0,nop,wscale 7], length 0
12:43:26.889674 IP 192.168.1.188.47250 > 192.168.1.115.3306: Flags [S], seq 945902876, win 62727, options [mss 8961,sackOK,TS val 1425297967 ecr 0,nop,wscale 7], length 0
12:43:35.081803 IP 192.168.1.188.47250 > 192.168.1.115.3306: Flags [S], seq 945902876, win 62727, options [mss 8961,sackOK,TS val 1425306159 ecr 0,nop,wscale 7], length 0
12:43:51.209876 IP 192.168.1.188.47250 > 192.168.1.115.3306: Flags [S], seq 945902876, win 62727, options [mss 8961,sackOK,TS val 1425322287 ecr 0,nop,wscale 7], length 0
^C
6 packets captured
6 packets received by filter
0 packets dropped by kernel

root@XXXXXXX:~# ufw status

Status: active

To                         Action      From
--                         ------      ----
Apache Full                ALLOW       Anywhere
3306                       ALLOW       127.0.0.1
3306                       ALLOW       192.168.1.106
22/tcp                     ALLOW       Anywhere
3306/tcp                   ALLOW       192.168.1.106
Apache Full (v6)           ALLOW       Anywhere (v6)
networking

0 Answers