Update remote mysql (from local) in terminal

I wonder if I could update a table in a remote database without using phpmyadmin.
I have remote IP, user and psw of the remote db.
I tried this code mysql -h [remote-IP] -u [myusername] -P 3306 -p, and I provided th correct psw, but I get this error:

ERROR 2002 (HY000): Can't connect to server on [remote-IP]

I have mariadb, and I changed, in local config file, the bind-address to
I know that there should be other steps, but my question is: it’s worth my effort?
On the one hand mantainer said that it would not possible bypass phpmyadmin, on the other WordPress can update the remote database without phpmyadmin.
Thank you!

do you have permission to modify the remote server’s firewall to allow connections on port 3306? Cause if not, you’re probably stuck.

Opening the port is a security vulnerability, so many hosts wont let you; but if you’re getting data remotely, it should be open…

1 Like

He also needs a user with access @%.

1 Like

indeed, but the message he’s getting right now indicates he cant reach the server, let alone a user with the right hostmask.

1 Like

Thank you, @m_hutley and @Thallius: if I follow other steps (such as said here, for example) there is some hope to reach my aim, or without permissions that only the maintainer can provide no effort could succeed?

Your best bet is to create an SSH tunnel to your webhost. If you have access to SSH that is.

ssh -L 3306:IP.of.database.server:3306 user@IP.of.web.server

Once you’re connected, connect to MySQL on localhost

1 Like

I tried your code, but I got this message:

ssh: connect to host [my-server-IP] port 22: Connection timed out

ops, my mistake: I used the same Ip for database.server and for web.server
indeed I don’t know the difference …

Maybe the simplest way, if I have to add few new rows in my database, is to dump these single rows and insert them on remote trough a sql query phpmyadmin.
However I wonder if importing a whole table in a (remote) database the same-named existing table is correctly replaced. If so, using phpmyadmin would be easier than I so far expected.

generally speaking, if you’re going to import an entire table as a replacement, the first lines of the import are DROP TABLE tablename; CREATE TABLE tablename [table definition code goes here]; then the record imports. It’s not about replacing per-se, as it is blowing it away and starting again.

(or DROP TABLE tablename IF EXISTS, but the effect is the same)


Indeed, DROP TABLE tablename; did the trick!
Otherwise I would get an error message (table already exists).
Thank you!!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.