MySQL > Interview with BAT

MySQL replication principle and process

MySQL myISam differs from InnoDB in at least 5 points

Varchar (50) = 50; varchar(50) = 50

Fourth, innoDB transaction and log implementation

MySQL binlog format

MySQL database CPU increases to 500%

Seven, SQL optimization

Backup plan, mysqlDump and XtranBackup

Mysql > select * from ‘mysqldump’ where ‘…. ‘ What if value()? What if the backup requires the master copy point information?

--skip-extended-insert
[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insertEnter password: KEY `idx_c1` (`c1`), KEY `idx_c2` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1; / *! 40101 SET character_set_client = @saved_cs_client */; -- -- Dumping datafortable `helei` -- LOCK TABLES `helei` WRITE; / *! 40000 ALTER TABLE `helei` DISABLE KEYS */; INSERT INTO 'helei' VALUES (1,32,37,38,'the 2016-10-18 06:19:24'.'susususususususususususu');
INSERT INTO `helei` VALUES (2,37,46,21,'the 2016-10-18 06:19:24'.'susususususu'); INSERT INTO 'helei' VALUES (3,21,5,14)'the 2016-10-18 06:19:24'.'susu');

Copy the code

Ten, 500 DB, restart in the fastest time

Innodb read and write parameter optimization

Global buffer andlocalBuffer; Global buffer: Innodb_buffer_pool_size Innodb_log_buffer_size Innodb_additional_mem_pool_sizelocalBuffer (the following are session variables for server layer, not InnoDB) :  Read_buffer_size Join_buffer_size Sort_buffer_size Key_buffer_size Binlog_cache_sizeCopy the code

innodb_flush_log_at_trx_commit
innodb_buffer_pool_size
insert_buffer_size
innodb_double_write
innodb_write_io_thread
innodb_flush_method

Copy the code

innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 0
Sync_binlog
Innodb_flush_log_at_trx_commit
Innodb_lru_scan_depth
Innodb_io_capacity
Innodb_io_capacity_max
innodb_log_buffer_size
innodb_max_dirty_pages_pct

Copy the code

How do you monitor your database? How do you search your slow logs?

Have you done master-slave consistency check? If so, how? If not, what are you going to do?

MySQL InnoDB engine row lock is done by adding on what? Why is that?

Select * from mysqldump; select * from mysqldump;

Full backup [root@HE1 ~]# mysqldump -uroot -p --single-transaction -A --master-data=2 >dump.sqlRestore only the contents of the ERP library [root@HE1 ~]# mysql -uroot -pMANAGER erp --one-database <dump.sql--one-database -o --one-database -o --one-database -o --one-database -o --one-database -o --one-database -o The specific implementation method is as follows: Extract the table structure of T table from the full database backup [root@HE1 ~]# sed -e'/./{H; $! d; }' -e 'x; /CREATE TABLE `t`/! d; q' dump.sqlDROP TABLE IF EXISTS`t`; / *! 40101 SET@saved_cs_client =@@character_set_client */; / *! 40101 SETcharacter_set_client = utf8 */; CREATE TABLE `t` ( `id` int(10) NOT NULL AUTO_INCREMENT, `age` tinyint(4) NOT NULL DEFAULT'0',
  `name` varchar(30) NOT NULL DEFAULT ' ', PRIMARY KEY (`id`) ) ENGINE=InnoDBAUTO_INCREMENT=4 DEFAULT CHARSET=utf8; / *! 40101 SETcharacter_set_client = @saved_cs_client */; Extract contents of t table from full database backup [root@HE1 ~]# grep'INSERT INTO `t`' dump.sqlINSERT INTO ` t ` VALUES (0, 0,' '), (1, 0,'aa'2, 0), (,'bbb'), (3, 25,'helei');

Copy the code

17. The biggest mysql DB problem you have encountered in your current job and how did you solve it?

InnoDB transaction isolation level: InnoDB transaction isolation level: InnoDB transaction isolation level: InnoDB transaction isolation level

The last

Welcome to pay attention to my public number [programmer chasing wind], the article will be updated in it, sorting out the data will be placed in it.