CentOS install Mysql 5.7

Download Mysql 5.7

bash

        cd /usr/local/src
wget xxx

Uninstall the system's built-in Mariadb

bash

        # Check if mariadb exists
rpm -qa|grep mariadb

# Remove mariadb
rpm -e --nodeps xxx

rpm mariad

Extract Mysql and move to /usr/local/mysql directory

bash

        tar -zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

Create directories for storing Mysql data

Here it is placed under the root directory Then create a data and log directory inside

mysql dir

Create mysql user group and user

bash

        groupadd mysql
useradd -g mysql mysql

Change the owner and group of the mysql directory

bash

        chown -R mysql /usr/local/mysql/
chgrp -R mysql /usr/local/mysql/

Configure my.cnf in /etc/my.cnf

Note that since mysql 5.7.18, the tar.gz package no longer includes the mysql-default.cnf file, so my.cnf needs to be created manually

The following are the default locations where mysql can automatically recognize my.cnf

/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options (server only)
defaults-extra-file Use --defaults-extra-file
Specified file, if any
~/.my.cnf User-specific options
~/.mylogin.cnf User-specific login path options (client only)

my.cnf

        [client]
# Port
port=3306

# Socket file
socket=/mysql/mysql.sock

[mysql]
default-character-set=utf8mb4

[mysqld]
# Server ID
server-id=1

# Port
port=3306

# Character set
character-set-server=utf8mb4

# Database installation directory
basedir=/usr/local/mysql

# Database data storage directory
datadir=/mysql/data

# Temporary directory
tmpdir=/tmp

# Socket file
socket=/mysql/mysql.sock

# Binary log file
log-bin=/mysql/log/mysql-bin

# Binary log format
binlog_format=ROW

# Log expiration time, 0 means no expiration
expire_logs_days=7

# Binary log database
#binlog-do-db=dbname

# Binary log ignore database
#binlog-ignore-db=dbname

# Enable slow query
slow_query_log=1

# Slow query log file
slow_query_log_file=/mysql/log/mysql-slow.log

# Record slow queries longer than 5 seconds
long_query_time=5

# Default values and NULL values in timestamp columns
explicit_defaults_for_timestamp=1

# Log file
log-error=/mysql/log/mysql-error.log

# PID file
pid-file=/mysql/mysql.pid

# SQL mode
#sql_mode=""

[mysqldump]
quick

Initialize the database

bash

        ./bin/mysqld --initialize --user=mysql

Add mysql system service, enable auto-start on boot

bash

        # Add MySQL service script to system services
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

# Grant executable permissions
chmod +x /etc/init.d/mysql

# Add mysql service
chkconfig --add mysql

# Enable mysql auto-start on boot
chkconfig --level 345 mysql on

# View service list
chkconfig --list

chkconfig

Configure global access

bash

        # Method 1 (recommended)
# Configure environment variables in /etc/profile
vim /etc/profile

export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin:$MYSQL_HOME/lib

source /etc/profile

# Method 2
# Create a symbolic link
ln -s /usr/local/mysql/bin/mysql /usr/bin

Start and stop MySQL service

bash

        service mysql start
service mysql stop
service mysql restart
service mysql status

View temporary password

Check the mysql-error.log file

mysql-error.log

Change password

You need to change the password first before performing other operations, otherwise you will get the error You must reset your password using ALTER USER statement before executing this statement.

bash

        ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
flush privileges;

alter pwd

Remote access

For cloud servers, you also need to configure the inbound rules in the security group

bash

        # Open port
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

# View open ports
firewall-cmd --zone=public --list-ports

bash

        # This method does not change permissions
update mysql.user set host='%' where user='root' and host='localhost';
# Or
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'test' WITH GRANT OPTION;

flush privileges;

Database Backup

bash

        mysqldump -uroot -pxxx Entering the password in the script will be warned as unsafe
If configured in [mysqldump] in my.cnf
user=root
password=xxx
you can omit entering the password
-a -m -c corresponds to access, modify, change
+/-  + after a certain number - within a certain number
-mmin +/- minutes
-mtime +/- days
For example: -mmin +1 after one minute

mysql_backup.sh

        #!/bin/bash

dir=/backup/mysql

# Backup database
db_name=test

# Backup time
time=$(date +'%Y-%m-%d_%H:%M:%S')

# File name
file_name=mysql

mysqldump $backup_name --databases $db_name | gzip > $dir/$file_name'_'$db_name'_'$time.sql.gz

find $dir -name $file_name'_'$db_name'_*.sql.gz' -type f -mmin +1 -exec rm -rf {} \; >/dev/null

Scheduled Backup

bash

        # Edit schedule
crontab -e
*/1 * * * * sh /sh/mysql_backup.sh

# View schedule
crontab -l

backup

If you forget to configure the bin-log expiration time, you can use the following command to delete logs older than 7 days

bash

        PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);