Shortdark Software Development

MySQL in the Command Line

Development4th Mar 2018.Time to read: 2 mins

Command LineMySQL

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

Previous: How to Fix Some Potential Issues When Using CDN (Cloudflare)Next: Upgrading from Debian 8 (Jessie) to Debian 9 (Stretch)