mysql tuning script

Original link: What to tune in MySQL 5.6 after installationOriginal date: September 17, 2013Translation Date: June 01, 2014Translators: AnchorWith the improvement of a large number of default options , MySQL 5.6 has significantly fewer options than the previous version needs tuning. In this article I will describe the configuration items that need to be optimized

) GTID2) in MySQL 5.6 gtid mode synchronous replication error cannot skip the workaround3) Improvement of Gtid replication in MySQL5.74) Gtid Replication traps5) Improvements to Gtid replication in MariaDB 10.1MySQL 5.6/5.7 Security recovery from library crashMariaDB 10.0/10.1 security recovery from vault crashSlave multi-threaded replication from librariesSlave supports multi-source replicationMySQL 5.7 Set synchronous replication filtering without r

Tags: MySQL performance tuning the selected character type for two field typesCharacter type: Char () is used to hold a fixed-length string with a maximum length of 255, and a value larger than the specified length is truncated, and a value smaller than the specified length is padded with spaces. Varchar () is used to hold variable-length strings, up to a length of 65535, to store only the length that the s

PHP tutorials and MySQL tutorial performance tuning notes The larger the tmp_table_size=200m tmp_table_size, the smaller the probability of being converted to a disk TMP table PHP Program page MySQL closed in time Set the MySQL timeout timeLow-priority-updates set query and update priority and tmp_table_size=200m

MySQL config file under Windows is called My.ini, under the MySQL installation root directory;Under Linux is called MY.CNF, the file is located in/etc/my.cnf. Host ' 127.0.0.1 ' isn't allowed to connect to this MySQL server General reason: The MySQL database configuration file My.ini set the parameters: Skip-na

',' Create_view_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',' Show_view_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',' Create_routine_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',' Alter_routine_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',' Create_user_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',' Event_priv ' enum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ','

, so entering the field where I have a non-null constraint fails.The reason is that the date format is indeterminate, so go straight to the problem,It is recommended that the field directly set the temporary table is varchar, followed by processing.Sqoop eval--connect jdbc:mysql://xx:3306/cms--username root--password xxx \--query "TRUNCATE TABLE Xx.stage_t_xx"Sqoop Export--connect jdbc:mysql://xxx:3306/xxx\--username root--password xxx--table stage_t_xxe \--hcatalog-database xxx--hcatalog-table

. The Block Nested-loop algorithm uses the result set of the driver table (which can be simply understood as the preceding table) as the circular underlying data, and then one article queries the data in that result set by using the data in the resulting dataset as a filter to the next table, and then merges the results. If there is a third participating join, then the join result set of the previous two tables is used as the base data for the join, once again querying the data by looping throug

purpose of the flow queue data is to determine the data in the table where the data will be keptis insert,update and delete, and each operation generates the corresponding log information. In MySQL, if theThe storage engine that holds the transaction, the amount of this log generation is doubled. And if we go through some mature third-party queue software toThe performance of this queue data processing function will be multiplied.3. Super-Large text

Tag: The user using ROM uses the RAC statement from the ORDER by nbspThe solution to this problem is that MySQL can use only one index per query, and your SQL statement's WHERE condition is not the same as the condition of ORDER by, and the index is not built. Then the ORDER by is not used for indexing. A Using filesort problem has occurred.The problem is to create a hybrid index that includes the WHERE and ORDER by conditions.For example, the origina

The day before yesterday to customers to do a database server adjustment, the previous one host abandoned, one from the library to promote the main, and then new to join other slave, here to record the operation steps:First, the old master library is turned on for read-only so that later steps are rolled back: Flush tables with read lockSecond, 110.10.1.102 upgrade to the main library, pause synchronization, reset to primary, clean up the relay information, restart the databasestop slave, reset

Label:This problem occurs because MySQL can use only one index per query, and your SQL statement WHERE condition and order by conditions are not the same, the index is not built, then the ORDER by is not used to index, there is a using filesort problem.The solution to this problem is to create a hybrid index that contains the WHERE and ORDER by conditions.For example, the original SQL statement is:SELECT * from the user U where u.id=100 order by u.upd

Label:Key_buffer_sizeMeaning: The buffer size used for the index block, increasing the index (for all read and multiple writes) that it can get better processing.Impact: The impact on the MyISAM table is not very large, MyISAM uses the system's cache to store the data, so machine memory that uses MyISAM tables is quickly exhausted. However, if the value is set too large (for example, greater than total memory 50%), the system is converted to a page and becomes extremely slow.

Label:With the long-term use of MySQL, you can fix the table to optimize and reduce disk footprint when optimizing. Easy backup.REPAIR table ' table_name ' repair form REPAIR table is used to repair corrupted tables.OPTIMIZE table is used to reclaim unused database space, and when the data rows on the table are deleted, the disk space occupied is not immediately reclaimed, the space is reclaimed after the OPTIMIZE Table command is used, and the data r

Tags: port way background native let DBN terminal delete MySQL script#!/bin/bashhostname= "127.0.0.1" #数据库本机地址PORT = "3306" username= "root" password= "pwd" dbname= "Tbs_ra_data" Execsql= "Call Delete_flow_onebyone ()" //here can be an SQL statement or invoke a stored procedure echo "Begin execute MYSQL SQL ... "My