MySQL Create Database with UTF8MB4 Character Set Syntax

Create database

I usually forgot the MySQL’s command line to create database with UTF8 character set syntax, so here it is:

CREATE DATABASE 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password'; 
FLUSH PRIVILEGES;

or

CREATE SCHEMA 'mydb' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
GRANT ALL ON 'mydb'.* TO 'username'@localhost IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Create user

Create user and give previledge to it

create user 'username'@localhost identified by 'password';
grant all privileges on *.* to username@localhost;

or

GRANT [type of permission] ON [database name].[table “” not found /]
TO ‘[username]’@'localhost’;

Change user’s password

Replace the password with the password that you want to use.

MySQL 5.7.6 and later:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

MySQL 5.7.5 and earlier:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

Here is a short list of other common possible permissions that users can enjoy.

  • ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the Select command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

Reference from :

  • http://www.euperia.com/development/mysql/mysql-create-database-with-utf8-character-set-syntax/1064
  • https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql

Leave a Reply

Your email address will not be published.

Captcha Captcha Reload