MySQL Basics Summary

Server: mysqld client: MySQL

Common Management Commands : (Note: All commands to be executed on the server end with;

2. Login: Mysql–uuser–ppassword–hhost Specify the user, password and MySQL host can log in, you can also create a. my.cnf configuration file in the home directory, the user name and password host configuration in the inside: Assuming root password is Mypass

   [email protected] ~]# cat << ' EOF ' >. my.cnf
   > [Client]
   > user = root
   > Password = mypass
   > host = localhost
   > EOF

   Of course, if the landing on the computer or Unix sock the fastest way, the least resource consumption, because this is implemented at the bottom of the kernel, if the IP address to log in will also need to send to the kernel TCP/IP protocol stack processing.

4. Add Password to User:

   1. Under MySQL command line execute SET passwrod for ' user ' @ ' host ' =password (' PASSWORD ');

   2. Shell under the Mysqladm modified: mysqladm–uroot–hlocalhost password ' mypass ' –predhat (send [email protected] Password changed from Redhat to Mypass)

   3. Modify MySQL library user table directly: UPDATE mysql.user SET password=password (' Redhat ') WHERE user= ' root ' and host= ' localhost ';

6. Create and delete users:

   1. CREATE USER ' root ' @ ' 172.16.100.100 ' identified by ' mypass ';

   2. DROP USER ' root ' @ ' 172.16.100.100 ';

8. Forget the administrator password how to complete:

   1. After the MYSQLD service is stopped, mysqld_safe–-skip-grant (skip Authorization form) –-skip-networking (do not allow remote access to MySQL to ensure security), after this method is started, no password after login to change password

10. User authorization and cancellation authorization:

    1. GRANT Select,update on Db_name.tb_name to ' user ' @ ' host ' [identified by PASSWORD ]; FLUSH privileges;

    2. REVOKE Select,update on Db_name.tb_name from USER;

12. View authorized user information: Show Grants\g

14. View MySQL thread information: show Processlist;

16. View Table index information: show INDEXES from Tb_name;

18. Mysqld–help–verbose can view the parameters that can be used in the MySQL configuration file

20. Various shows: Show DATABASES; SHOW TABLES; DESC Tb_name; SHOW ERRORS; SHOW STATUS; SHOW BINARY LOGS; SHOW BINLOG EVENTS;

22. Investigate detailed property information for a table in a library: Show Table STATUS Rlike '%user% '\g (with rlike to match regular expressions, \g in portrait mode, when columns compare more often, \ G can be sent directly to the server, no need to add a semicolon)

24. SHOW MASTER STATUS; To view the position of the current binary log

26. SHOW GLOBAL | SESSION VARIABLES like | Rlike '%log% '; View server variable Information

28. SET global.var_name=VALUE;

30. SET @ @GLOBAL. var_name=VALUE;

32. SELECT DATABASE (); SELECT last_insert_id ();

34. SHOW CREATE TABLE User; SHOW ENGINE INNODB STATUS;

36. Help Contents; –> SHOW administrations; ....

38. Mysql> Source/tmp/backup.sql | Mysql–uroot–pmypass </tmp/backup.sql

40. Mysql-uroot-predhat-e ' SHOW DATABASES; '

Tips: Since the cache is a hash of the query statement as a key store, and the hash is case-sensitive, so in order to improve the cache hit rate, the SQL statement to maintain a certain style, such as the command keyword all uppercase. Develop good programming habits to benefit people.

about the storage engine : MySQL is a plug-in storage engine that can be switched on-demand at any time ( storage engine can understand the logical data of the database and the binary data on disk middleware, it completes the two data formats between the conversion process )

MyISAM: is an improved version of ISAM, does not support hot spares, supports Win Bei, supports B-tree indexes, does not support T-tree indexes and hash indexes, supports full-text search (Full-text search Indexes: can be indexed to all data on a field), etc., and InnoDB The biggest difference is not support transactions (Transaction), support table-level locks, innodb support row-level locks, support delay Update index key (delay key update )

MyISAM data file Storage form: Db_name. MYI index file, db_name. MYD data file, db_name.frm framwork table definition file, storing table structure information, etc.

Suitable for Data Warehouse, OLAP (online analytical processing), write less, query many scenes, poor support for big data scenarios, large database (more than 500G) when the crash, it is difficult to recover, MariaDB enable Aira engine, is an improved MyISAM that provides a safe crash recovery.

InnoDB: InnoDB company developed the support business and foreign key storage engine, also absorbed the non-dead, Google and other companies Daniel's contribution, after being acquired by the fake bone man on the road of commercialization, but the fake bones of the people also to InnoDB did n many improvements and optimization.

InnoDB-supported row-level locks are not absolute and are locked for the entire table when the row being affected by the executed statement is not deterministic, such as the SELECT * from Db_name.tb_name WHERE name is like '%mysql% ';

InnoDB Support for foreign key, transaction, MVCC (multi-version Concurrency Control) multi-version concurrency control, clustered index ( index and table data are put together and arranged in a certain order, is ordered data, MyISAM storage is heap data, heap, does not support clustered index ), Gap Lock (gap Lock: Gap locks, lock a range, but does not include the record itself ), secondary index, adaptive hash index, support hot spare, row-level lock

Data file storage: ibdata1 tablespace file, it is recommended to turn on innodb_file_per_table = 1, one table space per table, TB_NAME.IBD, otherwise all tables in one file is not easy to manage, but a separate tablespace file stores only the table's data, Information such as indexing and inserting buffers, the rest of the information is stored in the default table space. TB_NAME.FRM Table Definition File

memory storage engine used when creating temporary tables

blackhole Black Hole, in the replication structure, in order to reduce the master pressure and the number of binary log copies, only one copy to one slave, the other slave all to this slave copy binlog, and this slave does not store data, Only provide binlog source, apply relaylog when all throw to blackhole to avoid unnecessary IO.

CSV can export data to CSV text format, you can exchange data between different databases

ARCHIVE 5.5 support index, suitable for storing logs, such as time series data collection, only support INSERT, select, Support good compression function

fedrated:

A storage engine that can access data on a remote server, Fedratedx

mrg_myisam merge MySQL has supported table partitioning, which is not very useful.

A storage engine capable of merging multiple MyISAM tables,

NDB: Cluster storage engine, dedicated to MySQL Cluster

Third-party storage engines,

OLTP class, online transaction processing

XtraDB, InnoDB's enhanced version

PBXT: Support transactions, community inactivity, MariaDB 5.5.32 and no longer supported by native

Tokudb is very cool, already open source, MySQL plug-in, can be used directly, support the fractal tree index structure, suitable for the analysis of the need to insert a large number of data-based scenarios

Diagram database, with complex connections

A column database that is best suited for storing massive amounts of data,

Column Storage Engine:

Infobright: The most famous, really used in production environments, dozens of TB, support compression, if used requires a custom MySQL server

Infinidb

MonetDB

Luciddb

Community Storage Engine

Aria

Groona: Full-text indexing engine

Qqgraph: Support diagram operation, Open query development

Sphinxse: The Sphinx has been adopted by the Maria community, MariaDB 5.5.32+

Spider: Support Shard (sharding)

Vpformysql: Supports vertical partitioning, vertical partition, spider author writes

About MySQL logs:

Binary log : Records statements that cause changes in database data:

• • SHOW BINARY | MASTER LOGS;

  • SHOW MASTER STATUS;

  • SHOW BINLOG EVENTS [in ' Log_name '] [from position] [LIMIT N] Offset N statement

  • FLUSH LOGS; Generally only scrolling binlog and Relaylog, each time the restart of the MySQL service will also cause binlog scrolling, log size up to the upper limit will automatically scroll, the general default 1G

  • Ways to clear Binlog security: PURGE BINARY | MASTER LOGS to ' log_name ' | Before ' 2012-10-10 12:09:09 ';

  • Log-bin =/path/to/some_log_file

  • Expire_log_days = 100

  • Sync_binlog when a transaction commits, whether it is written from the cache to disk, recommended to be turned on, slightly affected by performance, but more reliable, worthful

  • mysqlbinlog–end-position=234 mysql_bin.000012 >/tmp/1.sql

  • Mysql–uroot–pmypass </tmp/1.sql

  • When using binary to restore the database, to turn off the binary log, or repeat the operation, the increase in binary space

Innodb_flush_log_at_trx_commit = 0|1|2 Log brush writes to disk frequency, 0--1 seconds, 1--write on every transaction commit, 2--0+1

Slow query log : Suggest open, record execution time long query process to facilitate analysis optimization

• Slow_query_log defines whether to turn on slow query logging

• Slow_query_log_file define slow query log file storage location

• Long_query_time definition queries up to how many are considered slow queries

• Slow query Duration: From query initiation to query completion, regardless of the cause, the wall clock time, not CPU time, is counted.

Error log

• Record information in the server startup and shutdown process

• Log error messages from the server running

• Information generated when the event scheduler runs an event

• Information generated from the server process when it is started from the server

query log : Generally not recommended to open an account, consumption of server resources, impact performance

transaction log : Convert unordered io to ordered IO, not too large, if too large a server from the crash is the recovery will consume a large amount of time

Relay Log : Used to replicate the scene, slave from the master copied from the Binlog first placed in the trunk log, and then applied to the local

About InnoDB, about transactions : (Refer to http://javabkb.iteye.com/blog/1441197 content)

• A transaction naturally supports acid

  • Atomicity atomicity: All operations in the entire transaction are either complete or not complete, and rollback if an error occurs during execution, the data is the same as the transaction that did not occur

  • Consistency consistency

  • Isolation isolation

  • Durability Persistence

• Redo Log : instance and media failure, redo log files can come in handy, such as the database power down, the InnoDB storage engine will use redo log back to the time before power down, so as to ensure the integrity of the data. Parameter innodb_log_file_size Specifies the size of the redo log file,innodb_log_file_in_group Specifies the number of redo log files in the log filegroup, which defaults to 2 . Innodb_mirrored_log_groups Specifies the number of log mirror filegroups, which defaults to 1, representing only one log filegroup, without mirroring;Innodb_log_group_home_dir Specifies the path of the log file group, which is by default in the database path.
  Differences between binary and redo logs: First, the binary log records all mysql-related log records that cause data changes, including InnoDB, MyISAM, heap, and other storage engine logs. While the InnoDB storage engine redo log stores only the transaction log about itself, the second content is different, regardless of whether the binary log file is formatted as statement or row, or mixed, it records the specific operation of a transaction. The InnoDB storage engine's redo log files record the physical condition of each page change . In addition, the write time is different, the binary log file is logged before the transaction commits, and during the process of the transaction, there are constant redo log entries written to the redo log file.

• InnoDB table structure

  • Tablespace: InnoDB The top-level

  • Segment of the storage engine's logical structure: The table space consists of segments, such as data segments, index segments, rollback segments, etc.

  • Zone: 64 contiguous pages, each 16kb, i.e. 1MB

    per zone

  • Page: 16KB per page, and cannot be changed, data page, undo page, System page, transactional data page, insert Buffer bitmap page, insert buffered free list page, uncompressed binary large object page, compressed binary large object page

  • Line: The InnoDB storage engine is line-oriented (row-oriented), allowing up to 7992 rows of data per page

  • Row record format: After the common two line record formats compact and Redundant,mysql 5.1 versions, Mainly compact, this format regardless of char or varchar, NULL does not occupy storage space, for redudant, varchar null does not occupy space, Char's null occupies storage space.

• Lock:

  • shared lock (S Lock): allow transactions to read one row of data;

  • exclusive Lock (X Lock): allows transactions to be deleted or updated one row of data.          
         when a transaction has acquired a shared lock on row R, another transaction can immediately get a shared lock on row R, because reading does not change the data of row R, We call this case lock-compatible. However, if a transaction wants to obtain an exclusive lock on row R, it must wait for the transaction to release the shared lock on row R-a condition known as lock incompatibility.

  • Before InnoDB plugin, you can only view the current database request through commands such as show full processlist,show ENGINE inoodb status, and then determine the lock in the current transaction. In the new version of INNODB plugin, Innodb_trx, innodb_locks, Innodb_lock_waits are added under the INFORMATION_SCHEMA schema. With these three tables, you can more easily monitor the current transaction and analyze the problem of possible locks.

  • consistent non-locking read: the INNODB storage engine reads data from rows in the current execution time database in a row-versioning manner. If the read row is performing a delete, update operation, then the read operation does not wait for the row lock to be released, instead, the InnoDB storage engine reads a snapshot of the row data. Snapshot data refers to the data in the previous version of the row, which is implemented by the undo segment. Undo is used to rollback data in a transaction, so the snapshot itself has no additional overhead. In addition, snapshot data does not need to be locked out because there is no need to modify historical data. A row may have more than one snapshot data, so this technique is called line-to-multi-version technology. This brings concurrency control, called multi-version concurrency control (Multi versionconcurrency control, MVCC).

• algorithm of the Lock:
  Record Lock: A lock on a single-line record
  Gap Lock: A lock that locks a range but does not contain the record itself
  Next-key lock:gap lock + record lock, locks a range, and locks the record itself.

• Lock problem:

  • Missing updates: Classic database issues, missing update issues occur when two or more transactions select the same row, and then update the row based on the originally selected value. Every transaction is unaware of the existence of other transactions. The last update overrides updates made by other transactions, which results in data loss.          
             Example:         & nbsp
                 transaction A and transaction B modify the values of a row at the same time,          
                  1. Transaction a changes the value to 1 and submits          
                   2. Transaction B changes the value to 2 and commits it.          
                   When the value of the data is 2, the updates made by transaction A will be lost.          
                   WORKAROUND: Transaction parallel variable serial operation, add exclusive lock to update operation

  • Dirty read: One transaction reads dirty data to another transaction that has uncommitted update data.          
              Example:       & nbsp  
                  1.Mary's original salary is 1000, Finance staff changed Mary's salary to 8000 (but not committed)        
                   2.Mary read their wages and found their wages changed to 8000, merrily!          
                   3. While the financial discovery was wrong, and the transaction was rolled back, Mary's salary changed to 1000, as was the case with Mary's salary of 8000 being a dirty data.          
                   WORKAROUND: Dirty reads occur only if the transaction isolation level is READ UNCOMMITTED, InnoDB the default isolation level is repeatable read, so dirty reads do not occur in the production environment.

  • Non-repeatable reads: In the same transaction, the same data is read multiple times, and the results returned are different. In other words, subsequent reads can read the updated data that has been committed by another transaction. On the contrary, "repeatable read" ensures that the same transaction reads data more than once, that is, subsequent reads cannot be read to the updated data submitted by another transaction. The main difference between dirty reads and non-repeatable reads is that dirty reads are read to uncommitted data, and non-repeatable reads are read to the submitted data.
    Cases:
    1. In transaction 1, Mary reads her own salary of 1000, and the operation is not completed
    2. In transaction 2, the Financial officer modified Mary's salary to 2000 and submitted the transaction.
    3. In transaction 1, when Mary reads her salary again, the salary becomes 2000
    Workaround: Read the submitted data, the general database is acceptable, so the transaction isolation level is generally set to read Committed. Mysql InnoDB avoids non-repeatable reads through the Next-key lock algorithm, and the default isolation level is repeatable read.

• isolation level of the transaction : READ UNCOMMITTED, Read committed, Repeatable read, serializable. Under Read committed and Repeatable read, the INNODB storage engine uses non-locking consistent reads. However, the definitions for snapshots are different. Under the Read COMMITTED transaction isolation level, for snapshot data, non-conforming reads always read the latest snapshot data for the locked row. Under the REPEATABLE transaction isolation level, for snapshot data, non-conforming reads always read the row data version at the beginning of the transaction.

• 
  

 

This article from "Do not understand it Chinese medicine is not good it" blog, please be sure to keep this source http://zhishen.blog.51cto.com/1612050/1531580