MySQL Database Basics (i) Introduction to--mysql Database

MySQL Database Basics (i)--mysql database introduction One, MySQL Introduction 1, MySQL Introduction

MySQL is a lightweight relational database management system developed by the Swedish MySQL AB company, currently owned by Oracle Corporation. Currently MySQL is widely used in small and medium-sized web sites on the Internet, due to small size, fast, low total cost of ownership, open source, free, the general development of small and medium-sized Web sites have chosen Linux + MySQL as a site database.
MySQL is a relational database management system, MySQL is an associated database management system, the associated database to save data in different tables, rather than put all the data in a large warehouse, it adds speed and increased flexibility.
The web site of MySQL's official website is: www.mysql.com

2. mysql Features

MySQL is a widely used database with the following features:
A, written in C and C + +, and use a variety of compilers to test, to ensure the portability of the source code
B, support for AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, Os/2 Wrap, Solaris, Windows and many other operating systems.
C. Provides APIs for a variety of programming languages. Programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.
D, support multi-threading, make full use of CPU resources
E, optimized SQL query algorithm to improve query speed effectively
F, can be used as a separate application in the Client server network environment, but also can be embedded in other software as a library to provide multi-language support, common encoding such as Chinese GB 2312, BIG5, Japanese shift_jis, etc. can be used as data table name and data column name
G, provide a variety of database connection channels such as TCP/IP, ODBC and JDBC
H. Provides management tools for managing, inspecting, and optimizing database operations
I, can handle a large database with thousands records

3. mysql Application

Compared to large databases such as Oracle, DB2, SQL Server, MySQL has its shortcomings, such as small size, limited functionality (MySQL cluster is relatively poor in functionality and efficiency), but this does not diminish its popularity. MySQL provides more than enough functionality for general personal users and small and midsize businesses, and because MySQL is open source software, it can significantly reduce total cost of ownership. Currently, the popular web site architecture is lamp (linux+apache+mysql+php), that is, using Linux as the operating system, Apache as a Web server, MySQL as a database, PHP as a server-side script interpreter. Since linux+apache+mysql+php are free or open source software (FLOSS), using lamp can build a stable, free website system without spending a penny.

4. mysql Management

You can use the command-line tools to manage MySQL databases (command MySQL and mysqladmin), or you can download the graphics management tools MySQL administrator and MySQL Query Browser from the MySQL Web site.
phpMyAdmin is a MySQL repository system management program written by PHP that allows administrators to manage MySQL repositories in a web interface.
Phpmybackuppro is also written in PHP and can be used to create and manage databases through a web interface. It can create pseudo-cronjobs that can be used to automatically back up MySQL databases at a certain time or period.
In addition, there are other GUI management tools, such as the previous Mysql-front and EMS MySQL Manager,navicat and so on.

Second, MYSLQ storage engine 1, MySQL storage engine introduction

Plug-in storage engine is one of the most important features of MySQL database, the user can choose how to store and index database, use transaction and so on according to the needs of the application. MySQL supports a variety of storage engines by default to suit the needs of different domains of database applications. Users can use different storage engines to improve the efficiency of their applications, provide flexible storage, and user settings to customize and use their own storage engine to achieve maximum customization, as needed.
The common storage engine for MySQL is MyISAM, InnoDB, memory, MERGE, where InnoDB provides transaction security tables, and other storage engines are non-transactional security tables.
MyISAM is the default storage engine for MySQL. MyISAM does not support transactions or foreign keys, but it has fast access and no requirement for transactional integrity.
The InnoDB storage Engine provides transactional security with commit, rollback, and crash resiliency. However, compared with the MyISAM storage engine, InnoDB writes are less efficient and consume more disk space to preserve data and indexes. MySQL supports the foreign key storage engine only InnoDB, when creating foreign keys, requires that the schedule must have a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index.

2. mysql Storage engine Features

Mainly embodied in performance, transaction, concurrency control, referential integrity, caching, failure recovery, backup and back-up, and several other aspects
At present, the more popular storage engine is MyISAM and InnoDB, and MyISAM is the first choice of most Web applications. The main difference between MyISAM and InnoDB is in performance and transaction control.
MyISAM is an extended implementation of the early ISAM (Indexed sequential Access Method), and ISAM is designed to handle read frequencies much more than write frequencies, so ISAM and later MyISAM do not consider support for things, No transaction logging is required, and ISAM's query efficiency is considerable and memory consumption is minimal. While inheriting the benefits of ISAM, MyISAM provides a number of new and useful features and tools to keep pace with the times. For example, given concurrency control, table-level locks are provided. And because MyISAM is each table using its own separate storage files (myd data files and myi index files), it makes backup and recovery very convenient (copy overwrite), but also supports online recovery.
So if the application does not need transactions, does not support foreign keys, processing is only the basic crud (add-and-revise) operation, then MyISAM is the choice.

Third, MySQL database installation 1, MySQL download

MySQL version of the selection
MySQL Community Server Community version, open source is free, but does not provide official technical support.
MySQL Enterprise Edition is available for a fee and can be tested for 30 days.
MySQL Cluster Cluster Edition, open source free. Several MySQL servers can be packaged as one server.
MySQL Cluster CGE Premium Cluster Edition is subject to a fee.
MySQL Workbench (guitool), a er/database modeling tool designed for MySQL, is a successor to the famous database design tool DBDesigner4. Mysqlworkbench is also divided into two editions, namely the Community Edition (MySQL Workbench OSS), the Business Edition (MySQL Workbenchse).
This article selects MySQL Community Server 5.6.35
Download URL:
Https://dev.mysql.com/downloads/mysql/5.6.html#downloads
Operating System Selection:
Red Hat Enterprise linux/oracle Linux
Operating system version selection:
Red Hat Enterprise linux 6/oracle Linux 6 (x86, 64-bit)
Download MySQL Bundle:
Mysql-5.6.35-1.el6.x86_64.rpm-bundle.tar
The MySQL bundle consists of seven parts:
Rhel Compatibility Pack: mysql-shared-compat-5.6.35-1.el6.x86_64.rpm
MySQL Server program: mysql-server-5.6.35-1.el6.x86_64.rpm
MySQL client program: mysql-client-5.6.35-1.el6.x86_64.rpm
MySQL Development Library: mysql-devel-5.6.35-1.el6.x86_64.rpm
MySQL Shared library: mysql-shared-5.6.35-1.el6.x86_64.rpm
Embedded version: mysql-embedded-5.6.35-1.el6.x86_64.rpm
Test component: mysql-test-5.6.35-1.el6.x86_64.rpm

2. Uninstall the low version of MySQL

To view the MySQL version of Rhel 6.8 installation:
Rpm-qa|grep MySQL
Uninstall the low version of MySQL:
RPM-E mysql-libs-5.1.73-7.el6.x86_64--nodeps

3. Install MySQL

Unpacking the MySQL bundle:
Tar-xvf? Mysql-5.6.35-1.el6.x86_64.rpm-bundle.tar
A. Installing the Rhel Compatibility Pack
RPM-IVH mysql-shared-compat-5.6.35-1.el6.x86_64.rpm
B. Install MySQL shared library
RPM-IVH mysql-shared-5.6.35-1.el6.x86_64.rpm
C, install the MySQL server program
RPM-IVH mysql-server-5.6.35-1.el6.x86_64.rpm
D. Install the MySQL client program
RPM-IVH mysql-client-5.6.35-1.el6.x86_64.rpm
E. Install MySQL Development library
RPM-IVH mysql-devel-5.6.35-1.el6.x86_64.rpm

4. mysql Configuration

Copy MySQL config file to/etc directory
Cp/usr/share/mysql/my-default.cnf/etc/mysql.conf
Modify the/etc/mysql.conf as follows:
[Client]
Password = 123456
Port = 3306
Default-character-set=utf8
[Mysqld]
Port = 3306
Character_set_server=utf8
Character_set_client=utf8
Collation-server=utf8_general_ci
Lower_case_table_names=1
# #设置最大连接数, default is the maximum number of connections allowed for the 151,MYSQL server 16384
max_connections=1000
[MySQL]
Default-character-set = UTF8
Initialize MySQL database:
/usr/bin/mysql_install_db

5. Manage password changes

Get the random admin password when installing
Cat/root/.mysql_secret
# The random password set for the root user at Sun Apr 2 00:52:59 2017 (local time): MwgPucEp8gMz0e20
Reset Root Password
Mysql-u root-p
After entering password, enter the random admin password and enter MySQL.
Execute SQL statement set Admin password
SET PASSWORD for ' root ' @ ' localhost ' =password (' 123456 ');
Quit MySQL

6. Set the boot to start MySQL

Chkconfig MySQL on
View
Chkconfig--list | grep MySQL

7. mysql Default installation directory description

/var/lib/mysql/#数据库目录
/usr/share/mysql #配置文件目录
/usr/bin #相关命令目录
/etc/init.d/mysql #启动脚本

8. mysql Service port Open

Open MySQL service port 3306 and save
/sbin/iptables-i input-p TCP--dport 3306-j ACCEPT
/etc/rc.d/init.d/iptables Save

9, authorized root user Telnet connection

Allow root users to log in remotely from anywhere and have any permissions to operate on all libraries
Root login: mysql-u root-p "Root"
Execute the SQL statement in MySQL:

GRANT?ALL?PRIVILEGES?ON?*.*?TO?‘root‘@‘%‘?IDENTIFIED?BY?‘root‘?WITH?GRANT?OPTION;FLUSH?PRIVILEGES;

10. Import SQL script into MySQL database

Mysql?-u?root?-p?? Log in to MySQL connection
create?database?test;?? SQL statement to create an imported database
use?test;?? Select Activate Database
Source Test.sql;?? Import SQL Script

MySQL Database Basics (i) Introduction to--mysql Database