Can not connect to local MySQL server thru Apps Script (JDBC)

Question

Please see edits below, I'm leaving the original question to help others.

I am attempting to connect to a local MySQL server thru Apps Script and JDBC, but I keep getting one of two errors. This code:

function connectTest() {
  var conn = Jdbc.getConnection("jdbc:mysql://localhost", "root", "xxx");
}

Gives an error of Failed to establish a database connection. Check connection string, username and password.

This code:

function connectTest() {
  var conn = Jdbc.getConnection("jdbc:mysql://localhost:3306", "xxx", "pass");
}

Gives an error of Invalid argument: url.

I've tried dozens of combinations and can't get it to work. The attempts to log in from Apps Scripts do not show on the access log for MySQL (i.e. if I try to log on locally with the wrong info, I see [Note] Access denied for user 'host'@'localhost' (using password: YES). I've granted the appropriate access rights to root:

mysql> show grants for 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Do I have to do something else to expose the local DB to the internet / Google Apps Script?

EDIT:

I'm now able to get an access denied note from MySQL log (e.g. Access denied for user 'root'@'12.123.12.123' (using password: YES)), but the app still can't connect. I tried adding bind-address with my public IP, but MySQL won't start, I get ([ERROR] Can't start server: Bind on TCP/IP port: Can't assign requested address)

function connectTest() {
  var conn = Jdbc.getConnection("jdbc:mysql://12.123.12.123:3306", "root", "xxx");
}

EDIT2: I changed bind-address=0.0.0.0 and that does allow me to get denied access errors in the DB log, but still no luck connecting. I've successfully connected to other open MySQL DB's on the internet (e.g. ensembldb.ensembl.org:3306), but I still can't connect to my local DB.

Answer 1

Finally got it, here's how I got it to work:

1. Edit my.cnf with (this file can be located several different places and may not even exist, I put mine here /usr/local/etc/:

   [mysqld]
   bind-address=0.0.0.0
   skip-host-cache
   skip-name-resolve
   

2. Open port 3306 to internet (for Xfinity, that meant going to http://10.0.0.1/ > clicking "Advanced" on left > "Port Forwarding" > "Add Device" on the bottom, find the device MySQL is running on and click add)

3. Grant appropriate permission in MySQL and FLUSH PRIVILEGES;. You can do this with GRANT ALL PRIVILEGES ON *.* TO <USER>@'%' IDENTIFIED BY '<PASSWORD>', but that allows any IP, the Python script below will grant the Google IP's. You should check this IP list against Google's documentation for JDBC.

   from sqlalchemy import create_engine, MetaData
   
   conString = 'mysql+pymysql://<USER>:<PASSWORD>@localhost:3306/<DB>'
   mysql = create_engine(conString)
   
   ips = ['64.18.0.0 - 64.18.15.255',
   '64.233.160.0 - 64.233.191.255',
   '66.102.0.0 - 66.102.15.255',
   '66.249.80.0 - 66.249.95.255',
   '72.14.192.0 - 72.14.255.255',
   '74.125.0.0 - 74.125.255.255',
   '173.194.0.0 - 173.194.255.255',
   '207.126.144.0 - 207.126.159.255',
   '209.85.128.0 - 209.85.255.255',
   '216.239.32.0 - 216.239.63.255']
   
   for ip in ips:
       ip2 = ip.replace(' - ', '/')
       try:
           sql = "CREATE USER '<USER>'@'" + ip2 + "' identified by '<PASSWORD>';"
           mysql.execute(sql)
       except:
           print ip2
       sql = "GRANT ALL PRIVILEGES ON <DB>.* TO '<USER>'@'" + ip2 + "';"
       mysql.execute(sql)