MySQL Enterprise interview questions for Linux O & M meetings (with answers) and answers for mysql

MySQL Enterprise interview questions for Linux O & M meetings (with answers) and answers for mysql
(1) Test the basic test command 1. Enable the MySQL Service

/etc/init.d/mysqld startservice mysqld startsystemctl  start mysqld

2. Check whether the port is running

lsof -i :3306netstat -lntup |grep 3306

3. set or change the password for MySQL

Set Password

mysql -uroot -ppassword -e "set passowrd for root = passowrd('passowrd')"mysqladmin -uroot passowrd "NEWPASSWORD"

Change Password

mysqladmin -uroot passowrd oldpassowrd "NEWPASSWORD"use mysql;update user set passowrd = PASSWORD('newpassword') where user = 'root';flush privileges;

Modify the default password in msyql 5.7 or later versions

alter user 'root'@'localhost' identified by 'root' 

4. log on to the MySQL database

mysql -uroot -ppassword

5. view the character set of the current database

show create database DB_NAME;

6. view the current database version

mysql -Vmysql -uroot -ppassowrd -e "use mysql;select version();"

7. view the currently logged-on user

select user();

8. Create the database mingongge for the GBK character set and view the complete statements that have been created

create database mingongge DEFAULT CHARSET GBK COLLATE gbk_chinese_ci;

9. Create a user mingongge to manage the database mingongge

grant all on mingongge.* to 'mingongge'@'localhost' identified by 'mingongge';

10. view the permissions of the created user mingongge.

show grants for mingongge@localhost

11. view the users in the current database

select user from mysql.user;

12. Enter the mingongge Database

use mingongge

13. Create an innodb GBK table test, field id int (4) and name varchar (16)

create table test (     id int(4),     name varchar(16)     )ENGINE=innodb DEFAULT CHARSET=gbk;

14. view the SQL statements used to create the table structure and Table Structure

desc test;show create table test\G

15. Insert a piece of data "1, mingongge"

insert into test values('1','mingongge');

16. Insert two rows of data in batches: "2, migrant workers", "3, mingonggeedu"

Insert into test values ('2', 'migrant workers' Ge'), ('3', 'mingonggeedu ');

17. query records named mingongge

select * from test where name = 'mingongge';

18. Change the mingongge name with the data id equal to 1 to mgg.

update test set name = 'mgg' where id = '1';

19. Insert the age field before the field name, type tinyint (2)

alter table test add age tinyint(2) after id;

20. Back up the mingongge database without exiting the database

system mysqldump -uroot -pMgg123.0. -B mingongge >/root/mingongge_bak.sql

21. Delete all data in the test table and view

delete from test;select * from test;

22. Delete and view the test and mingongge databases.

drop table test;show tables;drop database mingongge;show databases;

23. Do not exit the database to restore the deleted data

system mysql -uroot -pMgg123.0. 

24. Change the GBK character set of the database table to UTF8.

alter database mingongge default character set utf8;alter table test default character set utf8;

25. Set the id column as the primary key and create a common index on the Name field.

alter table test add primary key(id);create index mggindex on test(name(16));

26. Insert the mobile phone number field (shouji) after the field name, type char (11)

Alter table test add shouji char (11); # insert a new column after the last column by default

27. Insert 2 records on all fields (set data by yourself)

insert into test values('4','23','li','13700000001'),('5','26','zhao','13710000001');

28. Create a common index for the first eight characters in the mobile phone Field

create index SJ on test(shouji(8));

29. view the created index and index type.

Show index from test; show create table test \ G # The command below can also view the index type show keys from test \ G

30. Delete the index of the Name and shouji Columns

drop index SJ on test;drop index mggindex on test;

31. Create a joint index for the first 6 Characters in the Name column and the first 8 characters in the mobile phone Column

create index lianhe on test(name(6),shouji(8));

32. query records whose mobile phone number starts with 137 and whose name is zhao (insert in advance)

select * from test where shouji like '137%' and name = 'zhao';

33. query the execution plan of the preceding statement (whether to use the Union index or not)

explain select * from test where name = 'zhao' and shouji like '137%'\G

34. Change the engine of the test table to MyISAM.

alter table test engine=MyISAM;

35. revoke the select permission of the mingongge user

revoke select on mingongge.* from mingongge@localhost;

36. Delete mingongge users

drop user migongge@localhost;

37. Delete the mingongge Database

drop database mingongge

38. Use mysqladmin to close the database

mysqladmin -uroot -pMgg123.0. shutdownlsof -i :3306

39. The MySQL password is lost. Can I retrieve it?

Mysqld_safe -- skip-grant-tables & # Start the Database Service mysql-uroot-ppassowrd-e "use mysql; update user set passowrd = PASSWORD ('newpassword ') where user = 'root'; flush privileges ;"

(2) MySQL basic O & M knowledge Interview Q & A 001: What are the concepts and main features of RDs?

A relational database model refers to a simple binary relationship for a complex data structure. Operations on data are performed on one or more Relational Tables. The biggest feature of a relational database model is a two-dimensional table, access data through SQL Structure Query statements to maintain strong Data Consistency

Question 002: What are the typical products, features, and application scenarios of relational databases?

1. Commonly Used mysql Internet Enterprises

2. oracle large-scale traditional enterprise application software

3. For example, data backup, complex connection query, and consistent data storage, it is best to use MySQL or other traditional relational databases.

Question 003: What are the concepts and main features of non-relational databases?

Non-relational databases, also known as NoSQL databases, do not need to have a specific and fixed table structure for data storage.

Features: High performance, high concurrency, simple installation

Interview question 004: What are the typical products, features, and application scenarios of non-relational databases?

1. memcaced pure memory

2. redis persistent Cache

3. mongodb documentation

If a query operation requires a short response, there is no data storage defined in good mode, or the data storage with frequent mode changes still uses NoSQL

Interview question 005: Describe the SQL statement category and corresponding representative keywords in detail.

SQL statements are classified as follows:

DDL Data Definition Language, used to define database objects: databases, tables, and columns

Keyword: create alter drop

DML data operation language, used to define database records

Keyword: insert delete update

DCL Data Control Language, used to define access permissions and security levels

Representative Keyword: grant deny revoke

DQL Data Query Language used to query Record Data

Representative keywords: select

Question 006: Describe the differences between char (4) and varchar (4)

The char length is fixed and immutable, and the varchar length is variable (within the settings). For example, if you write the same cn character, the char type corresponds to a length of 4 (cn + two spaces ), however, the length of the varchar type is 2.

Question 007: How to Create a utf8 character set database mingongge?

create database mingongge default character 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 mingongge@'172.16.1.0/24' identified by '123456';

Interview question 009: What is MySQL multi-instance? How do I configure MySQL multi-instance?

Multiple mysql instances enable multiple mysql services on the same server. They listen to different ports and run multiple service processes. They are independent of each other and provide external services without affecting each other, easy to save server resources and expand later Architecture

You can configure multiple instances in either of the following ways:

1. One configuration file for one instance, with different ports

2. Configure different instances in the same configuration file (my. cnf) based on the mysqld_multi tool.

Question 010: How can I enhance MySQL security? What are the feasible measures?

1. Default users not used to delete Databases

2. Configure the relevant permissions (including remote connection)

3. You cannot enter the database password on the command line interface.

4. Regularly change the password and enhance the complexity of the password

Interview question 011: How can I retrieve the MySQL root Password?

Refer to the previous answer

Interview question 012: What is the difference between delete and truncate to delete data?

The former can be recovered when data is deleted, and the deletion speed is slow one by one.

The latter is physical deletion and cannot be recovered. It is a fast overall deletion speed.

Interview question 013: how to solve the problem of too many MySQL Sleep threads?

1. You can kill the sleep process and kill the PID.

2. modify the configuration and restart the service.

[Mysqld] wait_timeout = 600interactive_timeout = 30 # If the production server cannot be restarted, use the following method to solve set global wait_timeout = 600 set global interactive_timeout = 30;

Question 014: What is the function of the sort_buffer_size parameter? How can I make changes online?

The first connection of each connection (session) needs to be used to improve the access performance. set global sort_buffer_size = 2 M

Interview question 015: how to clean up MySQL binlog online correctly?

The binlog log in MySQL records data changes to facilitate data recovery based on time points and locations.
However, the size of log files will increase and a large amount of disk space is used. Therefore, you need to regularly clear some log information.
Manually delete:

First, check the name of the binlog file in use by the master/Slave database.

Show master (slave) status \ G

Make sure to back up before deleting

Purge master logs before '2017-09-01 00:00:00 ';

# Delete logs generated before a specified time

Purge master logs to 'mysql-bin.000001 ';

Deletes a specified log file.

Automatic deletion:

The system automatically deletes logs by setting the binlog expiration time.

Show variables like 'expire _ logs_days ';

Et global expire_logs_days = 30;

# View the expiration time and set the expiration time

Interview question 016: What are the Binlog working modes? What are the features and how do enterprises choose?

1. Row (Row mode );

The log records each row of data in the modified form, and then modifies the same data on the slave end.

2. Statement (Statement mode)

Each modified data is completely recorded in the binlog of the master database master, and the SQL statement executed on the master database is fully executed on the slave.

3. mixed (mixed Mode)

In combination with the previous two modes, if you want to use functions, triggers, and other special features in your work, use the hybrid mode.

When the data volume reaches a relatively high Level, it will select the statement mode instead of the Row Level Row mode.

Interview question 017: how to restore the entire database by executing a drop SQL statement by mistake?

1. Stop master-slave replication, execute the lock table on the master database, refresh the binlog operation, and restore the original full backup file (such as the full backup)

2. Combine the binlog file at with the binlog file during full backup to the fault and export it into an SQL statement.

Mysqlbinlog-no-defaults mysql-bin.000011 mysql-bin.000012> bin. SQL

3. Delete the drop statement in the exported SQL statement and restore it to the database.

Mysql-uroot-pmysql123 <bin. SQL

Question 018: mysqldump backup uses the-A-B parameter. How can I restore A single table?

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

-B databasename: Backup specified data (used for single-Database Backup)

Interview question 019: Detailed MySQL master-slave replication principles and complete steps for configuring master-slave Databases

The principle of master-slave replication is as follows:

The binlog function is enabled for the master database and the slave database is authorized to connect to the master database. The slave database obtains the synchronization information of the master database by changing the master database, and then connects to the master database for verification. The IO thread of the master database is based on the request of the slave thread, start to retrieve information from the location point recorded starting from master.info, and send the obtained Location Point and the latest location along with binlog information to the slave database IO thread, the slave database stores related SQL statements in relay-log, and finally applies the SQL statements in relay-log to the slave database from the SQL thread of the database. This completes the synchronization process, the above process will be repeated infinitely.

The complete steps are as follows:

1. Enable binlog for the master database and perform full backup to push the full backup file to the slave database server.

2. show master status \ G records the current location information and binary file name

3. log on to the slave database to restore the full backup file

4. Execute the change master to statement.

5. Run start slave and show slave status \ G.

Interview question 020: How do I enable the binlog function of the slave database?

Modify the configuration file and add the following configuration

log_bin=slave-binlog_bin_index=slave-bin.index

Service needs to be restarted to take effect

Question 021: How does MySQL implement mutual master-slave replication and describe application scenarios?

Bidirectional synchronization is mainly used to solve the write pressure on a single master database. The specific configuration is as follows:

Master Database Configuration

[Mysqld] auto_increment_increment = 2 # Start IDauto_increment_offset = 1 # ID auto-incrementing interval log-slave-updates

Slave Database Configuration

[Mysqld] auto_increment_increment = 2 # Start IDauto_increment_offset = 2 # ID auto-incrementing interval log-slave-updates

The mysql service must be restarted for both master and slave database servers.

Interview question 022: How does MySQL implement cascade synchronization and describe application scenarios?

Cascade synchronization is mainly used as the master database of other databases in the slave database.

Add the following configuration to the database configuration file for Cascade synchronization.

log_bin=slave-binlog_bin_index=slave-bin.index

Interview question 023: How can I solve the MySQL master-slave replication fault?

Log on to the slave Database

1. Run stop slave to stop master-slave synchronization.

2. set global SQL _slave_skip_counter = 1; skip the error step.

3. Run start slave and check the master-slave synchronization status.

Follow these steps to perform master-slave synchronization again:

Enter the master database

1. Perform full backup database and refresh the binlog to view the status of the master database.

2. Restore the full backup file to the slave database, and then execute change master

3. enable the master-slave synchronization start slave and view the master-slave synchronization status

Interview question 024: How to monitor whether master-slave replication is faulty?

Mysql-uroot-ppassowrd-e "show slave status \ G" | grep-E "Slave_IO_Running | Slave_ SQL _Running" | awk '{print $2}' | grep-c Yes: Yes to monitor the master-slave replication status, normally equal to 2

Interview question 025: How does MySQL database implement read/write splitting?

1. Implemented through development programs

2. Use other tools (such as mysql-mmm)

Interview question 026: How to manually restore the master-slave database from downtime?

1. Execute stop slave or stop the service

2. Fix slave Database

3. Re-operate master Database Synchronization

Interview question 027: How can I manually recover a master database with multiple slave databases being down?

1. log on to the slave database to stop synchronization, check who has the latest data, and set it as the new master database so that other slave databases can synchronize their data.

2. After the master database is repaired, you can create a new master-slave synchronization step.

# Note that if the new master database is read-only, you need to disable this function to make it writable # You need to create a new slave database with the same synchronization users and permissions as the previous master database # other slave databases execute change master to master_port = ports of the new master database, start slave

Interview question 028: What database faults have you encountered at work? Please describe two examples?

1. Development and Use of root users to write data from the slave database results in master-slave data inconsistency, and the front-end does not display the content to be modified (still old data)

2. Master-slave synchronization failure caused by sudden server power failure in the Intranet test environment

Interview question 029: What are the causes of replication delay in MySQL? How can this problem be solved?

1. Too much data from the slave database to be synchronized

2. Slave Database hardware resources are poor and need to be upgraded

3. network problems, need to increase network bandwidth

4. The data writing volume of the master database is large, and configuration and hardware resources need to be optimized.

5. The execution of SQL statements is too long and requires optimization.

Interview question 030: What is the feasible backup solution for enterprise production of large MySQL clusters?

1. Dual-master-slave multi-slave architecture with master-slave synchronization, and then a slave database is used as a backup server.

2. write scripts to back up database/table shards and add scheduled tasks

3. Finally, the backup service is pushed to professional internal network servers, and the database server is retained for one week locally.

4. the backup server retains the backup data based on the actual situation (generally 30 days)

Interview question 031: what are database transactions and what are the features of transactions? How do enterprises choose?

A database transaction refers to a logical set of SQL statements that constitute each statement of this group of operations. The execution is either successful or failed.

Features: atomicity, isolation, durability, and consistency

Interview question 032: Could you explain the concepts of full backup, Incremental backup, cold backup, hot backup and enterprise practical experience?

Full backup: a full backup of all data in the database, that is, backing up all data in the current database

Backup addition: all new data is backed up on the basis of the last backup.

Cold backup: backup is performed on the basis of stopping the service.

Hot Backup: online backup is performed without affecting the normal operation of the database.

Full backup is generally performed every week or day in the enterprise, and Incremental backup is performed at other times.

Hot Standby is used when two databases provide services at the same time.

Cold backup is used in the early stage of the enterprise. The data volume is small and the number of servers is small. When some major operations such as databases and table structures are performed

Interview question 033: how to optimize MySQL SQL statements?

Create a primary key and add an index

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

1. The cluster architecture can adopt the dual-master and multi-slave mode, but the actual dual-master only provides services online, and the two masters perform mutual backup.

2. perform additional load balancing from the read-only server, and then extract one of them for professional backup.

Interview question 035: What do you need to pay attention to when developing a pile of data for dba execution?

1. Check whether the statement has a format error. execution may cause process interruption.

2. Check whether the statement execution time is too long and whether the stress on the server load will affect the actual production.

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

1. The table structure of the first pilot warehouse-d only exports the table structure, and then replaces it in batches.

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

3. Replace set names = xxxxx globally.

4. Delete the original database and table, create a new database, and import the database and table creation statements and all data.

Interview question 037: Describe the principles of Chinese data garbled in MySQL. How can we prevent Garbled text?

The character set of the server system, database, and client is inconsistent.

Interview question 038: how to optimize MySQL for enterprise production (please describe it from multiple perspectives )?

1. Improve server hardware resources and network bandwidth

2. Optimize the mysql service configuration file

3. Enable slow query logs and analyze the problem

Interview question 039: What are the features of the MySQL high availability solution and how do enterprises choose?

High-availability solutions

1. Master-slave architecture

2. MySQL + MMM

3. MySQL + MHA

4. mysql + haproxy + drbd

5. mysql + proxy + amoeba

Interview question 040: how to change the database table engine in batches?

Use the mysqldump command to back up an SQL file and then use the sed command to replace

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 question 041: How do I change the database character set in batches?

Use the mysqldump command to back up an SQL file, and then use the sed command to replace sed-I's/GBK/UTF8/G'

Interview question 042: the website is slow to open. Please provide troubleshooting methods. If the database is slow, how can we troubleshoot and solve the problem? Please analyze and give an example?

1. You can use top free and other commands to analyze system performance issues.

2. if the problem is caused by the database, you need to check the slow query log to find and analyze the problem.