Here is a guide to the basics of MySQL Server.
1) Creating a MySQL user
2) Setting or Changing password for an existing MySQL user
3) Listing all existing MySQL users
4) Removing a MySQL user
5) Creating a Database
6) Granting Privileges to a user on a database
7) Revoking Privileges for a user on a database
8) Listing all existing databases
9) Removing a Database
1) Creating a MySQL user
In the terminal, login to MySQL Server as root.
# mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
NOTE: Replace USERNAME, HOSTNAME and PASSWORD with your own values.
mysql> create user 'USERNAME'@'HOSTNAME' identified by 'PASSWORD';
This will create the user with the password we specified.
OPTIONS EXPLAINED
USERNAME syntax An account with a blank user name is an anonymous user. To specify an anonymous user in MySQL statements, use ''@'localhost'. example: mysql> create user ''@'localhost' identified by 'pS527$Pe'; This allows any user to connect from host localhost, using password pS527$Pe. You must know that is highly unsecure. If the username string containing special characters, quote them using backticks (“`”), single quotation marks (“'”), or double quotation marks (“"”). The username and hostname parts, if quoted, must be quoted separately. It is good to always use quotes to avoid mistakes. example: mysql> create user 'john@localhost'@'localhost' identified by 'pS527$Pe'; This creates a username john@localhost who can connect only from the localhost, using password pS527$Pe.
HOSTNAME syntax If the hostname string containing special characters, quote them using backticks (“`”), single quotation marks (“'”), or double quotation marks (“"”). The username and hostname parts, if quoted, must be quoted separately. It's good to always use quotes to avoid mistakes. example: mysql> create user 'john@localhost'@'localhost' identified by 'pS527$Pe'; This creates a username john@localhost who can connect only from the localhost, using password pS527$Pe. NOTE: Wrong usage of quotes like (create user 'john@localhost';) will result in username john@localhost who can connect from anywhere using a blank password. ♦ Hostname can be an IP address. ♦ Hostname or IP address can consist of wildcard characters “%” and “_”. → "%" matches any number of characters, even zero characters. For example, a host value of '%' matches any host name, whereas a value of '%.mysql.com' matches any host in the mysql.com domain. → "_" matches exactly one character. For example '192.168.1.%' matches any host in the 192.168.1 class C network. NOTE: Because you can use IP wildcard values in hostname (eg: '192.168.1.%'), someone could try exploiting this by naming a host 192.168.1.somewhere.com. To thwart such attempts, MySQL disallows matching on host names that start with digits and a dot(eg: 1.2.example.com). In total... An IP wildcard value can match an IP address only, not hostname. ♦ Hostname part is used as wildcard '%', if we specify only the username part of the account name, example: mysql> create user 'john' identified by 'pS527$Pe'; This allows any host to connect as user john using password pS527$Pe. ♦ For a hostname specified as an IP address, we can specify a netmask. example: mysql> create user 'david'@'192.58.197.0/255.255.255.0'; This enables david to connect from any client host having an IP address in the range from 192.58.197.0 to 192.58.197.255. examples of hosts: 192.0.0.0/255.0.0.0
Any host on the 192 class A network 192.168.0.0/255.255.0.0
Any host on the 192.168 class B network 192.168.1.0/255.255.255.0
Any host on the 192.168.1 class C network 192.168.1.1
Only the host with this specific IP address NOTE: The netmask can only be used to tell the server to use 8, 16, 24, or 32 bits of the address. 192.168.0.1/255.255.255.240
WILL NOT WORK because it masks 28 bits, which is not a multiple of 8 The server performs matching of hostnames against the client using DNS resolver for the hostname or IP address. Except while using netmask in hostname, the matching is a string match. This means that we should specify hostnames in the same format used by DNS. Here are 2 problems to look out for. → Suppose the hostname for client in MySQL Server is host1, but DNS lookup using client's DNS resolver returns host1.example.com. In this case the MySQL Server won't allow us to login. → Suppose the IP address for client in MySQL Server is 192.168.01.2, but DNS lookup using using client's DNS resolver returns 192.168.1.2. In this case the MySQL Server won't allow us to login. Bottomline...Always check the format in which the DNS server returns hostnames and addresses, and use values in the same format in MySQL account names.
PASSWORD syntax If no password is needed for a user, use the following command. example: mysql> create user 'john'@'localhost'; The user can connect without password. NOTE: This is highly unsecure. The password hash can be specified instead of password by adding an astersik in front of hash, that is '*HASH' example: create user 'john'@'localhost' identified by password '*16cda0a738de412079257bcaf70ee87b'; This allows user john to connect from localhost using password pS527$Pe.
2) Setting or Changing password for an existing MySQL user
In the terminal, login to MySQL Server as root.
# mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
NOTE: Replace USERNAME, HOSTNAME and PASSWORD with your own values.
mysql> set password for 'USERNAME'@'HOSTNAME' identified by 'PASSWORD';
This will change the existing password for the user we specified.
OPTIONS EXPLAINED PASSWORD syntax If no password is needed for a user, use the following command. example: mysql> create user 'john'@'localhost'; The user can connect without password. NOTE: This is highly unsecure. The password hash can be specified instead of password by adding an astersik in front of hash, that is '*HASH' example: create user 'john'@'localhost' identified by password '*16cda0a738de412079257bcaf70ee87b'; This allows user john to connect from localhost using password pS527$Pe.
3) Listing all existing MySQL users
From the terminal, login to MySQL Server as root user.
# mysql -u root -p
Enter the password when prompted.
The mysql database contains information about all users on the MySQL Server.
Let us use the mysql database.
mysql> use mysql;
Execute the following command in mysql shell.
mysql> select Host,User,Password from user;
This will list all the users including their hostname and password.
4) Removing a MySQL user
In the terminal, login to MySQL Server as root user.
# mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
NOTE:
• Replace USERNAME and HOSTNAME with your own values.
• DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user’s session is closed. Once the session is closed, the user is dropped, and that user’s next attempt to log in will fail.
mysql> drop user 'USERNAME'@'HOSTNAME';
This will remove the MySQL user we specified.
5) Creating a Database
In the terminal, login to MySQL Server as root user.
# mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
NOTE: Replace DATABASE with your own value.
mysql> create database DATABASE;
This will create the specified database.
6) Granting Privileges to a user on a database
In the terminal, login to MySQL Server as root user.
# mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
NOTE:
• Replace USERNAME, HOSTNAME and DATABASE with your own values.
• Read about MySQL privileges.
mysql> grant all on DATABASE.* to 'USERNAME'@'HOSTNAME';
This will grant all privileges (in this example, except GRANT OPTION privilege) to the specified user on selected database.
OR
In the mysql shell, execute the following commands.
NOTE:
• Replace USERNAME, HOSTNAME and DATABASE with your own values.
• Read about MySQL privileges.
mysql> grant select, insert on DATABASE.* to 'USERNAME'@'HOSTNAME';
This will grant selected privileges(in this example, SELECT and INSERT privileges) to the specified user on selected database.
7) Revoking Privileges for a user on a database
In the terminal, login to MySQL Server as root user.
# mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
NOTE:
• Replace USERNAME, HOSTNAME and DATABASE with your own values.
• Read about MySQL privileges.
mysql> revoke all privileges on DATABASE.* from 'USERNAME'@'HOST';
This will revoke all privileges (in this example, except GRANT OPTION privilege) for the specified user on database.
OR
In the mysql shell, execute the following commands.
NOTE:
• Replace USERNAME, HOSTNAME and DATABASE with your own values.
• Read about MySQL privileges.
mysql> revoke all privileges, grant option on DATABASE.* from 'USERNAME'@'HOSTNAME';
This will revoke all privileges for the specified user on database.
8) Listing all existing databases
In the terminal, login to MySQL Server as root user.
mysql> mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
mysql> show databases;
This will list all our existing databases.
9) Removing a Database
From the terminal, login to MySQL Server as root user.
# mysql -u root -p
Enter the password when prompted.
In the mysql shell, execute the following commands.
NOTE:
• Replace DATABASE with your own value.
• When a database is dropped, user privileges on the database are not automatically dropped.
mysql> drop database DATABASE;
This will drop all tables in the specified database and delete the specified database.
After working with the MySQL Server, you may go to the following section.
Installing Apache HTTP Server
