Managing MySQL…

Introduction

Previously, I installed MySQL for linux. Having installed MySQL on my system such that I am the admin, this post is a reference point or cheat sheet of sorts. Thus, I have included some of the basic admin commands to help me manage MySQL installation (sic).

Note: SQL is a case insensitive language, therefore select and SELECT are the same command!

MySQL basic usage

MySQL service control

From the installation guide, we find out how to start, stop and reset the MySQL server, and to check whether the server is running.

#### MySQL server service commands
# 1) check server serice run status: 
shell> sudo service mysql status
# 2) start server:
shell> sudo service mysql start
# 3) stop server
shell> sudo service mysql status
# 4) restart server 
shell> sudo service mysql restart

#### Alternative command syntax
# Note: syntax for these commands work with either "mysql" or "mysql.service"
# 1) check server serice run status: 
shell> sudo systemctl status mysql.service
# 2) start server:
shell> sudo systemctl start mysql
# 3) stop server
shell> sudo systemctl stop mysql
# 4) restart server 
shell> sudo systemctl restart mysql

MySQL terminal access

1) Connecting to server

From command line: user login syntax

# Note: "-h hostName" can be omitted for localhost
# secure - prompt to add hidden password on next line
shell> mysql -h hostName -u userName -p
# insecure - password visible (note: no space afer "-p")
shell> mysql -h hostName -u userName -ppasswordString

2) login as root (admin) mysql user:

Note: If you sudo the following command… it could result in a vivid world of pain! This is the issue I had, but there seems to be potential fixes discussed here and here. These solutions might be suitable for a personal setting, but for production scenarios check out the instruction here (and here).

shell> mysql -u root -p   

3) disconnecting

mysql> QUIT
-- or
mysql> \q

Seek help!

From this tutorial:

mysql> mysql --help
-- or
mysql> \h

MySQL comments

Some interesting info about creating comments:

mysql> # this text is a commment
mysql> /* this is also a comment */
mysql> -- this is also a comment but (--) must precede a space

Cancel Command

Cancel the execution of an unexecuted console command:

mysql> \c
-- or
mysql> clear

Database creation and access

Some of the following commands are covered in more detail here and here. More detail will be covered in tutorials in later posts.

Show databases

mysql> SHOW DATABASES;

Create databases

mysql> CREATE DATABASE databaseName;

Connecting to an existing database

1) From MySQL console

mysql> USE databaseName;

2) From Shell console

shell> mysql -h host -u user -p databaseName

Delete an existing database

More info here:

mysql> DROP DATABASE databaseName;
-- or
mysql> DROP SCHEMA databaseName;

Show users

• Show all users.

mysql> SELECT host, user, password FROM mysql.user;
-- describe structure of user table
mysql> DESC mysql.user;
-- or
mysql> DESCRIBE mysql.user;

• Show current user:

/* Show account associated with the current session */
mysql> CURRENT_USER()

Create users

• creating users

mysql> CREATE USER 'userName'@'hostName' IDENTIFIED BY 'password';

Remove users

• Using this syntax.

mysql> DROP USER 'userName'@'hostName';

Assign user privileges to a specific database

For more detail, see here:

-- All privileges in this case
mysql> GRANT ALL ON databaseName.* TO 'userName'@'hostName';

Conclusion

Now that we have a basic idea of how to navigate the MySQL environment in order to administer it, let’s go onto using it to populate and utilise databases using MySQL !