mysql Operations and Maintenance Basic Interview Questions and Answers

Interview Question 001: Explain the concept and main features of relational databases?

Answer: The relational database model refers to the simple binary relationship of complex data structure. All the operations on data are to establish one or more relational tables. The most important feature is the two-dimensional table, which is passed through the sql Language query statements are powerful in accessing data and maintaining data consistency.

Interview Question 002: What are the typical products, features and scenario applications of relational databases?

A: 1.mysql Common in Internet Enterprises

     2.oracle Large Traditional Enterprise Application Software

     3.Such as data backup, complex connection queries, consistent data storage, or using mysql Or other traditional relational databases.

Interview Question 003: Please explain the concept and main features of non-relational databases?

A: Non-relational databases are also known as NoSQL Database, data storage does not require a specific fixed table structure.

    Features: high performance, high concurrency, easy installation

Interview Question 004: What are the typical products, features and scenarios for non-relational databases?

A: 1.memcaced Pure memory

   2.redis Persistent Cache

   3.mongodb Document Oriented

  If a query operation requires a short response time, there is no data store defined by a good schema, or if the schema changes frequently, or if it is used NoSQL

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

A: sql Statements are categorized as follows:

   DDL A data definition language used to define database objects: libraries, tables, columns.

   Representational keywords: create alter drop 

   DML Data manipulation language for defining database records

   Representational keywords: insert delete update

   DCL Data Control Language, which defines access permissions and security levels

   Representational keywords: grant deny   revoke

   DQL Database Query Language, used to query recorded data

   Representational keywords: select

Interview Question 006: Please describe char(4) and varchar(4) in detail?

A:  char The length is fixed and immutable, varchar Length is variable (within settings) such as writing the same cn Characters, char The type corresponds to a length of 4 ( cn+Two spaces), but varchar The type corresponds to a length of 2.

Interview Question 007: How to create a database XXX with a urf8 character set?

A: create database xxxx default character utf8 collate urf8_general_ci;

Interview Question 008: How to authorize XXX users to access the database from 172.16.1.0/24.

create all on *.* to xxxx@'10.0.0.3' identified by "123456";

Interview Question 009: What is MySQL multi-instance and how do I configure MySQL multi-instance?

A: MySQL Multiple instances start multiple instances on the same server MySQL Services, which monitor multiple different ports and run multiple service processes, are independent of each other and do not affect each other in providing services to the outside world, saving server resources and late architecture expansion

 There are two ways to configure multiple instances:

 1.One instance, one configuration file, different ports

 2.Same profile ( my.cnf)The following configures different instances based on mysqld_multi Tools.

Interview Question 010: How to enhance MySQL security, please give specific and feasible measures?

  A: 1.Delete different default users from the database

      2.Configure appropriate permissions (including remote connections)

      3.Passwords that cannot be included in the database under the command line interface

      4.Modify passwords regularly and increase their complexity.

Interview Question 011: How can I retrieve the MySQL root password if I forget it?

 A: Add skip-grant-tables Parameters to/etc/my.cnf In the main configuration file[ msyqld]Options added.

  After adding mysql Login Default Skip mysql Logon password, which can be modified using commands.

  

update mysql.user set password = PASSWORD('123456')where user = 'root' ; flush privileges;

mysqladmin -uroot passowrd "NEWPASSWORD"

Both commands can change the password

The password must be changed after it has been changed/etc/my.cnf Configuration in file skip-grant-tables Parameter removed or commented out.

Interview Question 012: What is the difference between delete and truncate deleting data?

 A: The former can recover by deleting data, but it is slow by deleting data one by one.

     The latter is physical deletion, not recoverable, he deletes fast as a whole

Interview Question 013: How can I solve the problem of too many MySQL sleep threads?

A: 1.Can Kill sleep Process, kill PID

    2.Modify the configuration and restart the service

    [mysqld]

     wait_timeout = 600

     interactive_timeout = 30

     #If the production server does not restart at will, you can use the following methods to resolve this problem

     set global wait_timeout = 600

     set global interactive_timeout = 30;

Interview Question 014:sort_ Buffer_ What is the effect of the size parameter? How do online changes take effect?

A: At each connection(session)This is required for the first connection to improve access performance.

      set global sort_buffer_size = 2M

Interview Question 015: How to clean up MySQL binlog correctly online?

A: MySQL In binlog Logging records changes in the data, which facilitates point-in-time and location-based recovery of the data, but the size of the log file will become larger and occupy a large amount of disk space, so a part of the log information needs to be cleaned up regularly and deleted manually.

   #First look at the binlog file name being used by the master and slave Libraries

  show master(slave)status\G

  # Be sure to backup before deleting

  purge master logs before'2017-09-01 00:00:00'；

   #Delete log before specified time

  pugre master logs   to 'mysql-bin.000001'

   #Delete the specified log file

  Automatic Delete

  By setting binlog The log expiration time causes the system to delete the log automatically.

  show variables like 'expire_logs_days';

  et global expire_logs_days = 30;

   #View expiration time and set expiration date

Interview Question 016: What are the Binlog work patterns? What are the characteristics of each, and how do enterprises choose?

A: 1.Row(Line mode;

  The log is recorded as a modified version of each row of data, and then slave End to modify the same data.

  2.Statement(Sentence mode;

  Each modified data is fully recorded in the master database master Of binlog Inside the log, in slave Complete execution on master Statements on Execution

  3.mixed(Mixed Mode)

  Combining the previous two modes, if there is a need to use special functions such as function-aware triggers in the work, when the amount of data in mixed mode reaches a high level, he will choose statement Mode, not choice row Level Pattern

Interview Question 017: Wrong operation of a drop library sql statement, how to recover completely?

A: 1.Stop master-slave replication, execute lock table on master library and refresh binlog Operation, then restore the previous full file (such as 0 point full)

     2.Will be zero point binlog File and full until failure binlog File Merge and Export sql Sentence.

        mysqlbinog --no-defaults mysql-bin.000011 mysql-bin.000012 > bin.sql

     3.Will export sql In statement drop Statement deletion, restore to database

       mysql -uroot -p123456 < bin.sql

Interview Question 018: How to restore a form using the -A-B parameter for mysqldump backup

A:-A This parameter backs up all databases, (equivalent to --all-database)

    -B databasename Backup specified data (single library backup used)

Interview Question 019: Details MySQL master-slave replication principle and complete steps to configure master-slave

A: The principle of master-slave replication is as follows

  Main Library Open binlog Functionality and authorization to connect from library to master library via change master Get synchronization information about the main library, then connect to the main library to verify, the main library I/O Threads based on slave Libraries slave Thread's request from master.info The position of the starting record point starts to retrieve information downward, while the point where it was retrieved and the latest location are compared with the binlog Send message from library together I/O Threads, which will be related from libraries sql Statements stored in relay-log Inside, eventually from the library sql Threads will relay-log In sql Statements are applied from the library until the entire synchronization process is complete, and then the process is repeated indefinitely.

1,Main Library Open binlog Function and ready to push full files from library server

2,show master status\G Record current location information and binary file name

3,Log on to restore full files from library

4,implement change master to Sentence

5,implement start slave and show slave status\G

Interview Question 020: How to turn on binlog from library?

Answer: Modify the configuration file with the following configuration

   log_bin=slave-bin

   log_bin_index=slave-bin.index

  Service restart required to take effect

Interview Question 021: How does Mysql achieve two-way master-slave replication and explain the scenario?

A: Dual Primary Synchronization is mainly used to solve the pressure of writing from a single Primary Library, which is configured as follows

  Primary Library Configuration

  [ mysqld]

  auto_increment_increment = 2#Start ID

  auto)increment_offset = 1 #id self-increasing interval

  log-slave-update   

  Configure from Library

  [mysqld]

  auto_increment_increment = 2

  auto_increment_offset = 2 

  log-slave-update

Interview Question 022: How MySQL achieves cascade synchronization and explains common applications

A: Cascade synchronization is mainly used in slave libraries which need to be used as slave libraries for other databases

  Add the following configuration to the database profile where cascade synchronization is required

  log_bin=slave-bin

  log_bin_index=slave-bin.index

Interview Question 023: How can MySQL master-slave replication failures be resolved?

Answer: Log on from library

   1.implement stop slave Stop master-slave synchronization

   2.Then? set global sql_slave_skip_counter = 1 Skip this step error

   3.Final Execution start start，And view master-slave synchronization status

  Log on to the main library

   1.Perform full database and refresh binlog，View the status of the main library here

   2.Restore the full file to the slave library, then execute change master

   3.Turn on master-slave synchronization start slave And view the status from synchronization

Interview Question 024: How do I monitor if master-slave replication is failing?

A:  mysql -uroot -p123456 -e "show slave status\G" |grep -E "Slave_IO_Running"|awk   '{print $2}'|grep -c Yes

Pass judgment yes Number to monitor master-slave replication status, normally equal to 2.

Interview Question 025: How MySQL Database Separates Read from Write

A: 1.By developing programs

     2.Through other tools: mysql-mmm，mysql-proxy，mycat)

Interview Question 026: How to manually restore a master from a slave?

  A: 1.implement stop slave Or stop the service.

     2.Repair from database

     3.Then re-operate the main library synchronization: change master or. 

Interview Question 027: How to recover manually when a primary manufacturer and a secondary manufacturer shut down?

A: 1.Log in to each slave library to stop synchronizing and see who has the latest data, set it to the new master library and other slave libraries to synchronize their data.

   2.Once the master library is repaired, the steps to generate a new operation master-slave synchronization are ready

   #Note that the new main library is read-only and needs to be turned off to be writable.

   #Need to create the same synchronized users and permissions as the previous primary Library in the new slave Library

   #Other executions of change master to master_from Library Port = port of the new primary library, start slave.

Interview Question 028: What database failures have you encountered in your work? Describe 2 examples?

  A: 1.Development and Use root Writing data from libraries causes inconsistencies between master and slave data, and the front end does not show what needs to be modified (it is still old data).

      2.Sudden power outage of the internal network test environment server caused master-slave synchronization failure.

Interview Question 029: What are the reasons for the replication delay in Mysql? How to solve?

A: 1.Too much slave database data to synchronize

     2.Hardware data from the database is poor and needs to be improved

     3.Network problem, network bandwidth needs to be increased

     4.The primary database has a large amount of data written, requiring good configuration and hardware resources

     5.sql Statement execution is too long and needs to be optimized.

Interview Question 030: Give a feasible backup scenario for enterprise production of large mysql Cluster architecture?

A: 1.Dual master-slave, master-slave synchronization architecture, and then implement a slave library as a backup server

   2.Write scripts to back up database tables and add timed tasks

   3.Ultimately, the backup service is pushed to the intranet professional server, where the database server is kept locally for one week

   4.Backup server keeps backup data as it is (normally 30 days)

Interview Question 031: What is a database transaction, what are the characteristics of a transaction, and how an enterprise chooses

A: Database transactions are a logical set sql Statement, the statements that make up this operation, either succeeded or failed when executed

  Features: Atomicity, isolation, persistence, consistency,

Interview Question 032: Explain the concepts of readiness, readiness, readiness and business experience?

Answer: Complete: A full backup of all data in the database, that is, all data in the current database

Supplementary: All new data since the last backup

Cold standby: Make a backup operation on the basis of stopping the service.

Warm-up: Online backup operation will be carried out without affecting the normal operation of the database.

Basically once a week or once a day, all in the enterprise, and incremental backups at other times

Hot standby usage is when the database is served at the same time for two days, for archive mode databases

Cold standby usage is also early in the enterprise, and the amount of data and the number of servers is not large, may perform some important operations such as library, table structure, and so on.

Interview Question 033: How is the sql statement for MySQL optimized?

A: Primary keys and increased index

Interview Question 034: How does the mysql Cluster Architecture produced by the enterprise design a backup solution?

A: 1.Cluster architecture can adopt the dual-master-slave mode, but in fact, the Dual-master only has one master online to provide services, and the two hosts are ready for each other

     2.The other balances the load from the readable, then pulls one out of the other professionals to make a backup.

Interview Question 035: Developing a bunch of data to send a dba execution, what do you need to pay attention to in dba execution?

A: 1.It is important to note whether there are any formatting errors in the statement, which can cause interruptions in execution.

     2.It is also important to note whether statements take too long to execute and will put pressure on server load to affect actual production.

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

A: 1.Export the table structure of the library first -d Directs the table structure and replaces it in batches

   2.Export all the data in the library (without generating new data)

   3.Then replace globally set names = xxxx

   4.Delete and create new libraries and tables before importing library and table statements and all data

Interview Question 037: Please describe the principle of Chinese data scrambling in MySQL and how to prevent it?

Answer: Inconsistent tripartite character set between server system, database and client leads to the need for uniform characters

Interview Question 038: How to Optimize Mysql Produced by Enterprises (Please describe from multiple perspectives)

 A: 1.Increase server hardware resources and network bandwidth

    2.optimization mysql Service Profile

    3.Open the slow query log and analyze the problem

Interview Question 039: What are the highly available Mysql solutions, their characteristics, and how do companies choose?

A: 1.Master-Slave Architecture

     2.Mysql+MMM

     3.Mysql+MHA
 
     4.mysql+haproxy+drbd

     5.mysql+proxy+amoeba

Interview Question 040: How do I batch modify the engine that changes the datasheet?

A: Passed mysqldump Command backs up one sql File, reuse sed Command Replacement

 

  Or execute the following script to modify it

#!/bin/sh 

user=root 

passwd=123456 

cmd="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"` 

do 

for tables in `dump -e "show tables from $databses;"|sed '1d'` 

do 

$cmd "alter table $database.$tables engine = MyISAm;" 

done 

done

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

A: Passed mysqldump Command backs up one sql File, reuse sed Command Replacement sed -i  's/GBK/UTF8/g'

Interview Question 042: Give a method to troubleshoot the slow opening of the website. If the database is slow, how to troubleshoot and solve it, please analyze and give an example

A: 1.have access to top free Commands such as etc. analyze system performance issues

  2.If the database is the cause, you need to look at the slow query logs to find and analyze the problem.