This article explains MySQL and how to manage users and databases in the tool.
Databases are organized collections of data. You can use them to store pure data like credit card information,
product IDs, prices, lists of things, and so on. However, you wouldn't store more complex things such as images,
files, or documents. If it helps, it's a little like Microsoft®, Excel® where you only copy in the raw data
to be stored, processed, or retrieved later. And MySQL® is a very commonly used type of Linux® database.
You might have MySQL installed, but it looks like it's called MariaDB®. When Oracle® acquired MySQL in 2010,
the original founders then created MariaDB as a like-for-like replacement in case Oracle discontinued MySQL.
So MySQL and MariaDB are largely the same and interchangeable. The information in this article applies to both.
Run the following command to check your database version:
# mysql -v
Log into your database by using one of the following methods:
If this is already the default option, consider leaving it alone.
If not, create the following file with your chosen text editor:
# vim /root/.my.cnf [client] user=root password=<password>
Or if you prefer, you can instead create it this with:
# echo -e "\n[client]\nuser=root\npassword=<PASSWORD>" >> /root/.my.cnf
The SecureShell (SSH) into your server as
root, and log in to MySQL by running the following command:
If you SSH into your server first, your user connects to MySQL from the
you are already on the machine with MySQL.
Log in from localhost by running the following command:
# mysql -u <USER> -p
If you want to connect directly to MySQL from your computer, you need the hostname or IP address of the MySQL server.
Then, run the following command:
# mysql -h <HOSTNAME_OR_IP> -u <USER> -p
In software, a wildcard is a kind of placeholder represented with an asterisk (*).
* can mean any combination of letters/numbers/symbols, so you could represent '12345'
1* or *3* or *5. For example, if you want to find a name
beginning with the letter M, you could search
In MySQL, the wildcard character is a percentage (%) symbol.
For example, run the following command to search for all databases ending in the word schema:
> show databases like "%schema";
These are some common MySQL commands:
Note: End all MySQL commands with a semicolon (;).
> show databases;
Create a database:
> create database <DATABASE>;
Delete a database:
> drop database <DATABASE>;
All users connect from somewhere. If the hosts show as localhost, they connected to the server first and then to MySQL.
If it shows as an IP address, they are connected directly to MySQL.
Show DB users:
> select user, host from mysql.user;
Show DB users and encrypted passwords:
> select user, host, password from mysql.user;
Create user with full permissions:
> grant all on *.* to sher@localhost identified by "sdF5";
This can be for localhost, IP address, or % everywhere (which we don't recommend):
> grant all on *.* to [email protected] identified by "sdF5";
Rename user or change host:
> rename user user@ip1 to user@ip2;
> flush privileges;
In MySQL, the usual Linux-based read, write, and execute rules don't apply. Instead, you assign
permissions to users as privileges or grants, which is much more granular and specific.
The following sections show some examples:
Show user grants:
> show grants for '<user>'@<localhost/IP>;
Add user grants:
> GRANT SELECT,INSERT,UPDATE on <database>.* to <user>@<localhost/IP>;
Remove all user grants:
> revoke ALL on *.* from <user>@<localhost/IP>;
Create a database and add the user for it (read/write means all):
> create database dbase;
> grant all on dbase.* to <user>@<localhost/IP> identified by 'passwd';
The following list shows some of the common MySQL grants:
- ALL PRIVILEGES: All access (to specific DB / everything)
- CREATE: Create new tables / DBs
- DROP: Delete tables / DBs
- DELETE: Delete rows from tables
- INSERT: Insert rows into tables
- SELECT: Use the Select command to read through DBs (read-only)
- UPDATE: Update table rows
- USAGE: No privileges, default
- GRANT OPTION: Grant or remove other users' privileges
- CRUD: Create, Read, Update, Delete
Use the Feedback tab to make any comments or ask questions. You can also start a conversation with us.
Updated 3 months ago