Database Introduction and MySQL Version 5.7 Compile Installation (Actual!)

Keywords: MySQL Database Oracle Attribute

Basic concepts of databases

Data:

Symbolic records describing things are called data.
Include numbers, text, graphics, images, sounds, file records, etc.
Store in a uniform format as a "record".

Table:

A table is formed by grouping different records together
Is used to store specific data

Database:

A database is a collection of tables and a repository for storing data
Relevant data stored in an organized manner

History of Database System

First Generation Database

Since the 1960s, the first generation of database systems has been introduced.They are database systems for hierarchical and mesh models, providing strong support for unified data management and sharing

Second Generation Database

In the early 1970s, the second generation of databases - relational databases began to appear
In the early 1980s, IBM's relational database system DB2 came out. As the relational database of the second generation database system, it gradually replaced the hierarchical and network model database, became the dominant database, and became the mainstream of the industry.So far, relational database systems still dominate database applications

Third Generation Database

Since the 1980s, a variety of new database systems have been appearing, such as engineering databases, multimedia databases, graphics databases, intelligent databases, distributed databases and object-oriented databases, especially object-oriented database systems. Because of their high practicability and wide adaptability, they are favored by people.
In the late 1990s, a variety of database systems jointly supported the application.Of course, relational databases still dominate in business applications, but some new elements have been added to the mainstream business database systems.For example, Oracle supports a relational object database model

Introduction to mainstream databases

SQL Server (Microsoft Products)

Windows Operating System Oriented
Simple, easy to use, almost unused

Oracle (Oracle Products)

For all mainstream platforms
Safe, perfect and complex operation

DB2 (IBM Products)

For all mainstream platforms
Large, safe and perfect

MySQL (Oracle Acquisition)

Free, open source, small size

Sybase (relational database, Sybase products, USA)

Large and perfect

Relational Database

Relational database system is a database system based on relational model. Its basic concept comes from relational model
Relational models are based on the theory of relational algebra, and data structures are represented directly by simple Entity-Relation (E-R) diagrams using easy-to-understand two-dimensional data tables.
The E-R diagram contains three elements: entity (data object), relationship and attribute.
A collection of relationships between all entities and entities forms a relational database
The storage structure of relational databases is a two-dimensional table, and the data that reflects things and their relationships are stored in a tabular form.
In each two-dimensional table, each row is called a record to describe information about an object, and each column is called a field to describe an attribute of an object.

entity

Also known as an instance, it corresponds to "events" or things in the real world that can be distinguished from other objects, such as bank customers, bank accounts, etc.

attribute

An entity has a certain attribute, and an entity can have multiple attributes.For example, each entity in the Bank Customer entity set has attributes such as name, address, phone, and so on

contact

The corresponding relationship between sets of entities is called a relationship, also known as a relationship.For example, there is a "savings" relationship between bank customers and bank accounts

Relational Database

Oracle ，MySQL
SQLServer,Sybase
Informix,access
DB2,FoxPRO

Examples of relational database applications

12306 User Information System
Taobao Account Information System, Alipay Account System Mobile, Telecom, Unicom Mobile Number Information System, Billing System Bank User Account System
Web Site User Information System

Introduction to non-relational databases

1. Non-relational databases, also known as NoSQL(Not Only SQL), do not store data based on a relational model and do not require a fixed table format.

2. Non-relational databases, as a supplement to relational databases, play an efficient and high-performance role in the era of rapidly developing websites.

3. Advantages of non-relational databases:

High concurrent read and write requirements for databases
Efficient storage and access to large amounts of data
Requirements for high scalability and high availability of databases
4. Key-value method, which stores, deletes and alters data based on keys

5. Column-oriented, storing related data in the column family

6. Documentation. A database consists of a series of data items, each of which has a name and corresponding value

7. Graphic method, entity is vertex, relationship is edge, data is saved as a graphic

8. Memcached is an open source, high performance cache system with distributed memory objects that stores data as a key-value

Caching data to reduce database pressure and speed up access
Accelerate Dynamic Web Applications
Cached content is stored in memory

9. redis is also a key-value way to store data, which is also stored in memory but is periodically written to disk

10. Relative to Memcached, it has the following characteristics:

Supports memory caching
Support persistence
More data types
Support Clustering, Distributed
Support Queue

11. Examples of redis applications

Database Front End Cache
session sharing
When more data types than key/value need to be cached
When cached data needs to be persisted for a long time

Introduction to MySQL database

MySQL is a popular open source relational database

Products under Oracle

Compliance with GPL protocol, free use and modification

Characteristic:

Excellent performance and stable service
Open source, no copyright restrictions, low cost
Multi-threaded, multi-user
Based on C/S (client/server) architecture.
Safe and reliable

MySQL Business and Community Editions

MySQL Business Edition is developed and maintained by MySQL AB Company and requires a fee to use

MySQL Community Edition is developed and maintained by MySQL developers and enthusiasts scattered around the world and is free to use

The difference between the two:

Business edition organization management and testing link more stringent, better stability
Commercial version does not comply with GPL
Commercial editions are available for 7*24 hours of service, such as troubleshooting and patching

MySQL Product Campaign

First Camp: 5.0-5.1 Camp, a continuation of early products
Second Campaign: 5.4-5.7 Campaign, better integration of MySQL AB company, community, third-party company's storage engine to improve performance
Camp 3: Camp 6.0-7.1, the MySQL Cluster version, was developed to meet the database needs of a new era of clustering

MySQL download URL: http://www.dev.mysql.com/downloads

MySQL 5.7 Compile Installation

1. Install the environment packages required to compile mysql

[root@localhost ~]# yum -y install \
> ncurses \
> ncurses-devel \
> bison \
> cmake \
> gcc \
> gcc-c++
........//Omit installation process

2. Add a mysql user

[root@localhost ~]# useradd -s /sbin/nologin  mysql
[root@localhost ~]#

3. Unzip the mysql source package into the'/opt/'directory

[root@localhost ~]# mkdir /mnt/tools
[root@localhost ~]# mount.cifs //192.168.100.50/tools /mnt/tools/
Password for root@//192.168.100.50/tools:  
[root@localhost ~]# cd /mnt/tools/MySQL/
[root@localhost MySQL]# ls
boost_1_59_0.tar.gz  mysql-5.7.17.tar.gz
[root@localhost MySQL]# tar zxvf mysql-5.7.17.tar.gz -C /opt/
............//Omit decompression
[root@localhost MySQL]#tar zxvf boost_1_59_0.tar.gz -C /usr/local/
............//Omit decompression
[root@localhost MySQL]#

4. Rename the "boost_1_59_0" directory to "boost"

[root@localhost MySQL]# cd /usr/local/
[root@localhost local]# mv boost_1_59_0/ boost
[root@localhost local]# ls
bin  boost  etc  games  include  lib  lib64  libexec  sbin  share  src
[root@localhost local]#

5. Configure mysql service

[root@localhost local]# cd /opt/mysql-5.7.17/
[root@localhost mysql-5.7.17]# cmake \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \     //Installation Path
> -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \    //Define sock file connection database file
> -DSYSCONFDIR=/etc \    //Profile directory
> -DSYSTEMD_PID_DIR=/usr/local/mysql \   //PID File Directory
> -DDEFAULT_CHARSET=utf8 \    //Specify character set, utf8 supports Chinese characters
> -DDEFAULT_COLLATION=utf8_general_ci \    Specify Character Set Default
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \    Storage Engine
> -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
> -DMYSQL_DATADIR=/usr/local/mysql/data \     //Database Data File Directory
> -DWITH_BOOST=/usr/local/boost \     //Underlying runtime
> -DWITH_SYSTEMD=1      //Master-Slave Parameters

6. Compile and install MySQL services

[root@localhost mysql-5.7.17]#make && make install
...........//Omit compilation process
[root@localhost mysql-5.7.17]#

7. Change the ownership and group of the database catalog

[root@localhost mysql-5.7.17]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost mysql-5.7.17]#

8. Modify the configuration file

[root@localhost mysql-5.7.17]# vim /etc/my.cnf
[client]                            //Client
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]                           //Client
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]                         //The server
user = mysql                  //user
basedir = /usr/local/mysql      //Set the installation directory for mysql
datadir = /usr/local/mysql/data    //Set up the storage directory for the data in the mysql database
port = 3306                    //Set up port 3306
character_set_server=utf8           //Chinese Character Set
pid-file = /usr/local/mysql/mysqld.pid     //pid file path
socket = /usr/local/mysql/mysql.sock     //sock file path
server-id = 1                                     //Master-Slave Parameters

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
//Support module
[root@localhost mysql-5.7.17]# Chown mysql:mysql/etc/my.cnf //Modify the file to belong to the primary group
[root@localhost mysql-5.7.17]#

9. Add mysql related commands to the local environment configuration

[root@localhost mysql-5.7.17]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
//Write MySQL to local environment configuration
[root@localhost mysql-5.7.17]#  Echo'export PATH'>>/etc/profile//Set global environment configuration
[root@localhost mysql-5.7.17]#  Source/etc/profile//reload configuration file
[root@localhost mysql-5.7.17]#

10. Initialize the database

[root@localhost mysql-5.7.17]# cd /usr/local/mysql/
[root@localhost mysql]# bin/mysqld \
> --initialize-insecure \    //Initialization
> --user=mysql \   //user
> --basedir=/usr/local/mysql \    //Installation Directory
> --datadir=/usr/local/mysql/data   //Database Data File Directory

11. Copy MySQL service configuration file to/usr/lib/systemd/system/for easy systemctl management

[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /lib/systemd/system/
//copy
[root@localhost mysql]# systemctl daemon-reload //reload
[root@localhost mysql]# systemctl start mysqld.service //start service
[root@localhost mysql]# Netstat-ntap | grep 3306 //view tcp3306 port
tcp6       0      0 :::3306                 :::*                    LISTEN      78684/mysqld        
[root@localhost mysql]# systemctl enable mysqld.service //Set boot-up self-start
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@localhost mysql]#

12. Configure MySQL password

[root@localhost mysql]# mysqladmin -u root -p password
Enter password: 
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost mysql]#

13. Attempt to log on to MySQL database

[root@localhost mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 Source distribution

Copyright (c) 2000, 2016, 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> grant all privileges on *.* to 'root'@'%' identified by 'abc123' with grant option;
//Title enables all terminals to log on remotely
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit   //Sign out
Bye
[root@localhost mysql]# 

[root@localhost mysql]# systemctl stop firewalld.service //close firewall
[root@localhost mysql]# setenforce 0 //Turn off enhanced security features
[root@localhost mysql]#

14. Remote login with Navicat tools

15. Enter the database to view all databases

[root@localhost mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 Source distribution

Copyright (c) 2000, 2016, 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> show databases;   //view the database
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

Posted by fri3ndly on Thu, 21 Nov 2019 18:20:54 -0800

Programmer Group

Database Introduction and MySQL Version 5.7 Compile Installation (Actual!)

Basic concepts of databases

Data:

Table:

Database:

History of Database System

First Generation Database

Second Generation Database

Third Generation Database

Introduction to mainstream databases

SQL Server (Microsoft Products)

Oracle (Oracle Products)

DB2 (IBM Products)

MySQL (Oracle Acquisition)

Sybase (relational database, Sybase products, USA)

Relational Database

entity

attribute

contact

Relational Database

Examples of relational database applications

Introduction to non-relational databases

Introduction to MySQL database

Characteristic:

MySQL Business and Community Editions

The difference between the two:

MySQL Product Campaign

MySQL 5.7 Compile Installation

1. Install the environment packages required to compile mysql

2. Add a mysql user

3. Unzip the mysql source package into the'/opt/'directory

4. Rename the "boost_1_59_0" directory to "boost"

5. Configure mysql service

6. Compile and install MySQL services

7. Change the ownership and group of the database catalog

8. Modify the configuration file

9. Add mysql related commands to the local environment configuration

10. Initialize the database

11. Copy MySQL service configuration file to/usr/lib/systemd/system/for easy systemctl management

12. Configure MySQL password

13. Attempt to log on to MySQL database

14. Remote login with Navicat tools

15. Enter the database to view all databases

Hot Keywords