Remote MySQL Connection

[cchilderhose]

How do I access the MySQL database remotely? Is there commands to allow remote connections from applications like SQLyog?

I have all credentials but when testing connections get error 2003 - Can't connect to MySQL serer on IP address (0)

I need to pull data that the Web interface does not allow me to do.

[slansing]

You may need to enable remote access, as I believe mysql is set up by default to only accept connections from localhost:

http://www.cyberciti.biz/tips/how-do-i- ... erver.html

[cchilderhose]

At Step #5 of granting access to my laptop IP and get the following error -

ERROR 1146 (42S02) : Table 'nagiosql.db' doesn't exist

How do I get around this?

[tmcdonald]

What command is giving that error? Please post it exactly as you entered it.

[lmiltchev]

Can you show us the actual command that you are running from the command line, along with the output from it? (hide sensitive info)

Example:

Code: Select all

# echo 'show databases;' | mysql -t -u test -p'test' -h 192.168.x.x
+--------------------+
| Database           |
+--------------------+
| information_schema |
| nagiosql           |
| test               |
+--------------------+

[cchilderhose]

Here is the command that I am running and error -

mysql> update db set Host='X.X.X.X' where Db='nagiosql';
ERROR 1046 (3D000): No database selected

If I then select the database 'nagiosql' I then get error -

mysql> update db set Host='X.X.X.X' where Db='nagiosql';
ERROR 1146 (42S02): Table 'nagiosql.db' doesn't exist

So what is the correct command?

[lmiltchev]

Are you connected to the remote mysql server? If you are, run the following command and show us the output:

Code: Select all

show databases;

Select the database that you want to work with:

Code: Select all

use <database name>

Show tables:

Code: Select all

show tables;

If "db" is the name of your table, run:

Code: Select all

desc db;

and show us the output.

[cchilderhose]

Here is the output from each command -

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| nagiosql |
| test |
+--------------------+


mysql> use nagiosql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed



mysql> show tables;
+--------------------------------------------+
| Tables_in_nagiosql |
+--------------------------------------------+
| tbl_command |
| tbl_contact |
| tbl_contactgroup |
| tbl_contacttemplate |
| tbl_domain |
| tbl_host |
| tbl_hostdependency |
| tbl_hostescalation |
| tbl_hostextinfo |
| tbl_hostgroup |
| tbl_hosttemplate |
| tbl_info |
| tbl_lnkContactToCommandHost |
| tbl_lnkContactToCommandService |
| tbl_lnkContactToContactgroup |
| tbl_lnkContactToContacttemplate |
| tbl_lnkContactToVariabledefinition |
| tbl_lnkContactgroupToContact |
| tbl_lnkContactgroupToContactgroup |
| tbl_lnkContacttemplateToCommandHost |
| tbl_lnkContacttemplateToCommandService |
| tbl_lnkContacttemplateToContactgroup |
| tbl_lnkContacttemplateToContacttemplate |
| tbl_lnkContacttemplateToVariabledefinition |
| tbl_lnkHostToContact |
| tbl_lnkHostToContactgroup |
| tbl_lnkHostToHost |
| tbl_lnkHostToHostgroup |
| tbl_lnkHostToHosttemplate |
| tbl_lnkHostToVariabledefinition |
| tbl_lnkHostdependencyToHost_DH |
| tbl_lnkHostdependencyToHost_H |
| tbl_lnkHostdependencyToHostgroup_DH |
| tbl_lnkHostdependencyToHostgroup_H |
| tbl_lnkHostescalationToContact |
| tbl_lnkHostescalationToContactgroup |
| tbl_lnkHostescalationToHost |
| tbl_lnkHostescalationToHostgroup |
| tbl_lnkHostgroupToHost |
| tbl_lnkHostgroupToHostgroup |
| tbl_lnkHosttemplateToContact |
| tbl_lnkHosttemplateToContactgroup |
| tbl_lnkHosttemplateToHost |
| tbl_lnkHosttemplateToHostgroup |
| tbl_lnkHosttemplateToHosttemplate |
| tbl_lnkHosttemplateToVariabledefinition |
| tbl_lnkServiceToContact |
| tbl_lnkServiceToContactgroup |
| tbl_lnkServiceToHost |
| tbl_lnkServiceToHostgroup |
| tbl_lnkServiceToServicegroup |
| tbl_lnkServiceToServicetemplate |
| tbl_lnkServiceToVariabledefinition |
| tbl_lnkServicedependencyToHost_DH |
| tbl_lnkServicedependencyToHost_H |
| tbl_lnkServicedependencyToHostgroup_DH |
| tbl_lnkServicedependencyToHostgroup_H |
| tbl_lnkServicedependencyToService_DS |
| tbl_lnkServicedependencyToService_S |
| tbl_lnkServiceescalationToContact |
| tbl_lnkServiceescalationToContactgroup |
| tbl_lnkServiceescalationToHost |
| tbl_lnkServiceescalationToHostgroup |
| tbl_lnkServiceescalationToService |
| tbl_lnkServicegroupToService |
| tbl_lnkServicegroupToServicegroup |
| tbl_lnkServicetemplateToContact |
| tbl_lnkServicetemplateToContactgroup |
| tbl_lnkServicetemplateToHost |
| tbl_lnkServicetemplateToHostgroup |
| tbl_lnkServicetemplateToServicegroup |
| tbl_lnkServicetemplateToServicetemplate |
| tbl_lnkServicetemplateToVariabledefinition |
| tbl_lnkTimeperiodToTimeperiod |
| tbl_logbook |
| tbl_mainmenu |
| tbl_service |
| tbl_servicedependency |
| tbl_serviceescalation |
| tbl_serviceextinfo |
| tbl_servicegroup |
| tbl_servicetemplate |
| tbl_settings |
| tbl_submenu |
| tbl_timedefinition |
| tbl_timeperiod |
| tbl_user |
| tbl_variabledefinition |
| tmp_service |
+--------------------------------------------+
89 rows in set (0.00 sec)



mysql> desc db;
ERROR 1146 (42S02): Table 'nagiosql.db' doesn't exist

[bdgoecke]

That example is confusing..

The "db" in that sql command refers to a TABLE, and a COLUMN named "Db" in the "mysql" DATABASE. (With in mysql there is a database named "mysql" that holds system info, ie access info).

These might work better (update for you database name, user, and your IP )

Code: Select all

update mysql.db set Host='202.54.10.20' where Db='webdb';
update mysql.user set Host='202.54.10.20' where user='webadmin';

You can add the database name to the table, so you refer to that database.table.

Code: Select all

select db from mysql.db;

I hope that clear it up for you.

==>brian.

[cchilderhose]

Thanks Brian.

What user do I use to run the UPDATE commands? The user nagiosql does not have the update permission.