Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to tell if a MySQL database is available/connectable

Posted on 2014-02-11
4
Medium Priority
?
533 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 84

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 1000 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 35

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 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

606 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