CentOS 7 installation and configuration MySQL
MySQL version introduction
Quick install MySQL
Uninstall MariaDB
Query the MariaDB of CentOS 7:
rpm -qa | grep mariadb
Uninstall MariaDB:
rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
Uninstall old version
Query if MySQL is installed:
rpm -qa | grep MysqL
If a lower version of MySQL is installed:
Stop MySQL service:
systemctl stop MysqLd.service
Turn off MySQL service and start up:
systemctl disable MysqLd.service
Uninstall the lower version of MySQL:
rpm -e --nodeps MysqL-community-libs-5.7.1-1.el7.x86_64
rpm -e --nodeps MysqL-community-common-5.7.1-1.el7.x86_64
rpm -e --nodeps MysqL-community-server-5.7.1-1.el7.x86_64
rpm -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 of:
Download MySQL Yum Repository
see:
Corresponding RPM package name:
And MD5 values:
The corresponding Yum source download address is:
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
The MD5 value is consistent with that provided by the official and is safe. Add this 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
To install MySQL 5.7 community server:
yum -y install MysqL-community-server
To view the installed version of MySQL:
MysqLd -V
Start MySQL service:
systemctl start MysqLd.service
Set MySQL service startup:
systemctl enable MysqLd.service
To view MySQL service status:
systemctl status MysqLd.service
Basic configuration
Stop MySQL service:
systemctl stop MysqLd.service
Back up the MySQL master profile:
mv /etc/my.cnf /etc/my.cnf.bak
Create and modify a new MySQL master profile:
vi /etc/my.cnf
Insert the following configuration:
# MysqLd config
[MysqLd]
port=3306
datadir=/var/lib/MysqL
socket=/var/lib/MysqL/MysqL.sock
symbolic-links=0
log-error=/var/log/MysqLd.log
pid-file=/var/run/MysqLd/MysqLd.pid
character-set-server=utf8
# MysqL config
[MysqL]
socket=/var/lib/MysqL/MysqL.sock
default-character-set=utf8
# client config
[client]
socket=/var/lib/MysqL/MysqL.sock
default-character-set=utf8
Save, exit and start MySQL service:
systemctl start MysqLd.service
reset password
When MySQL is started for the first time, a password will be randomly generated for the root user in the log file:
grep 'temporary password' /var/log/MysqLd.log
The last 0an6yuml- (&b is the random password. Use this random password to log in to the MySQL console:
MysqL -u root -p -h localhost
Set a new password for the root user * * (< password > is the new password) * *:
ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';
If:
It indicates that the password strength is not enough, and it is necessary to replace the password with a higher strength * * (more than 16 digits are recommended, including symbols, upper and lower case letters and number combinations) * *. If it is set successfully, it will be displayed as follows:
Make the configuration effective immediately:
FLUSH PRIVILEGES;
Exit the MySQL console:
EXIT;
Modify MySQL database path
Stop MySQL service:
systemctl stop MysqLd.service
Create destination path:
mkdir -p /home/sql
Copy data files and file permissions:
cp -a /var/lib/MysqL /home/sql
To modify the MySQL master profile:
vi /etc/my.cnf
To all:
Replace with:
datadir=/home/sql/MysqL
Put all:
Replace with:
socket=/home/sql/MysqL/MysqL.sock
Save, exit and start MySQL service:
systemctl start MysqLd.service
Managing MySQL services using systemctl
To view firewall service status:
systemctl status MysqLd.service
Open, close and restart MySQL service:
# 打开
systemctl start MysqLd.service
# 关闭
systemctl stop MysqLd.service
# 重启
systemctl restart MysqLd.service
Open / close MySQL service startup:
# 查看 MysqL 服务是否开机启动
systemctl is-enabled MysqLd.service
# 打开 MysqL 服务开机启动
systemctl enable MysqLd.service
# 关闭 MysqL 服务开机启动
systemctl disable MysqLd.service
Manage MySQL using MySQL monitor
Login management
User login:
MysqL -u <user> -p -h localhost
User logout:
EXIT;
user management
Create user:
CREATE USER '<user>'@'<host>' IDENTIFIED BY '<password>';
The parameter < host > can be set to:
Modify the password of the currently logged in user:
SET PASSWORD = PASSWORD('<password>');
Modify the specified user password:
SET PASSWORD FOR '<user>'@'<host>' = PASSWORD('<password>');
Delete user:
DROP USER '<user>'@'<host>';
Database management
To view a list of databases:
SHOW DATABASES;
Create database:
CREATE DATABASE <database>;
Delete database:
DROP DATABASE <database>;
Authority management
Authorize the specified user to access the specified data table of the specified database from the specified domain:
GRANT <privilege> ON <database>.<table> TO '<user>'@'<host>';
The parameter < privilege > can be set to:
The parameter < Database > available * indicates all databases.
Parameter < Table > available * indicates all tables.
Revoke the permission of the specified user from the specified domain to the specified table of the specified data library:
REVOKE <privilege> ON <database>.<table> FROM '<user>'@'<host>';
Make the configuration effective immediately:
FLUSH PRIVILEGES;
MySQL common tools
Workbench
Workbench is an official MySQL management tool provided by mysql. It is open source and free, and supports the whole platform.
Download address: Download MySQL workbench