MySQL series-Chapter 1: MySQL introduction and installation, mysql Chapter 1

MySQL series-Chapter 1: MySQL introduction and installation, mysql Chapter 1
1. Database Introduction1. What is Database? '<Database>'

Simply put, a data warehouse is organized and stored according to a certain data structure <data structure refers to the organization form of data or the relationship between data>, we can use a variety of methods provided by the database to manage the data in the database. Our understanding of the data in a simple image, such as our human society, our ID card, and household register, is related to the database.

1. 2. Development

Databases emerged more than 60 years ago. With the development of information technology and the market, especially after the 1990s S, data management is no longer just about data storage and management, but is transformed into various data management methods required by users. There are many types of databases. From the simplest storage of tables with various types of data to the large database systems capable of storing massive data, they have been widely used in various aspects.

2. MYSQL introduction and Installation 2. 1. Why MYSQL? 3. MYSQL database category

MySQL Official Website

3. 1. Differences between Community edition and Enterprise Edition

• First of all, the Community edition is free of charge, and the Enterprise Edition requires high fees.
• Technically, the Enterprise Edition provides MySQL enterprise-level servers and MySQL enterprise-level system monitoring tools. The enterprise edition has been strictly tested and certified, and is not as strict as the development and testing environments of the Enterprise Edition and Community edition.
• In terms of service, community edition mysql does not provide any technical support, and is not liable for any exceptions during use. The Enterprise Edition is the opposite.
• The commercial version does not support the GPL protocol.
• Select stable edition select GA edition of the community
• Product lines, mainly 5.1 and 5.5, most 5.5
• Select MySQL GA version for 6 months or more
• It is recommended that you do not update the released version for a long time.
• Compatible with developer versions
• Test environment running for 3-6 months
• Running non-core services in the production environment for several months

4. Install MYSQL 4.1. How to select MYSQL in the production environment

• Select stable edition select GA edition of the community
• Product lines, mainly 5.1 and 5.5, most 5.5
• Select MySQL GA version for 6 months or more
• It is recommended that you do not update the released version for a long time.
• Compatible with developer versions
• Test environment running for 3-6 months
• Running non-core services in the production environment for several months

4.2 MYSQL Installation Method

• Binary installation, rpm/yum
• Source code compilation, Product Line 1 is compiled and installed with normal, line 2 is compiled and installed with cmake 5.5/6/7

4.3 install mysql 5.6.39 in yum

1. Clear MariaDB under CentOS7.

[root@MySql-01 ~]# rpm -qa | gremp mariadb[root@MySql-01 ~]# rpm -e --nodeps mariadb.x86_64 1:5.5.41-2.el7_0

2. Download the MySQL yum package

The repo provided by Linux does not automatically update the latest version of each software (usually a relatively low-reliability version). Therefore, you cannot install the advanced version of MySQL using yum. Therefore, we need to first install the rpm package with the currently available mysql5 series community edition resources (to ensure computer networking ).

[root@MySql-01 ~]# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm[root@MySql-01 ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm[root@MySql-01 ~]# yum install mysql-community-server

4.4 configure MySQL after installation is complete

After the installation is successful, add it to boot and start

[root@MySql-01 ~]# systemctl enable mysqld[root@MySql-01 ~]# systemctl start mysqld[root@MySql-01 ~]# ss -tnl |grep 3306LISTEN     0      80          :::3306                    :::*

Configure mysql (SET Password, etc)

[Root @ MySql-01 ~] # Mysql_secure_installationEnter current password for root (enter for none): # [Press enter] Set root password? [Y/n] Y # [set the root user password] New password: Re-enter new Password: password updated successfully! Reloading privilege tables... Success! Remove anonymous users? [Y/n] y # [Delete Anonymous Users] Disallow root login remotely? [Y/n] y # [disable root remote logon] Remove test database and access to it? [Y/n] y # [delete test database] Reload privilege tables now? [Y/n] y # [refresh permission] All done! If you 've completed all of the above steps, your MySQLinstallation shoshould now be secure. Thanks for using MySQL! Cleaning up...

5. MySQL client options

** Instance **-u, -- user # specify the connection user-h, -- host # specify the connection host-p, -- password # specify the connection password -- protocol = {tcp | socket | memory | pipe} # specify the connection protocol-P, -- port # specify the connection port, default listening port: tcp/3306 -- socket # specify the sock file for the local connection -- compress # data transmission adopts the compression format-D, -- database # specify the database-H by default after the connection, -- html # specify generate html output-X, -- xml # specify generate xml output -- safe-updates # refuse to use the update or delete command without the where clause # Use the instance: mysql-hlocalhost-uroot-p

6. MySQL management tool mysqladmin

# Format: mysqladmin [options] command [arg] [, command [arg]… # Common Commands include: create DB_Name: # create a database drop DB_Name: # delete a database debug: # Open the debugging log and record it in the error log status: # display brief status information -- sleep #: Set the Interval Duration -- count #: Set the displayed batch extended-status: # display the extension information, and output the status variables and assignments of mysqld, execute "mysql> show global status" variables: # output the variables flush-hosts: # Clear the host-related cache: DNS resolution cache; previously, access to the host list of mysqld was denied due to too many connection errors. flush-logs: # log rolling only allowed to scroll the binary log and refresh of the relay log: # It is equivalent to using both flush-hosts and flush-logsflush-privileges: # notifying mysqld to re-read the authorization table reload: # The function is the same as "flush-privileges" flush-status: # reset the value of the state variable flush-tables: # Close the currently opened table file handle flush-threads: # Clear the thread cache kill: # kill the specified thread and specify the thread ID; multiple Threads can be killed at a time, separated by commas (,), but no extra space is allowed. password: # modify the current user's password ping: # simulate the ping operation to check whether mysqld is online processlist: # display mysqld thread list shutdown: # Close mysqld process start-slave, stop-slave: # start/Close slave server thread

7. MySQL Data File Parsing

MyISAM Table: Each table has three files in the database directory.

Tb_name.frm: # table structure definition file tb_name.MYD: # data file tb_name.MYI: # index file

InnoDB table: There are two storage methods

Default Mode: Each table has one independent file and one file shared by multiple tables.

Tb_name.frm: # The table structure definition file, which is located in the ibdata #: # shared tablespace file in the database directory. It is located in the data directory (the directory pointed to by datadir) by default, such as ibdata1.

Custom mode: Independent tablespace

Tb_name.frm: # Table schema definition file tb_name.ibd: # unique tablespace File

How to enable the independent tablespace function during MySQL initialization:

Vi/etc/my. cnf (added under [mysqld]) innodb_file_per_table = ON # Note: table space is a data file in a specific format managed by InnoDB. It can store data and indexes simultaneously.