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 in to 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 Databases
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).
