FROM mysql.user' tells MySQL to get the data from the mysql database and the user table. A semicolon (;) ends the command. Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon. The following example is the output for the preceding query.
MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.
- Download a free 14 days trial of Navicatfor MySQL and try the latest features in Navicat version 15. Navicat Download Navicat for MySQL 14-day trial versions for Windows, macOS and Linux Products.
- The MySQL Yum repository for Oracle Linux, Red Hat Enterprise Linux and CentOS provides RPM packages for installing the MySQL server, client, MySQL Workbench, MySQL Utilities, MySQL Router, MySQL Shell, Connector/ODBC, Connector/Python and so on (not all packages are available for all the distributions; see Installing Additional MySQL.
MySQL Workbench Home
Windows, Linux, OS X
Visual Database Design
Windows, Linux, OS X
Windows, Linux, OS X
MySQL Workbench enables a DBA, developer, or data architect to visually design, model, generate, and manage databases. It includes everything a data modeler needs for creating complex ER models, forward and reverse engineering, and also delivers key features for performing difficult change management and documentation tasks that normally require much time and effort.
Learn More »
MySQL Workbench delivers visual tools for creating, executing, and optimizing SQL queries. The SQL Editor provides color syntax highlighting, auto-complete, reuse of SQL snippets, and execution history of SQL. The Database Connections Panel enables developers to easily manage standard database connections, including MySQL Fabric. The Object Browser provides instant access to database schema and objects.
Learn more »
MySQL Workbench provides a visual console to easily administer MySQL environments and gain better visibility into databases. Developers and DBAs can use the visual tools for configuring servers, administering users, performing backup and recovery, inspecting audit data, and viewing database health.
Learn more »
Visual Performance Dashboard
MySQL Workbench provides a suite of tools to improve the performance of MySQL applications. DBAs can quickly view key performance indicators using the Performance Dashboard. Performance Reports provide easy identification and access to IO hotspots, high cost SQL statements, and more. Plus, with 1 click, developers can see where to optimize their query with the improved and easy to use Visual Explain Plan.
Learn More »
MySQL Workbench now provides a complete, easy to use solution for migrating Microsoft SQL Server, Microsoft Access, Sybase ASE, PostreSQL, and other RDBMS tables, objects and data to MySQL. Developers and DBAs can quickly and easily convert existing applications to run on MySQL both on Windows and other platforms. Migration also supports migrating from earlier versions of MySQL to the latest releases.
Learn more »
A previous version of this tutorial was written by Hazel Virdó
MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It implements the relational model and uses Structured Query Language (better known as SQL) to manage its data.
This tutorial will go over how to install MySQL version
8.0 on an Ubuntu 20.04 server. By completing it, you will have a working relational database that you can use to build your next website or application.
To follow this tutorial, you will need:
- One Ubuntu 20.04 server with a non-root administrative user and a firewall configured with UFW. To set this up, follow our initial server setup guide for Ubuntu 20.04.
Step 1 — Installing MySQL
On Ubuntu 20.04, you can install MySQL using the APT package repository. At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.19.
To install it, update the package index on your server if you’ve not done so recently:
Then install the
This will install MySQL, but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next.
Step 2 — Configuring MySQL
For fresh installations of MySQL, you’ll want to run the DBMS’s included security script. This script changes some of the less secure default options for things like remote root logins and sample users.
Run the security script with
This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the password strength of new MySQL users before deeming them valid.
If you elect to set up the Validate Password Plugin, any MySQL user you create that authenticates with a password will be required to have a password that satisfies the policy you select. The strongest policy level — which you can select by entering
2 — will require passwords to be at least eight characters long and include a mix of uppercase, lowercase, numeric, and special characters:
Regardless of whether you choose to set up the Validate Password Plugin, the next prompt will be to set a password for the MySQL root user. Enter and then confirm a secure password of your choice:
Note that even though you’ve set a password for the root MySQL user, this user is not currently configured to authenticate with a password when connecting to the MySQL shell.
If you used the Validate Password Plugin, you’ll receive feedback on the strength of your new password. Then the script will ask if you want to continue with the password you just entered or if you want to enter a new one. Assuming you’re satisfied with the strength of the password you just entered, enter
Y to continue the script:
From there, you can press
Y and then
ENTER to accept the defaults for all the subsequent questions. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes you have made.
Once the script completes, your MySQL installation will be secured. You can now move on to creating a dedicated database user with the MySQL client.
Step 3 — Creating a Dedicated MySQL User and Granting Privileges
Upon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, and so on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the root MySQL user to create a new user account and grant it privileges.
In Ubuntu systems running MySQL
5.7 (and later versions), the root MySQL user is set to authenticate using the
auth_socket plugin by default rather than with a password. This plugin requires that the name of the operating system user that invokes the MySQL client matches the name of the MySQL user specified in the command, so you must invoke
sudo privileges to gain access to the root MySQL user:
Note: If you installed MySQL with another tutorial and enabled password authentication for root, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating:
Once you have access to the MySQL prompt, you can create a new user with a
CREATE USER statement. These follow this general syntax:
CREATE USER, you specify a username. This is immediately followed by an
@ sign and then the hostname from which this user will connect. If you only plan to access this user locally from your Ubuntu server, you can specify
localhost. Wrapping both the username and host in single quotes isn’t always necessary, but doing so can help to prevent errors.
You have several options when it comes to choosing your user’s authentication plugin. The
auth_socket plugin mentioned previously can be convenient, as it provides strong security without requiring valid users to enter a password to access the database. But it also prevents remote connections, which can complicate things when external programs need to interact with MySQL.
As an alternative, you can leave out the
WITH authentication plugin portion of the syntax entirely to have the user authenticate with MySQL’s default plugin,
caching_sha2_password. The MySQL documentation recommends this plugin for users who want to log in with a password due to its strong security features.
Run the following command to create a user that authenticates with
caching_sha2_password. Be sure to change
sammy to your preferred username and
password to a strong password of your choosing:
Note: There is a known issue with some versions of PHP that causes problems with
caching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure,
mysql_native_password plugin instead:
If you aren’t sure, you can always create a user that authenticates with
caching_sha2_plugin and then
ALTER it later on with this command:
After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:
PRIVILEGE value in this example syntax defines what actions the user is allowed to perform on the specified
table. You can grant multiple privileges to the same user in one command by separating each with a comma. You can also grant a user privileges globally by entering asterisks (
*) in place of the database and table names. In SQL, asterisks are special characters used to represent “all” databases or tables.
To illustrate, the following command grants a user global privileges to
DROP databases, tables, and users, as well as the power to
DELETE data from any table on the server. It also grants the user the ability to query data with
SELECT, create foreign keys with the
REFERENCES keyword, and perform
FLUSH operations with the
RELOAD privilege. However, you should only grant users the permissions they need, so feel free to adjust your own user’s privileges as necessary.
You can find the full list of available privileges in the official MySQL documentation.
GRANT statement, replacing
sammy with your own MySQL user’s name, to grant these privileges to your user:
Note that this statement also includes
WITH GRANT OPTION. This will allow your MySQL user to grant any that it has to other users on the system.
Warning: Some users may want to grant their MySQL user the
ALL PRIVILEGES privilege, which will provide them with broad superuser privileges akin to the root user’s privileges, like so:
Such broad privileges should not be granted lightly, as anyone with access to this MySQL user will have complete control over every database on the server.
Following this, it’s good practice to run the
FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding
CREATE USER and
Then you can exit the MySQL client:
In the future, to log in as your new MySQL user, you’d use a command like the following:
-p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.
Finally, let’s test the MySQL installation.
Step 4 — Testing MySQL
Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status.
You’ll see output similar to the following:
If MySQL isn’t running, you can start it with
sudo systemctl start mysql.
For an additional check, you can try connecting to the database using the
mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect as a MySQL user named sammy (
-u sammy), prompt for a password (
-p), and return the version. Be sure to change
sammy to the name of your dedicated MySQL user, and enter that user’s password when prompted:
You should see output similar to this:
Mysql Download Linux 64 Bit
This means MySQL is up and running.
Download Mysql 5.7 For Linux
You now have a basic MySQL setup installed on your server. Here are a few examples of next steps you can take:
Download Mysql On Linux
- Set up a LAMP stack or a LEMP stack