Pre installation instructions

Differences between CentOS6 and CentOS7 in MySQL usage:

1. Firewall: 6 yes iptables，7 yes firewalld 
2. Command to start the service: 6 yes service，7 yes systemctl

Installation of MySQL for Linux

4 major versions of MySQL

MySQL Community Server The community version is open source, free to download, but does not provide official technical support. It is suitable for most ordinary users.
MySQL Enterprise Edition Enterprise version, which requires payment and cannot be downloaded online. You can try it for 30 days. It provides more functions and more complete technical support, which is more suitable for enterprise customers with high requirements for database function and reliability.
MySQL Cluster Cluster version, open source, free. It is used to set up cluster servers, and several MySQL Server Package into one Server. It needs to be used on the basis of community version or enterprise version.
MySQL Cluster CGE Advanced cluster version, subject to payment.

So far, the latest official version is 8.0.27. Previously, 8.0.0 was released on September 12, 2016.
Version 8.0.25 is mainly used this time.

In addition, the official also provides MySQL Workbench (GUITOOL), an ER / database modeling tool designed specifically for MySQL. It is the successor of the famous database design tool DBDesigner4. MySQL Workbench is divided into two versions: community version (MySQL Workbench OSS) and Commercial Version (MySQL Workbench SE).

Download the specified version of MySQL

1. Download address
   Official website: https://www.mysql.com
2. Open the official website and click DOWNLOADS
   Then, click MySQL Community(GPL) Downloads

3. Click MySQL Community Server

4. Select the appropriate version in General Availability(GA) Releases. If MySQL is installed under Windows system, it is recommended to download MSI installer;
Click Go to Download Page to download

There are two kinds of installation programs for MySQL under Windows

mysql-installer-web-community-8.0.25.0.msi Download program size: 2.4M；Networking installation components are required during installation.
mysql-installer-community-8.0.25.0.msi Download program size: 435.7M；You can install it offline during installation. recommend.

1. Several ways of installing MySQL under Linux system
   There are three common ways to install software under Linux system:

Mode 1: rpm command
 use rpm The command installation extension is".rpm"Software package.
.rpm General format of package:

Mode 2: yum command
 It needs to be connected and obtained from the Internet yum Source, direct use yum Command installation.

Method 3: compile and install the source package
 in the light of tar.gz Such a compression format should be used tar Command to decompress; For other compression formats, use other commands.

MySQL is installed under Linux system. The official gives a variety of installation methods

The version of CentOS 7 system cannot be directly selected here, so the corresponding Red Hat Enterprise Linux is selected
https://downloads.mysql.com/archives/community/ Directly click Download to Download the full package of RPM Bundle. All the following components are included. You don't need to Download one by one.

We download version 8.0.25:

Download it; After the download is complete, see the following:

Document content:

Decompress the rpm installation package (the red box is the extracted installation package, which is required to install mysql8 version)


Upload the above five files to the virtual machine

Check MySQL dependency under CentOS7
1check / tmp temporary directory permissions (required)

During mysql installation, the mysql user will create a new TMP in the / tmp directory_ DB file, so please give / tmp greater permission. Execution:

chmod -R 777 /tmp

2 check the dependency before installation

rpm -qa|grep libaio

If libaio package exists, it is as follows:

rpm -qa|grep net-tools

If the net tools package exists, it is as follows:

If it does not exist, you need to install rpm in centos installation disk. If you install linux with a graphical interface, these are all installed.

MySQL installation under CentOS7
Execution: (must be executed in order)

be careful: If there is no inspection during inspection mysql Environment dependent installation mysql-community-server Will report an error
rpm yes Redhat Package Manage Abbreviations, by RPM Users can wrap the source code into rpm It is a file form with extension, which is easy to install.

-i , --install Install package
-v , --verbose Provide more detailed information output
-h , --hash Hash marks are listed during package installation (and -v The effect is better when used together)，Show progress bar

Install 1st dependency

Install 2nd dependency

Install 3rd dependency

When installing the third dependency, an error is reported
One command:

yum remove mysql-libs 

To solve the problem, clear the previously installed dependencies

Then reinstall from the 3rd dependency

Install the 4th dependency

Install the 5th dependency

View MySQL version
Execute the following command. If successful, mysql installation is successful. Similar to java -version, if you print the version and other information

mysql --version 
#or
mysqladmin --version

Execute the following command to check whether the installation is successful. You need to add - i. you don't need to be case sensitive, otherwise you can't find it.

rpm -qa|grep -i mysql

Service initialization
In order to ensure that the owner of the database directory and file is the mysql login user, if you are running the mysql service as root, you need to execute the following command to initialize:

mysqld --initialize --user=mysql

Note: - initialize option is initialized in "safe" mode by default. A password will be generated for root user and marked as expired. You need to set a new password after logging in. The generated temporary password will be recorded in the log.
View password:

cat /var/log/mysqld.log

root@localhost The initialization password e95eNgD1#QhS follows:

Start MySQL and check the status

#Add it or not The service suffix is OK 
Start: systemctl start mysqld.service 
close: systemctl stop mysqld.service 
Restart: systemctl restart mysqld.service 
View status: systemctl status mysqld.service

mysqld This executable represents MySQL Server program, running this executable file can directly start a server process.

Check whether the MySQL service starts automatically (start automatically)

systemctl list-unit-files|grep mysqld.service

The default is enabled.
If it is not enabled, you can run the following command to set self startup

systemctl enable mysqld.service

If you do not want to start automatically, run the following command to set

systemctl disable mysqld.service

MySQL login
3.1 first login
adopt

mysql -hlocalhost -P3306 -uroot -p

Log in and enter the initialization password in Enter password
The initialization password is the password e95eNgD1#QhS generated above

2. Change password
Because the initialization password is expired by default, an error will be reported when viewing the database
Change Password: the new password is: new_password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

After version 5.7 (excluding 5.7), mysql has added a new password security mechanism. Setting a new password is too simple and will report an error.

After changing to more complex password rules, the settings are successful and the database can be used normally

Set up remote login
Current issues
When configuring remote connection to Mysql database in SQLyog or Navicat, the following error message is encountered, which is caused by Mysql configuration that does not support remote connection.

Confirm network
1. Use ping ip address on remote machine to ensure smooth network

2. Use the telnet command on the remote machine to ensure that the port number is open for access

telnet ip Address port number

Extension: telnet command enabled:

1. Turn off firewall or open port
   Method 1: close the firewall
   CentOS6 :

service iptables stop

CentOS7:

systemctl start firewalld.service
systemctl status firewalld.service 
systemctl stop firewalld.service 

#Set boot enable firewall 
systemctl enable firewalld.service
#Set boot disable firewall 
systemctl disable firewalld.service

Mode 2: open port

View open port numbers

firewall-cmd --list-all

Set open port number

firewall-cmd --add-service=http --permanent

firewall-cmd --add-port=3306/tcp --permanent

service iptables restart

firewall-cmd --reload

Modify the configuration under Linux and test under MySQL on Linux system:

use mysql; 

select Host,User from user;

You can see that the current host configuration information of the root user is localhost.

Modify the Host to the wildcard% Host column, and specify the IP used to allow users to log in, such as user=root Host=192.168.1.1. This means that root can only be accessed through the 192.168.1.1 client. user=root Host=localhost, which means it can only be accessed through the local client. And% is a wildcard. If Host=192.168.1.%, It means that as long as the IP address prefix is "192.168.1." All clients can connect. If Host =%, all IPS have connection permissions.

Note: in the production environment, you can't set the host to% to save trouble. This will cause security problems. The specific settings can be set according to the IP of the production environment.

update user set host = '%' where user ='root';

The '%' is set by the Host to allow remote access.

After modifying the Host, remember to execute flush privileges to make the configuration take effect immediately:

flush privileges;

test
If it is MySQL 5 In version 7, you can successfully connect to MySQL using SQLyog or Navicat. If it is MySQL 8, the following problems will occur when connecting:

An error is reported when configuring a new connection: the error number is 2058. It is analyzed that the mysql password encryption method has changed.

Solution: under Linux, mysql -u root -p logs in to your mysql database, and then executes this SQL: the password is abc123

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

Then, after reconfiguring the connection of SQLyog, the connection is successful. OK.