The MariaDB Client

The MariaDB program is a command-line client used for sending commands to a MariaDB server. It can be used to enter SQL commands to query a database or alter table definitions; it also has its own set of commands to control its operation.

The MariaDB Program In this lesson and throughout this book, mysqlprinted in lower caserefers specifically to the MariaDB command-line client program.

Starting the Command-Line Client

To start the command-line client, simply invoke the mysql program from the command line. If your local MariaDB server allows anonymous connections, you can invoke mysql without any additional switches. The result looks similar to the following:

$ mysql Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2084 to server version: 4.1.12-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

The MariaDB> prompt indicates that you will now be typing commands into the MariaDB client instead of the system shell.

When MariaDB is first installed, anonymous connections are allowed unless you disable them. It is a good idea to remove anonymous user access; this is discussed in Appendix A, "Installing MariaDB."

If your server does not allow anonymous connections, you will see an error like the following when you attempt to start the MariaDB program:

$ mysql
ERROR 1045 (28000): Access denied for user 'chris'@'localhost'
(using password: NO)

If you will be using MariaDB to connect to a remote database and there is no local MariaDB server, or if the local MariaDB server is not running, the error will look like this:

$ mysql
ERROR 2002 (HY000): Can't connect to local MariaDB server through socket '/var/lib/mysql/mysql.sock' (2)

Connecting to MariaDB

Even if your database does allow anonymous connections, you will need to connect using a username and password to do anything useful. To connect to a MariaDB database, you must know the connection parameters to use.

Because MariaDB can accept connections over a network, you must specify the database server hostname. If you are connecting to a MariaDB server running on the local machine, the hostname is localhost. Otherwise, you can use the IP address or hostname of the remote server.

You also need to supply a username and password to authenticate with the database server. As you will learn in Lesson 18, "Managing User Access," the username and password to access a database can be locked down so that they work only when you connect from a specific location.

Finally, you must supply the database name to connect to. Each username may have access to one or more databases on the server; you must authenticate to gain access to your own databases.

The MariaDB program accepts a number of switches to specify how to connect to a database. Use the - -user switch to supply a username, and use the - -password switch to supply a password, as shown:

$ MariaDB --user=yourname --password=yourpass

Note that using this command as shown means that your password is visible onscreen. To avoid this, use the - -password switch without an argument to be presented with a password entry prompt.

$ mysql --user=yourname --password
Enter password:

Enter your password when prompted to log on to the MariaDB server. Note that your password is not displayed onscreen as you type.

Windows Menu On Windows systems, there is a menu item in the MariaDB program group named MariaDB Command Line Client. Selecting this item invokes mysql with a connection to the local server using the username root; you are prompted to enter a password to continue.

If there is a problem authenticating with the MariaDB server using either of these methods, you will see an error like this:

ERROR 1045 (28000): Access denied for user 'yourname'@'localhost'
(using password: YES)

To connect to a database on a remote server, use the - -host switch.

$ MariaDB - -host=host.yourdomain.com - -user=yourname - -password

The value given in - -host can be the IP address of the server or a fully qualified domain name.

Remote Connections Remember that your firewall must be configured to allow you to access the MariaDB port if you need to connect to a remote database.

Each of the connection parameter switches to mysql has a shorter version that you can use, if you prefer. The - -user switch can be replaced by -u, and - -password by - p. When using the shorter switches, note that the equals sign is not neededthe value is given immediately after the switch.

For instance, the following two commands are identical:

$ MariaDB --user=yourname - -password
$ MariaDB -uyourname -p

The - - host switch can be abbreviated to - h in the same way, as shown in the following command:

$ MariaDB - hhost.yourdomain.com - uyourname - p

MariaDB Port The default port for connections to MariaDB over a network is 3306. If your server uses a different port number, specify it using - -port= or -P when connecting using mysql.

The - -database switch can be abbreviated to -D. In fact, the - -database or -D switch can be omitted, and you can just specify the database at the end of the mysql command, as shown:

$ mysql-hhost.yourdomain.com -uyourname -ppassword dbname

Password Argument When using the short connection switches, make sure that there is no space between - p and the password. Otherwise, you will be prompted to enter a password, and the password you gave on the command line will be treated as the database name.