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:

For installing 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

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