This article explains how to create users and grant permissions in MySQL. Instructions apply to all platforms including Windows, Mac, and Linux.

What to Know

  • Enter CREATE USER ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’;Substitute wordpress_db with database name and L!f3W!r3 with preferred user name.Next, enter GRANT ALL PRIVILEGES ON wordpress_db.* TO > enter database and user name.

Create a MySQL Database

When you install a platform like Wordpress, it requires a database (most often a MySQL database). During the installation of a system like Wordpress, you’ll be asked to provide:

  • The name of the database to be used.The name of a user with permission to access the database.The password for the user that can access the database.

During the installation of the MySQL database, you’ll be asked to create a password for the admin user. You might be asking yourself, “Why not just use the admin user for this process?” The answer is simple: security. That MySQL admin user should only be used to administer the MySQL database server and its users, not as an account for the installation of third-party software. To that end, you should always create new users and grant the new user access to the third-party specific database. For example, if you’re installing Wordpress, you might create the following:

  • Database: wordpress_dbUser: wordpress_db_user

You would then create a password for wordpress_db_user and grant that user full access to the wordpress_db database.

Let’s go ahead and create a database. Here are the steps:

  • Access the MySQL prompt with the command:
  • mysql -u root -p
  • Type the MySQL admin password and use Enter/Return on your keyboard.
  • Create the database with the command
  • CREATE DATABASE wordpress_db; (substitute “wordpress_db”
  • Flush the database privileges with the command
  • FLUSH PRIVILEGES;
  • Exit out of the MySQL prompt with the command
  • quit

Create a User in MySQL

With the database in place, you can now create the user that will have access to the newly-created database. This is also done from the MySQL prompt. To create this new user, follow these steps:

Access the MySQL prompt with the command:

mysql -u root -p

Type the MySQL admin password and use Enter/Return on your keyboard.

Create the database with the command

CREATE DATABASE wordpress_db;

Flush the database privileges with the command

FLUSH PRIVILEGES;

Exit out of the MySQL prompt with the command

quit

  • Access the MySQL prompt with the command
  • mysql.exe -u -p
  • Type the MySQL admin password and use Enter/Return on your keyboard.
  • Create the user with the command
  • CREATE USER ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’; (substitute “wordpress_db_user” and “L!f3W!r3”
  • Flush the database privileges with the command
  • Exit out of the MySQL prompt with the command

Grant Permission in MySQL

Now we need to grant the newly created wordpress_db_user permission to access the newly created wordpress_db database. This is accomplished with the following steps:

Access the MySQL prompt with the command

mysql.exe -u -p

Create the user with the command

CREATE USER ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’;

  • Access the MySQL prompt with the command
  • mysql.exe –u –p
  • Type the MySQL admin password and use Enter/Return on your keyboard.
  • Grant the user access with the following command
  • GRANT ALL PRIVILEGES ON wordpress_db.* TO ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’;. (substitute wordpress_db, wordpress_db_user, and L!f3W!r3
  • Flush the database privileges with the command
  • FLUSH PRIVILEGES;
  • Exit out of the MySQL prompt with the command
  • quit

At this point, the local user wordpress_db_user has full access to the wordpress_db database. So when you go to install Wordpress (or whatever server software you intend to install), you can use wordpress_db_user as the database username and L!f3W!r3 as the password.

mysql.exe –u –p

Grant the user access with the following command

GRANT ALL PRIVILEGES ON wordpress_db.* TO ‘wordpress_db_user’@’localhost’ IDENTIFIED BY ‘L!f3W!r3’;.

Granting Remote Access

There’s one problem. The above permissions only work for the wordpress_db_user on the local machine. What if you’re database is housed on a remote server? For that, you need to alter the GRANT ALL PRIVILEGES command. Let’s say the machine you’re installing Wordpress (or whatever third-party server software) on is at IP address 192.168.1.100. In order to grant wordpress_db_user permission to access the database from that machine, the new GRANT ALL PRIVILEGES command would look like this:

GRANT ALL PRIVILEGES ON wordpress_db.* TO ‘wordpress_db_user’@’192.168.1.100’ IDENTIFIED BY ‘L!f3W!r3’;

As you can see, instead of granting full access to the wordpress_db on localhost, what we’ve done is grant the wordpress_db_user user on remote machine 192.168.1.100 full access to the wordpress_db database. That command will make it possible for you to install Wordpress (or whatever third-party server software you need) on the server at IP address 192.168.1.100 and have it access the wordpress_db MySQL database, as the wordpress_db_user.

Get the Latest Tech News Delivered Every Day