Detailed tutorial on installing and configuring MySql5.7 on Ubuntu 20.04

sudo cp /etc/apt/sources.list /etc/apt/sources.list.old

sudo vim /etc/apt/sources.list

# Tsinghua mirror source deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial main restricted universe multiverse
deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-updates main restricted universe multiverse
deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-updates main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-backports main restricted universe multiverse
deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-backports main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-security main restricted universe multiverse
deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-security main restricted universe multiverse

# Update the mirror source sudo apt-get update
# Update software sudo apt-get upgrade

# Execute the following installation command # Install mysql5.7 server sudo apt-get install mysql-server-5.7

# Install mysql5.7 client sudo apt-get install mysql-client-5.7

# Use c/c++ and other languages ​​to operate mysql dynamic link library. If you don't need it, you can install it. sudo apt install libmysqlclient-dev

mysql -V # View mysql version netstat -tap | grep mysql # View mysql service 

# Get it directly from the server (the server needs to be connected to the Internet)
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.31-1ubuntu18.04_amd64.deb-bundle.tar

# You can also directly paste the URL into the browser to download the installation compressed package https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.31-1ubuntu18.04_amd64.deb-bundle.tar

# Create a new directory mkdir mysql5.7.31
mv mysql-server_5.7.31-1ubuntu18.04_amd64.deb-bundle.tar ./mysql5.7.31

# Unzip cd mysql5.7.31
sudo tar -vxf mysql-server_5.7.31-1ubuntu18.04_amd64.deb-bundle.tar

# The decompressed deb installation package is as follows:
libmysqlclient20_5.7.31-1ubuntu18.04_amd64.deb
mysql-client_5.7.31-1ubuntu18.04_amd64.deb
mysql-community-source_5.7.31-1ubuntu18.04_amd64.deb
mysql-server_5.7.31-1ubuntu18.04_amd64.deb
mysql-common_5.7.31-1ubuntu18.04_amd64.deb
mysql-testsuite_5.7.31-1ubuntu18.04_amd64.deb
libmysqlclient-dev_5.7.31-1ubuntu18.04_amd64.deb
mysql-community-client_5.7.31-1ubuntu18.04_amd64.deb
mysql-community-server_5.7.31-1ubuntu18.04_amd64.deb
libmysqld-dev_5.7.31-1ubuntu18.04_amd64.deb
mysql-community-test_5.7.31-1ubuntu18.04_amd64.deb

# Delete 2 test-related packages sudo rm -f mysql-testsuite_5.7.31-1ubuntu18.04_amd64.deb
sudo rm -f mysql-community-test_5.7.31-1ubuntu18.04_amd64.deb

# Install with dpkg sudo dpkg -i mysql-*.deb 

# If the server can connect to the Internet, install it directly sudo apt-get install libtinfo5
sudo apt-get install libmecab2

# Downloading software packages requires distinguishing hardware architectures [admin0@Ubuntu20:/home/admin0]$ uname -m # View hardware architecture x86_64 # x86_64 is AMD64 

sudo dpkg -i libtinfo5_6.2-0ubuntu2_amd64.deb
sudo dpkg -i libmecab2_0.996-10build1_amd64.deb 

# Install again with dpkg cd mysql5.7.31
sudo dpkg -i mysql-*.deb

mysql -V # View mysql version netstat -tap | grep mysql # View mysql service 

# Stop the mysql service sudo service mysql stop

# Modify the login settings of MySQL and temporarily do not verify the login password sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# Comment out "skip-external-locking" under [mysqld] # Add "skip-grant-tables" and save and exit For example:
================================================
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
skip-grant-tables
=================================================

# Restart the mysql database sudo service mysql restart

# Log in to mysql database without password mysql -u root mysql

# Change the root password update mysql.user set authentication_string=password('111222333') where user='root';

# Refresh privileges;

# Restore MySQL login settings # Delete "skip-grant-tables" added under [mysqld] # Uncomment "skip-external-locking" and save and exit sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
For example:
================================================
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
=================================================

# Restart the mysql database sudo service mysql restart

# Log in to mysql using the new password
mysql -u username -p password database name# For example: 
mysql -uroot -p111222333 mysql

# Log in to mysql directly
mysql -u username -p password database name# For example: 
mysql -uroot -p111222333 mysql

# Log in first, then enter the password and select the database mysql -u root -p
Enter password: “root user password”
mysql> use mysql # Select the database to operate # Switch database use database name;

# View all databases show databases; 

# View all tables in the login database show tables; 

# View the data table structure desc table name;

# View the login user name select user(); 

# View the login database name select database();

# Check the version select version();

# Display the current time select now();

# View all users' usernames, login hosts, authentication plugins, and passwords select user,host,plugin,authentication_string from user;

# View the permissions of the currently logged in user show grants;

# View any user's permissions (the specific meaning of permissions will be discussed later)
show grants for root@localhost;

# Use the root user to log in to the database mysql -uroot -p111222333 mysql

# Create database create database "database name" charset="database encoding";
# For example:
create database pydb charset=utf8;

# Create a database user create user "user name"@"IP address" identified by "password";
# For example:
create user 'test0'@'localhost' identified by 'test0111';

# Delete data drop database [database name];
# For example:
drop database pydb;

# Delete database user drop user 'user name'@'ip address';
# For example:
drop user 'test0'@'localhost';

# View the permissions of user test0show grants for test0@localhost;

Display: [GRANT USAGE ON *.* TO 'test0'@'localhost']
USAGE: indicates an unauthorized user.

# Log in to the pydb database as user test0 mysql -utest0 -ptest0111 pydb

The error message indicates that the operation is not authorized: [ERROR 1044 (42000): Access denied for user 'test0'@'localhost' to database 'pydb']

# Use the root user to grant permissions to the test0 user: grant all privileges on 'database name'.'table name' to 'user name'@'IP address' identified by "password" with grant option;

# For example, set the local client to access this user:
mysql> grant all privileges on pydb.* to 'test0'@'localhost' identified by 'test0111' with grant option;

# Set any remote client to access this user:
mysql> grant all privileges on pydb.* to 'test0'@'%' with grant option;

# Refresh privilegesmysql> flush privileges;

# View local permissions show grants for 'test0'@'localhost';
Output: [GRANT ALL PRIVILEGES ON `pydb`.* TO 'test0'@'localhost' WITH GRANT OPTION]

# View remote permissions show grants for 'test0'@'%';
Output: [GRANT ALL PRIVILEGES ON `pydb`.* TO 'test0'@'%' WITH GRANT OPTION]
The permissions have been added successfully. Now both the local client and the remote client have the permission to log in to the test0 user and operate all tables under the pydb database.

# Explanation of the authorization command grant: Authorization keywords all privileges: Authorization type, all privileges means all permissions. You can also assign specific permissions such as select, update, create, drop, etc.
on: Indicates which table in which database the permission is to be granted to. *.* indicates all tables in all databases.
to: indicates which user the permission is to be granted to, in the format of "user name"@"login IP or domain name". localhost means that the local client can log in to this user, % means that any host client can log in to this user. If a specific IP address is configured, such as 'username'@'192.168.113', it means that only the host client 192.168.113 is allowed to log in to this user. You can also configure IP segments such as 'username'@'192.168.%'
identified by: Set the login password for this user, or you can choose not to set it.
with grant option: Indicates that this user is allowed to grant his or her permissions to other users.
Note: The permissions added by grant are automatically superimposed. For example, if you first add the select permission and then add the insert permission, the user will have both the select and insert permissions. If the user already has all privileges and select permission is added, the user still has all privileges.

# Remove permissions revoke insert on 'database name'.'table name' from 'user name'@'IP address';
# For example, to remove the insert permission:
revoke insert on pydb.* from 'test0'@'localhost';

# For example, all permissions:
revoke all on pydb.* from 'test0'@'localhost';

# Refresh privilegesmysql> flush privileges;

# Back up all databases:
mysqldump -uroot -p --all-databases > all.db

# Back up the specified database:
mysqldump -uroot -p pydb > test1.db

# Back up some tables of the specified database mysqldump -uroot -p pydb table name 1 table name 2 table name n > test2.db

# Back up the specified database excluding certain tables (excluding tables 1 and 2)
mysqldump -uroot -p pydb --ignore-table=pydb.Table1 --ignore-table=pydb.Table2 > test3.db

# Create test database create database test charset=utf8; 

# Restore the backup data. The imported database must exist. mysql -u root -p test < test1.db

# View monitoring netstat -an |grep 3306
# The current listening address is the local loopback address: tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 
# Remote client cannot access # Modify MySQL's local listening address sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Change the value of bind-address to bind-address = 0.0.0.0, save the file and exit.
You can also change it to the local IP address, for example: bind-address = 192.168.111.10

# Restart the mysql database sudo service mysql restart

# Test the network on the remote client machine. If telnet succeeds, it means the network is ok. telnet 192.168.111.10 3306

# Note: Alibaba Cloud Server has port restrictions. You need to configure port access rules to open port 3306. URL: [https://homenew.console.aliyun.com/](https://homenew.console.aliyun.com/)

If the connection fails, perform the following test:
[192.168.111.10] is your mysql server ip address 1. Test network and port telnet 192.168.111.10 3306

2. Check the server listening address sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0

3. Check whether the user has remote access rights and password verification plug-in select user, host, plugin from user;
The value of the login user host: % or remote client IP
Login user plugin value: mysql_native_password 
The password verification plugin for MySQL 5.7 is mysql_native_password 

# Add user access rights and set password verification plugin grant all privileges on pydb.* to 'test0'@'%' identified with mysql_native_password by 'test0111';

# If the user's remote access permission exists and the password verification plug-in is incorrect, you can only modify the password verification plug-in alter user 'test0'@'%' identified with mysql_native_password by '111222333';

# Refresh privilegesmysql> flush privileges;

# Uninstall mysql:
1.sudo apt-get autoremove mysql* --purge
2.sudo apt-get remove mysql-server
3.sudo apt-get remove mysql-common

# Clean up residual data sudo dpkg -l |grep mysql|awk '{print $2}' |sudo xargs dpkg -P 
sudo rm -rf /etc/mysql/ 
sudo rm -rf /var/lib/mysql

# Check whether the deletion is complete whereis mysql
sudo find / -name mysql