Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

How to tell if a MySQL database is available/connectable

I'm trying to automate a way to troubleshoot basic connectivity to mysql databases. Does anyone have a recommended way of doing this that has low impact to the performance of mysql?

I think the existence of mysqld is a good indicator that things are ok.  How do I tell the databases are up?

I've run across this query:  SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName';

But that doesn't necessarily mean the database is available.

I'm teaching myself MySQL as fast as I can but need to jump ahead a few chapters.

TIA!
0
data_bits
Asked:
data_bits
2 Solutions
 
amclaughlin01Commented:
Just use the mysql.exe command line tool that comes with Mysql. Like this:

mysql.exe -s -N -h hostname -P 3306 -D databasename -u username -ppassword

For help:
mysql.exe -?

You could write a small sql script and pass it to mysql.exe to test success or not.
0
 
Dave BaldwinFixer of ProblemsCommented:
The first thing is that the database server must be set up to accept external connections.  Some are not.  The second is that you must have 'privileges' in the form of a username, password, and connection to be able to actually make a connection.  Privileges are setup separately for each database on the server and possibly even at the table level.  You are right about 'mysqld' in that just because the server is running does not mean you have access.

You probably shouldn't jump ahead.  There isn't any generic connection to a MySQL server and that's true of all the other SQL database servers that I know of.  Details are important.
1
 
Dan CraciunIT ConsultantCommented:
If you really want to monitor your MySQL server, you can use something like Nagios (requires it's own server/VM) with the check_mysql_health plugin.
It will do much more than just testing if your server is up.

HTH,
Dan
0
 
Milind KoyandeCommented:
1) Check if your MySQL Server except remote connections.
2) You can write database connection strings to connect a DB using DB user and password. This connection string can let you know if you have access to the database or not.
3) To monitor MySQL you can use many third party applications (just google it)


Please check following URLs for reference (MySql Connection Strings):
https://www.connectionstrings.com/mysql/
http://www.w3schools.com/php/php_mysql_connect.asp
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Tackle projects and never again get stuck behind a technical roadblock.
Join Now