Accessing MySQL via the Command Line

With well over 10 million installations, MySQL is probably the most popular database management system for web servers. Developed in the mid-1990s, it’s now a mature technology that powers many of today’s most-visited Internet destinations.

Also Read: GUI and Command Line Tools for MySQL Users

One reason for its success must be the fact that, like PHP, it’s free to use. But it’s also extremely powerful and exceptionally fast—it can run on even the most basic of hardware, and it hardly puts a dent in system resources.

MySQL is also highly scalable, which means that it can grow with your website.

Basics of MySQL

A database is a structured collection of records or data stored in a computer system and organized in such a way that it can be quickly searched and information can be rapidly retrieved.

The SQL in MySQL stands for Structured Query Language. This language is loosely based on English and also used in other databases such as Oracle and Microsoft SQL Server. It is designed to allow simple requests from a database via commands such as:

SELECT title FROM books WHERE author = ‘ABC’;

A MySQL database contains one or more tables, each of which contains records or rows. Within these rows are various columns or fields that contain the data itself. Each row in the table is the same as a row in a MySQL table, and each element within a row is the same as a MySQL field.

Summary of Database Terms

The main terms you need to acquaint yourself with for now are:

Database – The overall container for a collection of MySQL data
Table – A subcontainer within a database that stores the actual data
Row – A single record within a table, which may contain several fields
Column – The name of a field within a row

Accessing MySQL via the Command Line

There are three main ways in which you can interact with MySQL: using a command line, via a web interface such as phpMyAdmin or Adminer, and through a programming language like PHP.

The following sections describe relevant instructions for Windows, OS X, and Linux.

1. Windows users

If you installed the Zend Server Free Edition WAMP, you will be able to access the MySQL executable from one of the following directories:

For 32-bit – C:\Program Files\Zend\MySQL55\bin
For 64-bit – C:\Program Files (x86)\Zend\MySQL55\bin

If you installed Zend Server in a place other than \Program Files (or \Program Files (x86)), you will need to use that directory instead.

By default, the initial MySQL user will be root and will not have had a password set.

Seeing as this is a development server that only you should be able to access, we won’t worry about creating one yet.

So, to enter MySQL’s command-line interface, select Start→Run, enter CMD into the Run box, and press Return. This will call up a Windows Command Prompt. From there, enter one of the following:

For 32-bit – “C:\Program Files\Zend\MySQL55\bin\mysql” -u root
For 64-bit – “C:\Program Files (x86)\Zend\MySQL55\bin\mysql” -u root

Note the quotation marks surrounding the path and filename. These are present because the name contains spaces, which the Command Prompt doesn’t correctly interpret, and the quotation marks group the parts of the filename into a single string for the command program to understand.

Similarly, if you are using XAMPP, then you can directly run the following command:

C:\xampp\mysql\bin\mysql.exe -u root

This command tells MySQL to log you in as user root, without a password. You will now be logged into MySQL and can start entering commands. So, to be sure everything is working as it should be, enter the following:

SHOW databases;

If this has not worked and you get an error, make sure that you have correctly installed MySQL along with Zend Server.

2. OS X users

To proceed with this, you should have installed Zend Server. You should also have the web server already running and the MySQL server started. To enter the MySQL command-line interface, start the Terminal program (which should be available in Finder→Utilities).

Then call up the MySQL program, which will have been installed in the directory /usr/local/zend/mysql/bin.

By default, the initial MySQL user is root, and it will have a password of root too. So, to start the program, type the following:

/usr/local/zend/mysql/bin/mysql -u root

This command tells MySQL to log you in as user root and not to request your password. To verify that all is well, type the following:

SHOW databases;

3. Linux users

On a system running a Unix-like operating system such as Linux, you will almost certainly already have PHP and MySQL installed and running, and you will be able to enter the examples. But first you should type the following to log into your MySQL system:

mysql -u root -p

This tells MySQL to log you in as the user root and to request your password. If you have a password, enter it; otherwise, just press Return.

Once you are logged in, type the following to test the program:

SHOW databases;

MySQL on a remote server

If you are accessing MySQL on a remote server, you should Telnet (or preferably, for security, use SSH) into the remote machine, which will probably be a Linux/FreeBSD/Unix type of box. Once in there, you might find that things are a little different, depending on how the system administrator has set the server up, especially if it’s a shared hosting server.

Therefore, you need to ensure that you have been given access to MySQL and that you have your username and password. Armed with these, you can then type the following, where username is the name supplied:

mysql -u username -p

Enter your password when prompted. You can then try the following command:

SHOW databases;

There may be other databases already created, and the test database may not be there. Bear in mind also that system administrators have ultimate control over everything and that you can encounter some unexpected setups.

For example, you may find that you are required to preface all database names that you create with a unique identifying string to ensure that you do not conflict with databases created by other users.

Therefore, if you have any problems, talk with your system administrator, who will get you sorted out. Just let the sysadmin know that you need a username and password. You should also ask for the ability to create new databases or, at a minimum, to have at least one database created for you ready to use.

You can then create all the tables you require within that database.

You may also like:

Basics of MySQL

Accessing MySQL via the Command Line

MySQL on a remote server

Related Posts