All MySQL installation and deployment methods

catalog

1, Foreword

There are many ways to install linux software, MySQL is no exception, this article will introduce all the ways to install mysql.

2, About MySQL installation

Generally, MySQL can be installed in four ways, each with its own advantages and different scenarios:

  • Install MySQL from yum, advantages: simple, convenient, applicable scenario: network environment can be accessed
  • Offline source code compilation and installation of MySQL, advantages: customizable, strong applicability, applicable scenario: no network, need to customize mysql, platform compatibility is not strong (the memory should be greater than 4G, or the compilation will generate a memory shortage error)
  • RPM package installs MySQL, advantages: simple, convenient, applicable scenario: redhat system
  • General binary package installation MySQL, advantages: simple, easy to maintain, applicable scenario: most environments are applicable (recommended)

3, Deployment planning

3.1 server planning

The server Operating system version CPU architecture MySQL installation mode
node6 CentOS Linux release 7.4.1708 x86_64 Source compilation and installation of MySQL
node7 CentOS Linux release 7.4.1708 x86_64 General binary package installation MySQL
node8 CentOS Linux release 7.4.1708 x86_64 Install MySQL with RPM package
node9 CentOS Linux release 7.4.1708 x86_64 yum install MySQL

3.2 database directory planning

file type File deployment location
Data directory /data/data (/ data directory make sure it is large enough)
configuration file my.cnf /etc/my.cnf
Error log error /data/log/mysql_error.log
Binary log bin /Data / binlogs / MySQL bin (for database recovery, master-slave replication, and audit operations)
Slow query log slow_query_log_file /data/log/mysql_slow_query.log
Socket file socket /data/run/mysql.sock
Process ID file mysql.pid /data/run/mysql.pid

4, Prepare tools

1.MySQL general binary package: mysql-5.7.28-linux-glibc2.12-x86_ Sixty-four tar.gz

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

2. MySQL RPM package: mysql-community-client-5.7.28-1.el7.x86_64.rpm

​ mysql-community-common-5.7.28-1.el7.x86_64.rpm

​ mysql-community-devel-5.7.28-1.el7.x86_64.rpm

​ mysql-community-libs-5.7.28-1.el7.x86_64.rpm

​ mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm

​ mysql-community-server-5.7.28-1.el7.x86_64.rpm

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

3.MySQL source package: mysql-boost-5.7.28 tar.gz

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

4. Yum source of MySQL: mysql57-community-release-el7-10 noarch.rpm

Or mysql-community-release-el7-5 noarch.rpm

Download method: wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

Or wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

5, General binary package installation MySQL

5.1 upload MySQL general binary installation package to / usr/local/src directory of node7

[root@node7 src]# pwd
/usr/local/src
[root@node7 src]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

5.2 extract MySQL to the specified directory and rename it

[root@node7 src]# tar -zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@node7 src]# cd /usr/local/
[root@node7 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.26-linux-glibc2.12-x86_64  sbin  share  src
[root@node7 local]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql
[root@node7 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src

5.3 creating MySQL users and user groups

[root@node7 local]# groupadd -g 1111 mysql
[root@node7 local]# useradd -g mysql -u 1111 -s /sbin/nologin mysql
[root@node7 local]# id mysql    #View user information
uid=1111(mysql) gid=1111(mysql) groups=1111(mysql)

5.4 configure MySQL bin directory to PATH path

[root@node7 local]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@node7 local]# source /etc/profile
[root@node7 local]# mysql    #Double click the tab key after entering Mysql to list the candidate MySQL commands
mysql                       mysql_client_test_embedded  mysqld-debug                mysqldumpslow               mysql_plugin                mysqlslap                   mysql_upgrade
mysqladmin                  mysql_config                mysqld_multi                mysql_embedded              mysqlpump                   mysql_ssl_rsa_setup         mysqlxtest
mysqlbinlog                 mysql_config_editor         mysqld_safe                 mysqlimport                 mysql_secure_installation   mysqltest_embedded          
mysqlcheck                  mysqld                      mysqldump                   mysql_install_db            mysqlshow                   mysql_tzinfo_to_sql

5.5 create MySQL data storage directory

[root@node7 ~]# mkdir -p /data/{data,log,binlogs,run}
[root@node7 ~]# tree /data    #If there is no tree command, install yum -y install tree
/data
├── binlogs
├── data
├── log
└── run

4 directories, 0 files
[root@node7 ~]# chown -R mysql:mysql /data
[root@node7 ~]# ll /data/
total 0
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 binlogs
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 data
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 log
drwxr-xr-x 2 mysql mysql 6 Dec  3 11:07 run

5.6 configure MySQL configuration file

[root@node7 mysql]# rm -rf /etc/my.cnf
[root@node7 mysql]# touch /etc/my.cnf
#my.cnf For details of configuration file, please refer to my previous article blog Of#https://www.cnblogs.com/renshengdezheli/p/11913248.html MySQL profile optimization reference for
[root@node7 mysql]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock

[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535

skip-name-resolve
lower_case_table_names=1

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0

key_buffer_size=64M

log-error=/data/log/mysql_error.log
log-bin=/data/binlogs/mysql-bin
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5

tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0

server-id=1

5.7 initialize MySQL database

[root@node7 mysql]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/data
[root@node7 mysql]# echo $?
0
[root@node7 mysql]# grep 'temporary password' /data/log/mysql_error.log    #View MySQL initialization password
2019-12-03T03:47:42.639938Z 1 [Note] A temporary password is generated for root@localhost: lhrh>J,p<8gw

5.8 generate ssl (optional)

#About enabling ssl to view MySQL https://www.cnblogs.com/mysql-dba/p/7061300.html
[root@node7 mysql]# mysql_ssl_rsa_setup --basedir=/usr/local/mysql --datadir=/data/data
Generating a 2048 bit RSA private key
......................................+++
.+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
....................................+++
............................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.....................................................................................+++
..............................................+++
writing new private key to 'client-key.pem'
-----
#After execution, *. pem files will be generated in the datadir directory
[root@node7 mysql]# ls /data/data/
auto.cnf    client-cert.pem  ibdata1      mysql               public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  performance_schema  server-cert.pem
ca.pem      ib_buffer_pool   ib_logfile1  private_key.pem     server-key.pem

5.9 configure MySQL startup entry and set startup

5.9.1 CentOS 6

cd /usr/local/mysql
cp support-files/mysql.server /etc/init.d/mysql.server
chkconfig --add mysql.server
chkconfig  mysql.server on
chkconfig --list

5.9.2 CentOS 7

[root@node7 system]# cd /usr/lib/systemd/system
[root@node7 system]# touch mysqld.service 
[root@node7 system]# vim mysqld.service 
[root@node7 system]# cat mysqld.service 
# Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
#
# systemd service file for MySQL forking server
#

[Unit]
Description=MySQL Server
Documentation=man:mysqld(5.7)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

PIDFile=/data/run/mysql.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 65535

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

[root@node7 system]# systemctl daemon-reload    #Reload service profile
[root@node7 system]# systemctl enable mysqld    #Set MySQL startup
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@node7 system]# systemctl is-enabled mysqld   #Check whether MySQL boot is set successfully
enabled

5.10 starting MySQL

[root@node7 system]# systemctl start mysqld
[root@node7 system]# systemctl status mysqld    #View MySQL startup status
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2019-12-03 14:42:14 CST; 9s ago
     Docs: man:mysqld(5.7)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 2905 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Main PID: 2907 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2907 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid

Dec 03 14:42:13 node7 systemd[1]: Starting MySQL Server...
Dec 03 14:42:14 node7 systemd[1]: Started MySQL Server.
[root@node7 system]# ps -ef | grep mysql         #View MySQL process
mysql      2907      1  2 14:42 ?        00:00:00 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
root       2942   2576  0 14:42 pts/0    00:00:00 grep --color=auto mysql

5.11 MySQL security initialization (optional)

[root@node7 system]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root:    #Here, enter the password generated during MySQL initialization (grep 'temporary password' /data/log/mysql_error.log )

The existing password for the user account root has expired. Please set a new password.

New password:   #Enter new password

Re-enter new password: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: n   #y install MySQL password plug-in
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y  #y remove anonymous users
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n  #Allow root remote login

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y  #Remove test database or not
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y  #Refresh permission table
Success.

All done!

5.12 modify the password and give the user permission (according to his own situation)

[root@node7 ~]# mysql -uroot -p111111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL > set password = password ('123456 '); ා modify the root password to 123456. If ERROR 1819 (HY000): Your password does not satisfy the current policy requirements is prompted, the password setting is too simple. If you want to set a simple password like 123456, you can execute it in SQL:
	#mysql> set global validate_password_policy=0;
	#mysql> set global validate_password_length=1;
	#In this way, SET PASSWORD = PASSWORD('123456 ') can be executed again successfully.
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> UPDATE  mysql.user  SET authentication_ String =PASSWORD ('123456') WHERE User='mysql'; ා change the password of MySQL user to 123456
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;   
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql > grant all privileges on *. * to mysql @ "%" identified by '123456' with grant option; ා give mysql users the ability to log in on any machine and have all permissions on all tables
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.07 sec)

MySQL > flush privileges; ා refresh the permission to make the modification take effect immediately
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

--------------------------------------------------------------------------------------
#The following is an introduction to assigning permissions to MySQL
 MySQL > grant permission 1, permission 2 Permission n on database name. Table name to user name @ user address identified by 'connection password';
Authority 1, authority 2 Permission n represents 14 permissions such as select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process and file.
When authority 1, authority 2 Permission n is replaced by all privileges or all, which means that all permissions are given to the user.
When the database name. Table name is replaced by *. *, it means that the user is authorized to operate all tables of all databases on the server.
The user address can be localhost, ip address, machine name and domain name. You can also connect from any address with a '%'.
'connection password' cannot be empty, otherwise creation fails.
For example:
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by '123′;
Assign the user joe from 10.163.225.87 the permission to select, insert, update, delete, create and drop the employee table of the database vtdc, and set the password to 123.
 
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by '123′;
Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables of database vtdc, and set the password to 123.
--------------------------------------------------------------------------------------

5.13 import time zone information to MySQL database

[root@node7 system]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p111111 mysql
#After doing so, time_zone,time_zone_leap_second,time_zone_name,time_zone_transition   ,time_zone_transition_ The type table will sometimes partition data
[root@node7 system]# mysql -uroot -p111111 mysql
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

5.14 viewing MySQL version information

[root@node7 system]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[root@node7 system]# mysqladmin version -uroot -p111111
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin  Ver 8.42 Distrib 5.7.26, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version		5.7.26-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/data/run/mysql.sock
Uptime:			31 min 53 sec

Threads: 1  Questions: 8855  Slow queries: 0  Opens: 214  Flush tables: 1  Open tables: 203  Queries per second avg: 4.628

5.15 if the firewall is open, port 3306 needs to be opened

[root@node7 system]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: active (running) since Tue 2019-12-03 15:22:18 CST; 3s ago
     Docs: man:firewalld(1)
 Main PID: 3343 (firewalld)
   CGroup: /system.slice/firewalld.service
           └─3343 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid

Dec 03 15:22:17 node7 systemd[1]: Starting firewalld - dynamic firewall daemon...
Dec 03 15:22:18 node7 systemd[1]: Started firewalld - dynamic firewall daemon.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'beyond-scope' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: beyond-scope: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'failed-policy' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: failed-policy: INVALID_ICMPTYPE: No supported ICMP type., ignorin...-time.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'reject-route' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: reject-route: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.
Hint: Some lines were ellipsized, use -l to show in full.
#Add firewall rule
[root@node7 system]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
success
#Reload firewall rules
[root@node7 system]# firewall-cmd --reload
success
#Check whether the rule settings are effective
[root@node7 system]# firewall-cmd --zone=public --query-port=3306/tcp
yes
#List all open ports of firewall
[root@node7 system]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens33
  sources: 
  services: ssh dhcpv6-client
  ports: 3306/tcp
  protocols: 
  masquerade: no
  forward-ports: 
  source-ports: 
  icmp-blocks: 
  rich rules: 

5.16 use logrotate to rotate MySQL logs (automatic log backup and cutting)

#For details of logrotate configuration, please refer to: https://www.linuxidc.com/Linux/2019-02/157099.htm
[root@node7 ~]# touch /root/.my.cnf
[root@node7 ~]# vim /root/.my.cnf 
[root@node7 ~]# cat /root/.my.cnf 
[mysqladmin]  
password=111111
user=root
[root@node7 ~]# chmod 600 /root/.my.cnf 
[root@node7 ~]# cp /usr/local/mysql/support-files/mysql-log-rotate /etc/logrotate.d/
[root@node7 ~]# chmod 644 /etc/logrotate.d/mysql-log-rotate 
[root@node7 ~]# vim /etc/logrotate.d/mysql-log-rotate 
[root@node7 ~]# cat /etc/logrotate.d/mysql-log-rotate 
# The log file name and location can be set in
# /etc/my.cnf by setting the "log-error" option
# in either [mysqld] or [mysqld_safe] section as
# follows:
#
# [mysqld]
# log-error=/usr/local/mysql/data/mysqld.log
#
# In case the root user has a password, then you
# have to create a /root/.my.cnf configuration file
# with the following content:
#
# [mysqladmin]
# password = <secret> 
# user= root
#
# where "<secret>" is the password. 
#
# ATTENTION: The /root/.my.cnf file should be readable
# _ONLY_ by root !

/data/log/mysql_*.log {
        # create 600 mysql mysql
        notifempty  #Do not rotate when the log file is empty
        daily  #rotate once a day by default
        rotate 52  #How many log files (how many rotations) are reserved? Four are reserved by default. That is, the number of rotations before deleting the log file is specified. 0 refers to no backup. Here, 52 day log is reserved
        missingok   #Do not display errors if the log file is missing
        compress    #Compress and dump the logs later through gzip
    postrotate   #Instructions executed
	# just if mysqld is really running
	if test -x /usr/local/mysql/bin/mysqladmin && \
	   /usr/local/mysql/bin/mysqladmin ping &>/dev/null
	then
	   /usr/local/mysql/bin/mysqladmin flush-logs
	fi
    endscript
}
[root@node7 ~]# 
[root@node7 ~]# logrotate -fv /etc/logrotate.d/mysql-log-rotate #Force log rotation
reading config file /etc/logrotate.d/mysql-log-rotate
Allocating hash table for state file, size 15360 B

Handling 1 logs

rotating pattern: /data/log/mysql_*.log  forced from command line (52 rotations)
empty log files are not rotated, old logs are removed
considering log /data/log/mysql_error.log
  log needs rotating
considering log /data/log/mysql_slow_query.log
  log needs rotating
rotating log /data/log/mysql_error.log, log->rotateCount is 52
dateext suffix '-20191203'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
renaming /data/log/mysql_error.log.52.gz to /data/log/mysql_error.log.53.gz 
(t -- won't try to dispose of it
.................
renaming /data/log/mysql_slow_query.log to /data/log/mysql_slow_query.log.1
running postrotate script
compressing log with: /bin/gzip
[root@node7 ~]# 
[root@node7 ~]# echo $?
0
#At this time, check the log directory and find that the log has been rotated and compressed
[root@node7 ~]# ls /data/log/
mysql_error.log  mysql_error.log.1.gz  mysql_slow_query.log  mysql_slow_query.log.1.gz

Since then, the general binary package installation of MySQL has completed.

6, Install MySQL using RPM package

6.1 upload the RPM package of MySQL to / usr/local/src directory

[root@node8 local]# cd /usr/local/src/
[root@node8 src]# ls
mysql-community-client-5.7.23-1.el7.x86_64.rpm  mysql-community-libs-5.7.23-1.el7.x86_64.rpm
mysql-community-common-5.7.23-1.el7.x86_64.rpm  mysql-community-libs-compat-5.7.23-1.el7.x86_64.rpm
mysql-community-devel-5.7.23-1.el7.x86_64.rpm   mysql-community-server-5.7.23-1.el7.x86_64.rpm

6.2 install RPM package

[root@node8 src]# rpm -ivh ./*.rpm
warning: ./mysql-community-client-5.7.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
	mariadb-libs is obsoleted by mysql-community-libs-5.7.23-1.el7.x86_64
	mariadb-libs is obsoleted by mysql-community-libs-compat-5.7.23-1.el7.x86_64
#The above error description appears: there is a conflict with the MariaDB LIBS component. Uninstall the MariaDB LIBS related components
[root@node8 src]# rpm -qa | grep mariadb*    #View MariaDB LIBS related components
mariadb-libs-5.5.56-2.el7.x86_64
[root@node8 src]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64 #Uninstall MariaDB LIBS component
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[root@node8 src]# rpm -ivh ./*.rpm
warning: ./mysql-community-client-5.7.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-5.7.23-1.e################################# [ 17%]
   2:mysql-community-libs-5.7.23-1.el7################################# [ 33%]
   3:mysql-community-client-5.7.23-1.e################################# [ 50%]
   4:mysql-community-server-5.7.23-1.e################################# [ 67%]
   5:mysql-community-devel-5.7.23-1.el################################# [ 83%]
   6:mysql-community-libs-compat-5.7.2################################# [100%]
#Now the RPM package is installed

6.3 start MySQL, modify password and empower users

[root@node8 src]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
[root@node8 src]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@node8 src]# grep password /var/log/mysqld.log 
2019-12-03T10:16:32.931929Z 1 [Note] A temporary password is generated for root@localhost: 3yGgt,Eipr%z
[root@node8 src]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD = PASSWORD('123456');#Modify the root password to 123456. If you are prompted with ERROR 1819 (HY000): Your password does not satisfy the current policy requirements, the password setting is too simple. If you want to set a simple password like 123456, you can execute it in SQL:
	#mysql> set global validate_password_policy=0;
	#mysql> set global validate_password_length=1;
	#In this way, SET PASSWORD = PASSWORD('123456 ') can be executed again successfully.
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> UPDATE mysql.user SET authentication_string =PASSWORD('123456') WHERE User='mysql';    #Change the password of MySQL user to 123456
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;   
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;   #Give mysql users the ability to log in on any machine and have all permissions on all tables
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> FLUSH PRIVILEGES ;   #Refresh permissions for changes to take effect immediately
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

--------------------------------------------------------------------------------------
#The following is an introduction to assigning permissions to MySQL
mysql> grant Authority 1,Authority 2,...jurisdiction n on Database name.Table name to user name@User address identified by 'Connection password';
//Authority 1, authority 2 Permission n represents 14 permissions such as select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process and file.
//When authority 1, authority 2 Permission n is replaced by all privileges or all, which means that all permissions are given to the user.
//When the database name. Table name is replaced by *. *, it means that the user is authorized to operate all tables of all databases on the server.
//The user address can be localhost, ip address, machine name and domain name. You can also connect from any address with a '%'.
'Connection password'Cannot be empty, otherwise creation failed.
//For example:
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by '123′;
//Assign the user joe from 10.163.225.87 the permission to select, insert, update, delete, create and drop the employee table of the database vtdc, and set the password to 123.
 
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by '123′;
//Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables of database vtdc, and set the password to 123.
--------------------------------------------------------------------------------------

Since then, the RPM installation of MySQL has been completed. This method automatically generates / etc/my.cnf , check the configuration file to know the log directory and data directory of MySQL.

7, Install MySQL using yum

7.1 download and install the official Yum Repository of MySQL

[root@node9 ~]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
--2019-12-03 23:23:44--  http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm [following]
--2019-12-03 23:23:57--  https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm [following]
--2019-12-03 23:24:00--  https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 184.29.107.217
Connecting to repo.mysql.com (repo.mysql.com)|184.29.107.217|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25548 (25K) [application/x-redhat-package-manager]
Saving to: 'mysql57-community-release-el7-10.noarch.rpm'

100%[================================================================================>] 25,548      21.5KB/s   in 1.2s   

2019-12-03 23:24:03 (21.5 KB/s) - 'mysql57-community-release-el7-10.noarch.rpm' saved [25548/25548]

-c: No such file or directory
No URLs found in -c.
FINISHED --2019-12-03 23:24:03--
Total wall clock time: 19s
Downloaded: 1 files, 25K in 1.2s (21.5 KB/s)

[root@node9 ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm 
Loaded plugins: fastestmirror
Examining mysql57-community-release-el7-10.noarch.rpm: mysql57-community-release-el7-10.noarch
Marking mysql57-community-release-el7-10.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql57-community-release.noarch 0:el7-10 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================================
 Package                           Arch           Version          Repository                                        Size
==========================================================================================================================
Installing:
 mysql57-community-release         noarch         el7-10           /mysql57-community-release-el7-10.noarch          30 k

Transaction Summary
==========================================================================================================================
Install  1 Package

Total size: 30 k
Installed size: 30 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql57-community-release-el7-10.noarch                                                                1/1 
  Verifying  : mysql57-community-release-el7-10.noarch                                                                1/1 

Installed:
  mysql57-community-release.noarch 0:el7-10                                                                               

Complete!

7.2 install MySQL server

#yum installation of MySQL will automatically resolve the dependency. One command is enough, but network access permission is required
[root@node9 ~]# yum -y install mysql-community-server 
.....

Installed:
  mysql-community-libs.x86_64 0:5.7.28-1.el7                mysql-community-libs-compat.x86_64 0:5.7.28-1.el7             
  mysql-community-server.x86_64 0:5.7.28-1.el7             

Dependency Installed:
  mysql-community-client.x86_64 0:5.7.28-1.el7                mysql-community-common.x86_64 0:5.7.28-1.el7               

Replaced:
  mariadb-libs.x86_64 1:5.5.56-2.el7                                                                                      

Complete!

7.3 start MySQL and view the initialization password of MySQL

[root@node9 ~]# systemctl start mysqld
[root@node9 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2019-12-04 10:22:00 CST; 1min 22s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 15965 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 15947 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 15968 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─15968 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.774472Z 0 [Warning] CA certificate ca.pem is self signed.
Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.774677Z 0 [Note] Skipping generation of RSA key pair as ...ctory.
Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.774897Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.774962Z 0 [Note] IPv6 is available.
Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.774980Z 0 [Note]   - '::' resolves to '::';
Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.775003Z 0 [Note] Server socket created on IP: '::'.
Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.791933Z 0 [Note] Event Scheduler: Loaded 0 events
Dec 04 10:22:00 node9 mysqld[15965]: 2019-12-04T02:22:00.792180Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Dec 04 10:22:00 node9 mysqld[15965]: Version: '5.7.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Co... (GPL)
Dec 04 10:22:00 node9 systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.

[root@node9 ~]# grep "password" /var/log/mysqld.log   #View MySQL initialization password
2019-11-05T06:35:28.565529Z 1 [Note] A temporary password is generated for root@localhost: T<&loC3=%t+Q

7.4 modify the root password of MySQL and give the user permission

mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';

MySQL > set password = password ('123456 '); ා modify the root password to 123456. If ERROR 1819 (HY000): Your password does not satisfy the current policy requirements is prompted, the password setting is too simple. If you want to set a simple password like 123456, you can execute it in SQL:
	#mysql> set global validate_password_policy=0;
	#mysql> set global validate_password_length=1;
	#In this way, SET PASSWORD = PASSWORD('123456 ') can be executed again successfully.
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> UPDATE  mysql.user  SET authentication_ String =PASSWORD ('123456') WHERE User='mysql'; ා change the password of MySQL user to 123456
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1

mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;   
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql > grant all privileges on *. * to mysql @ "%" identified by '123456' with grant option; ා give mysql users the ability to log in on any machine and have all permissions on all tables
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.07 sec)

MySQL > flush privileges; ා refresh the permission to make the modification take effect immediately
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

--------------------------------------------------------------------------------------
#The following is an introduction to assigning permissions to MySQL
 MySQL > grant permission 1, permission 2 Permission n on database name. Table name to user name @ user address identified by 'connection password';
Authority 1, authority 2 Permission n represents 14 permissions such as select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process and file.
When authority 1, authority 2 Permission n is replaced by all privileges or all, which means that all permissions are given to the user.
When the database name. Table name is replaced by *. *, it means that the user is authorized to operate all tables of all databases on the server.
The user address can be localhost, ip address, machine name and domain name. You can also connect from any address with a '%'.
'connection password' cannot be empty, otherwise creation fails.
For example:
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by '123′;
Assign the user joe from 10.163.225.87 the permission to select, insert, update, delete, create and drop the employee table of the database vtdc, and set the password to 123.
 
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by '123′;
Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables of database vtdc, and set the password to 123.
--------------------------------------------------------------------------------------

7.5 uninstall Yum Repository

#Since Yum Repository is installed, every yum operation will be updated automatically. You need to uninstall this one
[root@node9 ~]# yum -y remove mysql57-community-release-el7-10.noarch
Loaded plugins: fastestmirror
Resolving Dependencies
--> Running transaction check
---> Package mysql57-community-release.noarch 0:el7-10 will be erased
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================================
 Package                                   Arch                   Version                 Repository                 Size
==========================================================================================================================
Removing:
 mysql57-community-release                 noarch                 el7-10                  installed                  30 k

Transaction Summary
==========================================================================================================================
Remove  1 Package

Installed size: 30 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Erasing    : mysql57-community-release-el7-10.noarch                                                                1/1 
  Verifying  : mysql57-community-release-el7-10.noarch                                                                1/1 

Removed:
  mysql57-community-release.noarch 0:el7-10                                                                 
Complete!

Since then, install MySQL.

8, Source compilation and installation of MySQL

To compile and install MySQL from source code, you can view my previous blog: https://www.cnblogs.com/renshengdezheli/p/11913248.html , no more details here.

9, References

https://www.cnblogs.com/luohanguo/p/9045391.html

Tags: MySQL RPM Database yum

Posted on Thu, 28 May 2020 05:00:37 -0400 by Jack Sparrow