(DBA Road 10) Reading notes of the development optimization and management maintenance of MySQL database

1>mysql configuration file in/etc my.cnf

2) MySQL service is a series of background processes, MySQL database is a series of data directories and data files

MySQL database must be accessed after the MySQL service is started

3) Check the status of the MySQL service:

Netstat-nlp

4) Start/close Service

Cd/usr/bin

./mysqld_safe &

Mysqladmin-uroot shutdown

5) Startup/Shutdown Service for PRM package installation

Service MySQL Start

Service MySQL Restart

Service MySQL Stop

When you start MySQL on the command line, if you do not add--console, the boot shutdown information is not displayed in the interface, but the record

In the installation directory of the data directory, the file name is generally homename.err, you can

This file to view MySQL console information.

6) SQL full name structure Query Language Structured Query language

Classification:

DDL: Data Definition Language: create,drop,alter (involving the structure of a table)

DML: Data Manipulation Language: Insert,delete,update,select

DCL: Data Control Language: Grant,revoke

7) Bring your own database:

INFORMATION_SCHEMA: It mainly stores some database object information in the system, such as user table information, column information, permission information, character set information, partition information, etc.

Cluster: Storage of the system's cluster information

MySQL: Stored user rights information for the system

Test: The system automatically creates a testing database that can be used by any user

8) View Comprehensive table definition information:

Show CREATE TABLE emp \g;

9) Query statement Select the DEFAULT keyword is ASC (ascending)

The difference between having and where

Having is a condition filter for the results of the aggregation.

Where is the filtering of records prior to aggregation

The efficiency of using where is higher than having a

11) Internal and external connections

The inner connection selects only the matching records in two tables

Outer joins to select other mismatched records

Outer joins divided into left and right connections

Left join: Contains all the records in the left table or even the records that do not match it in the right table

Right connection: Contains all records in the right-hand table, even in the left table and no matching records

12) Sub-query

Keywords are: In, not in, =,! =, exists, not exists, etc.

Use = instead of in when the number of records for a subquery is unique

(table joins are used in many cases to refine subqueries)

13) Record union: Used to query two tables of data according to a certain query criteria and then merge together to display

Keywords: Union and UNION ALL

The main difference between union and union all is that

14) Use MySQL to bring your own Help

? Contents is used to display all categories available for querying

Extra? Contents can view further information

15) quickly query a grammar:? Space a plus specific command

16) query binary number (bit saved)

Need to use Bin (), Hex () function

The difference between char and varchar

The length of the char column is fixed to the length declared when the table was created, with a length range of 0-255

The value in the varchar column is a variable-length string, which can be up to 0-65535

The char column removes trailing spaces when retrieved, and varchar preserves these spaces

In fact, in summary, when the length of the stored data is less than the set value when you set Varchar,char, use the length () function to see the actual length of the Varchar,char, you will find that the length of the display is less than the set value, display the specified length of your output data,

How does varchar retrieve the data, which displays all the data you have entered, and does not delete the space at the trailing end of the data?

Char, when retrieved, automatically deletes trailing spaces in the input data.

When you enter data after the deposit of varchar and char is longer than your initial set value will be reported warning, and will truncate your stored data, so that the actual length of the stored data exactly equal to your initial set length.

In summary, for char and varchar, the length of the stored data is not greater than the initial set length, but the amount of RAM requested by varchar to the memory will be adjusted according to the input data size will be greater than the actual number of bytes used, and char unchanged, the number of applications is equal to the number of sets

enum Enum type

An enumeration of 1-255 members requires a byte storage

Two bytes of storage for 255-65535 members

The enum type ignores case and inserts the first value in the enum collection for inserting values that are not within the scope specified by the enum

The enum type only runs from a collection of values to select a single value, not multiple values at a time

) Set

Set can select multiple members at a time and enum can select only one

If the inserted value recurs, take only one time

20) safe equals operator <=> even null can be used

21) The result of any string and null for concat is NULL

INSERT (STR,X,Y,INSTR) function: Starts the string str from the x position, and the substring of y characters long

Replace with a string instr

Truncate and round use the same method, the difference is that round is rounded and truncate is only truncated.

MyISAM is stored as three files on disk:

. frm (save table definition)

MYD (MYData, storing data)

MYI (myindex, storage index)

MyISAM the space after static storage is deleted, the preceding space is saved

Record fixed length of static table

InnoDB how to store tables and indexes

A. Using shared tablespace storage, the table structure of tables created in this way is saved in the. frm file,

Indexes and data are saved in Innodb_data_home_dir and Innodb_data_file_path

You can have more than one file in a defined table space.

B. Using multi-tablespace storage, the table structure of tables created in this way is still saved in the. frm file, but each table's

Data and indexes are stored separately in. ibd. If it is a partitioned table, each partition corresponds to a separate. ibd file,

The file name is "table name + partition name", you can specify the location of each partition's data file when creating the partition.

This is to distribute the IO of the table evenly across multiple disks.

MyISAM recommends replacing variable-length data columns with fixed-length data columns

Memory (MyISAM table collection, delete does not delete data from the MyISAM table) all are treated as char types

InnoDB: It is recommended to use varchar.

) BLOB can hold binary data, text can only hold character data

If you frequently do delete and modify records, perform the Optimize table function periodically to defragment the tables

28) If the database only needs to support the general Chinese, the data volume is very large, the performance requirements are very high

That should choose GBK (only 2 bytes per kanji, and UTF-8 3 bytes)

Can improve performance by reducing disk I/O, Database cache, and network transfer time.

29) If the application mainly handles English characters, only a small number of Chinese characters data is selected UTF-8 better

30) If the database requires a large number of comparisons, sort the fixed-length character set would be better, because faster

31) The advantage of stored procedures and functions is that data processing can be placed in the data

Server, avoid transferring a large number of result sets to the client, retrieving the transfer of data, but it is best not to store procedures and functions

, you should allocate these operations to the application server as much as possible.

31) Use regular expressions in database: XXX REGEXP "mode"

GROUP BY with rollup get more complete packet aggregation information

InnoDB the purpose of using gap Lock: To prevent Phantom Reading, to meet the needs of its reply and replication

The 20th chapter needs to see a few more times

MySQL Error handling options

-F: Enforces SQL, that is, even if there is an error, skips the error to execute the next statement.

-V: Show more information

--show-warning: Display warning message

Myisampack (MyISAM table Compression tool), the table is only readable after compression.

Mysqlchk can be used to check and repair the MyISAM table,

However, InnoDB does not support repairing with Mysqlcheck.

PNS) mysqlhotcopy (MyISAM table hot Backup tool)

It can only be used to back up MyISAM and only run in Linux/unix environments.

Perror (Error code viewing tool)

Usage:

Perror Error code number

Replace (text Replacement tool)

Example:

Overwrite the way to replace

Replace the A1 and B1 in file a with AA1,BB1 respectively;

Replace A1 Aa1 B1 BB1--a

Non-overridden way to replace

Replace A and B in file A with C and D, respectively

Replace a C b d <a

40) Backup

Full backup:

The advantage is keeping up-to-date backups

Incremental backup: Only need to back up the incremental log per day

However, recovery requires full backup plus a backup to all logs before the failure.

The main need to master: Mysqlhotcopy and Manual hot backup method.

The load data infile is the fastest way to load a database.

41) How to handle table corruption of the MyISAM storage engine

A. Repair with MYISAMCHK-R tablename

B. Checking the table for damage by first using the check table name

Use the repair table name to fix the table

There's a little bit behind .... To be continued, in fact my exclusive lock and shared lock There is still confused Ah, and the master and slave .....

A long-to go ...

(DBA Road 10) Reading notes of the development optimization and management maintenance of MySQL database