Install MySQL (version 5.7) on CentOS7.5 and master MySQL basic operation.

Summary of today's mainstream databases:

    在数据库技术日益发展的今天，主流数据库代表着成熟的数据库技术。了解常用数据库，就能知道数据库技术发展的程度，以及未来的大体方向。    数据库分关系型数据库和非关系型数据库。    关系型数据库：基于关系模型的数据库系统，是关系模型应用到数据库领域的实例化。它的基本概念来自于关系模型。市场上主流商业关系型数据库有SQL Server、Oracle、IBM DB2、MySQL等    非关系型数据库：也称作NoSQL(Not Only SQL),存储数据不以关系模型为依据，不需要固定的表格式。非关系型数据库作为关系型数据库的一个补充，在日益快速发展的网站时代，发挥着高效率与高性能。常用的非关系型数据库如Memcached、Redis、MongoDB、HBase等。    非关系型数据库的优点：1.数据库高并发读写的需求。2.对海量数据高效率存储于访问。3.数据库的高扩展性于高可用性的需求。

MySQL Database introduction:

MySQL是一个关系型数据库管理系统，现已被Oracle公司收购。MySQL运行与Linux操作系统之上，Apache和Nginx作为web服务器，MySQL作为后台数据库，PHP/Perl/Python作为脚本解释器。这四款软件都是免费或开源的，也就是说企业可以不花一分钱（除人工外）就能建立起一个稳定、高速的网站系统，业内称为“LNMP”组合。因此，其以体积小、速度快、开源等特点，霸占了中小型网站相当大的市场。

MySQL compilation installation process: Installation Preparation:

1 • Source Bundle: mysql-5.7.17.tar.gz
2 • Source Bundle: boost_1_59_0.tar.gz
3 • Source installation MySQL requires CMake to compile the installation.
4 · The MySQL 5.7 version needs to be supported by the Boost library, so the boost library must be downloaded, and the source will need to specify the location of the boost library when compiling the installation.

Next install the experiment: first Yum installs the Environment pack:

Yum-y install \
GCC \
gcc-c++ \
ncurses \
Ncurses-devel \
Bison \
CMake
Installation Complete:

In order to strengthen the database permissions control, we recommend the use of dedicated MySQL running users, do not need to login system, and do not need to create home directory:

Useradd-s/sbin/nologin MySQL

Use the WINSCP tool to drag the mysql-5.7.17.tar.gz, boost_1_59_0.tar.gz into the CentOS7, and then unzip to/OPT:

Tar zxvf mysql-5.7.17.tar.gz-c/opt/

Unzip the boost_1_59_0.tar.gz to/usr/local and rename it to boost, and for the next cmake it is convenient to specify the boost library seat:

Tar zxvf boost_1_59_0.tar.gz-c/usr/local/
cd/usr/local/
MV Boost_1_59_0 boost

Switch to/opt/mysql-5.7.17/under Cmake start configuration:

CMake \
-dcmake_install_prefix=/usr/local/mysql \ Specify what seat the database is installed in
-dmysql_unix_addr=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/ETC \ Specify initialization parameter file directory
-dsystemd_pid_dir=/usr/local/mysql \ Specify PID file location
-DDEFAULT_CHARSET=UTF8 \ Specifies the default character set encoding
-DDEFAULT_COLLATION=UTF8_GENERAL_CI \ Specify character set proofing rules
-dwith_innobase_storage_engine=1 \
-dwith_archive_storage_engine=1 \
-dwith_blackhole_storage_engine=1 \
-dwith_perfschema_storage_engine=1 \
-dmysql_datadir=/usr/local/mysql/data \ Specify the storage seat of the MySQL database
-dwith_boost=/usr/local/boost \ Specify the BOOST library in what seat, 5.7 version must add this parameter.
-dwith_systemd=1

Error NOTE:

If there is an error in the process of CMake, when the error is resolved, you need to remove the CMakeCache.txt file from the source directory, and then cmake again, otherwise the errors will remain.

Next, make && make installs, and other operational configurations are required after the installation is complete. After the MySQL installation is complete, adjust the database directory permissions and create the configuration file, adding the following:

Chown-r mysql.mysql/usr/local/mysql/

Vi/etc/my.cnf

[Client]
Port = 3306
Default-character-set=utf8
Socket =/usr/local/mysql/mysql.sock

[MySQL]
Port = 3306
Default-character-set=utf8
Socket =/usr/local/mysql/mysql.sock

[Mysqld]
user = MySQL
Basedir =/usr/local/mysql
DataDir =/usr/local/mysql/data
Port = 3306
Character_set_server=utf8
Pid-file =/usr/local/mysql/mysqld.pid
Socket =/usr/local/mysql/mysql.sock
Server-id = 1

Sql_mode=no_engine_substitution,strict_trans_tables,no_auto_create_user,no_auto_value_on_zero,no_zero_in_date, No_zero_date,error_for_division_by_zero,pipes_as_concat,ansi_quotes

To modify the administrative user for profile/etc/my.cnf:

Chown mysql:mysql/etc/my.cnf

To facilitate the use of MySQL commands in any directory, you need to set the environment variable in/etc/profile:

Echo ' path=/usr/local/mysql/bin:/usr/local/mysql/lib: $PATH ' >>/etc/profile
echo ' Export PATH ' >>/etc/profile
Source/etc/profile

Initialize the database:

cd/usr/local/mysql/

BIN/MYSQLD \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

Add system Management, manage through SYSTEMCTL in the CentOS7 operating system, and start the MySQL viewing port:

cp/usr/local/mysql/usr/lib/systemd/system/mysqld.service/usr/lib/systemd/system/
Systemctl Daemon-reload
Systemctl Start mysqld
NETSTAT-ANPT | grep 3306

Next you can log in to MySQL.

Mysql-u root-p Login

Next I'll look at some of the basic operations of the MySQL database as follows:

show databases; See which databases are available
Show global variables like ' port '; The first is to view the port number of the MySQL database
Create Database School; Create a database
Use school; Enter the database
CREATE table info (ID int not NULL PRIMARY key Auto_increment,name char (TEN) not Null,score decimal (5,2), hobby int (2)); Write table structure
DESC info; View table Structure
Show tables;//view the table in MySchool
Selectfrom info;//view data in a table
Insert into info (id,name,score) VALUES (5, ' Tianqi ', 55); Insert data Column Contents
Insert into info (id,name,score) VALUES (5, ' Tianqi ', null); Insert data Column Contents
Selectform info WHERE id = 6; Where condition
Update info set score=75 where id=6; Update information (set to find columns, where to determine good rows)
Delete from info where name= ' test '; Delete information (delete entire line)
SelectFrom info where 1=1 ORDER by score ASC;//(to score score) sort (ascending ASC, descending desc)
SelectFrom info inner join Hob where info.hobby=hob.id; Multi-Table Query
Select Info.name,info.score,hob.hobname from info inner join Hob where info.hobby=hob.id; Query Development Columns
Select I.name,i.score,h.hobname from info i inner join hob H where i.hobby=h.id; Alias query
CREATE TABLE Infos Select I.name,i.score,h.hobname from info i inner join hob H where i.hobby=h.id; Generate a new table for the results of the association lookup
Aggregation functions:
Statistics count () For example: SELECT COUNT (*) from infos; Total number of Trainees select COUNT (1) from Infos;
Average AVG () For example: Select AVG (score) from Infos; Average of Scores
Deleting a database For example: Drop DB School;
Delete Table for example: drop table hobby;

Setting the Telnet permission locally on MySQL allows the client to log on:

Grant all privileges in . to ' root ' @ '% ' identified by ' abc123 ' with GRANT option;

Then open a Windows side to log in:

Mysql-h 192.168.100.70-uroot-p//telnet, log on at the client.

Write here temporarily, follow up to continue to add!

Install MySQL (version 5.7) on CentOS7.5 and master MySQL basic operation.