Shulou(Shulou.com)06/01 Report--

This article mainly introduces MySQL concept, application, management and other detailed explanation, which involves things, from theoretical knowledge to learn, there are many books, literature for everyone to refer to, from a practical point of view, accumulated years of practical experience can be shared with you.

1. Introduction to MySQL

MySQL is a lightweight relational database management system developed by MySQL AB of Sweden and currently owned by Oracle. MySQL is widely used in small and medium-sized websites on the Internet at present. Because of its small size, fast speed, low total cost of ownership, open source and free, Linux + MySQL is generally selected as the website database for the development of small and medium-sized websites.

MySQL is a relational database management system, MySQL is a relational database management system, relational database data stored in different tables, rather than all the data in a large warehouse, increased speed and increased flexibility.

MySQL's official website is www.mysql.com

2. MySQL characteristics

MySQL is a widely used database with the following characteristics:

A. Written in C and C++, and tested with various compilers to ensure the portability of the source code

B. Support AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, OS/2 Wrap, Solaris, Windows and other operating systems.

C. Provides APIs for multiple programming languages. Programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.

D. Support multithreading and make full use of CPU resources

E. Optimized SQL query algorithm, effectively improving query speed

F. It can be used as a separate application in the client Cloud Virtual Machine network environment, or embedded into other software as a library to provide multi-language support. Common codes such as GB2312 and BIG5 in Chinese and Shift_JIS in Japanese can be used as data table names and data column names.

G. Provide TCP/IP, ODBC and JDBC and other database connection ways

H. Provide management tools for managing, checking and optimizing database operations

I. Can handle large databases with tens of millions of records

3. MySQL application

Compared with large databases such as Oracle, DB2, SQL Server, MySQL has its own shortcomings, such as small size, limited functionality (MySQL Cluster's functionality and efficiency are relatively poor), etc., but this does not reduce its popularity. MySQL provides more than enough functionality for the average consumer and small and medium business, and because MySQL is open source software, it can significantly reduce the total cost of ownership. At present, the popular website architecture on the Internet is LAMP (Linux+Apache+MySQL+PHP), that is, Linux as the operating system, Apache as the Web server, MySQL as the database, PHP as the server-side script interpreter. Because Linux+Apache+MySQL+PHP are free or open source software (FLOSS), LAMP can be used to build a stable, free website system without spending a penny.

4. MySQL management

MySQL databases can be managed using command-line tools (mysql and mysqladmin commands), or graphical management tools MySQL Administrator and MySQL Query Browser can be downloaded from MySQL's website.

phpMyAdmin is a MySQL database management program written in PHP that allows administrators to manage MySQL databases using a Web interface.

phpMyBackupPro is also written in PHP and allows you 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 certain times or cycles.

In addition, there are other GUI management tools, such as the earlier mysql-front and ems mysql manager, navicat, etc.

MySLQ Storage Engine 1. MySQL Storage Engine Introduction

Plug-in storage engine is one of the most important features of MySQL database, users can choose how to store and index database according to the needs of the application, whether to use transactions, etc. mySQL supports multiple storage engines by default to meet the needs of database applications in different fields. Users can choose to use different storage engines to improve the efficiency of the application, providing flexible storage, and user settings can customize and use their own storage engines according to their needs for maximum customization.

MySQL commonly used storage engines are MyISAM, InnoDB, MEMORY, MERGE, of which InnoDB provides transaction security tables, and other storage engines are non-transaction security tables.

MyISAM is MySQL's default storage engine. MyISAM does not support transactions or foreign keys, but it is fast to access and does not require transaction integrity.

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. However, InnoDB writes are less efficient than MyISAM storage engines and take up more disk space to retain data and indexes. MySQL supports foreign key storage engine only InnoDB. When creating foreign key, it requires that the attached table must have corresponding index. When creating foreign key, the child table will also automatically create corresponding index.

MySQL Storage Engine Features

It is mainly embodied in performance, transaction, concurrency control, referential integrity, caching, failure recovery, backup and storage, etc.

MyISAM and InnoDB are popular storage engines, and MyISAM is the first choice for most Web applications. The main difference between MyISAM and InnoDB is performance and transaction control.

MyISAM is an extended implementation of the early ISAM(Indexed Sequential Access Method), which is designed to handle situations where the read frequency is much greater than the write frequency, so ISAM and later MyISAM do not consider the support for things, do not need transaction records, ISAM query efficiency is considerable, and memory footprint is small. MyISAM inherits the advantages of ISAM and keeps pace with the times by providing a large number of useful new features and related tools. Table-level locking is provided, for example, to allow for concurrency control. And because MyISAM uses separate storage files for each table (MYD data files and MYI index files), it makes backup and recovery very convenient (copy overwrite), and it also supports online recovery.

So if the application does not need transactions, does not support foreign keys, and only handles basic CRUD (add, delete, and change) operations, then MyISAM is the best choice.

MySQL database installation 1. MySQL download

MySQL version selection

MySQL Community Server Community Edition, open source free, but does not provide official technical support.

MySQL Enterprise Edition is available for a fee and can be tried for 30 days.

MySQL Cluster Edition, open source and free. Several MySQL servers can be packaged into one Server.

MySQL Cluster CGE Advanced Cluster Edition, for a fee.

MySQL Workbench (GUITOOL) is an ER/database modeling tool designed specifically for MySQL and is the successor to the famous database design tool DBDesigner4. MySQL Workbench is divided into two versions: Community Edition (MySQL Workbench OSS) and Commercial Edition (MySQL Workbench SE).

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 package: 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 lower versions of MySQL

Check out RHEL 6.8 installed MySQL version:

rpm -qa|grep mysql

Uninstalling older versions of MySQL:

rpm -e mysql-libs-5.1.73-7.el6.x86_64 --nodeps

3. Install MySQL

Unpack MySQL bundle:

tar -xvf MySQL-5.6.35-1.el6.x86_64.rpm-bundle.tar

A. Install RHEL compatibility package

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 MySQL server program

rpm -ivh MySQL-server-5.6.35-1.el6.x86_64.rpm

D. Install 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 configuration files to/etc directory

cp /usr/share/mysql/my-default.cnf /etc/mysql.conf

Modify/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

##Set the maximum number of connections, the default is 151, the maximum number of connections allowed by MySQL server is 16384

max_connections=1000

[mysql]

default-character-set = utf8

Initialize MySQL database:

/usr/bin/mysql_install_db

5. Manage password modification

Get random administration password during installation

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 prompt input password, input random management password, enter mysql.

Execute SQL statements to set administrative passwords

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('123456');

Exit mysql

6. Set boot to start MySQL

chkconfig mysql on

Views

chkconfig --list | grep mysql

7. MySQL default installation directory description

/var/lib/mysql/ #Database directory

/usr/share/mysql #Configuration file directory

/usr/bin #Related command directories

/etc/init.d/mysql #Start script

8. MySQL service port is 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. Authorize root to log in remotely

Allow root to log in remotely from anywhere and have any access to all libraries

First root login: mysql -u root -p"root"

Executing SQL statements in mysql:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;FLUSH PRIVILEGES;10. Import SQL scripts into mysql database

mysql -u root -p //login mysql connection

create database test; //SQL statement creates an imported database

use test; //check to activate database

testsource. sql; //import sql script

11. Client connects to MySQL server

telnet connection test MySQL server

Use telnet xx.xx.xx 3306 to test whether Windows clients can connect to MySQL servers.

If you can't connect, it may be the firewall of the Linux server or the security group restriction of the cloud host.

12. Turn off the domain name resolution function of MySQL service

MySQL usually performs domain name resolution on connected clients, which affects the speed of accessing MySQL. So you need to turn off MySQL domain name resolution. Set in the/etc/my.cnf configuration file.

Set skip-name-resolve in [mysqld] service bar as follows:

# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0skip-name-resolve

Restart MySQL service.

Read the above MySQL concept, application, management and other detailed introduction, I hope to give you some help in the actual application. Due to the limited space of this article, it is inevitable that there will be deficiencies and needs to be supplemented. You can continue to pay attention to the industry information section and regularly update the industry news and knowledge. If you need more professional answers, you can contact our 24-hour pre-sales service on the official website to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.