MySQL in the Command Line

Following the setting up a Linux webserver tutorial. You may also wish to create a MySQL database. First, install MySQL in the Linux console and give the root user a password. Here is what you might do for PHP 7.0 (needs sudo unless a superuser)…

apt-get install mysql-server
    mysql_secure_installation
    apt-get install libapache2-mod-php7.0 php7.0-mysql php7.0-curl php7.0-json

Now, from the linux command line you can login to MySQL like this…

mysql -u root -p

Now, the rest of the commands in this tutorial are run from the mysql> prompt.

After installing MySQL the main reasons you’re probably wanting to access MySQL from the command line are to create a database and create another user. To create a database…

CREATE DATABASE [database name];

To create a user, grant the user privileges on a database then reload the privileges…

CREATE USER '[username]'@'localhost' IDENTIFIED BY '[password]';
    GRANT ALL PRIVILEGES ON [database name] . * TO '[username]'@'localhost';
    FLUSH PRIVILEGES;

If you wanted to grant the new user privileges to everything you could do this…

GRANT ALL PRIVILEGES ON * . * TO '[username]'@'localhost';

Or just a specific table of a specific database…

GRANT ALL PRIVILEGES ON [database name] . [database table] TO '[username]'@'localhost';

This is probably all you’ll need to do in the command line. Everything else you can either do in PHPMyAdmin or in an MVC framework.

You can get a list of the databases with this command…

SHOW DATABASES;

If you wanted to manually create a table you would first “use” a database…

USE [database name];

Then, these commands are starting to look more like SQL commands you may have written in PHP…

CREATE TABLE example ( id not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
    INSERT INTO example ( name ) VALUES ( 'Sample data' );

To import a SQL file in the Linux command line, very similar to logging in normally…

mysql -u [username] -p < [SQL file name]

To find the version of MySQL you have installed run this command…

SHOW VARIABLES LIKE "%version%";

To exit out of MySQL, some of the normal ways of exiting a command line application may not work, you’ll have to use…

quit