Logging Onto A MySQL Database

28th January 2008 - 4 minutes read time

If you have command line access to your MySQL database server you will need to use certain parameters to log in. Most web hosts will not allow you to do this, so you might want to install MySQL into a local computer and give it a go.

To log into mysql you must run the program called mysql with certain parameters. Here is an example.

./mysql -u username

One thing you must realise is that all usernames are associated with a host so if the user you specified can't access the server from this host then you won't get far. To specify the host location enter the -h flag.

./mysql -h hostaddress -u username

If your user is able to access this server then you will be asked for a password. You can set the password in the string using the -p flag. If this doesn't work then leave out the space between the -p and the password.

./mysql -h hostaddress -u username -ppassword

For security reasons the MySQL server might have been told to use a different port than the standard 3306. You can therefore set the port using the -P flag.

./mysql -h hostaddress -u username -ppassword -P 3309

Users are also restricted to the tables and commands that they can use, so you can set a default database to use that database once you have logged in. This is useful to do if you don't have access to administrative commands like SHOW DATABASES. To set the database to be used just add it to the end of the line, no flag is needed.

./mysql -h hostaddress -u username -ppassword -P 3309 test

You should try to log into the MySQL command line if you are trying to learn SQL, but it is useful if you have some special things to do like data importing or carrying out a backup. One alternative to doing it this way is to get hold of the wonderful MySQL GUI tools which will allow you to anything you can do through the command line without having to have an in depth knowledge of SQL syntax.

MySQL

Add new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
1 + 7 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.

Related Content

Solving Query Performance Problems With The MySQL Slow Query Log

21st January 2024

MySQL's slow query log is a key component in your MySQL administration setup. Whilst normal logging can help you in terms of tracking down issues with your database system, the slow query log can help you track down issues in your database setup before they become problematic.

Read more

Creating An Authentication System With PHP and MariaDB

15th October 2023

Using frameworks to handle the authentication of your PHP application is perfectly fine to do, and normally encouraged. They abstract away all of the complexity of managing users and sessions that need to work in order to allow your application to function.

Read more

Case Insensitive Like Searches In MySQL

2nd July 2011

I needed to create a query that did a case insensitive search using the LIKE command in MySQL and I quickly realised that in order to do this I would need to alter both the parameter and the table data to be the same case. This can be done by using the MySQL UPPER() command on the table data and the strtoupper() PHP function on the input data.

Read more

Connecting To A Non Standard MySQL Socket In PHP

11th February 2011

Connecting to a MySQL database in PHP is usually not a difficult thing to do, in fact it is one of the first things that many tutorials will go though. However, there are certain curcumstances that require more information than the standard host, password and username details.

Read more

Count Number Of Characters In A String With MySQL

30th September 2010

Today I needed to grab some data from a table where there was more than one occurrence of a string within another string. Basically, I needed to find all URL's from a table that were more than 3 levels deep (i.e. with 3 slashes), but realised there wasn't a function to do this in MySQL.

Read more

MySQL Order Table By Character Length

24th September 2009

As part of debugging a bit of code I needed to know the longest possible field lengths that a record contains. You might need to know this if you are performing a database migration. The following query returns a field, along with the length of the string, and orders the results by the number of characters in that string.

Read more