MYSQL

MYSQL installation steps

CentOS 7

# CentOS 7
yum remove akonadi-mysql.x86_64

#MySQL depends on libaio, so first install libaio
yum search libaio
yum install libaio

#Check if MySQL is installed
yum list installed | grep mysql

#Download MySQL Yum Repository
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

#Add MySQL Yum Repository
yum localinstall mysql-community-release-el7-5.noarch.rpm

#Verify that it was added successfully
yum repolist enabled | grep "mysql.*-community.*"

######Select the MySQL version you want to enable

#View MySQL version, execute
yum repolist all | grep mysql
#You can see 5.5, 5.7 is disabled by default, because the latest stable version is 5.6

#Some versions can be started with statements like
yum-config-manager --disable mysql56-community
yum-config-manager --enable mysql57-community-dmr

#View the currently enabled MySQL version
yum repolist enabled | grep mysql

#Install MySQL via Yum
yum install mysql-community-server

#View the MySQL installation directory
whereis mysql

Start and stop MySQL Server

#start MySQL Server
systemctl start  mysqld

#check MySQL Server status
systemctl status  mysqld

#stop MySQL Server, If needed
systemctl stop mysqld

Self-starting

#Check if MySQL service starts automatically
systemctl is-enabled mysql.service;echo $?

#If it is enabled, it means it is on automatically, if not, execute
chkconfig --levels 235 mysqld on

Firewall settings (firewall-cmd)

To access MySQL remotely, open the default port 3306.

#Turn on the firewall, it will open successfully without any prompts
systemctl start firewalld

#Open the corresponding port
firewall-cmd --permanent --zone=public --add-port=3306/tcp
firewall-cmd --permanent --zone=public --add-port=3306/udp

#Make the latest firewall settings rules take effect
firewall-cmd --reload

Ubuntu 18.04LTS

#install the default MySQL package
apt install mysql-server
apt install libmysqlclient-dev
#The above command will install the MySQL server and all required dependencies. Once the installation is completed, the MySQL service will start automatically.

#verify whether the MySQL service is running
service mysql status

vi /etc/mysql/mysql.conf.d/mysqld.cnf
#Look for the line below and comment it out using the # character:
# bind-address = 127.0.0.1
#Save the file and exit, afterwards restart the mysql service like so:
service mysql restart

MySQL is installed as a systemd service. It means that you can manage it using standard systemd commands like systemctl or service on Ubuntu.

#To start the MySQL service
service mysql start

#To check the current status of the MySQL service
service mysql status

#Stop the service anytime by typing:
service mysql stop

#If you want to restart the service
service mysql restart

#To reload the MySQL service
service mysql reload

#By default, MySQL is configured to start automatically when the server starts. You can disable the automatic startup by typing:
systemctl disable mysql

#If you want to enable the automatic startup again, just type:
systemctl enable mysql

MySQL Basic Settings

MySQL security settings

During the installation, the installer will not prompt you to choose a password for the root user or confirm any other configuration changes. Since this leaves the MySQL installation insecure, you need to manually run the included security script to set a password and change other security defaults.

#Execute in terminal
mysql_secure_installation

First, enter MySQL the root user password (it is empty for initial installation). Next, for security, MySQL will prompt you to reset the root password, remove other user accounts, disable root remote login, remove the test database, and reload the privilege table , You just enter y to continue execution. At this point, the entire MySQL installation is complete.

Create specified user

#Execute in terminal
mysql -u root -p
#Then enter the password you set previously

#Create a normal user 'data' with a password of 'xia17pxn'
CREATE USER 'data'@'%' IDENTIFIED BY 'xia17pxn';

#Give this user remote access permissions of SELECT, INSERT, UPDATE, DELETE. This account is generally used to provide system access for implementation.
GRANT SELECT,INSERT,UPDATE,DELETE  ON *.* TO 'data'@'%';

#Create an administrator user 'admin' with password 'xia17pxn'
CREATE USER 'admin'@'%' IDENTIFIED BY 'xia17pxn';

#Grant this user all remote access permissions. This user is mainly used to manage the entire database, backup, restore and other operations.
GRANT ALL  ON *.* TO 'admin'@'%';

#Make the authorization effective immediately
flush privileges;

#Exit
exit

Basic database operation commands

mysql  -h 127.0.0.1 -u admin -p
mysql  -h 127.0.0.1 -u data -p
#Here 127.0.0.1 means to view the machine, if remotely query from other computers, change to the IP of the corresponding computer, and then enter the password