MySQL Configuration and Installation

Project Official: https://downloads.mariadb.org/mariadb/repositories/
Domestic Mirror: https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/
https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/

RPM Package Install MySQL

CentOS 7 and above: Installation CD-ROM directly available
mariadb-server Server package
mariadb Client Kit
CentOS 6:
mysql-server Server package
mysql client Kit

Start Services

[root@centos8|~]#systemctl start mariadb-server

Initialization scripts to improve security

Run the initialization script: mysql_secure_installation

Set database administrator root password 
Prohibit root remote login 
Delete anonymous user account 
Delete test database

MySQL Program

Client Programs

• mysql: Interactive CLI tool
• mysqldump: Backup tool that initiates a query request to mysqld based on the mysql protocol and converts all the data found to
• Write operation statements such as insert to save text files
• mysqladmin: manage mysqld based on mysql protocol
• mysqlimport: Data import tool
  Management tools for MyISAM storage engine:
• myisamchk: check the MyISAM Library
• myisampack: Package MyISAM tables, read-only

Server-side Programs

• mysqld_safe
• mysqld
  /usr/bin/mysqld_safe
• Mysqld_multiple multiple multiple instances, example: mysqld_multi ple --example

User Account

The mysql user account consists of two parts:
'USERNAME'@'HOST'
Explain:
HOST Restricts which remote hosts this user can connect to the mysql server
Wildcards are supported:
%matches any character of any length
172.16.0.0/255.255.0.0 or 172.16.%.%
_Match any single character

mysql command

mysql usage pattern:

• Interactive mode:
  There are two types of runnable commands:
• Client commands:
  h, help
  \u，use
  \s，status
  !，system

Server-side commands:

• SQL statement, requires statement terminator';'
• Script mode:
  mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
  mysql>source /path/from/somefile.sql

mysql command using format

Common mysql client options:
-A, --no-auto-rehash prohibits completion
 -u, --user=user name, default to root
 -h, --host=server host, default to localhost
 -p, --passowrd=user password, recommended -p, blank password by default
 -P, --port=server port
 -S, --socket=Specify connection socket file path
 -D, --database=Specify default database
 -C, --compress Enables Compression
 -e "SQL" Execute SQL Commands
 -V, --version display version
 -v --verbose display details
 --print-defaults Gets the default configuration used by the program
 Run mysql command: default empty password login
mysql>use mysql
 Mysql>select user(); view current user
mysql>SELECT User,Host,Password FROM user;
Login system: mysql-uroot-p

Command Classification

Client command: execute locally
mysql> help
Each command is in full and abbreviated form
Mysql> status or\s
Server-side command: Send to server via mysql protocol to execute and retrieve results
Each command must end with a command end symbol, which defaults to a semicolon
Example: SELECT VERSION();

mysqladmin command

#Check if the mysql service is working if prompted correctly
 mysqladmin -uroot -p123456 ping 
#Close the mysql service, but the mysqladmin command cannot be opened 
mysqladmin –uroot –p123456 shutdown 
#Create database testdb 
mysqladmin -uroot –p123456 create testdb 
#Delete database testdb 
mysqladmin -uroot -p123456 drop testdb 
#Modify root password 
mysqladmin –uroot –p123456 password 'magedu' 
#Log scrolling, generating new file/var/lib/mysql/mariadb-bin.00000N 
mysqladmin -uroot -p123456 flush-logs

Server-side Configuration

Server-side (mysqld): There are several ways to configure working features
1. Command line options:
2. Configuration file: class ini format, centralized configuration, can provide configuration information for mysql applications
Server side profile:

/etc/my.cnf #Global s Options  
/etc/mysql.my.cnf #Global s Options  
~/.my.cnf #User-specific option  

Profile format:
[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]
Format: parameter = value
Description: _Same as -
1, ON, TRUE have the same meaning, 0, OFF, FALSE have the same meaning
socket address
Two socket addresses that the server listens on:
ip socket: listens on port 3306 of tcp, supports remote communication, listens on port 3306/tcp can be on IP with one or all interfaces bound
unix sock: listens on sock files and only supports local communication
For example: /var/lib/mysql/mysql.sock
Note: unix sock is automatically used when host is localhost

Close mysqld network connection

Listen only for local clients, all client-server interactions are implemented through a socket file, and the socket configuration is stored in/var/lib/mysql/mysql.sock, which can be modified at/etc/my.cnf
Example:

vim /etc/my.cnf 
[mysqld] 
skip-networking=1 #Disable Network Remote Users Can't Connect

[root@centos8|~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql   #mysql path
socket=/var/lib/mysql/mysql.sock   #socket file
log-error=/var/log/mariadb/mariadb.log  
pid-file=/run/mariadb/mariadb.pid

Modify Command Prompt

#Modify mysql prompt
[root@centos8 ~]#vim /etc/my.cnf.d/mysql-clients.cnf 
[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"                                         
          
[root@centos8 ~]#mysql --print-defaults -v
mysql would have been started with the following arguments:
--prompt=\r:\m:\s(\u@\h) [\d]>\_ -v

General Binary Format Installation Process

Download address: https://downloads.mysql.com/archives/community/
Download: mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz

[root@centos7|~]#yum install -y perl-Data-Dumper   libaio

1. Prepare users

[root@centos7|~]#groupadd -r -g 306 mysql
[root@centos7|~]#useradd -r -g 306 -u 306 -d /data/mysql mysql
[root@centos7|~]#getent passwd mysql
mysql:x:306:306::/data/mysql:/bin/bash
[root@centos7|~]#id mysql
uid=306(mysql) gid=306(mysql) group=306(mysql)

2. Prepare a data catalog and recommend using logical volumes (optional)

[root@centos7|~]#mkdir /data/mysql
[root@centos7|~]#chown mysql:mysql /data/mysql
[root@centos7|~]#ll -d /data/mysql
drwxr-xr-x. 2 mysql mysql 6 2 January 1420:07 /data/mysql

Third, prepare the binary program

[root@centos7|~]#tar xvf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz -C /usr/local/  
[root@centos7|local]#cd /usr/local/
[root@centos7|local]#ln -sv mysql-5.6.46-linux-glibc2.12-x86_64/ mysql        
"mysql" -> "mysql-5.6.46-linux-glibc2.12-x86_64/"
[root@centos7|local]#chown -R root:root /usr/local/mysql/
[root@centos7|mysql]#pwd
/usr/local/mysql
[root@centos7|mysql]#cp -b support-files/my-default.cnf /etc/my.cnf
cp: Whether to Overwrite"/etc/my.cnf"？ yes
[root@centos7|~]#vim /etc/my.cnf
#Add the following three options to the mysql statement block
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on #Above mariadb5.5 is the default value, not added
skip_name_resolve = on    #Disable hostname resolution, recommended

Fourth, create database files

[root@centos7|~]#cd /usr/local/mysql
[root@centos7|mysql]#./scripts/mysql_install_db --datadir=/data/mysql --user=mysql

5. Prepare service scripts and start services

[root@centos7|mysql]#pwd
/usr/local/mysql
[root@centos7|mysql]#cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@centos7|mysql]#chkconfig --add mysqld
[root@centos7|mysql]#service mysqld start
Starting MySQL.Logging to '/data/mysql/centos7.err'.
 SUCCESS! 
 #If there is a corresponding service file, you can execute the following
cp support-files/systemd/mariadb.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl enable --now mariadb

PATH variable

[root@centos7|~]#echo 'PATH=/user/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7|~]#. /etc/profile.d/mysql.sh

Security Initialization

root@centos7|~]#mysql_secure_installation 

Source Compile Install mariadb

Download URL: https://downloads.mariadb.org/mariadb/10.3.22/
Download: mariadb-10.3.22.tar.gz

1. Install dependent packages

[root@centos7|~]#yum install -y bison bison-devel zlib-devel libcurl-devel libarchive-devel boostdevel  gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssldevel libevent-devel libaio-devel

2. Prepare user and data catalogs

[root@centos7|~]#useradd -r -s /sbin/nologin -d /data/mysql mysql

3. Preparing database catalogs

mkdir /data/mysql
chown mysql:mysql /data/mysql

Fourth, download and decompress the source package

tar xvf mariadb-10.2.18.tar.gz

5. Source compilation and installation of mariadb

[root@centos7|~]#cd mariadb-10.2.18/


 cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/ \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

[root@centos7|~]#make && make install

Tip: If an error occurs, execute rm-f CMakeCache.txt

Sixth, prepare environment variables

[root@centos7|~]#echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7|~]#. /etc/profile.d/mysql.sh

Seven, Generate Database Files

[root@centos7|~]#cd /app/mysql/
[root@centos7|~]#scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql

Eighth, prepare the configuration file

[root@centos7|~]#cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf
[root@centos7|~]#vim /etc/my.cnf
#Add the following three options to the mysql statement block
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on #Above mariadb5.5 is the default value, not added
skip_name_resolve = on    #Disable hostname resolution, recommended

9. Prepare the startup script and start the service

[root@centos7|~]#cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7|~]#chkconfig --add mysqld
[root@centos7|~]#service mysqld start

Ten, security initialization

[root@centos7|~]#mysql_secure_installation

One-click script for installing mysql-5.6 binary packages

#!/bin/bash
#mysql-install.sh
DIR=`pwd`
NAME="mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz"
FULL_NAME=${DIR}/${NAME}
DATA_DIR="/data/mysql"
yum install perl-Data-Dumper   libaio vim gcc gcc-c++ wget autoconf net-tools 
lrzsz   -y
yum install curl policycoreutils openssh-server openssh-clients postfix -y
if [ -f ${FULL_NAME} ];then
    echo "Installation File Exists"
else
    echo "Installation file does not exist"
    exit 3
fi
if [ -h /usr/local/mysql ];then
    echo "Mysql Installed"
    exit 3
else
   tar xvf ${FULL_NAME}   -C /usr/local/src
    ln -sv /usr/local/src/mysql-5.6.34-linux-glibc2.5-x86_64 /usr/local/mysql
    if id mysql;then
        echo "mysql User already exists, skip creating user process"
    else
       useradd  -r   -s /sbin/nologin mysql
     fi
    
    if id mysql;then
   chown  -R mysql.mysql /usr/local/mysql/* 
        if [ ! -d /data/mysql ];then
            mkdir -pv /data/mysql && chown  -R mysql.mysql /data   -R
           /usr/local/mysql/scripts/mysql_install_db  --user=mysql --
datadir=/data/mysql  --basedir=/usr/local/mysql/
 cp /usr/local/src/mysql-5.6.34-linux-glibc2.5-x86_64/supportfiles/mysql.server /etc/init.d/mysqld
 chmod a+x /etc/init.d/mysqld
 cp ${DIR}/my.cnf   /etc/my.cnf
 ln -sv /usr/local/mysql/bin/mysql /usr/bin/mysql
 /etc/init.d/mysqld start
 else
            echo "MySQL Data directory already exists,"
 exit 3
 fi
    fi
fi

[root@centos7|~]#vim /etc/my.cnf
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/tmp/mysql.pid

Binary Installation Install MySQL 5.7

Install related packages

[root@centos7|~]#yum install libaio numactl-libs

Users and Groups

[root@centos7|~]#groupadd mysql
[root@centos7|~]#useradd -r -g mysql -s /bin/false mysql

Preparer File

[root@centos7|~]#tar xvf mysql-5.7.28-el7-x86_64.tar.gz –C /usr/local
[root@centos7|~]#cd /usr/local/
[root@centos7|local]#ln -s mysql-5.7.28-el7-x86_64/ mysql
[root@centos7|~]#chown -R root:root /usr/local/mysql/

Preparing environment variables

echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

Prepare configuration file

[root@centos7|~]#cp /etc/my.cnf /etc/my.cnf.bak
[root@centos7|~]#vim /etc/my.cnf
[root@centos7|~]#vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock        
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

Generate database file and extract root password

[root@centos7|~]#mysqld --initialize --user=mysql --datadir=/data/mysql 
...ellipsis...
2019-07-04T13:03:54.258140Z 1 [Note] A temporary password is generated for

root@localhost: LufavlMka6,!  #Note the initial password for generating the root
[root@centos7|~]#grep password /data/mysql/mysql.log


2019-12-26T13:31:30.458826Z 1 [Note] A temporary password is generated for root@localhost: LufavlMka6,!

[root@centos7|~]#awk '/temporary password/{print $NF}' /data/mysql/mysql.log
LufavlMka6,!

Preparing service scripts and starting

[root@centos7|~]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7|~]#chkconfig --add mysqld
[root@centos7|~]#service mysqld start

Modify Password

[root@centos7|~]#mysqladmin -uroot -p'LufavlMka6,!'   password magedu

Test Login

[root@centos7|~]#mysql -uroot -pmagedu

Multiple instances of MySQL implementation: a physical server installs multiple MySQL services (mostly for test environments)

Install mariadb

[root@centos8|~]#yum install mariadb-server
 #Be careful not to start the service first

Catalog for preparing three instances

[root@centos8|~]#mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@centos8|~]#tree /mysql/
/mysql/
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
├── 3307
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

21 directories, 0 files
//Modify File Properties
[root@centos8|~]#chown -R mysql:mysql /mysql/

Generate database file

[root@centos8|~]#mysql_install_db --datadir=/mysql/3306/data --user=mysql
[root@centos8|~]#mysql_install_db --datadir=/mysql/3307/data --user=mysql
[root@centos8|~]#mysql_install_db --datadir=/mysql/3308/data --user=mysql

Prepare configuration file

cp /etc/my.cnf /mysql/3306/etc/
vim /mysql/3306/etc/my.cnf
[mysqld]
#Add this line and omit it if port is 3306
port=3306  
datadir=/mysql/3306/data/
socket=/mysql/3306/socket/mysql.sock
[mysqld_safe]   #The old version has settings, the new version does not
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid
#Repeat the steps above to set 3307, 3308

Example

[root@centos8|~]#cp /etc/my.cnf.d/mariadb-server.cnf /mysql/3306/etc/
[root@centos8|~]#vim /mysql/3306/etc/mariadb-server.cnf 
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid


[root@centos8|~]#cp /mysql/3306/etc/mariadb-server.cnf /mysql/3308/etc/mariadb-server.cnf

[root@centos8|~]#vim /mysql/3307/etc/mariadb-server.cnf

[mysqld]
port=3307
datadir=/mysql/3307/data
socket=/mysql/3307/socket/mysql.sock
log-error=/mysql/3307/log/mariadb.log
pid-file=/mysql/3307/pid/mariadb.pid


[root@centos8|~]#cp /mysql/3306/etc/mariadb-server.cnf /mysql/3308/etc/mariadb-server.cnf
[mysqld]
port=3308
datadir=/mysql/3308/data
socket=/mysql/3308/socket/mysql.sock
log-error=/mysql/3308/log/mariadb.log
pid-file=/mysql/3308/pid/mariadb.pid

Prepare startup script

vim /mysql/3306/bin/mysqld 
#!/bin/bash
port=3306   #Create startup scripts for 3307 and 3308, respectively 
mysql_user="root"
mysql_pwd="123456"  #root password
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
     printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
    else
     printf "MySQL is running...\n"
      exit
    fi
}
function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock}
shutdown
   fi
}
function_restart_mysql()
{
   printf "Restarting MySQL...\n"
   function_stop_mysql
    sleep 2
   function_start_mysql
}
case $1 in
start)
   function_start_mysql
;;
stop)
   function_stop_mysql
;;
restart)
   function_restart_mysql
;;
*)
   printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

Repeat the above process to build 3307, 3308 startup scripts, respectively

Start shutdown service

[root@centos8|~]#/mysql/3306/bin/mysqld  start
[root@centos8|~]#/mysql/3307/bin/mysqld  start
[root@centos8|~]#/mysql/3308/bin/mysqld  start

Logon Instance

[root@centos8|~]#/mysql/3306/bin/mysqld  start
[root@centos8|~]#mysql -uroot -S /mysql/3306/socket/mysql.sock
mariadb>show variables like 'port'  #Confirm Connected Port

Modify root password

[root@centos8|~]#mysqladmin -uroot -S /mysql/3306/socket/mysql.sock password 'magedu'  #Add a new password
#Or log in to mysql and execute the following
Mariadb>update mysql.user set password=password("centos") where user='root';
Mariadb>flush privileges;
#Repeat the steps to modify the root password for the other two instances 3307 and 3308 respectively

Test Connection

[root@centos8|~]#mysql -uroot -S /mysql/3306/socket/mysql.sock –p #Prompt for password to login

mysqladmin shutdown closes the database