MySQL face test

(2) MySQL Operation basic Knowledge interview questions

Interview 001: Please explain the concept and key features of relational database.

The relational database model is to boil down the complex data structure into a simple two-element relationship, and the most important thing is to set up one or more relational tables, the most characteristic of which is the two-dimensional table, the data is accessed by SQL structure query statement, and the data consistency is very strong.

Interview 002: Please state the typical product, feature and application scenario of relational database.

The relational database model is to boil down the complex data structure into a simple two-element relationship, and the most important thing is to set up one or more relational tables, the most characteristic of which is the two-dimensional table, the data is accessed by SQL structure query statement, and the data consistency is very strong.

Interview 003: Please explain the concept and main features of non-relational database.

A non-relational database is also called a NoSQL database, and data storage does not require a unique fixed table structure: High performance, high concurrency, easy installation

Interview 004: Please name the non-relational database typical products, features and application scenarios?

1, memcaced pure memory 2, Redis persistent cache 3, MongoDB document-oriented if you need a short response to the query operation, there is no good schema-defined data storage, or mode change frequently data storage or NoSQL

Interview 005: Please describe in detail the SQL statement classification and corresponding representative keywords.

The SQL statements are categorized as follows in the DDL data Definition language, which defines database objects: library, table, column Representative keywords: create alter DROPDML data manipulation language, used to define database records representative keywords: insert delete UPDATEDCL Data Control language, Used to define access rights and security level representative keywords: Grant deny REVOKEDQL data query language for querying record Data representative keywords: select

Interview 006: Please describe in detail the difference between char (4) and varchar (4)

The char length is fixed, the varchar length is variable (within the set) such as the same as the CN character, the char type corresponds to a length of 4 (cn+ two spaces), but the varchar type corresponds to a length of 2

Interview 007: How do I create a database Mingongge for a UTF8 character set?

Create DATABASE Mingongge default charset UTF8 collate utf8_general_ci;

Interview question 008: How to authorize Mingongge users to access the database from 172.16.1.0/24.

Grant all on * * to [e-mail protected] ' 172.16.1.0/24 ' identified by ' 123456 ';

Interview question 009: What is MySQL multi-instance, how to configure MySQL multi-instance?

MySQL multi-instance is to enable multiple MySQL services on the same server, they listen to different ports, run multiple service processes, they are independent of each other, non-impact external services, easy to save server resources and post-schema extension multi-instance configuration methods There are two: 1, an instance of a configuration file, Configure different instances under different port 2, same configuration file (MY.CNF), based on Mysqld_multi tool

Interview 010: How to enhance MySQL security, please give feasible concrete measures?

1. Remove the default user 2 that the database does not use, configure the appropriate permissions (including remote connection) 3, do not enter the password for the database at the command line interface 4, regularly change the password and enhance the complexity of the password

Interview question 011:mysql root password forgot how to retrieve it?

Refer to the previous answer

Interview questions 012:delete and truncate the difference between deleting data?

The former deleted data can be recovered, it is deleted one by one the slower the latter is physically deleted, not recoverable, it is the overall removal speed fast

Interview question 013:mysql How to solve the sleep thread too much?

1, you can kill the sleep process, kill PID2, modify the configuration, restart the service

[Mysqld]wait_timeout = 600interactive_timeout=30# If the production server is not able to restart randomly, the set global Wait_timeout=600set Global can be resolved using the following method interactive_timeout=30;

Interview question 014:sort_buffer_size parameter function? How does the online modification take effect?

Required for the first connection of each connection (session), could be free access performance  

Interview 015: How to properly clean up MySQL binlog online?

The Binlog log in MySQL records data changes in the data to facilitate time-based and location-based restores of data, but the size of the log files will grow larger, with a large amount of disk space. It is necessary to periodically clean up some log information manually delete: first check the master/slave library is using the Binlog file name show master (slave) Status\g Delete must be back up purge master logs before ' 2017-09-01 00:00:00 '; #删除指定时间前的日志purge master logs to ' mysql-bin.000001 '; #删除指定的日志文件自动删除: Let the system automatically delete logs by setting the expiration time of Binlog show variables like ' expire _logs_days '; Set Global Expire_logs_days =: #查看过期时间与设置过期时间

What is the working mode of the interview question 016:binlog? What are the characteristics of the enterprise how to choose?

1.Row (line mode), the log is recorded in the form of each row of data is modified, and then on the slave end of the same data modified 2. Statement (statement mode) Each modified data will be fully recorded into the binlog of the Master Library master, and the full execution of SQL statement 3.mixed (mixed mode) executed on the master in slave, combined with the previous two modes, if there are functions used in the work or a special function such as a trigger, when the amount of data used in mixed mode is higher, it chooses statement mode instead of row level mode

Interview 017: Misoperation executed a drop library SQL statement, how to complete recovery?

1, stop the master-slave replication, execute the lock table on the main library and refresh the binlog operation, and then restore the previous full file (such as 0 full-time) 2, the 0 O'Clock Binlog file and fully prepared to the failure period of Binlog file merged into SQL statement Mysqlbinlog-- No-defaults mysql-bin.000011 mysql-bin.000012 >bin.sql3, delete the drop statement from the exported SQL statement, revert to the database mysql-uroot-pmysql123 < Bin.sql

Interview question 018:mysqldump backup uses the-a-b parameter, how to implement a recovery single table?

-a The function of this parameter is to back up all databases (equivalent to--all-databases)-B databasename Backup specified data (for library backup use)

Interview 019: detailed MySQL master-slave replication principle and configuration Master-slave complete steps

The principle of master-slave replication is as follows: The main library to open the Binlog function and authorized to connect to the main library from the library, from the library through change master to obtain the relevant synchronization information of the main library, and then connect the main library for verification, the main library IO thread according to the request from the library slave thread, Starting from the point where the Master.info starts recording, start fetching the information,
At the same time, the location point and the latest location and the Binlog information to send from the library IO thread, from the library to store the relevant SQL statements in Relay-log, and finally from the library's SQL thread will relay-log SQL statements from the library, the entire synchronization process is completed, After that will be infinite repeat the above process complete steps are as follows: 1, the main library to open the Binlog function, and fully prepared to push the full file from the Library server 2, show Master Status\g record the current location information and binary file name 3, log in from the library to recover full files 4, Perform change master to statement 5, execute start slave and show slave status\g

Interview 020: How to open the Binlog function from the library?

Modify the configuration file plus the following configuration Log_bin=slave-binlog_bin_index=slave-bin.index need to restart the service to take effect

Interview question 021:mysql How to achieve two-way mutual master from replication, and explain the application scenario?

Bidirectional synchronization is primarily used to address the pressure of a single main library write, with the following configuration of the main library [mysqld]auto_increment_increment  = 2  #起始IDauto_increment_offset     = 1  #ID自增间隔log-slave-updates   from library configuration [mysqld]auto_increment_increment  = 2  #起始IDauto_increment_offset     = 2  #ID自增间隔log-slave-updates the master/slave server needs to restart the MySQL service

Interview question 022:mysql How to achieve cascade synchronization and explain the application scenario?

The main application of Cascade synchronization is to add the following configuration to the database configuration file which needs to be done as the other database from the library in order to Log_bin=slave-binlog_bin_index=slave-bin.index

Interview question 023:mysql master-slave replication failure how to solve?

Log in from library 1, execute stop slave; stop master-Slave synchronization 2, then set global sql_slave_skip_counter = 1; skip one step Error 3, and finally execute start slave; And check the master-slave synchronization status requires a master-slave synchronization process to enter the main library 1, make a full database and refresh Binlog, see the main library of this state 2, restore the full file to the library, and then perform change Master 3, turn on master-slave synchronization start slave; And check the master-slave synchronization status

Interview 024: How to monitor master-slave replication failure?

Mysql-uroot-ppassowrd-e "show slave Status\g" |grep-e "slave_io_running| Slave_sql_running "|awk ' {print $} ' |grep-c Yes to monitor the master-slave replication status by judging the number of yes, normally equal to 2

Interview question 025:mysql How does the database implement read-write separation?

1, through the development process to achieve 2, through other tools to achieve (such as mysql-mmm)

Interview 026: Production of a master more from the library down, how to manually recover?

1. Execute stop slave or stop service 2, repair from library database 3, and then re-operate the main library synchronization.

Interview 027: Production one master more from the main library down, how to manually recover?

1, landing each from the library to stop syncing, and to see who's data up-to-date, set it as the new main library to synchronize its data from the Library 2, repair the main library, the new operation master-slave synchronization of the steps can be

# need to be aware of the new main library if it was previously read-only, need to turn off this feature to make it writable # needs to be created in the new from the library with the same synchronization as before the main library user and Permissions # other from the library perform change master to master_port= Port of the new main library, start slave

Interview 028: What database failures have you encountered at work, please describe 2 examples?

1, the development uses the root user writes the data from the library to cause the master-slave data to be inconsistent, and the front end does not show the need to modify the content (still is old data) 2, the intranet test environment server sudden power outage causes the master-slave synchronization fault

What are the causes of replication delays in interview questions 029:mysql? How to solve?

1, need to synchronize from the library data too much 2, from the library of poor hardware resources, the need to improve 3, network problems, the need to improve network bandwidth 4, the main library of large data write, the need for optimal configuration and Hardware Resources 5, SQL statement execution too long caused, need to optimize

Interview 030: Give the enterprise to produce large MySQL cluster architecture feasible backup scheme?

1, the dual master multi-slave, master and subordinate synchronization of the architecture, and then the implementation of a library from the professional as a backup server 2, script the implementation of sub-database sub-table for backup, and join the scheduled Task 3, eventually push the backup service to the intranet Professional server, the database server locally reserved for a week 4, Backup server retains backup data according to actual situation (typically 30 days)

Interview 031: What are database transactions and what are the characteristics of transactions? How do companies choose?

A database transaction is a logical set of SQL statements that make up the individual statements of this set of operations, either successfully or unsuccessfully: atomicity, isolation, persistence, consistency

Interview 032: Please explain the concept of full preparation, preparation, cold, hot preparation and corporate practice.

Full: A full backup of all data in the database, that is, to back up all data additions to the current database: Back up on the basis of the last backup to now all new data cold: The backup operation is based on the Stop Service hot standby: the implementation of online backup operations, does not affect the normal operation of the database in the enterprise is basically a weekly or a day, the other time is the incremental backup hot standby use situation is two databases in the case of the same service, the database for the archiving mode of cold standby use of the enterprise, the data volume is not large and the number of servers, may perform some libraries, Table structure and other important operations

How is the SQL statement for the interview question 033:mysql optimized?

Establish primary key and add index

Interview 034: How to design a backup solution for the MySQL cluster architecture of enterprise production?

1, the cluster architecture can adopt dual-master multi-slave mode, but the actual dual master only one main online service, the two master do mutual preparation 2, another from the can read load balance, and then one of the extraction professional to do backup

Interview 035: Develop a bunch of data for DBA execution, what should you pay attention to DBA execution?

1, need to pay attention to whether the statement has a format error, the execution will cause the process to break 2, also need to pay attention to whether the execution time of the statement is too long, will be pressure on the server load affect the actual production

Interview 036: How to adjust the character set of MySQL database in the production line.

1, first export the library's table structure-D only export the table structure, and then bulk replace 2, export all the data in the library (without generating new data) 3, and then globally replace the Set names = xxxxx 4, delete the original library and table, and newly created, and then import the database and the build table statement and all the data

Interview question 037: Please describe the MySQL Chinese data garbled principle, how to prevent garbled?

Server system, database, client the three-party character set is inconsistent resulting in the need for uniform characters

Interview question 038: How to optimize the production of MySQL (please describe the multi-angle)?

1, improve the server hardware resources and network bandwidth 2, optimize the MySQL service Profile 3, open the slow query log and then analyze the problem

Interview question 039:mysql What are the high-availability programs, their respective characteristics, and how do companies choose?

Highly available scenarios are
1, master-slave Architecture 2, mysql+mmm 3, Mysql+mha 4, MYSQL+HAPROXY+DRBD 5, Mysql+proxy+amoeba

Interview 040: How do I bulk change the engine of a database table?

Back up a SQL file with the mysqldump command, then replace it with the SED command or execute the following script to modify #!/bin/shuser=rootpasswd=123456cmd= "MYSQL-U$USER-P$PASSWD" dump= "MYSQLDUMP-U$USER-P$PASSWD" for Database in ' $cmd-e "show databases;" | Sed ' 1,2d ' |egrep-v "Mysql|performance_schema" ' dofor tables in ' DUMP-E ' show tables from $databses; "| Sed ' 1d ' do$cmd "ALTER TABLE $database. $tables engine = MyISAm;" Donedone

Interview 041: How do I bulk change the database character set?

Back up a SQL file with the mysqldump command and replace Sed-i ' s/gbk/utf8/g ' with the SED command

Interview Question 042: The website opens slowly, please give the troubleshooting method, if the database is slow, how to troubleshoot and solve, please analysis and examples?

1, can use the top free and other commands to analyze the system performance and other aspects of the problem 2, if the cause of the database, you need to check the slow query log to find and analyze the problem

MySQL face test