Mysql DBA Advanced Operations Learning Note-mysql log files in the database

1.MySQL Engine Overview 1.1 What is a storage engine

When we record a video file, it can be converted to different formats such as mp4,avi,wmv, and on the computer's disk will also exist in different types of file system Windows common Ntfs,fat32, exist in the Linux operating system common EXT3,EXT4, Xfs. But the content that we present is the same, the intuitive difference is that the size of the system space is not the same as the clarity level. The database storage engine can also be stored in many ways. No matter what storage engine is used for storage, the data that the user sees is the same. Different engine storage, engine features, the size of the space occupied, read performance may be different.

The most commonly used storage engines for MySQL are: MyISAM and InnoDB.

1.2 Mysqk Storage Engine architecture

MySQL's storage engine is an important part of MySQL database, and MySQL's common table engine is MyISAM and InnoDB two kinds. Each of the MySQL storage engines is used by plugins in MySQL, MySQL can support multiple engines at the same time, and here is the architecture diagram of the MySQL storage engine.

2.MyISAM Engine Introduction 2.1 What is MyISAM engine

The MyISAM engine is the default storage engine (MySQL5.5.5) of the MySQL relational database management system. This MySQL table storage structure extends a lot of useful functionality from the old ISAM code, and in the new version of MySQL, the InnoDB engine has a bit of a gradual substitution for the MyISAM engine due to its referential integrity to things and higher concurrency.

View the storage engine for the MySQL5.5 database.

mysql> select version();+------------+| version()  |+------------+| 5.5.32-log |+------------+1 row in set (0.00 sec)mysql> show databases;+--------------------+| Database   |+--------------------+| information_schema || dfhjdhf|| linzhongniao   || mysql  || nn || performance_schema || school || test   |+--------------------+8 rows in set (0.11 sec)mysql> use linzhongniao;Database changedmysql> show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MySIAM  DEFAULT CHARSET=latin11 row in set (0.00 sec)

Each myisam is stored as three files on disk. The first file name begins with the name of the table, and the extension indicates the file type. frm file stores the table definition. The data file has an extension of. MYD (MYData). The extension of the index file is. MYI (Myindex). Most of the tables in the MySQL system use the MyISAM engine.

[[email protected] ~]# ll /data/3306/data/mysql/user.* -rw-rw----. 1 mysql mysql 10630 2月   8 09:57 /data/3306/data/mysql/user.frm -rw-rw----. 1 mysql mysql  1068 2月   9 18:12 /data/3306/data/mysql/user.MYD -rw-rw----. 1 mysql mysql  2048 2月  10 04:51 /data/3306/data/mysql/user.MYI

2.2 MySQL Service transaction description

Let's start with the business.

(1) Introduction to Database transactions

A transaction is a logical set of SQL statement operations that make up each SQL statement that makes up this set of operations, either full success or failure.

For example: Zhangsan to Lisi transfer 5 dollars, the process is as follows:

A. Remove 5 pieces from the Zhangsan bank card and calculate the test money-5.

B. Put the above 5 dollars into Lisi's account, Lisi received 5 yuan, money+5.

The above-mentioned transfer process, the corresponding SQL statement is

Update zhangsan_account set money=money-5 where name=’zhangsan’;Update lisi_account set money=money+5 where name=’lisi’;

Of the two SQL operations above, the operations in the transaction are either executed or not executed.
This is the atomic nature of the transaction.

MySQL5.5 support for transactional engines: innodb/ndb

NDB is the engine of the MySQL cluster

(2) Four characteristics of the transaction (ACID)

1. atomicity (atomicity)

A transaction is an indivisible unit, and all operations such as SQL in a transaction either occur or do not occur.

2. Consistency (consistency)

The integrity of the data must be consistent before and after a transaction occurs.

3. Isolation (Isolation)

When data is accessed concurrently. An executing transaction is not visible to other sessions until the execution is complete, and the data between multiple concurrent transactions is isolated from each other. Remember the backup parameter--single-transaction?

[[email protected] ~]# mysqldump --help|grep single  turn --lock-all-tables on, unless --single-transaction is  forget to read about --single-transaction below). In all  whole dump. Automatically turns --single-transaction and  --lock-all-tables on, unless --single-transaction is  forget to read about --single-transaction below). In all  ensures that only a single newline is used.  --single-transaction   single transaction. Works ONLY for tables stored in  --single-transaction dump is in process, to ensure asingle-transactionFALSE

4. Persistence (Durability)

Once a transaction is committed, his changes to the data in the database are permanent. If an error transaction is not allowed to be revoked, only the "compensating transaction" can be passed.

(3) Opening of the transaction

The default transaction for a database is auto-committed, that is, it executes a single SQL message. If you want multiple SQL to be executed in a transaction, you need to use a transaction to process it, and when we open a transaction and do not commit, MySQL automatically rolls back the transaction, or we use the rollback command to roll back the transaction manually.

There are two ways that a database opens a transactional command:

First Kind

Begin to open a transaction

Rollback rolling back transactions

Commit COMMIT Transaction

The second Kind

Set autocommit = on disables auto-commit

Set autocommit = OFF turn on auto-commit

Rollback ROLLBACK TRANSACTION, execute SQL statement if there is an unsuccessful rollback transaction

Commit commits the transaction, and so on commits the transaction successfully.

(4) Actual demonstration, verify the role of automatic submission:

1. Let's check to see if the transaction is turned on

mysql> show variables like ‘%auto%‘;+-----------------------------+-------+| Variable_name   | Value |+-----------------------------+-------+| auto_increment_increment| 2 || auto_increment_offset   | 1 || autocommit  | ON|| automatic_sp_privileges | ON|| innodb_autoextend_increment | 8 || innodb_autoinc_lock_mode| 1 || sql_auto_is_null| OFF   |+-----------------------------+-------+7 rows in set (0.00 sec)

2. We see that autocommit is turned on and we set it to off

mysql> set global autocommit=OFF;Query OK, 0 rows affected (0.00 sec)

3. Check if it is off, if it is not turned off, log back in again to view the MySQL

mysql> show variables like ‘%autoco%‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit| OFF   |+---------------+-------+1 row in set (0.00 sec)

4. Take a look at the data from the student table in the Linzhongniao library

mysql> select * from linzhongniao.student;+----+---------------+| id | name  |+----+---------------+|  1 | linzhongniao2 ||  3 | linzhongniao2 ||  5 | linzhongniao2 ||  6 | linzhongniao2 ||  8 | linzhongniao2 || 10 | linzhongniao2 || 11 | linzhongniao2 || 13 | linzhongniao2 || 15 | linzhongniao2 |+----+---------------+9 rows in set (0.00 sec)

5. Insert a piece of data into the student table and view the data

mysql> insert into student(name) values(‘张三‘);Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+---------------+| id | name  |+----+---------------+|  1 | linzhongniao2 ||  3 | linzhongniao2 ||  5 | linzhongniao2 ||  6 | linzhongniao2 ||  8 | linzhongniao2 || 10 | linzhongniao2 || 11 | linzhongniao2 || 13 | linzhongniao2 || 15 | linzhongniao2 || 17 | 张三  |+----+---------------+10 rows in set (0.00 sec)

6. Do not commit the transaction quit MySQL re-login MySQL view data

We did not commit the transaction and found that the data we had just inserted was gone. Because no commit transaction is inserted, the data is written to the disk without writing to the memory.

mysql> use linzhongniao;Database changedmysql> mysql> select * from student;+----+---------------+| id | name  |+----+---------------+|  1 | linzhongniao2 ||  3 | linzhongniao2 ||  5 | linzhongniao2 ||  6 | linzhongniao2 ||  8 | linzhongniao2 || 10 | linzhongniao2 || 11 | linzhongniao2 || 13 | linzhongniao2 || 15 | linzhongniao2 |+----+---------------+9 rows in set (0.00 sec)

7. Insert the data and commit the transaction with commit

If the autocommit transaction is not turned on to commit the transaction

mysql> select * from student;+----+---------------+| id | name  |+----+---------------+|  1 | linzhongniao2 ||  3 | linzhongniao2 ||  5 | linzhongniao2 ||  6 | linzhongniao2 ||  8 | linzhongniao2 || 10 | linzhongniao2 || 11 | linzhongniao2 || 13 | linzhongniao2 || 15 | linzhongniao2 |+----+---------------+9 rows in set (0.00 sec)mysql> insert into student(name) values(‘张三‘);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)

8. Log back in to MySQL to see if the data is plugged

mysql> use linzhongniao;Database changedmysql> select * from student;+----+---------------+| id | name  |+----+---------------+|  1 | linzhongniao2 ||  3 | linzhongniao2 ||  5 | linzhongniao2 ||  6 | linzhongniao2 ||  8 | linzhongniao2 || 10 | linzhongniao2 || 11 | linzhongniao2 || 13 | linzhongniao2 || 15 | linzhongniao2 || 19 | 张三  |+----+---------------+10 rows in set (0.00 sec)

Features of the 2.3 MyISAM engine

1. Transactions are not supported (transactions refer to a logical set of operations.) The units that make up this set of data are either completely successful or fail completely.

2. Table-level locking (locks the entire table when the database is updated): Its locking mechanism is a table-level index, although it can make the implementation cost of locking a little but also greatly reduces its concurrency performance.

3. Read and write to each other: not only in the time of writing to block the read, MyISAM will also block the write while reading, but the read itself does not block the other read.

4. Only the index is cached: MyISAM can significantly improve access performance by Key_buffer_size cache indexes to reduce disk IO, but this buffer caches the index only and does not cache the data.

[[email protected] ~]# grep key_buffer /data/3306/my.cnf key_buffer_size = 16M

5. Read faster and consume less resources

6. Foreign KEY constraints are not supported, but full-text indexing is supported

The 7.MyISAM engine is the default storage engine in the MySQL5.5.5 zone.

Production scenarios used by the 2.4 MyISAM engine

1. Businesses that do not require transactional support (such as loading will not work, recharge payments).

2. Generally for reading data more than the site application. Read and write are often inappropriate, read more or write more are suitable.

3. Read-write concurrent access to a relatively small number of services (read-only write high concurrency is also possible) (locking mechanism issues).

4. To read the main business, such as: Www,blog image information Database, user database, commodity library and other business.

5. The data modifies a relatively small number of businesses (clogging problems).

6. The requirements for data consistency are not very high business.

7. The machine with poor hardware resources can be used MyISAM.

Summary: The operation of a single database can be used MyISAM, the so-called single is as far as possible to read, or write pure (insert,update,delete) and so on.

2.5 MyISAM Engine Tuning essentials

1. Set the appropriate index (caching mechanism).

2. Adjust the read-write priority to ensure that important operations are prioritized based on actual requirements.

3. Enable deferred insertion improves bulk write performance (reduces write frequency and writes as many data as possible).

4. Try to keep the insert data written to the tail to reduce clogging.

5. Decompose the large operation to reduce the blockage time of the individual operation.

6. Reduce the number of concurrency (reduce access to MySQL), some high concurrency scenarios through the application of queuing queue mechanism.

7. For relatively static (infrequently changing) database data, the full use of the query cache or memcached cache service can greatly improve access efficiency.

[[email protected] ~]# grep query /data/3306/my.cnf query_cache_size = 2M  缓存的空间呢大小query_cache_limit = 1M  缓存的限制query_cache_min_res_unit = 2k 最小的缓存的对象不要搞太大long_query_time = 1

The count of 8.MyISAM is particularly efficient when full-table scans, and count with other conditions requires actual access.

9. Use the MyISAM engine from the library with the master-slave copy of the main library using InnoDB. (not recommended).

3.InnoDB Engine Introduction 3.1 What is the InnoDB engine?

The InnoDB engine makes the MySQL database another important storage engine. The advantage of the InnoDB engine is that it supports acid-compatible transactions (like PostgreSQL), as well as parameter integrity (that is, support for foreign keys). Oracle acquired Innobase.innobase in October 2005 with a dual authentication license. It uses the GNU distribution and also allows other groups that want to combine InnoDB with commercial software to be licensed.

More reference refman-5.5-en.html-chapter/storage-engines.html

The default storage engine for the database after MySQL5.5.5 is InnoDB.

Example:

mysql> select version();+------------+| version()  |+------------+| 5.5.32-log |+------------+1 row in set (0.00 sec)mysql> use linzhongniao;Database changedmysql> show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf81 row in set (0.01 sec)

Tip: InnoDB's file format is only ibdata1, it shares table space, all the data is stored here and then sorted.

[[email protected] ~]# ll /data/3306/data/总用量 143396drwx------. 2 mysql mysql  4096 2月   8 19:17 dfhjdhf-rw-rw----. 1 mysql mysql 134217728 2月  14 21:29 ibdata1-rw-rw----. 1 mysql mysql   4194304 2月  15 22:18 ib_logfile0-rw-rw----. 1 mysql mysql   4194304 2月   8 09:59 ib_logfile1-rw-rw----. 1 mysql mysql   4194304 2月   8 09:59 ib_logfile2drwx------. 2 mysql mysql  4096 2月  12 14:02 linzhongniao-rw-rw----. 1 mysql mysql79 2月  15 22:18 master.infodrwx------. 2 mysql root   4096 2月   8 09:57 mysql-rw-rw----. 1 mysql mysql   616 2月  13 03:24 mysql.logdrwx------. 2 mysql mysql  4096 2月  13 01:11 nndrwx------. 2 mysql mysql  4096 2月   8 09:57 performance_schemadrwx------. 2 mysql mysql  4096 2月   8 18:21 schooldrwx------. 2 mysql root   4096 2月   8 09:57 test

Features of the 3.2 InnoDB engine

1. Support transactions: Support 4 transaction Isolation levels, support multi-version read.

2. Row-level locking (usually locks the current row when updating); Through indexing, full table scans will still be table locks, paying attention to the effects of intermittent locks.

3. Read-write blocking is related to the transaction isolation level.

4. Has very efficient cache features: Can cache the index, can also cache data.

5. The entire table and primary key are stored in cluster form, forming a tree of balance.

6. All Secondary index will hold the primary key information.

7. Support partition, table space, similar to Oracle database.

8. Support for FOREIGN KEY constraints, MySQL5.5 previously did not support full-text indexing, later supported.

9. Compared with the MyISAM engine, the hardware resource requirements are higher.

Summary: Support transactions, row-level locks, support for foreign keys

Production business scenarios used by the 3.3 InnoDB engine

1. Business that requires transaction support (with good transactional characteristics).

2. Row-level locking is good for high concurrency, but you need to ensure that queries are done by indexing.

3. Data read and write and update more frequent scenes, such as Bbs,sns, Weibo and so on.

4. Data consistency requires a high level of service such as: top-up transfer, bank card transfer.

5. Hardware Device memory is large, can take advantage of InnoDB better cache capacity to improve memory utilization, reduce disk IO as much as possible.

6. Compared to the MyISAM engine, the InnoDB engine consumes more resources and is faster than the MyISAM engine.

InnoDB configuration file for the environment

[[email protected] ~]# grep -i innodb /data/3306/my.cnf #default_table_type = InnoDB#InnoDB_sort_buffer_size = 1M#InnoDB_max_sort_file_size = 10G#InnoDB_max_extra_sort_file_size = 10G#InnoDB_repair_threads = 1#InnoDB_recoverinnodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0#innodb_file_per_table#innodb_data_home_dir = /data/3306/data/xxx#innodb_log_group_home_dir = /data/3306/data/xxx共享表空间对应物理数据文件[[email protected] ~]# ll /data/3306/data/ibdata1 -rw-rw----. 1 mysql mysql 134217728 2月  14 21:29 /data/3306/data/ibdata1

Physical data file corresponding to a stand-alone table space

#innodb_file_per_table#innodb_data_home_dir = /data/3306/data/xxxinnodb_data_file_path = ibdata1:128M:autoextend

3.4 InnoDB Engine Tuning

1. The primary key is as small as possible to avoid too much space burden on the secondary index.

2. Avoid full table scans, because table locks are used.

3. Cache all indexes and data as much as possible, improve response speed, and reduce disk IO consumption.

4 When large batches of small inserts, try to control their own transactions instead of using autocommit automatic submission. There are switches to control the way the submissions are submitted.

5. Set innodb_flush_log_at_trx_commit the parameter values reasonably, do not over-pursue security.

If innodb_flush_log_at_trx_commit the value is 0,log buffer, the log file is written to disk every second, and no action is taken when committing the transaction.

innodb_flush_log_at_trx_commit=0Every time each thing commits, each second, writes the data of the transaction log buffer to the log file, and flushes the log file data to the disk, equals 0 is the best performance, the same security is the worst, when the system is down, will lose one second of data.

innodb_flush_log_at_trx_commit = 1Each transaction commits, writes the transaction log from the cache to the log file, and flushes the log file's data to disk.

innodb_flush_log_at_trx_commit = 2When each transaction commits, it writes the transaction log data from the buffer to the log file, and refreshes the log file every second.

6. Avoid the primary key update, as this will result in a lot of data movement.

4. Special instructions for the MySQL engine

The above three storage engines for MyISAM, InnoDB and NDB are the most commonly used storage engines, especially the first two.

5. How can I determine which engines are available on the MySQL server?

You can use the Display engine command in MySQL to get a list of available engines

Mysql> Show engines\g*************************** 1. Row *************************** Engine:mrg_myisam support:yes comment:collection of identical MYISAM tablesTransaction S:no Xa:no savepoints:no*************************** 2. Row *************************** Engine:innodb support:default comment:supports transactions, Row-level locking, and fo Reign Keystransactions:yes Xa:yes savepoints:yes*************************** 3.  Row *************************** engine:federated support:no comment:federated MySQL Storage Enginetransactions:null Xa:null savepoints:null*************************** 4. Row *************************** engine:blackhole support:yes Comment:/dev/null storage Engine (anything write to I T disappears) Transactions:no Xa:no savepoints:no*************************** 5. Row *************************** Engine:myisam support:yes comment:myisam storage enginetransactions:no xa:no SAVEP oints:no*************************** 6. Row ******Engine:memory Support:yes comment:hash based, stored in MEMORY, useful for temporary Tablestrans Actions:no Xa:no savepoints:no*************************** 7. Row *************************** engine:performance_schema support:yes comment:performance schematransactions:no XA: NO savepoints:no*************************** 8.  Row *************************** engine:csv support:yes comment:csv storage enginetransactions:no xa:no Savepoints: NO8 rows in Set (0.00 sec)

Description of the my.cnf corresponding parameters of the 6.MySQL engine

InnoDB Engine Key parameters

[[email protected] 3306]# grep -i innodb /data/3306/my.cnf #default_table_type = InnoDB#InnoDB_sort_buffer_size = 1M#InnoDB_max_sort_file_size = 10G#InnoDB_max_extra_sort_file_size = 10G#InnoDB_repair_threads = 1#InnoDB_recoverinnodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 2048Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0#innodb_file_per_table#innodb_data_home_dir = /data/3306/data#innodb_log_group_home_dir = /data/3306/data

The most important of these parameters is innodb_buffer_pool_size that the cache will put the data in here. More content we can look at official documents, also can see innodb-heavy this inside have. In the MySQL installation path, my MySQL installation path is/usr/local/mysql/.

[[email protected] ~]# ll /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf -rw-r--r--. 1 root root 19791 2月  16 02:26 /usr/local/mysql/support-files/my-innodb-heavy-4G.cnf

7. How to batch change MySQL engine in production environment

It is recommended to use SED to transform the backup content engine, and do not forget to modify the MY.CNF to support and efficiently use the corresponding engine.

(1) method one MySQL command statement modification

Create post-engine changes, more than 5.0

alter table student ENGINE = MyISAM;alter table student ENGINE = InnoDB;

Demo: Change a table of the storage engine, we take the student table as an example. The storage engine for bulk modification tables can list the tables and then log in to the MySQL database for batch modifications.
My MySQL engine is InnoDB, now the InnoDB engine is changed to MyISAM engine.

mysql> use linzhongniao;Database changedmysql> show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> alter table student ENGINE = MyISAM;Query OK, 10 rows affected (0.31 sec)Records: 10  Duplicates: 0  Warnings: 0mysql> show create table student\G*************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf81 row in set (0.00 sec)

(2) Method 2 using SED to replace the contents of the backup engine

The data is exported to a single instance, then replaced with the SED command, and the data is then directed in. Suitable for smaller data volumes if the amount of data is too general to have a problem.

Mysqldump > linzhongniao.sqlSed –e ‘s#MyISAM#Innodb#g’linzhongniao.sql > linzhongniao2.sqlMysql < linzhongniao2.sql

(3) Method 3, modify with the mysql_convert_table_format command

[[email protected] bin]# mysql_convert_table_format --user=root --password=123456 --socket=/data/3306/mysql.sock --engin student[[email protected] ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use linzhongniao;show create table student\G" *************************** 1. row ***************************   Table: studentCreate Table: CREATE TABLE `student` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

Tip: The mysql_convert_table_format following error occurs when you execute a command

Can‘t locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/mysql/bin/mysql_convert_table_format line 20.BEGIN failed--compilation aborted at /usr/local/mysql/bin/mysql_convert_table_format line 20.

This is because the system does not press the install DBI component.

DBI (Database Interface) is the interface for Perl connected databases. It is the best way to connect to a database in Perl, and he supports most databases, including ORCAL,SYBASE,MYSQL,DB2. Yum installs Perl-dbd-mysql.

Mysql DBA Advanced Operations Learning Note-mysql log files in the database