Install mysql5.7 or above database on linux x86 architecture

Article directory

1 **Install mysql5.7 or above database**
- 1.1. Download
- 1.2. Place the compressed package in the tools directory and decompress it.
- 1.3. Establish user groups and users
- 1.4. Set permissions
- 1.5. Install mysql
- - **If an installation error message appears:**
- 1.6. Start the service
- 1.7. Put mysql into the local system service
- 1.8. Set msyql system command
- 1.9. Log in to mysql database
- - Login error:
- 1.10. Set a new password for the root user. If an error is reported above, it has already been set.
- 1.11. Modify the root user to be able to log in on any client.
- 1.12. Configure firewall
- 1.13. Client connection mysql test

1 Install mysql5.7 or above database

1.1, download

Download address: https://dev.mysql.com/downloads/mysql/5.7.html#downloads

1.2. Place the compressed package in the tools directory and decompress it

Upload mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz to the /data/tools directory and extract it to the /data/app directory

mkdir /data/apps
mkdir /data/tools
cd /data/tools
tar -zxvf mysql-5.7.43-linux-glibc2.12-x86_64.tar.gz -C /data/apps/
cd /data/apps/
mv mysql-5.7.43-linux-glibc2.12-x86_64 mysql-5.7.43

1.3. Establish user groups and users

groupadd -g 501 mysql
useradd -u 501 -g mysql -c mysqldb -r -s /bin/false mysql

Here, it is necessary to explain -r -s /bin/false. In fact, this is officially recommended by mysql. It recommends that the msyql user only serves as the owner of the file or directory and does not have the permission to log in to the shell. -r specifies the system User, -s /bin/false indicates that the mysql user does not have the permission to log in to the shell. Of course, you can also let mysql exist as an ordinary user with login shell permissions.

1.4. Set permissions

Let me also make a note here. All files involving mysql will have their owner and group set to mysql.

 chown -R mysql:mysql mysql-5.7.43

1.5, install mysql

cd /data/apps/mysql-5.7.43/
./bin/mysqld --user=mysql --basedir=/data/apps/mysql-5.7.43/ --datadir=/data/apps/mysql-5.7.43/data --initialize

If an installation error message appears:

./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Solution:

# yum install -y libaio

If an installation error message appears:

./bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

Solution:

# yum -y install numactl

After the installation is complete, execute the mysql command again to install:

# ./bin/mysqld –user=mysql –basedir=/data/apps/mysql-5.7.43/ –datadir=/data/apps/mysql-5.7.43/data –initialize

If the installation is successful, here, you must pay attention to the initial password generated at the end, and be sure to copy it and keep it for later use. When MySQL logs in for the first time, you need to use this initial password, as shown in the red box below:

Create /etc/my.cnf

vi /etc/my.cnf

[mysqld]
#Set service id
server-id=30
#skip login
#skip-grant-tables=1
#Enable mysql binlog function
log-bin = mysql-bin
# The way binlog records content, recording each line that is operated
binlog_format = ROW
# Reduce the content of the log and only record the affected columns.
binlog_row_image = minimal
basedir=/data/apps/mysql-5.7.43
datadir=/data/apps/mysql-5.7.43/data
socket=/var/lib/mysql/mysql.sock
character_set_server=utf8
user=mysql
port=3306
#Ignore case
lower_case_table_names=1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


#Maximum number of links allowed
max_connections=1000
max_allowed_packet = 500M
group_concat_max_len = 10240

#maximum sleep time
wait_timeout=300
#Timeout setting
interactive_timeout =500
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/data/apps/mysql-5.7.43/data/mysqld.log
pid-file=/data/apps/mysql-5.7.43/data/mysql.pid
tmpdir=/tmp

Authorization file:

 chown -R mysql:mysql /etc/my.cnf
 mkdir -p /var/lib/mysql
 Add soft connection
 ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

1.6. Start service

 ./support-files/mysql.server start

The service starts without error as shown in the figure:

1.7. Put mysql into the local system service

 cp support-files/mysql.server /etc/init.d/mysqld

chown -R mysql:mysql /etc/init.d/mysqld

Start mysql again with the system command and see if the system command takes effect:

service mysqld restart

1.8. Set msyql system command

 vi ~/.bash_profile

Add the following at the end of the file:

export PATH=$PATH:/data/apps/mysql-5.7.43/bin

To make changes take effect immediately:

source ~/.bash_profile

1.9. Log in to mysql database

Enter the initial password just now

# mysql -uroot -p

Login error:

ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

1. Modify /etc/my.cnf and add: in [mysqld]:

skip-grant-tables=1

2. Restart the mysqld service:

service mysqld restart

3. Log in to mysql using the root user: (If you don’t have a password, just press Enter)

mysql -uroot -p

Enter the command mode of myql and execute the following command:

update mysql.user set password_expired='N' where user='root';
update mysql.user set authentication_string=password('root') where user='root' and Host = 'localhost';
flush privileges;

4. Modify /etc/my.cnf and comment out:

skip-grant-tables=1

5. Restart the mysqld service:

service mysqld restart

Or execute the following command:

systemctl restart mysqld

6. Change password:

mysql -uroot -p

 USE mysql;
 UPDATE user SET authentication_string=PASSWORD("dbPassw0rd") WHERE User='root';
 FLUSH PRIVILEGES;
update user set user.Host='%' where user.User='root';

flush privileges;

7. Log in again

mysql -uroot -p

After entering:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'UzBHGHf!4dSd';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

1.10. Set a new password for the root user. If the error is reported above, it has already been set

mysql -uroot -p
alter user 'root'@'localhost' identified by 'dbPassw0rd';
use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

where xxx is the new password

1.11. Modify the root user to be able to log in on any client

mysql> use mysql;

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

mysql> flush privileges;

\#change Password

mysql> grant all on *.* to root@'%' identified by 'dbPassw0rd' with grant option;

1.12. Configure firewall

#Start port 3306
firewall-cmd --permanent --add-port=3306/tcp
#Restart firewall
systemctl restart firewalld

Configuration method for specific IP to access specific port

firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.132" port protocol="tcp" port="3306 "accept"
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.133" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.134" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.135" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.136" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.137" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.138" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.139" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.140" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.141" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.142" port protocol="tcp" port="3306" accept "
firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="10.193.6.143" port protocol="tcp" port="3306" accept "

1.13, client connection mysql test

Use the client Navicat Premium tool to connect to mysql, as shown in the figure: