Introduce and install MySQL database in detail

Keywords: Linux MySQL Database Oracle glibc

Basic concepts of database

data

  • The symbolic record that describes things is called Data
  • Including numbers, words, graphics, images, sounds, archives, etc
  • Store in a unified format in the form of "record"

surface

  • Organize different records together to form a "table"
  • It is used to store specific data

data base

  • A database is a collection of tables and a warehouse for storing data
  • Interrelated data stored in an organized way

History of database system development

First generation database

  • Since 1960s, the first generation of database system has come out. They are database systems of hierarchical model and mesh model, which provide powerful support for unified management and data sharing

Second generation database

  • In the early 1970s, the second generation of database relational database began to appear
  • In the early 1980s, IBM's relational database system DB2 came out. As the second generation of relational database system, it began to gradually replace the hierarchical and mesh model database, becoming the dominant database and the mainstream of the industry. So far, relational database system still occupies the main position of database application

Third generation database

  • Since the 1980s, a variety of new database systems adapted to different fields have emerged, such as engineering database, multimedia database, graphic database, intelligent database, distributed database and object-oriented database, etc., especially object-oriented database system, which is favored by people because of its strong practicability and wide adaptability
  • In the late 1990s, a variety of database systems supported the application together. Of course, in business applications, relational database is still the mainstream, but some new elements have been added to the mainstream business database system. For example, the relational object database model supported by Oracle

Introduction to today's mainstream databases

SQL Server (Microsoft products)

  • For Windows operating system
  • Simple and easy to use

Oracle (Oracle products)

  • For all mainstream platforms
  • Safe, perfect and complex operation

DB2 (IBM products)

  • For all mainstream platforms
  • Large, safe and perfect

MySQL (acquired by Oracle)

  • Free, open source, small size

relational database

  • Relational database system is a database system based on relational model. Its basic concept comes from relational model
  • The relational model is based on the theory of relational algebra. The data structure is represented by a simple two-dimensional data table and a simple entity relationship diagram
  • The following E-R diagram contains three elements: entity (data object), relationship and attribute

entity

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

attribute

  • An entity has a certain property. An entity can have multiple attributes. For example, each entity in the bank customer entity set has attributes such as name, address, telephone, etc

contact

  • The correspondence between entity sets is called relation, also called relation. For example, there is a "savings" relationship between a bank customer and a bank account

The collection of all entities and their relationships constitutes a relational database

The storage structure of relational database is two-dimensional table, and the data reflecting things and their relations are saved in the form of table

In each two-dimensional table, each row is called a record, which is used to describe the information of an object; each column is called a field, which is used to describe an attribute of an object

Application of relational database

Relational database

  • Oracle´╝îMySQL
  • SQLServer,Sybase
  • Informix,access
  • DB2,FoxPRO

Application example of relational database

  • 12306 user information system
  • Taobao account information system, Alipay account system, mobile, telecom, Unicom mobile phone signal information system, accounting system, bank account system
  • Website user information system

Introduction to non relational database

  • Non relational database is also called NoSQL (Not Only SQL). The data stored is not based on the relational model and does not need a fixed table format
  • As a supplement of relational database, non relational database is playing an important role in high efficiency and high performance in the network era
  • Advantages of non relational database
    • The requirement of high concurrent read and write in database
    • Efficient storage and access to massive data
    • The requirement of high expansibility and high availability of database

Non relational database storage

  • Key value method: store, delete and modify data based on key
  • Column oriented, which stores related data in the column family
  • In document mode, the database consists of a series of data items, each of which has a name and corresponding value
  • Graphic mode, entity as vertex, relationship as edge, data saved as a graphic

Non relational database products

  • Memcached is an open-source, high-performance cache system with distributed memory objects, which stores data in key value mode

    • Cache data to reduce database pressure and speed up access
    • Accelerate dynamic Web applications
    • Cached content is stored in memory
  • redis also stores data in key value mode. The data is also saved in memory, but it will write data to disk regularly
  • Compared with FMemcached, it has the following characteristics
    • Memory cache support
    • Support persistence
    • More data types
    • Support cluster and distributed
    • Support queue

Example of redis application

  • Database front end cache
  • session sharing
  • When you need to cache more data types than key/value
  • When cached data needs to be stored for a long time

Introduction to MySQL database

  • MySQL is a popular open source relational database
  • Oracle products
  • It complies with the GPL protocol and can be used and modified free of charge
  • Characteristic
    • Excellent performance and stable service
    • Open source, no copyright restrictions, low cost
    • Multi thread, multi-user
    • Based on C/S (client / server) architecture
    • Safe and reliable

MySQL business and community

  • MySQL Business Edition is developed and maintained by MySQL AB company, and can only be used for a fee
  • MySQL Community Edition is developed and maintained by MySQL developers and enthusiasts all over the world. It can be used for free
  • Difference between them
    • The organization, management and testing of commercial version are more strict and stable
    • Commercial version does not comply with GPL
    • The commercial version can obtain 7 * 24-hour service, such as fault maintenance and patching, etc

MySQL product camp

  • The first camp: 5.0-5.1 camp, which can be said to be the continuation of early products
  • The second camp: 5.4-5.7 camp, which better integrates the storage engines of MySQL AB company, community and third-party companies, so as to improve performance
  • The third camp: the 6.0-7.1 camp, the MySQL Cluster version, is developed to meet the needs of the database in the new era
  • MySQL download website

MySQL installation instance

  • Open the Linux system in the VMware virtual machine, download the MySQL compressed package to the host computer, and set the folder share to save the MySQL compressed package, which is convenient for the Linux system to mount and use Download MySQL 5.7
[root@localhost ~]# Yum install GCC gcc-c + + make ncurses ncurses devel bison cmake - Y / / install the environment package
//Loaded plug-ins: faststmirror, langpacks
base                                                               | 3.6 kB  00:00:00     
extras                                                             | 2.9 kB  00:00:00
...
//Installed:
  bison.x86_64 0:3.0.4-2.el7                          cmake.x86_64 0:2.8.12.2-2.el7       
  gcc.x86_64 0:4.8.5-39.el7                           gcc-c++.x86_64 0:4.8.5-39.el7       
  ncurses-devel.x86_64 0:5.9-14.20130511.el7_4       

//Installed as a dependency:
  cpp.x86_64 0:4.8.5-39.el7               glibc-devel.x86_64 0:2.17-292.el7              
  glibc-headers.x86_64 0:2.17-292.el7     kernel-headers.x86_64 0:3.10.0-1062.4.3.el7    
  libmpc.x86_64 0:1.0.1-3.el7             libstdc++-devel.x86_64 0:4.8.5-39.el7          
  m4.x86_64 0:1.4.16-10.el7              

//Update completed:
  make.x86_64 1:3.82-24.el7             ncurses.x86_64 0:5.9-14.20130511.el7_4            

//Upgraded as a dependency:
  glibc.x86_64 0:2.17-292.el7                 glibc-common.x86_64 0:2.17-292.el7         
  libgcc.x86_64 0:4.8.5-39.el7                libgomp.x86_64 0:4.8.5-39.el7              
  libstdc++.x86_64 0:4.8.5-39.el7             ncurses-base.noarch 0:5.9-14.20130511.el7_4
  ncurses-libs.x86_64 0:5.9-14.20130511.el7_4

//Complete!
[root@localhost ~]# useradd -s /sbin/nologin mysql / / create a MySQL program user
[root@localhost ~]# mount.cifs //192.168.100.8/shares /mnt / / mount the MySQL compressed package path in the host to the Linux system
Password for root@//192.168.100.8/shares:  
[root@localhost ~]# cd /mnt / / / enter the mount point directory
[root@localhost mnt]# tar zxvf mysql-boost-5.7.20.tar.gz -C /opt / / extract the MySQL package to the opt directory
......
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/extract_key.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/buckets.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/allocate.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/util.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_map.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_set.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_set_fwd.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_map_fwd.hpp
mysql-5.7.20/boost/boost_1_59_0/boost/timer.hpp
[root@localhost mnt]# cd /opt/mysql-5.7.20/
[root@localhost mysql-5.7.20]# cmake \ / / configure mysql
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \        //Specify installation path
> -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \  //Specify mysql.sock. Path
> -DSYSCONFDIR=/etc \                               //Specify the location of the configuration file
> -DSYSTEMD_PID_DIR=/usr/local/mysql \             //Specify pid file as location
> -DDEFAULT_CHARSET=utf8 \                         //Character set format utf-8
> -DDEFAULT_COLLATION=utf8_general_ci \          
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \             //Turn on the storage engine
> -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
> -DMYSQL_DATADIR=/usr/local/mysql/data \      //Specify data storage location
> -DWITH_BOOST=boost \                          //Association supports c + + runtime
> -DWITH_SYSTEMD=1                            //Turn on systemd
....
-- CMAKE_C_LINK_FLAGS: 
-- CMAKE_CXX_LINK_FLAGS: 
-- CMAKE_C_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF
-- Configuring done
-- Generating done
-- Build files have been written to: /opt/mysql-5.7.20
[root@localhost mysql-5.7.20]# Make / / make process (long time, wait patiently)
....
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/readline.cc.o
Linking CXX executable mysql_embedded
[100%] Built target mysql_embedded
Scanning dependencies of target mysqltest_embedded
[100%] Building CXX object libmysqld/examples/CMakeFiles/mysqltest_embedded.dir/__/__/client/mysqltest.cc.o
Linking CXX executable mysqltest_embedded
[100%] Built target mysqltest_embedded
Scanning dependencies of target my_safe_process
[100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o
Linking CXX executable my_safe_process
[100%] Built target my_safe_process
[root@localhost mysql-5.7.20]# make install / / install
...
-- Installing: /usr/local/mysql/./COPYING-test
-- Installing: /usr/local/mysql/./README-test
-- Up-to-date: /usr/local/mysql/mysql-test/mtr
-- Up-to-date: /usr/local/mysql/mysql-test/mysql-test-run
-- Installing: /usr/local/mysql/mysql-test/lib/My/SafeProcess/my_safe_process
-- Up-to-date: /usr/local/mysql/mysql-test/lib/My/SafeProcess/my_safe_process
-- Installing: /usr/local/mysql/mysql-test/lib/My/SafeProcess/Base.pm
-- Installing: /usr/local/mysql/support-files/mysqld_multi.server
-- Installing: /usr/local/mysql/support-files/mysql-log-rotate
-- Installing: /usr/local/mysql/support-files/magic
-- Installing: /usr/local/mysql/share/aclocal/mysql.m4
-- Installing: /usr/local/mysql/support-files/mysql.server
[root@localhost mysql-5.7.20]# cd /usr/local / / / enter the installation directory
[root@localhost local]# Chown - R mysql.mysql MySQL / / MySQL directory change users and groups
[root@localhost local]# mv /etc/my.cnf /etc/my.cnf.bak / / change the profile name
[root@localhost local]# vim /etc/my.cnf / / re edit the configuration file
[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
:wq
[root@localhost local]# vim /etc/profile
...
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH     
export PATH                                //Redeclare environment variables
:wq
[root@localhost local]# source /etc/profile / / re execute
[root@localhost local]# cd /usr/local/mysql/bin / / enter MySQL command directory
[root@localhost bin]# Mysqld -- initialize execute -- user = MySQL -- basedir = / usr / local / MySQL -- dataDir = / usr / local / MySQL / data / / initialize the database
2019-12-02T11:51:28.112082Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-12-02T11:51:28.263398Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-12-02T11:51:28.287408Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-12-02T11:51:28.341284Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 131f32df-14fa-11ea-87e6-000c297265cb.
2019-12-02T11:51:28.342106Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-12-02T11:51:28.342452Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@localhost ~]# cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service  /lib/systemd/system/ 
//Copy MySQL startup script driver to system directory
[root@localhost ~]# systemctl enable mysqld.service / / set MySQL to start from scratch
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@localhost ~]# systemctl start mysqld.service / / start MySQL
[root@localhost ~]# mysqladmin -u root -p password / / set MySQL database password
Enter password:                  //No original password, enter directly
New password:                   //Enter new password
Confirm new password:           //Enter password again
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.  
//Safety tips
[root@localhost ~]# mysql -u root -p / / log in to MySQL
Enter password:          Input password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, 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)

Posted by hno on Tue, 03 Dec 2019 01:22:28 -0800