Linux MySQL Server Installation, Database and User Creation, and Administrative Commands

Wednesday, May 8, 2013

The following steps will install MySQL quickly and securely on Fedora and Enterprise Linux based distributions. In addition, commonly used administrative commands are shown.

Replace all instances of $USER, $PASSWORD, and $DATABASE with your own values.

Install MySQL Server

Repository Packages Required

yum install mysql-server

Start and Enable MySQL Service

On RHEL and CentOS

service mysqld start

chkconfig mysqld on

On Fedora 17 and newer

systemctl start mysqld

systemctl enable mysqld

Initial MySQL Secure Setup

Assuming you accept the defaults, the following mysql command will set the root MySQL password, remove anonymous MySQL users, disallow root login remotely, remove test databases and access to them, and reload the privileges table:

mysql_secure_installation

Log into Interactive MySQL CLI

Login as the root MySQL user and prompt for password:

mysql -u root -p

Create New User for Application(s)

mysql> CREATE USER '$USER'@'localhost' IDENTIFIED BY '$PASSWORD';

Create Database for Application(s)

mysql> CREATE DATABASE $DATABASE;

Grant New User All Privileges to the New Database

mysql> GRANT USAGE ON $DATABASE.* to '$USER'@'localhost';

mysql> FLUSH PRIVILEGES;

Configure Your Application

Assuming your application runs on the same server as the MySQL service, it should only need the database name, username, and password you created above to connect.

MySQL Administrative Commands

Show All Databaes

mysql> SHOW DATABASES;

Select a Database

mysql> USE $DATABASE;

Show All Tables Inside of a Database

mysql> SHOW TABLES;

Show All Users

mysql> SELECT user FROM mysql.user;

Set Existing User Password

mysql> SET PASSWORD FOR '$USER'@'localhost' = PASSWORD('$PASSWORD');

Delete Existing User

mysql> DROP USER '$USER'@'localhost';

Show Existing User Privileges

mysql> SHOW GRANTS FOR '$USER'@'localhost';

Grant Various User Privileges

Grant ALL PRIVILEGES:

mysql> GRANT ALL PRIVILEGES ON $DATABASE.* TO '$USER'@'localhost';

Grant only SELECT, UPDATE, INSERT, and DELETE privileges:

mysql> GRANT SELECT, UPDATE, INSERT, DELETE ON $DATABASE.* TO '$USER'@'localhost';

After doing any of the above be sure to perform the following:

mysql> FLUSH PRIVILEGES;

Revoke Various User Privileges

Revoke ALL PRIVILEGES:

mysql> REVOKE ALL PRIVILEGES ON $DATABASE.* FROM '$USER'@'localhost';

Revoke only SELECT, UPDATE, INSERT, and DELETE privileges:

mysql> REVOKE SELECT, UPDATE, INSERT, DELETE ON $DATABASE.* FROM '$USER'@localhost';

After doing any of the above be sure to perform the following:

mysql> FLUSH PRIVILEGES;

Human Readable SQL Query Output

Looking up data via the MySQL CLI can be impossible to read when you have output like the following:

mysql> SELECT * FROM mysql.user LIMIT 1;

+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | root | *AWEFAWEFAEWGAWE$RAW$#RARGARFESFDA | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)

To actually make this human readable, append \G just before the semicolon in the SQL query:

mysql> SELECT * FROM mysql.user LIMIT 1\G;

*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password: *AWEFAWEFAEWGAWE$RAW$#RARGARFESFDA
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
           Event_priv: Y
         Trigger_priv: Y
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

Show Database Character Set

mysql> SHOW CREATE DATABASE $DATABASE;

Set Default Character Set to Survive on Service Restart

Open and edit /etc/my.cnf to look like the following:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set = utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

Other MySQL Notes

The % Character

% is a wildcard in MySQL. If you are defining your database table and in the host field enter %, that means any host can access that database (Of course, that host must also have a valid MySQL user).



comments powered by Disqus