Solved

How to tell if a MySQL database is available/connectable

Posted on 2014-02-11
4
473 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Importing and exporting data Magento 1.x ? 4 69
Insert with SET how to handle join 6 57
Instering to MySQL table 5 38
myqsl update statement on phpMyAdmin 8 22
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…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now