mysql8.0.23 linux (centos7) installation complete and detailed tutorial

The previous article introduced the main updates of MySQL 8.0.23 (interpretation of new features). If you are interested, click to check it out!

The latest version of windows mysql-8.0.23-winx64, click to download

mysql8.0.23 linux (centos7) installation tutorial (attached: configure external network connection user authorization and case-insensitive configuration)

(The blogger will talk a few words here and get to the point later. In the development process, sometimes the combination of databases will double the efficiency of the program)

What is a relational database?

Common relational databases:
(In fact, the blogger has only used MySQL Oracle sqlServer)
Oracle, DB2, PostgreSQL, Microsoft SQL Server, Microsoft Access, MySQL

• A relational database is a database created based on the relational model.
• The so-called relational model is a "one-to-one, one-to-many, many-to-many" and other relational models. The relational model refers to a two-dimensional table model. Therefore, a relational database is a data organization composed of two-dimensional tables and the connections between them.
• Relational data can store some relational model data very well, such as one teacher corresponds to multiple students' data ("many-to-many"), one book corresponds to multiple authors ("one-to-many"), and one book corresponds to one publication date ("one-to-one")
• The relational model is a model that we often encounter in our lives. Relational databases are generally used to store this type of data.
• The relational model includes data structure (data storage issues, two-dimensional tables), operation instruction sets (SQL statements), and integrity constraints (data constraints within tables, constraints between tables).

Characteristics of relational databases:
It is safe (because it is stored on disk, the data will not be lost in case of a sudden power outage), easy to understand (based on the relational model), but does not save space (because it is based on the relational model, it must follow certain rules, such as allocating space even if the value of a field in the data is empty)

What is a non-relational database?

• Non-relational databases are mainly based on "non-relational models" (because relational models are too large, "non-relational" is generally used to represent other types of databases). Non-relational models include:

Column model: data is stored in columns. A relational database uses a row as a record, and a column model database uses a column as a record. (In this model, data is indexed, IO is very fast, and it is mainly some distributed databases)
Key-value pair model: The data stored is a "key-value pair", for example, name:liming, then the value stored in the name key is liming
Document model: Store data as individual documents, which is somewhat similar to a "key-value pair".

The blogger has no other non-relational databases installed on his machine (I won’t take screenshots here~)

It is efficient (because it is stored in memory), but insecure (data is lost in case of power outage, but redis can synchronize data to disk). Now many non-relational databases begin to support transfer to disk.

MySQL 8.0 version features and introduction

Related literature address
https://dev.mysql.com/doc/
mysql server documentation page
https://dev.mysql.com/doc/refman/8.0/en/

Performance upgrade level. Officials say MySQL 8.0 is 2 times faster than MySQL 5.7. MySQL 8.0 has better performance than MySQL 5.7 in read/write workloads, IO-intensive workloads, and high-contention workloads.

(Photo courtesy of Alibaba Cloud)

2. Stronger support for NoSQL documents. MySQL has provided NoSQL storage functionality since version 5.7, and this functionality has been greatly improved in version 8.0. This feature eliminates the need for a separate NoSQL document database, and the MySQL Document Store also provides multi-document transaction support and full ACID compliance for JSON documents in schema-less mode.

(Photo courtesy of Alibaba Cloud)

Window functions. That is, a special function that is executed on a set of records that meet certain conditions. It can be used to implement several new query methods. Window functions are similar to aggregate functions such as SUM() and COUNT(), but they do not merge multiple rows of query results into one row, but put the results back into multiple rows. That is, window functions do not require GROUP BY. The use of window functions will greatly improve the efficiency of related analytical scenarios.

UTF-8 encoding. Starting from MySQL 8.0, utf8mb4 is used as the default character set for MySQL and Unicode 9 is supported. The default character set will be changed from latin1 to utf8mb4, and the default collation will be changed from latin1_swedish_ci to utf8mb4_800_ci_ai.

Hide index. The index can be set to be hidden or displayed through commands. Hidden indexes will not be used by the query optimizer. We can use this function to debug the performance of related queries. By hiding or displaying them, we can analyze the reasons for differences in database performance. We can also remove invalid indexes.

To hide an index:

ALTER TABLE TABLENAME ALTER INDEX IDXNAME INVISIBLE;

Restore display of this index

ALTER TABLE TABLENAME ALTER INDEX IDXNAME VISIBLE;

Persistent settings. MySQL 8.0 adds the SET PERSIST command. The configuration value of this command is saved in the mysqld-auto.cnf file in the data directory. After restarting, the file is read and the configuration in it is used to overwrite the default configuration file, which makes up for the deficiency that the SET GLOBAL command can only take effect temporarily.
The command is as follows:

Restore display of this index

SET PERSIST max_connections = 400;

Reconstruct BLOB. Restructuring BLOB speeds up fragment read/update operations and can speed up operations on JSON data. Support for JSON has been greatly improved, with the addition of the JSON_EXTRACT() function for extracting data from JSON fields based on a path query parameter, and the JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregation functions for grouping data into JSON arrays and objects, respectively.

Transactional data dictionary. Completely separated from the MyISAM storage engine, the data dictionary is actually placed in some tables in InnoDB, and FRM, TRG, PAR and other files are no longer needed; Information Schema now appears as a view of the data dictionary table. In principle, the MyISAM data table type is not needed, and system tables can be placed in InnoDB.

SQL roles. You can create roles, set or remove roles for users, which greatly facilitates the management of permissions.

In fact, there are many related features. Here I just picked out some of the more important ones. For more information, you can check the document link above.

The first step in installing mysql8.0.23 is to download the binary file (installation package)

Official website address
https://www.mysql.com/
(The download location of the official website may change as the version is updated)

Download the mysql binary file to match your own linux version

Step 2 Upload the file to the specified folder and decompress it

cd /opt/mysql
tar -xvf mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.23-linux-glibc2.12-x86_64 mysql-8.0.23 # The name of the unzipped folder is long, so we can modify it slightly here

Step 3: Create user groups, users, passwords and authorize them

Create a user group and user and password groupadd mysql
 useradd -g mysql mysql 

Authorized user (e.g. the following directories specified when configuring my.cnf need to be authorized by the mysql user)
	chown -R mysql.mysql /opt/mysql/mysql-8.0.23 

Step 4 Initialize basic information (remember to set case-insensitive information during initialization)

Create a data storage directory mkdir data 
Switch to the bin directory cd bin
Initialization basic information Remember to remember that mysql8 must be set to case-insensitive during initialization, otherwise there will be no difference between subsequent modifications and deletions and reinstallations. After initialization, lower_case_table_names = 1 in the original my.con is invalid, so be sure to add --lower-case-table-names=1 during initialization.
 ./mysqld --user=mysql --basedir=/opt/mysql/mysql-8.0.23 --datadir=/opt/mysql/mysql-8.0.23/data/ --initialize --lower-case-table-names=1

Get the system initialization random default password. Record the password here for subsequent operations (knock on the blackboard)

Step 5 Edit the my.cnf file

There are many other configurable properties for my.cnf. For details, please refer to the official documentation. Here we only set the main parameters. The file path folder of my.cnf must exist (if the file does not exist, it will prompt that the file does not exist when it is started)

 #Set the installation directory of mysql basedir=/opt/mysql/mysql-8.0.23
 #Set the storage directory of mysql database data datadir=/opt/mysql/mysql-8.0.23/data
 #Set the client's default character set character-set-server=UTF8MB4
# The default storage engine that will be used when creating a new table default-storage-engine=INNODB
#Set whether to be case sensitive (this parameter must also exist here after initialization)
lower_case_table_names=1
# By default, the "mysql_native_password" plug-in is used for authentication. default_authentication_plugin=mysql_native_password
#Comment All configurations under mysqld_safe will be output to the datadir directory #[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid 

Step 6: Add mysqld service to system authorization and add service

 Enter the home directory cd /opt/mysql/mysql-8.0.23
Add mysqld service to the system cp -a ./support-files/mysql.server /etc/init.d/mysql
Authorize and add services chmod +x /etc/init.d/mysql
 chkconfig --add mysql 

Step 7: Start the service and establish a service synchronization connection

 service mysql start #Service start service mysql status #Check service status service mysql stop #Stop service service mysql restart #Restart service

When some friends start the service, they may be prompted that the folder does not have permission. You can view the log under datadir and set the mysql permission.

like:

chown -R mysql:mysql /opt/mysql/mysql-8.0.23/data

Establish a service synchronization connection (friends who don’t understand ln -s can search Baidu for the function of this command)

 ln -s /opt/mysql/mysql-8.0.23/bin/mysql /usr/bin

Step 8: Log in to mysql and change the password

#Enter mysql console mysql -uroot -p
#Change password ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your password'; 
#Flush privileges; 

Step 9: Configure external network connection authorization

#Select mysql database use mysql;
 #Modify the connection address of the root user. Now localhost is the local machine. You can also specify a fixed IP address. Here % opens access to all IP addresses. update user set host='%' where user='root';
 #Flush privileges; 

The last step is to exit the console and test the external network remote connection (installation completed)

exit; 

This is the end of this article about the complete and detailed tutorial on mysql8.0.23 linux (centos7) installation. For more relevant linux mysql8.0.23 installation content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!