MySQL Advanced Knowledge Architecture Introduction

mysql video tutorialThe column introduces the structure of MySQL advanced knowledge

Recommended (free): mysql video tutorial

1. Introduction to MySQL

Overview

• MySQL is a relational database management system developed by the Swedish MySQL AB company and currently belongs to Oracle.

• MySQL is a relational database management system that stores data in different tables instead of putting all data in one big warehouse, which increases speed and improves flexibility.

• Mysql is open source, so you don’t need to pay extra.

• Mysql supports large databases. Can handle large databases with tens of millions of records.

• MySQL uses the standard SQL data language form.

• Mysql can be used on multiple systems and supports multiple languages. These programming languages ​​include C, C, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl, etc.

• Mysql has good support for PHP, which is currently the most popular web development language.

• MySQL supports large databases and data warehouses with 50 million records. The 32-bit system table file can support a maximum of 4GB, and the 64-bit system supports a maximum table file of 8TB.

• Mysql can be customized and adopts the GPL protocol. You can modify the source code to develop your own Mysql system.

Advanced MySQL involves knowledge

• mysql kernel
• sql optimization siege lion
• mysql server optimization
• Various parameter constant settings
• Query statement optimization
• Master-slave replication
• Software and hardware upgrade
• Disaster recovery backup
• sql programming

• Complete mysql optimization requires a deep foundation. Large companies even have dedicated DBAs to write the above

2. Installation of MySQL Linux version

• This time I installed MySQL 5.5, and the installation environment is CentOS 6.5
• version download address official website download address
  • DownloadMySQL-Client and MySQL-Server
  • http://downloads.skysql.com/archives/mysql-5.5/MySQL-server-5.5 .16-1.rhel4.i386.rpm
  • http://downloads.skysql.com/archives/mysql-5.5/MySQL-client-5.5.16-1.rhel4.i386.rpm
  • http://downloads.skysql.com/archives/mysql-5.5/MySQL-devel-5.5.16-1.rhel4.i386.rpm
• Check whether the current system Installed MySQL
  • Query command: rpm -qa|grep -i mysql
  • Delete command: rpm -e --nodeps RPM package full name
• Install mysql server (Pay attention to the tips)

  

  • Set password Tip

• Install mysql client
• View the mysql user and mysql group created during MySQL installation
  • # cat /etc /passwd | grep mysql
  • cat /etc/group | grep mysql
• Starting and stopping the mysql service
  • View MySQL start and stop status: # ps -ef | grep mysql
  • Start and stop operations:
    • # /etc/init.d/mysql start
    • # /etc/init.d/mysql stop
    • or
    • #service mysql start
    • #service mysql stop
  • Set MySQL self-starting service
    • #chkconfig mysql on Set automatic startup
    • # chkconfig --list | grep mysql Check whether automatic startup is set up
  • Modify the configuration file location
    • Copy the current 5.5 version: cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
    • 5.6 version cp /usr/share/mysql/my-default.cnf /etc/my.cnf
  • Modify character set and data storage path
    • View characters Set
      • show variables like 'character%';
      • show variables like '%char%';

      • 
        

        
        

• #character set
  • The default is that both the client and the server use latin1, so the characters will be garbled.
  • Modify the character set and modify the previously copied configuration file. (Detailed follow-up code)
  • MySQL installation location
    • View the installation directory under linuxps -ef|grep mysql

##/var/lib/mysql/Mysql database file storage path/var/lib/mysql/atguigu.cloud.pid/usr/share/mysqlConfiguration file directorymysql.server command and configuration file/usr/binRelated command directorymysqladmin mysqldump and other commands/etc/init.d/mysqlStart and stop related scripts

MySQL installation location

[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# 这一行需要设置字符集
default-character-set=utf8
 
# The MySQL server
[mysqld]
port = 3306

# 还有这三行
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci

socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
 
[mysql]
no-auto-rehash
# 还有这一行
default-character-set=utf8

3. Mysql configuration file

Main configuration file

• Binary log log-bin
  • Master-slave replication

  • 
    

##Error log log-error
• It is turned off by default, recording serious warnings and error messages, detailed information on each startup and shutdown, etc.
Query log log
• It is turned off by default and records query SQL statements. If it is turned on, it will reduce the overall performance of mysql, because recording logs also consumes system resources
Data files
• Two systems
  • windows
    • #You can choose many libraries in the D:\devSoft\MySQLServer5.5\data directory
    Linux:
    • Default path
      • #cd /var/lib/mysql/
      Look at all the libraries in the current system Then go in
      • #ls -1F | grep ^d
  ##frm file
  • : Store table structure**myd file: ** Store table data
  • **myi file: ** Store table index
  How to configure
• Windows: my.ini file
  • Linux: /etc/my.cnf file
4. Introduction to Mysql logical architecture

Overall overview

Compared with other databases, MySQL is a little different. Its architecture can be applied and work well in a variety of different scenarios. Mainly reflected in the architecture of the storage engine,

the plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction
• . This architecture allows the selection of appropriate storage engines based on business needs and actual needs.

  
  

1. Connection layer
• The top layer is some clients and connection services, including local sock communication and large Most communication similar to tcp/ip is implemented based on client/server tools. It mainly completes some connection processing, authorization authentication, and related security solutions. The concept of thread pool is introduced on this layer to provide threads for clients that securely access through authentication. SSL-based secure links can also be implemented on this layer. The server also verifies the operating permissions it has for each client that securely accesses it.
  2. Service layer
  • The second layer architecture mainly completes most of the core service functions, such as SQL interface, and completes cached queries. , SQL analysis and optimization and execution of some built-in functions. All cross-storage engine functions are also implemented in this layer, such as procedures, functions, etc. At this layer, the server will parse the query and create the corresponding internal parse tree, and complete the corresponding optimization such as determining the order of the query table, whether to use indexes, etc., and finally generate the corresponding execution operation. If it is a select statement, the server will also query the internal cache. If the cache space is large enough, it can greatly improve system performance in an environment that handles a large number of read operations.
  3. Engine layer
  • Storage engine layer, the storage engine is really responsible for the storage and extraction of data in MySQL. The server communicates with the server through API Communicate with the storage engine. Different storage engines have different functions, so we can choose according to our actual needs. We will introduce MyISAM and InnoDB later
  4. Storage layer
  • The data storage layer mainly stores data in a file system running on a raw device. above, and complete the interaction with the storage engine.
Query description

First of all, the query process of mysql is roughly:

• mysql client Establish a connection with the mysql server through the protocol, send the query statement, first check the query cache, if it hits, return the result directly, otherwise perform statement parsing
  • There is a series of preprocessing, such as checking whether the statement is written correctly, and then the query Optimize (such as whether to use index scan, terminate early if it is an impossible condition), generate a query plan, then start the query engine, start executing the query, call the API from the underlying storage engine to obtain data, and finally return it to the client. How to store data and how to retrieve data are all related to the storage engine.
  • Then, mysql uses the BTREE index by default, and a general direction is that no matter how you toss sql, at least for now, mysql only uses at most one index in the table.
5. Mysql storage engine

View command

• View what storage engine the current MySQL provides
  • mysql> show engines;
    Look at your MySQL current default storage engine:
  • show variables like '%storage_engine% ';
Default storage engine

• MyISAM and InnoDB

  

  Comparison of the two engines

• Which one should Alibaba and Taobao use?
• • Percona has improved the MySQL database server and is better in function and performance. MySQL has been significantly improved. This version improves the performance of InnoDB under high load conditions, provides DBAs with some very useful performance diagnostic tools, and has more parameters and commands to control server behavior.
  • The company has created a new storage engine called xtradb which can completely replace innodb, and has improved performance and concurrency. To do a better job,
  • Most of Alibaba’s mysql databases actually use the prototype of percona and modify it

Path	Explanation	Remarks

The above is the detailed content of MySQL Advanced Knowledge Architecture Introduction. For more information, please follow other related articles on the PHP Chinese website!