Solved

How to tell if a MySQL database is available/connectable

Posted on 2014-02-11
4
490 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

710 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