First, Download

Download address: https://dev.mysql.com/downloads/mysql/

After entering the page, click the link on the right.

Download the corresponding version.

Upload to the server through xftp6 and other tools.

II. Installation and Configuration

2.1 Query and uninstall Mariadb from the system

rpm -qa | grep mariadb
rpm -e --nodeps file name

2.2 Establishing User Groups and Users

Check the existence of mysql composite users

# Check whether mysql groups and users exist or not, or create if none exists
cat /etc/group | grep mysql
cat /etc/passwd | grep mysql

Create a mysql user and mysql user group

# Add mysql user groups
groupadd mysql

# Add mysql users
useradd -g mysql mysql -d /home/mysql

# Modify the login password of mysql users
passwd mysql

2.3 Create temporary directories, data directories, and log directories

/home/mysql/3306/data
/home/mysql/3306/log
/home/mysql/3306/tmp

2.4 Unzip and modify permissions

Upload the downloaded mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz installation package to the server/usr/local directory

# Decompress again
tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

# Establish soft links for future upgrades
ln -s mysql-5.7.27-linux-glibc2.12-x86_64 mysql

# Modify users and user groups of all files under mysql folder
chown -R mysql:mysql mysql/

2.5 Create Configuration Files

Create my.cnf configuration file, which is recommended to be written and uploaded to the ET directory

[client]                                        # Client settings, the default connection parameters for the client
port = 3306                                    # Default connection port
socket = /home/mysql/3306/tmp/mysql.sock                        # For socket sockets for local connections, the mysqld daemon generates this file

[mysqld]                                        # Server Basic Settings
# Foundation setup
user = mysql
bind-address = 0.0.0.0                         # Allow any ip host to access this database
server-id = 1                                  # The unique number of Mysql service Each MySQL service Id needs to be unique
port = 3306                                    # MySQL listening port
basedir = /usr/local/mysql                      # MySQL installation root directory
datadir = /home/mysql/3306/data                      # MySQL Data File Location
tmpdir  = /home/mysql/3306/tmp                                  # Temporary directories, such as load data infile, will be used
socket = /home/mysql/3306/tmp/mysql.sock        # Specify a socket file for local communication between MySQL client program and server
pid-file = /home/mysql/3306/log/mysql.pid      # The directory where the pid file is located
skip_name_resolve = 1                          # Only use IP address to check the client's login, not the host name.
character-set-server = utf8mb4                  # Database default character set, mainstream character set support some special emoticons (special emoticons occupy 4 bytes)
transaction_isolation = READ-COMMITTED          # Transaction isolation level, which is repeatable by default. MySQL is repeatable by default.
collation-server = utf8mb4_general_ci          # The character set of database corresponds to some sort rules, etc. Be careful to correspond to character-set-server.
init_connect='SET NAMES utf8mb4'                # Set up the character set when client connects mysql to prevent scrambling
lower_case_table_names = 1                      # Is it case sensitive to sql statements, 1 means insensitive
max_connections = 400                          # maximum connection
max_connect_errors = 1000                      # Maximum number of false connections
explicit_defaults_for_timestamp = true          # TIMESTAMP allows NULL values if no declaration NOT NULL is displayed
max_allowed_packet = 128M                      # The size of the SQL packet sent, if there is a BLOB object suggested to be modified to 1G
interactive_timeout = 1800                      # MySQL connection will be forcibly closed after it has been idle for more than a certain period of time (in seconds)
wait_timeout = 1800                            # The default value of MySQL wait_timeout is 8 hours. The interactive_timeout parameter needs to be configured concurrently to take effect.
tmp_table_size = 16M                            # The maximum value of interior memory temporary table is set to 128M; for example, group by, order by with large amount of data may be used as temporary table; if this value is exceeded, it will be written to disk, and the IO pressure of the system will increase.
max_heap_table_size = 128M                      # Defines the size of memory tables that users can create
query_cache_size = 0                            # Disable mysql's cached query result set function; later test to determine whether to turn on or not based on business conditions; in most cases, close the following two items
query_cache_type = 0

# Memory settings allocated by user processes, and each session will allocate memory size for parameter settings
read_buffer_size = 2M                          # MySQL read buffer size. Requests for sequential table scans allocate a read buffer for which MySQL allocates a memory buffer.
read_rnd_buffer_size = 8M                      # Random Read Buffer Size of MySQL
sort_buffer_size = 8M                          # Buffer size used for MySQL execution sort
binlog_cache_size = 1M                          # A transaction produces a log that is recorded in Cache when it is not committed, and persists the log to disk when it needs to be committed. Default binlog_cache_size 32K

back_log = 130                                  # How many requests can be stored on the stack in a short time before MySQL temporarily stops responding to new requests; the official recommendation is back_log = 50 + (max_connections/5), with a cap of 900

# log setting
log_error = /home/mysql/3306/log/error.log                          # Database Error Log File
slow_query_log = 1                              # Slow Query sql Log Settings
long_query_time = 1                            # Slow query time; Slow query over 1 second
slow_query_log_file = /home/mysql/3306/log/slow.log                  # Slow Query Log Files
log_queries_not_using_indexes = 1              # Check sql that is not used in the index
log_throttle_queries_not_using_indexes = 5      # Represents the number of SQL statements per minute that are allowed to be logged to a slow log and are not indexed. The default value is 0, indicating that there is no limit.
min_examined_row_limit = 100                    # The number of rows retrieved must reach this value before they can be recorded as slow queries. SQL that returns fewer than the rows specified by this parameter is not recorded in the slow query log.
expire_logs_days = 5                            # MySQL binlog log log file saved expiration time, automatically deleted after expiration

# Master-slave replication settings
log-bin = mysql-bin                            # Open mysql binlog function
binlog_format = ROW                            # The way a binlog records content, recording each row being manipulated
binlog_row_image = minimal                      # For binlog_format = ROW mode, reduce the content of the log and record only the affected columns

# Innodb settings
innodb_open_files = 500                        # Restrict the data of tables Innodb can open. If there are too many tables in the library, add this. This value defaults to 300
innodb_buffer_pool_size = 64M                  # InnoDB uses a buffer pool to store indexes and raw data, usually 60% to 70% of physical storage; the larger the settings here, the less disk I/O you need to access the data in the table.
innodb_log_buffer_size = 2M                    # This parameter determines the size of memory used to write log files in M. Buffers are larger to improve performance, but unexpected failures can result in data loss. MySQL developers recommend settings between 1 and 8M
innodb_flush_method = O_DIRECT                  # O_DIRECT reduces the conflict between the cache of the operating system level VFS and the buffer cache of Innodb itself.
innodb_write_io_threads = 4                    # CPU multi-core processing capability settings are adjusted according to read-write ratio
innodb_read_io_threads = 4
innodb_lock_wait_timeout = 120                  # InnoDB transactions can wait for a locked timeout second before being rolled back. InnoDB automatically detects transaction deadlocks and rolls back transactions in its own lock table. InnoDB notices the lock settings with the LOCK TABLES statement. The default value is 50 seconds.
innodb_log_file_size = 32M                      # This parameter determines the size of the data log file. Larger settings can improve performance, but also increase the time required to recover the failed database.

2.6 Installation of database

# The current location is / home/mysql/bin

# Initialize the database and specify the user to start mysql
./mysqld --initialize --user=mysql

• After the installation is completed, an error. log file is generated under the datadir directory configured in my.cnf, which records the random password of the root user and uses less/home/mysql/3306/data/error.log to view the file.

2.7 Set up boot-up self-startup service

# The current location is / home/mysql

# Copy the startup script to the resource directory and modify mysql.server. It's better to modify mysqld as well. These two files are best synchronized.
cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

# Increase the execution privileges of mysqld service control scripts
chmod +x /etc/rc.d/init.d/mysqld

# Add mysqld service to system service
chkconfig --add mysqld

# Check whether the mysqld service is in effect
chkconfig --list mysqld

# Switch to mysql user
sudo mysql

# mysql start
service mysqld start

# View mysql status
service mysqld status

# Check mysql related processes
ps aux|grep mysql

# Restart mysql
service mysqld restart

2.8 Configuring environment variables

# Modify the configuration file and add export PATH=$PATH:/usr/local/mysql/bin
vim /etc/profile

# Immediate effect
source /etc/profile

2.9 Log on, Modify Password

# Landing mysql
mysql -uroot -p

# Modify root user password
set password for root@localhost=password("root");

3. Firewall Configuration

 

IV. Adding Remote Access Permissions

For example, you can use Navicat to access databases remotely.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
3 rows in set (0.00 sec)

Five, test

 

6. Common Questions

• Configuration file is not valid, refresh configuration file:

/etc/rc.d/init.d/mysqld reload or systemctl reload mysqld

• Restart service:

service mysql restart