How to grant privileges Mysql user for remote host machine

In this tutorial, we will learn how to grant privileges MySQL users for remote host/ip_address of the machine

SSH into remote server:

First, let's enter the remote server where the MySQL server is allocated.

ssh server_user_name@server_ip_address

Use your own server_user_name and server_ip_address.

ssh ubuntu@65.110.98.71

Now, logged in as MySQL's root user

mysql -u root_username -p

Enter your root username and hit enter. After that, it will ask for MySQL password use your MySQL password

Grant All Privileges for all host:

GRANT ALL PRIVILEGES ON *.* TO 'mysql_username'@'%' IDENTIFIED BY 'mysql_password';

FLUSH PRIVILEGES;

Here, we are giving the MySQL user access to it from any host or IP address by specifying %. Don't forget to flush privileges after granting privileges to the user.

Grant All Privileges for a host:

If we want to restrict MySQL user from all the hosts and give access to the specific IP address or host machine then we can use the following command

GRANT ALL PRIVILEGES ON *.* TO 'mysql_username'@'ip_address' IDENTIFIED BY 'mysql_password';
FLUSH PRIVILEGES;

Make sure to use your own mysql_username and ip_address to allow access from that host.

Revoke privileges:

REVOKE ALL PRIVILEGES ON *.* FROM 'mysql_username'@'ip_address';
FLUSH PRIVILEGES;

List host users:

SELECT user, host FROM mysql.user;

This will list all the users associated with the hosts.

List all the privileges given to users:

SELECT * FROM information_schema.user_privileges;

This will show all the users with hosts and privileges available for that user and host.

Delete user associated with host:

DROP USER 'mysql_username'@'ip_address';

This will drop the user associated with that IP address and revoke all the privileges.

Read More

Install Mysql in Docker on Ubuntu Linux server

In this tutorial, we will learn how to install Mysql on Docker.

Please follow the steps to install docker using this tutorial Install Docker on Ubuntu

Install Mysql using Docker:

To install the desired version of MySQL first, visit docker mysql image section where we can find the available MySQL version. In this tutorial, we are using MySQL version 5.7.

Use the following command on the server to install the MySQL image

docker pull mysql:5.7 

Use your own version instead of 5.7

This will pull the image and install Mysql inside docker. We can verify the image using the following command

docker images

The output of the above command will be similar to this

mysql          5.7                   314749b3a5c   17 hours ago    431MB

Create Mysql Container:

Let’s create the MySQL container. To create a MySQL container, use the following command.

docker run --name=cn-mysql -d -p 3316:3306 --env="MYSQL_ROOT_PASSWORD=root1249" mysql:5.7

Note: use your own MySQL root password instead of root1249 and the port instead of 3316 and container name instead of cn-mysql

This will create the container. We can see the container using the following command

docker container ls -a 
OR
docker ps -a

Enter into the Mysql server:

If we want to do the regular MySQL operation, then we need to enter the MySQL server inside docker as below

docker exec -it elint-mysql bash

Now login into MySQL using the command

mysql -u root -p

Use your own MySQL password which is used while creating a container i.e root1249

Now we can do operations like creating databases and users.

Creating Mysql User:

In order to create a MySQL user and give permission visit the tutorial, Create Mysql User and Grant Privileges

Let's create a user

create user 'username'@'%' identified by 'password';

Note: change your own username and password. If you need to connect your created user from any client, you should create the user with “%” which signifies, that you can connect this user from any host.

Now, let's grant privileges

grant all privileges on *.* to username;

Use the previously created username

flush privileges;

Read More

Error while importing mysql dump sql file Unknown collation: utf8mb4_0900_ai_ci

This is a quick tutorial on how we can resolve the issue while importing the MySQL dump SQL file. Sometimes due to the incompatibility of the MySQL version, the following error might occurs

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Before resolving the issue, make sure to back up the dump SQL file

sudo cp sql_file_name.sql sql_file_name_backup.sql 

In order to resolve this issue, we are going to replace the collation utf8mb4_0900_ai_ci with the valid collation utf8_general_ci. For this open, the SQL file with vim or vi and hit Shift + : and add the following replace command and hit enter.

%s/utf8mb4_0900_ai_ci/utf8_general_ci/g

The above command will find each occurrence of 'utf8mb4_0900_ai_ci' (in all lines), and replace it with 'utf8_general_ci'.

Now lets change the CHARSET=utf8mb4 to CHARSET=utf8

%s/utf8mb4/utf8/g

Make sure to hit enter after each command.

Now save the file by Shift+: and type wq!

After the changes, import the dump SQL file which supposes to work fine.

Read More

Create Mysql User and Grant Privileges

In this tutorial, we are going to learn how we can create MySQL users and grant privileges to that users.

For MySQL installation please follow the tutorial, Install Mysql Specific Version on Ubuntu.

Create MySQL User:

Before creating a new user we need to first log in with the root user, for this, use the following command.

mysql -u username -puserpassword

Here, username is the root user's username and userpassword is the root user's password, use your root user's username and password.

Now, let's create the user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace the username with your desired new user name and password as desired strong password. Here we are using localhost as we are considering the MySQL setup from the same server, if it is for the remote server then use IP address of host/server as below.

CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';

For this created user, they can access the database from that server not remotely. If we want to create user that can connect from any server or machine as below.

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Grant Privileges:

The general syntax for grant privileges is

GRANT <privileges_type> ON <database_table> TO 'username'@'localhost';

Let's look into some examples

Grant all privileges to user for all database

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

Here, *.* will grant for all databases, and "ALL PRIVILEGES" will grant all the privileges

Grant all privileges to the user for a particular database

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

database_name.* will grant all privileges for all the tables of that database with the database name "database_name"

Grant all privileges to user for a particular database table

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'localhost';

We can do database query-specific privileges like Insert, Delete, Create, Drop, Select, Update, etc. For example

GRANT INSERT ON *.* TO 'username'@'localhost';

Here, the user can insert rows into tables for all the databases but can't do other operations like drop, create, update, etc.

Flush Privileges:

After we change the user privileges we need to flush it to reflect the changes. We can do so using following command

FLUSH PRIVILEGES;

Revoke Privileges:

We can revoke the grant privileges for the user as below

REVOKE <privileges_type> ON <database_table> FROM 'username'@'localhost';

Remove User:

We can remove the MySQL user as well by using the following command

DROP USER 'username'@'localhost';

Read More

Install Mysql Specific Version on Ubuntu/Linux using debian package

In this tutorial, we are going to install MySQL version 5.7 using the Debian package(.deb)

Step 1. Add the MySQL Apt Repository

First download the Debian(.deb) package from MySQL Apt Repo. Here we are using the wget to download as below:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

Step 2. Configure the .deb file

sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb

You can see the below prompt screen

If somehow we are unable to see the prompt screen then either dpkg is interrupted or broken. Use the below command to clean the config after that use the previous dpkg command, now we can see the screen

apt-get purge mysql-apt-config

Select the ubuntu bionic option and the second screen will pop up as below. Now select the first option to select the MySQL version

Now select the desired version of MySQL, Here we are selecting the MySQL 5.7

Once selected the desired version of MySQL then select ok to configure.

Now the configure suppose to get successful and we can see the output as below:

Selecting previously unselected package mysql-apt-config.
(Reading database ... 36242 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.12-1_all.deb ...
Unpacking mysql-apt-config (0.8.12-1) ...
Setting up mysql-apt-config (0.8.12-1) ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK

Step 3. Install the MySQL Server

Now update the MySQL repository

sudo apt-get update

Install the MySQL using the following command:

sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7*

If you chose the latest version of MySQL then no need to specify the package.

Next, the screen will pop up to enter the root password. Enter the password and press ok. After that MySQL is installed and running successfully. Test the MySQL version and MySQL status by using the below command:

mysql --version
sudo systemctl status mysql

Step 4. Secure MySQL Installation

Use the following command to secure MySQL installation:

sudo mysql_secure_installation

Enter your MySQL root password and answer all of the security questions.

Now, log in using the root credential

mysql -u root -p

Step 5. Create a New Mysql User

Before creating new users we need to first log in with the root user as mentioned above. Now, create the user using the following command:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Here, use your own username and password and localhost as we are working on the same machine.

For example:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpassword';

If we want to create a user that can be connected from any machine then use the following command

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Now, let's grant the permission to the newly created user.

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

Here, user have full access to the database with ALL PRIVILEGES and for all the databases as we are using *.*

However, we can grant privileges for specific databases as

GRANT ALL PRIVILEGES ON database_name.* TO 'testuser'@'localhost';

After grant privileges to user, we need to flush the privileges

FLUSH PRIVILEGES;

We can revoke privileges using the following command:

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

Remove User:

DROP USER 'testuser'@'localhost';

Step 6: Some commands for MySQL server:

Stop the MySQL server:

sudo systemctl stop mysql

Start the MySQL server:

sudo systemctl start mysql

Check the status of MySQL server:

sudo systemctl status mysql

Restart MySQL server

sudo systemctl restart mysql

Step 7: Completly remove/uninstall MySQL server

sudo apt-get remove --purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean

Read More

How to configure multiple database in Grails application

In this tutorial, we are going to learn how to configure multiple databases in the grails 3.x application. This is necessary when you are dealing with multiple databases from the same application especially when dealing with an existing remote database. We are using different MySql databases for testing.

Let's create three MySql databases called db_default, db_one, db_two. Let's look into the application.yml file to configure the different databases and we will do it for the development environment, in other environments, the procedure will be the same.

environments:
    development:
        dataSource:
            dbCreate: update
            url: jdbc:mysql://localhost:3306/db_default
            driverClassName: com.mysql.jdbc.Driver
            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
            username: root
            password: root

Here, we are setting the default database as db_default. So for this database, all the gorm queries will be the same as in a normal application.

Now, let's set up for other two databases.

        dataSources:
            first:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_one
                dbCreate: update
            second:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_two
                dbCreate: update

For more than two, we need to define the databases by defining dataSources and giving the corresponding names. Here the custom name is whatever name you want. We are giving the name first for db_one and second for db_two.

The overall implementation for application.yml file looks as below:

environments:
    development:
        dataSource:
            dbCreate: update
            url: jdbc:mysql://localhost:3306/db_default
            driverClassName: com.mysql.jdbc.Driver
            dialect: org.hibernate.dialect.MySQL5InnoDBDialect
            username: root
            password: root
        dataSources:
            first:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_one
                dbCreate: update
            second:
                dialect: org.hibernate.dialect.MySQLInnoDBDialect
                driverClassName: com.mysql.jdbc.Driver
                username: root
                password: root
                url: jdbc:mysql://localhost:3306/db_two
                dbCreate: update

Now, how we can use the domain classes for the specific databases. Let's create a simple domain class called Book.groovy.

class Book {
    String title
    
    static mapping = {
        datasource 'first' 
    }
}

In the above example, the Book table is mapped to the db_one database so all operations regarding this will be in db_one database. Similarly, we can map the second database as well. We can map the single domain to multiple databases as below:

class Book {
    String title
    
    static mapping = {
        datasources([ConnectionSource.DEFAULT, 'first', 'second'])
    }
}

In the above example, the Book domain will be available in all the databases.

Now, let's look into how we can query for the specific database.

The first DataSource specified is the default when not using an explicit namespace, so in this case, the default one is used. But you can call GORM methods on the 'first' or 'second' DataSource with the DataSource name, for example:

def book = Book.first.get(1) // this will get from db_one
book.first.save() //this will save in db_one

def book = Book.second.get(1) // this will get from db_two
book.second.save() // this will save in db_two

def book = Book.get(1) // this will get from db_default
book.save() // this will save in db_default

We can use groovy native SQL as well. Let's look at the example. 

import grails.transaction.Transactional
import groovy.sql.Sql
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.beans.factory.annotation.Qualifier

import javax.sql.DataSource

@Transactional
class DbOneService {

    @Autowired
    @Qualifier('dataSource_first')
    DataSource dataSource

    def test() {
        def sql = new Sql(dataSource)
        def rows = sql.rows("select * from....")
        println "rows: "+rows
    }
}

Here, we are creating the DbOneService which is annotated with Qualifier where the db_one database name is configured as dataSource_first. This will provide the db_one database connection to do DB operation. You can simply execute the native SQL command as shown above. Similarly, we can do the same for the second database called db_two.

import grails.transaction.Transactional
import groovy.sql.Sql
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.beans.factory.annotation.Qualifier

import javax.sql.DataSource

@Transactional
class DbTwoService {

    @Autowired
    @Qualifier('dataSource_second')
    DataSource dataSource

    def test() {
        def sql = new Sql(dataSource)
        def rows = sql.rows("select * from....")
        println "rows: "+rows
    }
}

This way, we can deal with multiple databases.

Read More