ubuntu 20.04 mysql

1.sudo apt install mysql-server

2.systemctl enable mysql

3.sudo mysql_secure_installation

Output
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
 2
 Output
Please set the password for root here.


New password: 

Re-enter new password: 
Output
Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

4.sudo mysql -u root -p

5.SHOW VARIABLES LIKE ‘validate_password%’;

展示mysql安全策略

set global validate_password_policy=LOW; //设置密码策略为低
set global validate_password_length=6;//设置密码长度
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
or
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

6.修改字符编码集

1.先查看字符编码集 
    show variables like 'character%';
    character_set_database   | latin1 
    character_set_server     | latin1
    安装的时候这两项默认拉丁文,之后项目向数据库中插数据时发现无法插入,这时我们就需要修改编码   集
2.找到mysqld.cnf这个配置文件
    如果你使用apt-get安装的,可以在/etc/mysql/mysql.conf.d下找到
    cd /etc/mysql/mysql.conf.d
3.修改配置文件
    使用你熟悉的编辑器打开配置文件,这里我使用的是vim
    sudo vim mysqld.cnf
    找到[mysqld]的部分,如:
    [mysqld]
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    ----------------------------------------------
    在[mysqld]部分加入
    character_set_server = utf8
    init_connect = 'SET NAMES utf8'
    然后保存退出
4.重启mysql服务 
    sudo service mysql restart
5.查看编码集是否修改
    mysql -u yfx -p
    输入密码
    show variables like 'character%';
    下面字段显示utf-8,说明修改成功
    character_set_database   | utf8        
    character_set_server     | utf8                  

7.Unisntall mysql

sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get remove mysql-community-client-core
sudo apt-get remove mysql-community-server-core
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/lib/mysql

8.install mysql5.7 on ubuntu 20.04

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

Select Ubuntu Bionic option and click on Ok.


Select MySQL 5.7 server and click on OK.

Confirm that showing MySQL 5.7 on First option and Click on OK.

#2
sudo apt-get update
sudo apt-cache policy mysql-server
Output:
mysql-server:

  Installed: (none)

  Candidate: 8.0.20-0ubuntu0.20.04.1

  Version table:

     8.0.20-0ubuntu0.20.04.1 500

        500 http://in.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages

        500 http://in.archive.ubuntu.com/ubuntu focal-security/main amd64 Packages

     8.0.19-0ubuntu5 500

        500 http://in.archive.ubuntu.com/ubuntu focal/main amd64 Packages

     5.7.30-1ubuntu18.04 500

        500 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Packages

sudo apt install -f mysql-client=5.7.32-1ubuntu18.04

#3
sudo apt install -f mysql-community-server=5.7.32-1ubuntu18.04

Installation process will prompt default password for root user and again same password.


Install mysql-server=5.7.30 package also

sudo apt install -f mysql-server=5.7.32-1ubuntu18.04

Secure MySQL Installation

mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No

... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.

- Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Step 6: Create MySQL Remote User
First, Login to MySQL Server with root user using command line, Below is command is to create user , here i am creating user “fosstechnix“.

mysql> CREATE USER 'fosstechnix'@'%' IDENTIFIED BY 'FOSSTechNix@123';