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
- Download address
Official website: https://www.mysql.com
- 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-18.104.22.168.msi Download program size: 2.4M；Networking installation components are required during installation. mysql-installer-community-22.214.171.124.msi Download program size: 435.7M；You can install it offline during installation. recommend.
- 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:
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
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
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.
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
3.1 first login
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
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.
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:
- Turn off firewall or open port
Method 1: close the firewall
service iptables stop
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
Set open port number
firewall-cmd --add-service=http --permanent firewall-cmd --add-port=3306/tcp --permanent
service iptables restart
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:
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.