Install MySQL on Linux (centos7), Ubuntu (apt-get)

The content is divided into two parts: the latest MySQL8.0 installation details (top), and MariaDB 5.5 and 10.x installation introduction (bottom).

MySQL 8 is up to 2 times faster than MySQL 5.7, with a number of improvements and faster performance.

Delete default or previously installed MySQL/MariaDB

Delete the default MySQL or any other version of MySQL installed before.

The following method is also the correct way to uninstall/delete MySQL.

The RPM delete MySQL

In the simplest way, run RPM -q to query the installed mysql software and RPM -e to uninstall the software package.

  1. (1) Use shell command loop to query all software related to mysql name and uninstall it in sequence:
[root@VM_0_15_centos ~]# for i in $(rpm -qa|grep mysql); do rpm -e $i --nodeps; done
Copy the code

RPM -qa: -q Query, -a Query all software packages.

RPM -e -e is short for erase.

–nodeps: ignores dependencies. It is usually used together with –force to force uninstallation.

  1. (2) Delete MariaDB.

CentOS 7.0, the system default database MariaDB. MariaDB conflicts with MySQL. You also need to delete it.

[root@VM_0_15_centos ~]# for i in $(rpm -qa|grep MariaDB); do rpm -e $i --nodeps; done
Copy the code
  1. Delete MySQL related directories (MySQL software directory and configuration directory) [Optional]

It is recommended to rename MySQL’s software and data directories rather than delete them, as described in the next section.

[root@VM_0_15_centos ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf
Copy the code

If the user is not root, run the sudo command to upgrade the permission as follows:

for i in $(rpm -qa|grep mysql);do sudo rpm -e $i --nodeps;done
或 for i in $(rpm -qa|grep MariaDB);do sudo rpm -e $i --nodeps;done

sudo rm -rf /var/lib/mysql && sudo rm -rf /etc/my.cnf
Copy the code

Delete using yum package management command

It is convenient to remove packages using commands like yum remove.

  1. useyum removeDelete:

To remove all MySQL packages and dependencies, you still need to use a loop:

$ for i in $(rpm -qa|grep mysql);do sudo yum remove -y $i;done

或 for i in $(rpm -qa|grep MariaDB);do sudo yum remove -y $i;done
Copy the code
  1. Rename mysql related directories.

It is recommended to rename the /var/lib/mysql software data directory and /etc/my.cnf configuration directory files as a backup.

$ sudo mv /var/lib/mysql /var/lib/mysql_old_backup
$ sudo mv /etc/my.cnf /etc/my.cnf_old_backup
Copy the code

Yum install Mysql8.0

The installation process is generally divided into three steps:

  1. Configure the IP address of the MySQL yum repository
  2. The installation
  3. Initial Configuration

Configure the YUM repository address in two ways

For MySQL8.0, there are two ways to configure the yum repository address:

  • One is the yum Repo RPM package provided by MySQL.
  • The other is to configure the domestic mirror source (take Tsinghua Mirror as an example)

Use the official RPM package for configuring the yum repository

Yum yum yum yum Yum Yum Yum Yum Yum

Wait until the official download address: repo.mysql.com//mysql80-co…

  • useyum localinstallInstallation [Recommended]
$ sudo yum localinstall https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
Copy the code

Since you specify the RPM package address directly, you can also use yum install here.

  • Or download the RPM package first and use itrpm -iThe installation

Below, use wget to download first.

$ wget -c https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm --2021-09-02 14:46:23-- https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm Resolving repo.mysql.com (repo.mysql.com)... 23.72.33.24 Connecting to repo.mysql.com (repo.mysql.com) | 23.72.33.24 | : 443... connected. HTTP request sent, awaiting response... 200 OK Length: 26024 (25K) [application/x-redhat-package-manager] Saving to: 'mysql80 - community - release - el7-3. Noarch. RPM' 100% [= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = >] 26024 116 KB/sin0.1s 2021-09-02 14:46:24 (116 KB/s) - 'mysql80-community-release-el7-3.noarch. RPM' saved [26024/26024]Copy the code

Install with RPM -i (also available with yum localinstall)

$ sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm
warning: mysql80-community-release-el7-3.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]
Updating / installing...
   1:mysql80-community-release-el7-3  # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]
Copy the code
  • Check the installation
$ rpm -q mysql80-community-release
mysql80-community-release-el7-3.noarch
Copy the code
  • View the installed mysql-community repository source

Configure the MySQL8.0 repository source using Tsinghua image

Enter tsinghua mirror MySQL warehouse address: mirrors.tuna.tsinghua.edu.cn/mysql/yum/m…

You can find the corresponding mysql80-community-release-el7-3.noarch. RPM package.

As follows:

Originally intended to demonstrate aliyun’s mirror source. But Aliyun’s current mirror source lookup is really… It’s a long story.

Or tsinghua mirror organizational structure is clear, easy to find. Mysql /yum/mysql80-community-el7/ mysql80-community-el7/ mysql80-community-el7/ mysql80-community-el7/ mysql80-community-el7/ mysql80-community-el7/ mysql80-community-el7 Find mysql80.

As you can see below, after entering the source address of tsinghua mirror, it is very convenient to find the package you want according to the name of the software to be installed.

Perform the install repository source package:

$ sudo yum install https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql80-community-el7/mysql80-community-release-el7-3.noarch.rpm
Copy the code

MySQL installation

Install mysql – community – server

With the warehouse source installed, you are ready to install.

$ sudo yum install -y mysql-community-server
Copy the code

Start the mysqld

$ sudo systemctl start mysqld
Copy the code

Start mysqld

$ sudo systemctl enable mysqld
Copy the code

Check mysqld status

$ sudo systemctl status mysqld
Copy the code

Configure the MySQL

Query the temporary password for MySQL

During the default installation of MySQL, a temporary password is generated for the root user. This password can only be used once. You must reset the password after logging in for the first time.

The temporary password is stored in the /var/log/mysqld.log file in the log directory. Use the keyword ‘temporary password’ to search for the temporary password.

To view temporary passwords:

$ sudo grep 'temporary password' /var/log/mysqld.log
2021-09-03T09:11:28.993923Z 6 [Note] [MY-010454] [Server] A temporary password is generated forroot@localhost: jus? >Y6kkd1dCopy the code

Mysql > insert into mysqllanding

Mysql -p logon with temporary password

Note the sudo permissions.

$ sudo mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
Copy the code

Temporary password The password must be reset after login. Otherwise, no statement can be executed.

Mysql -uroot -p is usually used to log in to the mysql database. That is, the user name is specified. The default user name is not root.

Change the password

ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘XXXX ‘;

Password Policy Verification

Because the security policy is modified, a simple password cannot be specified for a user by default:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>
Copy the code

A simple password is not recommended!

Modify the VALIDate_password_policy configuration

Validate_password is a plugin for MySQL (introduced in MySQL5.6 and installed automatically with subsequent releases).

Validate_password_policy is used to check whether the changed password complies with the current policy.

  • Validate_password_policy values
Policy Tests Performed
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

Example Change the password policy to a low level and the minimum length of the password is 1 (the actual value is 4 and the minimum value is 4).

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=1;
Query OK, 0 rows affected (0.00 sec)
Copy the code

The password is changed.

Remember to use Flush to save permissions.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
Copy the code

You can also use SET PASSWORD = ‘123456’ directly; Set password:

mysql> SET PASSWORD = '123456';
Query OK, 0 rows affected (0.02 sec)
Copy the code

MySQL > SET PASSWORD = PASSWORD(‘123456’); Set the password of the login user.

PASSWORD() is not supported in MySQL8.

Log out and re-log in using the new password.

mysql> exit
Bye
[root_test@VM_0_15_centos ~]$ sudo mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help; ' or '\h' for help. Type '\c'to clear the current input statement. mysql> select version(); + -- -- -- -- -- -- -- -- -- -- -- + | version () | + -- -- -- -- -- -- -- -- -- -- -- + | 8.0.26 | + -- -- -- -- -- -- -- -- -- -- - + 1 rowin set (0.00 sec)

Copy the code

MySQL several information to view Settings

Check the port

mysql> show variables like 'port';  -- or show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.01 sec)
Copy the code

View user authorization information

mysql> select host,user,authentication_string from mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | $A$005$   l[QL*}     _GW4qyR6aL6uR3/CYdIvRHPZA60sgS.Iwo5Sn9qzoiF.QBNQ8 |
+-----------+------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Copy the code

Mysql > select host,user,password from MySQL5.6; mysql > select host,user,password from mysql.user; .

After 5.7, the password field was changed to authentication_string.

View and set the validATE_password parameter

Show variables like ‘validate_password%’; View several parameters related to password authentication.


mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 4     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
Copy the code

As you can see above, if you set validate_password.length to 1, it will also be 4 (the minimum length).

You can use set global validate_password. XXX to set parameters related to validate_password.

Validate_password. number_count specifies the minimum number of digits in a password. Validate_password. mixed_case_count — Minimum number of cases; Validate_password. special_char_count — The minimum number of special characters; Dictionary_file represents the dictionary file

As a global variable, you can view it with @@validate_password.length.

mysql> select @@validate_password.length;
+----------------------------+
| @@validate_password.length |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.00 sec)
Copy the code

MySQL8.0 does not support the PASSWORD function

The PASSWORD function is no longer supported in MySQL8.0, so you cannot use PASSWORD (‘ XXX ‘) to set a PASSWORD. Otherwise, a syntax ERROR (ERROR 1064) is reported.

MySQL5.7.5 will no longer support the PASSWORD function.

mysql> select password('123456');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('123456') ' at line 1
mysql> SET PASSWORD = PASSWORD('123456');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('123456') ' at line 1
Copy the code

About the password change pit [note]

The password is actually set by updating the authentication_string field or password field in the user table (before MySQL5.7.6).

However, never use the UPDATE statement to directly assign authentication_string to UPDATE the user password: UPDATE user set authentication_string =’ XXX ‘where user=’root’; And execute Flush Privileges; Refresh permission, which will cause you to fail to log into MySQL.

Because the authentication_string field stores the encrypted character string of SHA2, assigning the value directly will cause the authentication during login and cannot correspond to the encrypted password.

However,

You can useSET PASSWORD = 'xxx';Extremely easy to change the current user password.

You can also useALTER USER 'root' IDENTIFIED BY 'xxx';Quickly change the password of a specified user.

The FOR keyword in SET PASSWORD can also be used to specify a user:

SET PASSWORD FOR 'root'@'localhost' = 'xxx';
Copy the code

The recommended and preferred statement for changing passwords is: ALTER USER ‘user_name’ IDENTIFIED BY ‘XXX ‘;

The password() function is supported in MySQL5.7.5 and earlier versions of the user table with the password field. UPDATE the password by executing the following UPDATE statement:

update user set password = password('123456') where user='root';
Copy the code

Install the specified version of MySQL

View the available MySQL packages

yum repolist all | grep mysql 
Copy the code

As you can see, the default installed version is mysql80-community.

Note: above installationmysql80-community-release-el7-3.noarch.rpmWarehouse source package, containing 5.5, 5.6, 5.7, version of mysql – community – server.

Install the specified version of MySQL

If you want to install the mysql57-Community version, you have two options:

Repos. D /mysql-community. Repo (not recommended)

Second, run the yum-config-manager command to configure the configuration.

  1. Uninstall mysql80-community by default using yum-config-manager –disable mysql80-community

  2. Use yum-config-manager –enable mysql57-community to make mysql57-community the default installed version of yum.

Look again at the default installed version:

MySQL > install mysql57;

yum install -y mysql-community-server
Copy the code

In ancient times (MySQL younger than 5.5), the MySQL installation package was called MySQL mysql-server.

Yum install mysql mysql-server yum remove mysql mysql-server

Question:

As follows, also is to use the yum repolist all | grep mysql check mysql packages available.

Somehow! Mysql80-community has an exclamation mark. I don’t know what it means. It can be installed normally.