Solved

How to tell if a MySQL database is available/connectable

Posted on 2014-02-11
4
476 Views
Last Modified: 2014-02-12
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
Comment
Question by:data_bits
4 Comments
 
LVL 4

Expert Comment

by:amclaughlin01
ID: 39851363
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
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 250 total points
ID: 39851943
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39852363
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
 
LVL 7

Accepted Solution

by:
Milind Koyande earned 250 total points
ID: 39852893
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question