1. Preparing Resources for MySQL Installation

1.1 Resolving MySQL Installation Conflicts

• Clear pre-installed MySQL and MariaDB to avoid conflicts
• Delete the my.cnf file under /etc
• Check the user group: id mysql

1.2 Environment Preparation

1.2.1 User Setup

useradd -M -s /usr/sbin/nologin mysql

1.2.2 Installation package download

Resource download: MySQL official website
Recommended resource: LNMP manual deployment – without panel tools

1.2.3 Environment preparation before compilation

mkdir -p /www/mysql
sudo -u mysql tar -zxvf mysql_linux.tar.gz -C /www/mysql
chown -R mysql:mysql /www/mysql

##Configure Environment Variables – Usually set only for the current user (.bash_profile)
MYSQL_HOME=/www/mysql
export PATH=$MYSQL_HOME/bin
source .bash_profile

1.3 Compilation installation considerations

--defaults-file: Specify the configuration file (should be placed before --initialize)
--user: Specify the user
--basedir: Specify the installation directory
--datadir: Specify the initialization data directory
--initialize-insecure: Initialize without a password (otherwise, a random password will be generated)

2. MySQL Version 5.x Deployment Process

2.1 Compilation Installation

2.1.1 Create MySQL Installation Configuration File

vim /etc/my.cnf

[mysqld]
datadir=/www/mysql/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=400
innodb_file_per_table=1
lower_case_table_names=1
log-bin=mysql-bin
server_id=1
binlog-format=row
sync_binlog=1
log_slave_updates=1
default_authentication_plugin=mysql_native_password

2.1.2 Compilation Initialization

Note that during initialization, a temporary password will be printed. Since MySQL 5.x versions may vary, the –initialize-insecure parameter may be ineffective.

cd /www/mysql/bin
./mysqld --user=mysql --datadir=/www/mysql/data --basedir=/www/mysql --initialize-insecure

2.2 MySQL Startup

2.2.1 Modify MySQL Server Files

vim /www/mysql/support-files/mysql.server
Change /usr/local/mysql to /www/mysql (actual installation location, modify five instances in total).

##The modification example code is as follows:

# Set some defaults
mysqld_pid_file_path=
if test -z "$basedir"
then
  basedir=/usr/local/mysql
  bindir=/usr/local/mysql/bin
  if test -z "$datadir"
  then
    datadir=/usr/local/mysql/data
  fi
  sbindir=/usr/local/mysql/bin
  libexecdir=/usr/local/mysql/bin
else
  bindir="$basedir/bin"
  if test -z "$datadir"
  then
    datadir="$basedir/data"
  fi
  sbindir="$basedir/sbin"
  libexecdir="$basedir/libexec"
fi

2.2.2 MySQL Start and Stop

cd /www/mysql/support-files
./mysql.server start
./mysql.server stop

2.2.3 Login and User Configuration

## with temporary password
mysql -u root '-p[temporary_password]'

## without password (using parameter)
mysql -u root

## Change root password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_passwd';

## Enable remote connection
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

## Verify remote connection
mysql -uroot -p'root' -h127.0.0.1 -P3306

## Check binlog
show variables like "log_%";

2.3 MySQL Service Manger

cp /www/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --list

3. MySQL Version 8.x Deployment Process

3.1. Add Directory Structure for Version 8.x

mkdir -p /www/mysql/data
mkdir -p /www/mysql/meta
mkdir -p /www/mysql/data/log
mkdir -p /www/mysql/data/temp
mkdir -p /www/mysql/data/binlog

3.2 Create Config File my.cnf

###### [client]config module ######
[client]
default-character-set=utf8mb4
socket=/www/mysql/data/tmp/mysql.sock
​
###### [mysql]config module ######
[mysql]
# set MySQL client default character
default-character-set=utf8mb4
socket=/www/mysql/data/tmp/mysql.sock
​
###### [mysqld]config module ######
[mysqld]
skip_host_cache
skip-name-resolve=1
port=21001
user=mysql
innodb_strict_mode=0
# set sql mode,The sql_mode causing the error "*this is incompatible with sql_mode=only_full_group_by" in group queries. It's best to remove ONLY_FULL_GROUP_BY in this case.

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# MySQL Server Installation 
basedir=/www/mysql
# MySQL Server Data
datadir=/www/mysql/meta
socket=/www/mysql/data/tmp/mysql.sock
​
# server ID
server-id = 221
​
# Whether read-only: 1 represents read-only, 0 represents read-write. It's recommended to set the slave as read-only. The SUPER privilege can bypass this rule (e.g., for the root account).

read-only=0
​
# If the MySQL 8 password authentication plugin is not set, lower version Navicat will not be able to connect.

authentication_policy=mysql_native_password
​
# Allow Max Connections
max_connections=100
​
# The default character set used by the server is the 8-bit encoded `latin1` character set.

character-set-server=utf8mb4
​
open_files_limit = 2000
table_open_cache = 2048M

# The default storage engine used when creating a new table.
default-storage-engine=InnoDB
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit=1
innodb_io_capacity=5000
innodb_log_buffer_size=16M
general_log=0

# 0: The table name will be stored as specified and the comparison will be case-sensitive.
# 1: The table name will be stored in lowercase on the disk and the comparison will be case-insensitive.

lower_case_table_names=1

max_allowed_packet=16M
​
​
# Set the timezone
default-time_zone='+8:00'
​
# Binlog configuration: As long as the `log_bin` address is configured, binary logging will be enabled.

log_bin = /www/mysql/data/binlog/mysql_bin

# Log retention days: Default is 0, meaning the logs are stored permanently.
# If the database undergoes periodic archiving, it's recommended to set a retention period for the binlog logs. There's no need to store binlog logs indefinitely; theoretically, only the logs after archiving need to be kept.

expire_logs_days = 30

# binlog max value
max_binlog_size = 2048M

# Specify the binlog format: There are three binlog formats—`statement`, `row`, and `mixed`. The default is `statement`. It is recommended to use the `row` format.

binlog_format = ROW

# After committing `n` transactions, binlog is flushed to disk. Setting 0 means no forced flushing, and the file system controls the log file flush. If dealing with online transactions and financial data, it's recommended to set it to 1. For other types of data, you can keep it set to 0.

sync_binlog = 1
​
# MySQL Server secure startup configuration section
[mysqld_safe]
# mysqld_safe log basedir
log-error=/www/mysql/data/log/mysqld_safe.err
# mysqld_safe pid basedir
pid-file=/www/mysql/data/tmp/mysqld.pid
# mysqld_safe socket basedir
socket=/www/mysql/data/tmp/mysql.sock
​
# MySQLadmin Tool configuration section
[mysqladmin]
# mysqladmin The socket file location used by the tool
socket=/www/mysql/data/tmp/mysql.sock

3.3 Initialization

mysqld --defaults-file=/etc/my.cnf --basedir=/www/mysql --datadir=/www/mysql/meta --user=mysql --initialize-insecure​

3.4 MySQL start and stop

--safe to start
mysqld_safe --defaults-file=/etc/my.cnf &
​
--server file to start
./mysql.server start

3.5 Login and Configuration

mysql -u root --skip-password
​
## alter passwd
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
​
## FLUSH PRIVILEGES
FLUSH PRIVILEGES;
​
## see all users
use mysql;
select user,host,plugin,authentication_string from user;
​
## create user
CREATE user 'root'@'%';

## Set the initial password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

## Grant all privileges to the user and flush privileges.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

4. Performance Tuning

For simple performance optimization, you can search the following parameters for their effects.

innodb_buffer_pool_size = 24G
innodb_log_buffer_size=48M​
max_allowed_packet=16M

5. MySQL Installation — Master-Slave Replication

5.1 Create Master-Slave Replication User on the Master Node

create user 'slave'@'%' identified with mysql_native_password by 'slave';
grant replication slave on *.* to 'slave'@'%';

5.2 Check binlog on the Master Node

show master status\G
​
## remember logfile and position

5.3 Enable Replication on the Slave Node

stop slave;
change master to  master_host='master_ip',master_user='slave_user',master_password='slave_passwd',master_port=21001,master_log_file='master_logfile',master_log_pos=master_position;
start slave;

5.4 Check if Master-Slave Replication is successfully enabled

show slave status\G
​
## The following two items should be set to Yes.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Summary

## Changing the password requires attention to the version.
## 5.6/5.1
update mysql.user set password=password('i2') where user='i2';
## 5.7
ALTER USER 'i2'@'%' IDENTIFIED BY 'i2';
set password for 'i2'@'%'=password('i2');
​
## Resolve MySQL max connections not taking effect**

find && vim / -name mysqld.service
LimitNOFILE=65535
LimitNPROC=65535
service mysqld restart

## Due to version differences, some parameters may still cause issues. Specific errors will be printed in the logs, and you can use the logs to locate and resolve the problem.