mysql_queries_references.md

Queries that I use once in a while, but are very useful.

Once in a while I forget the queries because I don't use them daily. I have created this document as a reference to me and maybe it can be useful to you.

1. Create a database with a defined collation and charset.
2. Create a new user and grant all privileges.
3. How to grant remote access to a given user in our MySQL/MariaDB server?
4. Importing a database from the command prompt.
5. Display full columns information.

Create a database with a defined collation and charset

CREATE DATABASE `database_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note Using the collation and charset are overtaken while creating a new database. You realized that you screwed things up after you have your database fill with a lot of information and then insert information with a different collation. Then you have to update the database or the table collation to fix things up.

Back to top

Create a new user and grant all privileges

-- Create a new user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'xxxxxxxxxx';

-- Grant all privileges to a given database.
GRANT ALL PRIVILEGES ON `database_name` . * TO 'username'@'localhost';

Back to top

How to grant remote access to a given user in our MySQL/MariaDB server?

Well first yo need to remove the bind address directive from the MySQL configuration file.

# bind-address      = 127.0.0.1

Now, just like you added a new user and granted permision you have to do the same but with the client IP that want to connect. For example, if the IP 192.168.1.10 needs to connect then you have to add the user and grant the access like this:

-- Create a new user with IP 192.168.1.10
CREATE USER 'username'@'192.168.1.10' IDENTIFIED BY 'xxxxxxxxxx';

-- Grant all privileges to a given database.
GRANT ALL PRIVILEGES ON `database_name` . * TO 'username'@'192.168.1.10';

This will grant remote access only to the IP 192.168.1.10. What if you want to grant access to an IP range? You have to use the wildcard %. Say that we want to grant access in a IP range 192.168.1.0/30 you will use the wildcard like this 192.168.1.%. You can even short that range with 192.168.%

-- Create a new user with IP 192.168.%
CREATE USER 'username'@'192.168.%' IDENTIFIED BY 'xxxxxxxxxx';

-- Grant all privileges to a given database.
GRANT ALL PRIVILEGES ON `database_name` . * TO 'username'@'192.168.%';

Back to top

Importing a database from the command prompt

By using the followoing script you will import the database using the admin user. It will prompt you to type the password.

mysql -u admin -p -v database_name < /tmp/database_file.sql

Back to top

Display full columns information.

This will display additional information that is not visible when using DESC table_name; such as the collation and the table privileges.

SHOW FULL COLUMNS FROM table_name;

Back to top