MySQL enterprise Interview questions for Linux operation and maintenance

(1) Basic written test command inspection

1. Start the MySQL service

/etc/init.d/mysqld start
service mysqld start
systemctl  start mysqld
Copy the code

2. Check whether the port is running

lsof -i :3306
netstat -lntup |grep 3306
Copy the code

3. Set or change the password for MySQL

Set the password

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

Change password

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

Msyql 5.7 or later command used to change the default password

alter user 'root'@'localhost' identified by 'root' 
Copy the code

4. Log in to the MySQL database

mysql -uroot -ppassword
Copy the code

5. View the character set of the current database

show create database DB_NAME;
Copy the code

6. View the current database version

mysql -V
mysql -uroot -ppassowrd -e "use mysql; select version();"
Copy the code

7. View the current login user

select user();
Copy the code

Create GBK character set database mingongge, and check the complete statements of the database

create database mingongge DEFAULT CHARSET GBK COLLATE gbk_chinese_ci;
Copy the code

9. Create user mingongge so that it can manage database mingongge

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

10. Check the permissions of the created user mingongge

show grants for mingongge@localhost
Copy the code

11. Check which users are in the current database

select user from mysql.user;
Copy the code

12. Access the Mingongge database

use mingongge
Copy the code

Create innoDB GBK table test with id int(4) and name varchar(16)

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

14. View the SQL statement for creating a table structure

desc test;
show create table test\G
Copy the code

15. Insert data “1,mingongge”

insert into test values('1'.'mingongge');
Copy the code

16. Batch insert 2 rows of data “2, mingonggeedu” and “3,mingonggeedu”

insert into test values('2'.'Brother Migrant worker'), ('3'.'mingonggeedu');
Copy the code

Select mingongge from mingongge

select * from test where name = 'mingongge';
Copy the code

18. Rename mingongge where id = 1 to MGG

update test set name = 'mgg' where id = '1';
Copy the code

Insert age (tinyint(2)) before name;

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

20. Complete backup of mingongge database without exiting the database

System mysqldump-uroot-PMGG123.0. -b mingongge >/root/mingongge_bak.sqlCopy the code

21. Delete all data from the test table and view it

delete from test;
select * from test;
Copy the code

Delete table test and mingongge database and view

drop table test;
show tables;
drop database mingongge;
show databases;
Copy the code

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

System mysql -uroot -pmGG123.0. </root/mingongge_bak.sqlCopy the code

Alter table GBK character set UTF8

alter database mingongge default character set utf8;
alter table test default character set utf8;
Copy the code

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

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

Select * from shouji; char(11); select * from shouji;

alter table test add shouji char(11);
Insert the new column after the last column by default
Copy the code

27. Insert 2 records on all fields (custom data)

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

28. Create a normal index for the first 8 characters in the cell phone field

create index SJ on test(shouji(8));
Copy the code

29. View information about the created index and index type

show index from test;
show create table test\G
The following command can also be used to view the index type
show keys from test\G   
Copy the code

30. Drop index Name, SHOUji

drop index SJ on test;
drop index mggindex on test;
Copy the code

Create joint index on first 6 characters of Name column and first 8 characters of mobile column

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

Select * from zhao where id = 137;

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

SQL > select * from SQL where SQL > select * from SQL where SQL > select * from SQL

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

Alter table test engine to MyISAM

alter table test engine=MyISAM;
Copy the code

35. Revoke select privileges for mingongge users

revoke select on mingongge.* from mingongge@localhost;
Copy the code

36. Delete mingongge users

drop user migongge@localhost;
Copy the code

37. Delete mingongge database

drop database mingongge
Copy the code

38. Close the database using mysqladmin

Mysqladmin-uroot -pmgg123.0.shutdown lsof -i :3306Copy the code

MySQL password lost, please find?

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;"
Copy the code

(2) basic knowledge of MySQL operation and maintenance

001: Explain the concept and main features of a relational database?

Relational database model is the complex data structure into a simple binary relationship, the operation of the data is to establish one or more relations on the table, the biggest characteristic is two-dimensional table, through SQL structure query statement access to data, maintain data consistency is very powerful

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

Mysql or other traditional relational databases are most suitable, such as data backup, complex connection query, consistent data storage, etc

003: Explain the concept and key features of a non-relational database?

Non-relational databases, also known as NoSQL databases, do not need to have unique fixed table structure characteristics: high performance, high concurrency, easy to install

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 is document-oriented

Interview question 005: Please describe in detail the categories of SQL statements and their corresponding representative keywords.

DDL data Definition Language (DDL) is used to define database objects: libraries, tables, columns Create ALTER DROP DML data manipulation language, used to define the representation of database records. Grant deny REVOKE DQL Data query language, used to query record data representation keyword: SELECT

Interview question 006: Describe in detail the difference between char(4) and varchar(4)

For example, when writing cn characters, the length of a char is 4(cn+ two Spaces), but the length of a vARCHar is 2

How to create a utF8 character set mingongge database?

create database mingongge default character utf8 collate utf8_general_ci;
Copy the code

Interview question 008: How do I authorize the mingongge user to access the database from 172.16.1.0/24?

grant all on *.* to mingongge@'172.16.1.0/24' identified by '123456';
Copy the code

Question 009: What is MySQL multiinstance? How to configure MySQL multiinstance?

Mysql multi-instance is to enable multiple mysql services on the same server. They listen to different ports and run multiple server processes. They are independent of each other and provide external services without affecting each other. CNF/mysqLD_MULTI/mysqLD_MULTI/mysqLD_MULTI/mysqLD_MULTI/mysqLD_MULTI/mysqLD_MULTI/mysqLD_MULTI

010: How to strengthen MySQL security, please give feasible specific measures?

Delete the default users that are not used by the database. 2. Configure the corresponding permissions (including remote connections). 3

011: How to retrieve MySQL root password?

Refer to the previous answer

Interview question 012: What is the difference between delete and TRUNCate?

The former delete data can be recovered, it is a slow deletion, the latter physical deletion, unrecoverable, it is the overall deletion speed

013: How to solve the problem of MySQL Sleep thread overload?

1. You can kill the Sleep process and PID. 2

[mysqld]
wait_timeout = 600
interactive_timeout=30
If the production server cannot be restarted, use the following method to resolve the problem
set global wait_timeout=600
set global interactive_timeout=30;
	 
Copy the code

014: What does the sort_buffer_size parameter do? How do I take effect online?

This parameter is required for the first connection of each connection(session) to improve access performanceset global sort_buffer_size = 2M 
Copy the code

015: How to correctly clean MySQL binlog online?

MySQL binlog logs record data changes in the data, facilitating data recovery based on point in time and location. However, the size of log files becomes larger and larger, requiring a large amount of disk space. Therefore, some logs need to be periodically cleared and manually deleted.

Purge master logs before’2017-09-01 00:00:00′ show master(slave) status\G purge master logs before’2017-09-01 00:00:00′ Purge master logs to’mysql-bin.000001′; Show variables like ‘expire_logs_days’; et global expire_logs_days = 30; # View expiration time and set expiration time

016: What are the Binlog working modes? What are the characteristics and how do enterprises choose?

1.Row; The modified data is recorded in the log, and the same data is modified on the slave server. 2.Statement Each modified data is recorded in the master binlog. Execute the SQL statement executed by the master completely on the slave. 3. Mixed (mixed mode) In combination with the above two modes, if the work requires special functions such as functions or triggers, the mixed mode will select the Statement mode when the data volume is relatively high. Row Level Row mode is not selected

017: A drop database SQL statement was executed by mistake.

1, Stop the master/slave replication, execute the lock table on the master database and refresh the binlog operation. 2. Merge the binlog file generated at 0 o ‘clock and the binlog file generated during the full backup period into the SQL statement mysqlbinlog –no-defaults mysql-bin.000011 000012 >bin. SQL 3. Delete the drop statement in the exported SQL statement and restore the mysql -uroot -pmysql123 < bin

018: How do I restore A single table from mysqldump?

-a Database is used to back up all databases. -b databasename Backs up specified data.

Interview question 019: Explain the principle of MySQL primary/secondary replication and the complete steps for configuring the primary/secondary replication

The principle of master-slave replication is as follows: The master library enables the binlog function and authorizes the slave library to connect to the master library. The slave library obtains relevant synchronization information of the master library through change master, and then connects to the master library for verification. The IO thread of the master library begins to fetch information from the position recorded from master.info according to the request of the slave thread. At the same time, the obtained location and the latest location are sent together with the binlog information to the IO thread of the slave library. The slave library stores the relevant SQL statements in the relay log. Finally, the SQL thread of the slave library applies the SQL statements in the relay log to the slave library. The process is then repeated indefinitely as follows:

1. Enable the binlog function in the primary library and perform full backup. 2. Run the show master status\G command to record the current location and binary file name. 3. Log in to the slave database to restore the full backup file slave status\G

Interview question 020: How to enable the binlog function of the slave library?

Modify the configuration file to add the following configuration

log_bin=slave-bin
log_bin_index=slave-bin.index
Copy the code

You need to restart the service to take effect

Interview question 021: How does MySQL implement two-way master-slave replication and explain the application scenario?

Bidirectional synchronization is mainly used to solve the write pressure of a single primary library. The configuration is as follows

[mysqld]
auto_increment_increment  = 2  Start ID #
auto_increment_offset     = 1  #ID increment interval
log-slave-updates   
Copy the code

From library configuration

[mysqld]
auto_increment_increment  = 2  Start ID #
auto_increment_offset     = 2  #ID increment interval
log-slave-updates 
Copy the code

The mysql service needs to be restarted on both the primary and secondary library servers

Interview question 022: How to implement cascading synchronization of MySQL and describe the application scenario?

Cascading synchronization is mainly used when the secondary database serves as the primary database of other databases. Add the following configuration to the configuration file of the database for cascading synchronization

log_bin=slave-bin
log_bin_index=slave-bin.index
Copy the code

023: How do I solve the primary/secondary replication failure of MySQL?

Log in from the library

1. Run the stop slave command. Set global sql_slave_skip_counter = 1; 3. Execute the start slave command. And check the master/slave synchronization status

To perform primary/secondary synchronization again, perform the following steps to access the primary database

2. Restore the full backup file to the slave database and run the change master command. 3. And check the master/slave synchronization status

Interview question 024: How do I monitor whether primary/secondary replication fails?

mysql -uroot -ppassowrd -e "show slave status\G" |grep -E "Slave_IO_Running|Slave_SQL_Running"|awk '{print $2}'| grep -c Yes by judging the number of Yes to monitor the state of master-slave replication, normal equal to 2Copy the code

Interview question 025: How to implement read/write separation in MySQL database?

2, through other tools (such as mysql-MMM)

Interview question 026: How to recover the production one Master multiple Slave Library?

Execute stop slave or stop service. 2. Repair the slave database. 3

Interview question 027: The production of one master with multiple slave Master database is down. How to manually Recover it?

1, log in each slave database to stop synchronization, and check whose data is the latest, set it as the new master database and let other slave databases synchronize their data. 2, after repairing the master database, the new operation master/slave synchronization steps can be done

If the new primary library was previously read-only, you need to turn this feature off to make it writable
You need to create the same synchronized users and permissions in the new slave as in the previous master
Change master to master_port= start slave
Copy the code

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

1. The development uses root user to write data into the secondary database, resulting in inconsistency between the primary and secondary data, and the front-end does not display the content to be modified (still old data). 2

029: What are the causes of MySQL replication delay? How to solve it?

The hardware resources of the secondary library are poor, which needs to be improved. 3. Network problems, which need to improve network bandwidth

030: Give a feasible backup scheme for enterprise production of large MySQL cluster architecture?

1, more than double master from, master-slave synchronization architecture, and then implement some from the library profession as a backup server 2, to write a script to depots back up the table, and add timing task 3, eventually will push backup service to professional network server, database server local retained a week 4, according to actual condition to keep the backup data backup server (usually 30 days)

Interview question 031: What are database transactions and what are their features? How do companies choose?

Database transaction refers to a logical set of SQL statements, each statement composed of this group of operations, the execution of either success or failure characteristics: atomicity, isolation, persistence, consistency

032: Please explain the concepts of full, additional, cold and hot backup and corporate experience?

Full backup: a complete backup of all data of the database, that is, all data of the current database. Incremental backup: All new data is backed up on the basis of the last backup. Cold backup: A backup operation is performed on the basis of service interruption. For online backup operation, do not affect the normal operation of the database Perfect in the enterprise is basically once a week or days, other time is incremental backup Heat is used for two databases provide service at the same time, in view of the archive mode database Cold standby usage with enterprises in the early and the server data is small, Some significant operations, such as library and table structures, may be performed

033: How to optimize MySQL SQL statement?

Create primary keys and add indexes

034: How to design a backup solution for aN enterprise MySQL cluster?

1. The cluster architecture can adopt the mode of dual-master and multi-slave, but in reality, only one of the dual-master provides services online and the two master provide mutual backup. 2

Interview question 035: Development has a lot of data sent to the DBA for execution. What should the DBA pay attention to?

1. It is necessary to pay attention to whether there are formatting errors in the statement, which may lead to the interruption of the process. 2

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

Select * from table_name where table_name = XXXXX; select * from table_name where table_name = XXXXX; select * from table_name where table_name = XXXXX; select * from table_name where table_name = XXXXX

Interview question 037: Please describe the principle of Chinese data garble in MySQL. How to prevent garble?

If the character sets of the server system, database, and client are inconsistent, unified characters are required

Interview question 038: How to optimize enterprise MySQL production (please describe from multiple angles)?

Improve server hardware resources and network bandwidth. Optimize the mysql service configuration file. 3

Interview question 039: What are the high availability solutions of MySQL?

There are high availability solutions

MySQL+MMM 3 MySQL+MHA 4 MySQL+ HAProxy + DRBD 5 MySQL+ proxy+ AMOeba

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

Run the mysqldump command to back up an SQL file, and then run the sed command to replace it or run 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, 2 d'|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
Copy the code

Interview question 041: How to batch change database character set?

Sed -i ‘s/GBK/UTF8/g’

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

You can use the top free command to analyze the system performance problems. 2. If the problem is caused by the database, you need to view the slow query logs to find and analyze the problem