CentOS 7 Installation Configuration MySQL

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications.

MySQL Version Introduction

• MySQL Community Server Community Edition, open source is free, but does not provide official technical support.
• MySQL Enterprise Edition is available for a fee and can be tested for 30 days.
• MySQL Cluster cluster edition, open source free, can encapsulate several MySQL servers into one server.
• MySQL Cluster CGE Premium Cluster Edition is subject to a fee.
• MySQL Workbench Community Edition (MySQL Workbench OSS) Community Edition MySQL database modeling tool, open source free.
• MySQL Workbench standard Edition (MySQL Workbench SE) Commercial MySQL Database modeling tool is available for a fee.

Quick Install MySQL Uninstall Mariadb

Query CentOS 7 for MARIADB:

rpm -qa | grep mariadb

Mariadb-libs-5.5.56-2.el7.x86_64

Uninstall MARIADB:

rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

Uninstalling older versions

Query whether MySQL is installed:

rpm -qa | grep mysql

If you have installed a lower version of MySQL:

Mysql-community-libs-5.7.1-1.el7.x86_64
Mysql-community-common-5.7.1-1.el7.x86_64
Mysql-community-server-5.7.1-1.el7.x86_64
Mysql-community-client-5.7.1-1.el7.x86_64

To stop the MySQL service:

systemctl stop mysqld.service

Turn off the MySQL service boot:

systemctl disable mysqld.service

Uninstall the lower version of MySQL:

rpm -e --nodeps mysql-community-libs-5.7.1-1.el7.x86_64rpm -e --nodeps mysql-community-common-5.7.1-1.el7.x86_64rpm -e --nodeps mysql-community-server-5.7.1-1.el7.x86_64rpm -e --nodeps mysql-community-client-5.7.1-1.el7.x86_64

Add Yum Source

Go to the MySQL website to view the latest version:

Download MySQL Yum Repository

View:

Red Hat Enterprise linux 7/oracle Linux 7 (Architecture Independent), RPM package

The corresponding RPM package name:

mysql57-community-release-el7-11.noarch.rpm

and MD5 values:

C070b754ce2de9f714ab4db4736c7e05

Then the corresponding Yum source is:

http://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm

Download the Yum source to the current directory:

wget http://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm

Calculate MD5:

md5sum mysql57-community-release-el7-11.noarch.rpm

C070b754ce2de9f714ab4db4736c7e05 mysql57-community-release-el7-11.noarch.rpm

The MD5 value is consistent with the official offer and is safe to add that source:

yum -y localinstall mysql57-community-release-el7-11.noarch.rpm

Install new version

To view the currently installable MySQL packages:

yum repolist enabled | grep mysql

mysql-connectors-community/x86_64 MySQL Connectors Community 42
mysql-tools-community/x86_64 MySQL Tools Community 51
mysql57-community/x86_64 MySQL 5.7 Community Server 227

Install MySQL 5.7 Community Server:

yum -y install mysql-community-server

To view the installed MySQL version:

mysqld -V

Mysqld Ver 5.7.20 for Linux on x86_64 (MySQL Community Server (GPL))

To start the MySQL service:

systemctl start mysqld.service

To set up the MySQL service boot:

systemctl enable mysqld.service

To view the status of MySQL service:

systemctl status mysqld.service

Mysqld.service-mysql Server
? Loaded:loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset:disabled)
? Active:active (running) since Mon 2017-10-23 20:53:59 CST; 48s ago
? Docs:man:mysqld (8)
? Http://dev.mysql.com/doc/refman/en/using-systemd.html
Main pid:25918 (mysqld)
? CGroup:/system.slice/mysqld.service
? └─25918/usr/sbin/mysqld--daemonize--pid-file=/var/run/mysqld/mysqld.pid

OCT 20:53:52 Web systemd[1]: Starting MySQL Server ...
OCT 20:53:59 web systemd[1]: Started MySQL Server.

Basic Configuration

To stop the MySQL service:

systemctl stop mysqld.service

To back up the MySQL master configuration file:

mv /etc/my.cnf /etc/my.cnf.bak

Create and modify a new MySQL master configuration file:

vi /etc/my.cnf

Insert the following configuration:

# mysqld config[mysqld]port=3306datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socksymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidcharacter-set-server=utf8# mysql config[mysql]socket=/var/lib/mysql/mysql.sockdefault-character-set=utf8# client config[client]socket=/var/lib/mysql/mysql.sockdefault-character-set=utf8

Save exit to start the MySQL service:

systemctl start mysqld.service

Reset Password

When you start MySQL for the first time, a password is randomly generated for the root user in the log file:

grep ‘temporary password‘ /var/log/mysqld.log

2017-10-23t12:53:54.742077z 1 [Note] A temporary password is generated for [email protected]: 0an6yuml-(&b

The last 0an6yuMl-(&b is a random password, use this random password to log in to the MySQL console:

mysql -u root -p -h localhost

Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 8
Server version:5.7.20

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

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

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

To set a new password for the root user * * ( <password> for new password) * *:

ALTER USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘<password>‘;

If it appears:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Note that password strength is not enough, need to replace stronger password * * (more than 16, including symbols, uppercase and lowercase letters, number combination) * *, the success of the settings are shown as follows:

Query OK, 0 rows Affected (0.00 sec)

Make the configuration effective immediately:

FLUSH PRIVILEGES;

Query OK, 0 rows Affected (0.00 sec)

Exit the MySQL console:

EXIT;

Modifying the MySQL database path

To stop the MySQL service:

systemctl stop mysqld.service

To create a destination path:

mkdir -p /home/sql

Copy data files and file permissions:

cp -a /var/lib/mysql /home/sql

To modify the MySQL master configuration file:

vi /etc/my.cnf

Put to all of:

Datadir=/var/lib/mysql

Replace with:

datadir=/home/sql/mysql

Put all the:

Socket=/var/lib/mysql/mysql.sock

Replace with:

socket=/home/sql/mysql/mysql.sock

Save exit to start the MySQL service:

systemctl start mysqld.service

Managing MySQL Services with Systemctl

To view the Firewall service status:

systemctl status mysqld.service

To turn on, turn off, and restart the MySQL service:

# 打开systemctl start mysqld.service# 关闭systemctl stop mysqld.service# 重启systemctl restart mysqld.service

Turn on and off the MySQL service boot:

# 查看 MySQL 服务是否开机启动systemctl is-enabled mysqld.service# 打开 MySQL 服务开机启动systemctl enable mysqld.service# 关闭 MySQL 服务开机启动systemctl disable mysqld.service

Managing MySQL login management with MySQL Monitor

User login:

mysql -u <user> -p -h localhost

User Log out:

EXIT;

User Management

To create a user:

CREATE USER ‘<user>‘@‘

Parameters can be set to:

• localhost: Users can only log on in the local computer;
• %: Wildcard, the user can log on any host;
• <IP>: The user can only log on from the host specified IP (the% wildcard is available in IP, for example: 192.168.100.%).

To modify the user password for the current login:

SET PASSWORD = PASSWORD(‘<password>‘);

To modify the specified user password:

SET PASSWORD FOR ‘<user>‘@‘

To delete a user:

DROP USER ‘<user>‘@‘

Database management

To view a list of databases:

SHOW DATABASES;

To create a database:

CREATE DATABASE <database>;

To delete a database:

DROP DATABASE <database>;

Rights Management

Authorizes the specified user to access the specified data table for the specified database from the specified domain:

GRANT <privilege> ON <database>.<table> TO ‘<user>‘@‘

Parameters <privilege> can be set to:

• All: Indicates all permissions;
• SELECT / INSERT / UPDATEAnd so on (multiple permissions are separated by the specified permissions , ).

Parameters <database> can be used to * represent all databases.

Parameters <table> can be used to * represent all tables.

Revokes the specified user from the specified domain to specify the table's permissions on the library for the specified data:

REVOKE <privilege> ON <database>.<table> FROM ‘<user>‘@‘

Make the configuration effective immediately:

FLUSH PRIVILEGES;

MySQL Common Tools Workbench

MySQL Workbench provides DBAs and developers an integrated tools environment for:

• Database Design & Modeling
• SQL Development
• Database Administration
• Database Migration

The Community (OSS) Edition is available from this page under the GPL.

Workbench is MySQL's official MySQL management tool, open source free, support full platform.

: Download MySQL Workbench

Reference documents

• Installing MySQL on Linux Using the MySQL Yum Repository

CentOS 7 Installation Configuration MySQL