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

One: comparison between MySQL and other databases

1) functional comparison

As a mature database system management system, it now has the related functions of a general database management system.

2) comparison of ease of use

For ordinary users, the operation is relatively simple and easy to use. In terms of installation, the MySQL installation package is only about 100MB, and the installation is much easier than Oracl. The database is much simpler, no matter through the compiled binary distribution package or source code compilation and installation.

3) performance comparison

MySQL performs very well, and in other commercial general-purpose database systems, only Oracle database can be compared.

4 Reliability) there are three principles in the development process of MySQL database, simple, efficient and reliable.

Second: MySQL main scene

1) web website system

MySQ; is open source and can be used for free

2) logging system

MySQL database insertion and query are very efficient, when using MyISAM storage, the two can not lock each other, to achieve a very efficient concurrency ability, for example, login log, operation log, are very application scenarios.

3) data warehouse system

With the growth of the amount of data, the need for more and more space, so that the statistical analysis of data becomes lower and lower.

High-performance hosts are used to improve computing, and high-end storage devices are used to improve I / O performance. 2 is to improve performance computing power by copying data and using large-capacity hard disks, but the storage space is limited. 3 data can be stored by horizontal split. MySQL has great advantages. Through simple replication function, data can be copied from one host to another.

Three: composition of physical files

Log file: error log query log slow query log transaction log

Logging changes during the operation of the mysql database is equivalent to recording the connection status of mysql database connection customers.

The execution of SQL statements and error messages, etc., when the data is accidentally damaged, you can see the reason through the error log, and the log for data recovery.

Error log: enabled by default in mysql, error log is stored in the mysql database directory

The error log information can be configured by yourself, and the error log can be defined through log-error and storage location log-warninge

Whether to define the warning message in the error log

The server runs the process error message, and the event scheduler runs an event generated when starting the server process from the server.

Therefore, mysql provides two sets of commands to view the system settings and running status.

1. View system settings:

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

SHOW VARIABLES:shows the values of MySQL system variables.

2. Running status:

SHOW [GLOBAL | SESSION] STATUS [like_or_where]

SHOW STATUS:provides server status information.

How to modify system configuration

Method 1: configuration file setting my.cnf

For example, binlog_cache_size = 1m

Method 2:set globalbinlog_cache_size = 1048576

Use set to change the security level to 2

3: view the mysql version

Mysql-V mysql > status; select version ()

4 error log status

Note log_error defines the error file path

Log__error_verbosity security level

View error log

Tail / usr/local/mysql/data/mysqld.err

Two: delete the error log

The database administrator can delete the error log from a long time ago. To ensure the hard disk space of the server, you can use mysqladmin-u root flush-logs FLUSH LOGS to open a new error log.

Binary log

It is mainly used to record mysql statements that modify data or may cause data changes, and record the time when the statement occurs, the length of execution, the operation of data, and so on. The general upper limit of volume is 1G.

First of all, we can see that if the binary system is turned off, OFF needs to be configured in vim / etc/my.conf.

Bin_log=/usr/local/mysql/data/log_bin is enabled

Generally set to 512 or 1 G reaches the upper limit, mysql re-creates a log to start recording

In order to ensure mysql security, the same thing will not be recorded separately in two binlog.

Transaction log

The transaction log table submits a task for a thing, and either executes or does not execute multiple statements contained in the transaction.

Non-transaction table does not support this operation. If an error is encountered in the process, the statement executes successfully before the error, and then the execution is not successful.

Specify the binary log type

There are three main ways of Mysql replication based on SQL statement replication mixed mode replication based on row replication

Third, select to open binary log by editing log-bin in my.conf

The DIR parameter specifies the binary file storage path, and the filename parameter specifies the file name of the secondary system, in the form of filename.number 000001. 000002 and so on, each time you restart the mysql service or run flush logs;, a new binary is generated.

View binary file information

Command to view binary log information:

Syntax format: SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

# View all binary information

Mysql > show binlog events\ G

# View the binary information of the specified log

Mysql > show binlog events in 'mysql-bin.000013'\ G

Delete binary log information

If the binaries are not cleaned for a long time, the disk space will be affected. After deletion, the database may fail to recover, so delete the binaries first and back up the database first.

Grammatical form:

Mysql > PURGE {BINARY | MASTER} LOGS {TO 'log_name' | BEFORE datetime_expr}

Where TO 'log_name' means to delete all other files before this file, or you can use BEFORE datetime_expr to specify when the binary files before this file have been deleted.

You can also specify a time to delete the binary log

View the binary log from the command line:

Since you cannot directly open and view the binary log using methods such as cat, you must use the mysqlbinlog command. However, it is recommended that you do not use this to open binary log files in use when you are performing mysql read and write operations

Delete all binary logs

3 things log

Can help improve the efficiency of transactions. Using the transaction log, the storage engine only needs to modify the memory copy of the table when it modifies the data, and then records the modification behavior in the transaction log on the hard disk, instead of persisting the modified data to the disk every time. Mysql provides a variety of storage engines by default, which you can view through the following:

See what storage engine your mysql now provides:

Mysql > show engines

Create table library name. Table name engine = innodb

This allows you to change the engine of the table to the innodb engine.

You can also change it after creating the table with the following statement:

Alter table library name. Table name engine = innodb

Whether innodb synchronizes the log from the buffer to the file when the transaction commits, when this value is 1 (the default), when each transaction commits, the log buffer is written to the log file, and the log file is refreshed by disk operation. The performance will be very poor, resulting in a large number of disk I and O, but this is the safest way.

The concept of brushing and writing

A setting of 1 is of course the safest, but the performance page is the worst (compared to the other two parameters, but not unacceptable). If the requirements for data consistency and integrity are not high, you can set it to 2. If you only want the most performance, such as a log server with high concurrent writes, set it to 0 to get higher performance.

Slow query log

Record the time of the statement execution, the time consumed by the execution, the execution user, connect to the host and other related information.

The function of slow query log:

Slow query logs are used to record queries that have been executed for more than a specified time. Through the slow query log, you can find out which query statements are inefficient for optimization. It is generally recommended to turn it on, which has little impact on server performance, but can record queries that have been executed on the mysql server for a long time.

Start and set the query log

Slow_query_log: off off status on on statu

Storage location of slow_query_log_file slow query log

Long_query_time option to set a time value in seconds, which can be accurate to microseconds. If the query time exceeds this time value (the default is 10 seconds), the query statement will be recorded in the slow query log. If set to 0, all queries will be recorded.

If you do not specify a storage path, the slow log is stored in the data file of the mysql database by default. If you do not specify a file name, the default file name is hostname-slow.log.

Modify the my.cnf file:

Method 2: define it directly by logging in to the mysql server as follows:

Mysql > set global slow_query_log=1; # enable slow log

Query OK, 0 rowsaffected (0.35 sec)

Mysql > set session long_query_time=0.0001; # change time (in the current session, exit is reset)

Query OK, 0 rowsaffected (0.00 sec)

Mysql > set global long_query_time=0.0001; # change time (globally, restart the service to reset)

Mysql > SHOW VARIABLES LIKE 'long%'; # query definition time

View slow query log

Mysql > use mysql

Mysql > selectuser,host from user where user= "root"

3) data document

In MySQL, every database has a folder named after the database under the defined (or default) data directory, which is used to store various table data files in the database.

Mysql > show create table table name

Create table library name. The table name engine = innodb; allows you to change the table engine to the innodb engine.

Log in to mysql, create a database such as testdb, and create a table in the database

Create a test table tb2 with the myisam storage engine

.frm "file

The meta information related to the table is stored in the ".frm" file, including the definition information of the table structure, and so on. Regardless of the storage engine (the two commonly used storage engines in MySQL are MyISAM and InnoDB), each table has a ".frm" file named after the table.

MyISAM database table file: .MYD file: table data file; .MYI file: index file

MYD "file

The ".MYD" file is dedicated to the MyISAM storage engine and stores data from MyISAM tables. Each MyISAM table has a ".MYD" file corresponding to it, which is also stored in the folder of the database to which it belongs, along with the ".frm" file.

MYI "file

The ".MYI" file is also dedicated to the MyISAM storage engine and mainly stores the index-related information of the MyISAM table.

InnoDB uses table space (tablespace) to manage data and store table data and indexes.

.ibd file: a single table space file, each table uses a table space file (file per table), to hold the user database table data and indexes.

.ibd "files and ibdata files

These two kinds of files are files for storing Innodb data. The reason why there are two kinds of files to store Innodb data (including indexes) is that the data storage method of Innodb can be configured to decide whether to use shared table space to store data or exclusive table space to store data.

Both shared and exclusive tablespaces are based on the way data is stored.

Shared tablespaces: all the table data and index files in a database are placed in one file.

Exclusive tablespace: each table will be generated and stored as a separate file, with each table having a .frm table description file and an .ibd file. This file includes the data contents of a single table and the contents of the index.

All the data and indexes are stored in one file, and multiple tables and indexes are mixed in the tablespace, so there will be a lot of gaps in the tablespace after a large number of deletions for a table, especially for statistical analysis. applications such as daily value systems are the least suitable for shared tablespaces.

Independent tablespaces:

Advantages:

1. Each table has its own independent tablespace.

2. The data and indexes of each table will be stored in its own tablespace.

3. It is possible to move a single table in different databases.

4. Space can be recycled.

View the tablespace management type of the current database

ON stands for independent tablespace management, and OFF stands for shared tablespace management

Innodb shared tablespace configuration:

768, 6412.

Innodb_data_home_dir = "/ path/" the directory where the database file is stored

Innodb_log_group_home_dir = "/ path/" log storage directory

Innodb_data_file_path=ibdata1:10M:autoextend sets a data file (shared data file) with an expandable size of 10MB named ibdata1. The location of the file is not given, so the default is in the data directory of MySQL.

Innodb_file_per_table=1 | 0 / 1: use exclusive tablespace, 0: use shared tablespace

Set 6400 pages in the / etc/my.cnf file and the current ibdata1 is 768 pages

Need to calculate 768amp 64mm 12

Start mysql, successful!

Calculation formula: 64pages is equivalent to 1m and 1page is 16KB

Call stored procedure

View the number of rows in the table:

For INNODB, directly use the command show table status to view the tablespace occupancy of a table

If the space occupied by each table in the MySQL database and the number of rows recorded by the table, you can open the information_schema database of MySQL. There is a TABLES table in the library, and the main fields of this table are:

TABLE_SCHEMA: database name

TABLE_NAME: table name

ENGINE: storage engine used

TABLE_ROWS: number of records

DATA_LENGTH: data siz

INDEX_LENGTH: index size

Master.info file

It includes Master host address, connection user, connection password, connection port, current log location, log location that has been read, and other information.

Elay log and relay log index

The mysql-relay-bin.xxxxxn file is used to store the Binary Log information read by the Master thread on the Slave side, and then the SQL thread on the Slave side reads and parses the corresponding log information from the relay log, converts it into SQL statements executed by Master, and then applies it on the Slave side.

The function of the mysql-relay-bin.index file is similar to mysql-bin.index, which is also the absolute path to the location where the log is stored, except that what he records is not Binary Log, but Relay Log.

3) relay-log.info file:

Similar to master.info, it stores messages that are written to the local relay log through the Slave's I _ map O thread.

Interest. The SQL thread on the Slave side and some administrative operations can obtain the current replication information at any time

Socket files are also available in the Unix/Linux environment. In the Unix/Linux environment, users can connect to MySQL directly using Unix Socket without going through the TCP/IP network.

There are two ways to connect mysql, and the common one is tcp.

Mysql-h mysql host ip-uroot-pxxx

Mysql-S / path / mysql.sock

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.