• Lock contention in MySQL


    Lock is a mechanism for computer to coordinate multiple processes or threads to access a resource concurrently!No matter MyISAM or InnoDB, lock contention will occur. In theAll kinds of locks in MySQLIn this article, we have talked about how to view table lock and row lock contention ======================================================================= 1. Table locking SHOW STATUS LIKE ‘table%’; […]

  • Atomicity and implementation principle of MySQL 8.0 DDL


    1. Overview of DDL atomicity Before 8.0, there was no unified data dictionary DD. the server layer and engine layer had a set of metadata respectively. The metadata of sever layer included (. Frm,. Opt,. Par,. TRG, etc.), which was used to store table definition, partition table definition, trigger definition and other information; InnoDB layer […]

  • Exploration of MySQL partition table


    background If you need to regularly clean up the historical data in an ordinary large table. You can use one or more delete statements with a where condition (where condition is time). If the amount of table data is large, it will cause great pressure on the database. Even if we delete the old data, […]

  • 😎 P03 DB database constraints, table relations, modify table syntax and copy table;


    Content summary constraint condition Establishing relationships (constraints) between tables Modify the complete syntax of the table Copy table constraint condition Default default “”” #Supplement: you can specify fields when inserting data create table t1 ( id int, name varchar(16) ); insert into t1 (name, id) values (‘samuel’, 1); create table t2 ( id int, name […]

  • Record the actual combat record of MySQL slave library recovery once


    Condition Description: Today, I logged in to a MySQL database slave node host and found that a large number of MySQL relay bin files are stored in / var / lib / MySQL. The earliest file creation date is even 2018. I remember that after the slave database synchronizes the log operation records of the […]

  • Easy to understand indexing principle


    About clustered index For a data table without a primary key, the data is stored in the memory row by row in disorder. If a primary key is added, that is, a clustered index, it will become a balanced tree. The structure of the whole data table will change from an unordered table to a […]

  • How to deal with the addition of fields in MySQL large tables without downtime


    It’s always a headache to add fields to a table of tens of millions or even more in MySQL. How to deal with this situation? In this paper, three common scenarios are used to illustrate the case. 1. Environmental preparation Database version:   5.7.25-28 (percona branch) Server configuration:  Three CentOS 7 virtual machines with 2cpu […]

  • InnoDB: unable to lock / ibdata1 error


    Error in MySQL startup under OS X environment: 016-03-03T00:02:30.483037Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 35 2016-03-03T00:02:30.483100Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files. The terminal repeatedly prints the above error log. From the error log, it seems that […]

  • Analysis of MySQL deadlock (intermediate)


    InnoDB actively detects the deadlock and rolls back a waiting transaction. The question is, how does InnoDB detect deadlocks?The intuitive method is that when two transactions wait for each other, when the waiting time of one transaction exceeds a set threshold, one transaction is rolled back, and the other transaction can continue to execute. This […]

  • Environment construction: install MySQL database under linux environment


    1. mount this database 1)   Yum – y install MySQL server (simple) Yum command automatically searches for MySQL service resources from the Internet, downloads them to the local and completes the installation     2)   You can also download the MySQL service on the Internet, transfer it to the Linux system through xftp, and […]

  • The influence of null on index in MySQL


    preface I have read many blogs and heard many people say, including our company’s DBA, that if a column in MySQL contains null, then the index containing the column is invalid. After reading the second edition of high performance mysql and the second edition of InnoDB storage engine, I didn’t see the introduction about this. […]

  • Master slave synchronization of MySQL data


    Environment construction Master server configuration [mysqld] #base configuration server-id=1 port=3306 #binlog-do-db=proxy_ heart_ Set the specified database to generate binary files basedir=/usr/local/mysql datadir=/data/dbfiles/mysql tmpdir=/data/dbfiles/mysql/tmp socket=/data/dbfiles/mysql/mysql.sock pid-file=/data/dbfiles/mysql/ log-error=/data/dblogs/mysql/mysql.err #log-bin configuration log-bin = mysql-bin log-bin-index = mysql-bin.index binlog_format=mixed log_bin_trust_function_creators=1 expire_logs_days=10 Configure from server [client] port=3306 socket= /data/dbfiles/mysql/mysql.sock [mysqld] #base configuration server-id=2 port=3306 //There are two ways: first, the […]