Update remote mysql (from local) in terminal

[web148]

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 0.0.0.0.
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!

[m_hutley]

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…

[Thallius]

He also needs a user with access @%.

[m_hutley]

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

[web148]

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?

[rpkamp]

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

[web148]

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 …

[web148]

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.

[m_hutley]

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)

[web148]

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