Btukfyl

I have mysql on my localhost and I am able to log in with root:

[root@pocnnr1n1 etc]# mysql -u root -p

Enter password:

Welcome to the MariaDB monitor.  Commands end with ; or g.

Your MariaDB connection id is 8

Server version: 5.5.52-MariaDB MariaDB Server



Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.



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



MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

Now, I have a docker on this host which has ip address of '172.17.0.2'

There is no problem ping from docker to host:

root@eaa90c1059f2:/app/airflow/dags# ping 192.168.211.251

PING 192.168.211.251 (192.168.211.251): 56 data bytes

64 bytes from 192.168.211.251: icmp_seq=0 ttl=64 time=0.208 ms

From docker, if I manually run pymysql to create a connection:

conn= pymysql.connect(host='192.168.211.251', port=3306, user='root',

passwd='root', db='airflow')

I have the following error:

pymysql.err.OperationalError: (1045, "Access denied for user
'root'@'172.17.0.2' (using password: YES)")

If I change the ip address to '172.17.0.2' as below:

conn= pymysql.connect(host='172.17.0.2', port=3306, user='root',

passwd='root', db='airflow')

I have the following error:

pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on
'172.17.0.2' ([Errno 111] Connection refused)")

Update:
my.cnf is as below:

[mysqld]

transaction-isolation = READ-COMMITTED

# Disabling symbolic-links is recommended to prevent assorted security risks;

# to do so, uncomment this line:

# symbolic-links = 0



key_buffer = 16M

key_buffer_size = 32M

max_allowed_packet = 32M

thread_stack = 256K

thread_cache_size = 64

query_cache_limit = 8M

query_cache_size = 64M

query_cache_type = 1



max_connections = 550

#expire_logs_days = 10

#max_binlog_size = 100M



#log_bin should be on a disk with enough free space. Replace '/var/lib/mysql/mysql_binary_log' with an appropriate path for your system

#and chown the specified folder to the mysql user.

log_bin=/var/lib/mysql/mysql_binary_log

#explicit_defaults_for_timestamp = 1



binlog_format = mixed



read_buffer_size = 2M

read_rnd_buffer_size = 16M

sort_buffer_size = 8M

join_buffer_size = 8M



# InnoDB settings

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit  = 2

innodb_log_buffer_size = 64M

innodb_buffer_pool_size = 4G

innodb_thread_concurrency = 8

innodb_flush_method = O_DIRECT

innodb_log_file_size = 512M



[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

I just sorted out as below:

This issue is about the right privilege was not granted to the user airflow@172.17.0.2

What I need to do is on the host's mysql,

GRANT ALL TO 'airflow'@'172.17.0.2' IDENTIFIED BY 'airflow' ;

FLUSH PRIVILEGES;

This is very clear if you do: select User, Host, Password from mysql.user;

Now the connection can be created using pymysql.

For the convenience of testing, one does not have to test it within airflow dag, this can be tested using python only (but with pymysql imported)

Hope this help.

This is from the mysql-doc

Make sure that the server has not been configured to ignore network
connections or (if you are attempting to connect remotely) that it has
not been configured to listen only locally on its network interfaces.
If the server was started with --skip-networking, it will not accept
TCP/IP connections at all. If the server was started with
--bind-address=127.0.0.1, it will listen for TCP/IP connections only locally on the loopback interface and will not accept remote
connections.

When something from the docker network try to connect the localhost: it's also trying to access the mysql from remote.

Find the my.cnf(usually /etc/mysql/my.cnf) comment the line bind-address=127.0.0.1