When I want to build connections between database and Python programs with SQLAlchemy on Google Colab, it occurs errors saying: (mysql.connector.errors.InterfaceError) 2003: Can’t connect to MySQL server on ‘localhost:3306’ (111 Connection refused) and Can’t connect to MySQL server on ‘localhost:3306’ [Errno 99] Cannot assign requested address. Show Here’s my solution. It can also be understood as to how to allow remote MySQL database connection. Step1. Modify bind-address sudo vim /usr/local/etc/my.cnf While the location of ‘bind-address’ in files is not fixed, it may summarize are:
2. /etc/mysql/mariadb.conf.d/50-server.cnf 3. /etc/mysql/mysql.conf.d 4. /etc/mysql/mariadb.conf.d 5. /etc/mysql/my.conf ……. In this .cnf, the mission is to replace ‘bind-address = 127.0.0.1’ by ‘bind-address = 0.0.0.0’. First, ‘Ctrl + v’ to enter —VISUAL BLOCK — mode. Use arrow keys to select lines that need to be adjusted. Secondly, press ‘Shift+i’ to enter insert mode. That’s to say we will comment out the line ‘bind-address = 127.0.0.1’ with ‘#’ and insert a new line ‘bind-address = 0.0.0.0’. After done all the modifications, press ‘ESC’ to change mode, then input:
2. :w file- save the changes as a new file, not exit from vim 3. :wq- save files and exit Before the next step, check the changes do exits in target files. Step2. Restart MySQL Server service mysql start Step3. Create new users to visit remotely mysql -u root -p Then we will need to add a user to access the database. grant all privileges on *.* to root@"%" identified by "."; The above code, which you might find the most common, will lead to ERROR 1064 (42000). Because MySQL 8.0 hasn’t supported this method anymore. The correct way is: CREATE USER 'user'@'xx.xx.xx.xx' IDENTIFIED BY 'password'; If you want the user to manage all databases, use ‘*’ as database_name. If you want to connect from anywhere, replace ‘xx.xx.xx.xx’ with ‘%’. For example, 6.2.22 Troubleshooting Problems Connecting to MySQLIf you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.
How do I fix MySQL connection refused?Check that the DB username, DB password, database host, and database port are correct. (if you are unsure, reach out to your database administrator or check in your web hosting account for the up to date credentials). If the config file references host = "localhost" , you can try to change it to 127.0.
Can't connect to database connection refused?A Connection Refused (IP Address) error occurs when: You use the wrong IP address in the connection string. Use the database's private IP address in the connection string and try to connect from a Droplet that isn't allowed to access the VPC network.
Why do I get connection refused?The two most common causes of this are: Misconfiguration, such as where a user has mistyped the port number, or is using stale information about what port the service they require is running on. A service error, such as where the service that should be listening on a port has crashed or is otherwise unavailable.
How do I fix MySQL server error?Some permanent solutions are:. Determine what is wrong with your DNS server and fix it.. Specify IP addresses rather than host names in the MySQL grant tables.. Put an entry for the client machine name in /etc/hosts on Unix or \windows\hosts on Windows.. Start mysqld with the skip_name_resolve system variable enabled.. |