Access denied when trying to connect to remote MySQL from Google Cloud Virtual Machine

[Anthony Fuentes]

I created a Google Cloud Virtual Machine with a static IP address. I added that IP address into Remote MySQL on my dedicated server that is hosted somewhere else. Every time I run my sample code below I am getting this error:

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

I am able to run my sample code from my home computer without any issues. I am pretty sure it has something to do with the Google Cloud Virtual Machine. I tried many different settings for the firewalls, etc... and Googled a decent amount as well. Have not found anything talking about this problem directly. If anyone has any insight, it would be much appreciated.

import pymysql.cursors


if __name__ == '__main__':
    host = 'XXX.XXX.XXX.XXX'
    user = 'user'
    password = 'password'
    db = 'db'
    charset = 'utf8mb4'
    cursorclass = pymysql.cursors.DictCursor


    connection = pymysql.connect(host=host,user=user,password=password,db=db,charset=charset,cursorclass=cursorclass)
    with connection.cursor() as cursor:
        cursor.execute('TRUNCATE TABLE my_table')
    connection.close()

[Quinn Comendant]

On Sun, 10 Jun 2018 07:05:11 -0700 (PDT), Anthony Fuentes wrote:
>> pymysql.err.OperationalError: (1045, "Access denied for user
>> 'user'@'XXX.XXX.XXX.XXX' (using password: YES)")

Make sure you have granted access for that user from the IP you are connecting from. If you run this query while on your GCE instance:

mysql -e "SELECT * FROM information_schema.schema_privileges"

You should see find like this in the first column:

'you'@'111.111.111.111'

Where 'you' is the mysql user and '111.111.111.111' is the IP of your dedicated server.

Quinn

[Anthony Fuentes]

I am trying to connect to a MySQL database that is on my dedicated server from the GCE instance (the same code I posted is what I am running on the GCE instance). Little confused as to why I would run that command on the GCE instance but I did anyways and got the following error "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)".

[Quinn Comendant]

On Sun, 10 Jun 2018 13:01:54 -0500, Anthony Fuentes wrote:
> I am trying to connect to a MySQL database that is on my dedicated

> server from the GCE instance […]

Oh, sorry, I misunderstood.

You want to check the grants of the server where mysqld is running, so you would run the command I gave you on your dedicated server, in that case. You then want to ensure that a grant exists to connect from the GCE server's public IP address. If the GCE IP isn't shown in the first column, you'll need to add a grant that allows it using:

GRANT ALL ON `DBNAME`.* to `USER`@`HOST`

Where HOST is your GCE public IP. (Assuming you've already set a password for USER; if not, you can set one at the same time using:

GRANT ALL ON `DBNAME`.* to `USER`@`HOST` IDENTIFIED BY 'DBPASS'

You may not want to give "ALL" permissions to this user, just give as much as is needed to do the job.

Quinn

[Anthony Fuentes]

Thank you! That help me figure it out. For some reason the user was wrong for that user@ip. I just went into cpanel, removed the GCE IP and added it again. Now it works! Crazy lol, thank you again!