Linux operations must be the MySQL Enterprise face examination questions Daquan

(1) Examination of basic written order

1. Turn on MySQL service
/etc/init.d/mysqld start
Service mysqld Start
Systemctl Start mysqld

2. Detect if the port is running
Lsof-i: 3306
Netstat-lntup |grep 3306

3. Set a password 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;
MSYQL 5.7 or later modify the default password command
Alter user ' root ' @ ' localhost ' identified by ' root '

4. Log in 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-v
Mysql-uroot-ppassowrd-e "Use Mysql;select version ();"

7. View the currently logged on user.
Select User ();

8. Create a database Mingongge for the GBK character set and view the complete statement of the built library
Create DATABASE Mingongge DEFAULT CHARSET GBK COLLATE gbk_chinese_ci;

9. Create a user Mingongge so that it can manage the database Mingongge
Grant all on mingongge.* to ' mingongge ' @ ' localhost ' identified by ' Mingongge ';

10. See what permissions the user Mingongge has created
Show grants for [email protected]

11. See which users are in the current database
Select User from Mysql.user;

12. Enter the Mingongge database
Use Mingongge

13. Create a 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 SQL statements that construct a table structure and table structure
DESC test;
Show CREATE TABLE Test\g

15. Insert a data "1,mingongge"
INSERT into test values (' 1 ', ' Mingongge ');

16. Bulk Insert 2 rows of data "2, migrant brother", "3,mingonggeedu"
INSERT into test values (' 2 ', ' migrant brother '), (' 3 ', ' mingonggeedu ');

17. Query for records with the name Mingongge
SELECT * FROM test where name = ' Mingongge ';

18. Change the name of the data ID equal to 1 mingongge 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. Do not exit the database, complete the backup Mingongge database
System mysqldump-uroot-pmgg123.0. -B Mingongge >/root/mingongge_bak.sql

21. Delete all data from the test table and view
Delete from test;
SELECT * from Test;

22. Delete the table test and Mingongge database and view
drop table test;
Show tables;
Drop database Mingongge;
show databases;

23. Do not exit database recovery above deleted data
System mysql-uroot-pmgg123.0. </root/mingongge_bak.sql

24. Modify the GBK character set of the library 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 normal index on the name field.
ALTER TABLE test add primary key (ID);
CREATE INDEX Mggindex on test (name (16));

26. Insert the Phone number field (Shouji) after the field name, type char (11).
ALTER TABLE test add Shouji char (11);
#默认就是在最后一列后面插入新增列

27. Insert 2 records on all fields (self-setting data)
INSERT into test values (' 4 ', ' P ', ' Li ', ' 13700000001 '), (' 5 ', ' + ', ' Zhao ', ' 13710000001 ');

28. Create a normal index on the phone field for the first 8 characters.
CREATE INDEX SJ on test (Shouji (8));

29. View information such as index and index type created.
Show index from test;
Show CREATE TABLE Test\g
#下面的命令也可以查看索引类型
Show keys from Test\g

30. Delete the index of the Name,shouji column.
Drop index SJ on test;
Drop index mggindex on test;

31. Set up a federated index on the first 6 characters of the Name column and the first 8 characters of the phone column.
CREATE INDEX Lianhe on test (name (6), Shouji (8));

32. Check the phone number starts with 137, the record with the name Zhao (insert early)
SELECT * FROM test where Shouji like ' 137% ' and name = ' Zhao ';

33. Query the execution plan for the above statement (whether to use federated indexes, etc.)
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. Recover SELECT permissions for Mingongge users
Revoke select on mingongge.* from [email protected];

36. Delete Mingongge Users
drop user [email protected];

37. Delete the Mingongge database.
Drop Database Mingongge

38. Use Mysqladmin to close the database
mysqladmin-uroot-pmgg123.0. Shutdown
Lsof-i: 3306

39.MySQL Password lost, please find it?
Mysqld_safe--skip-grant-tables & #启动数据库服务
Mysql-uroot-ppassowrd-e "Use Mysql;update user Set passowrd = PASSWORD (' newpassword ') where user = ' root '; flush Privile GES; "

(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-yuan relationship, and the operation of the information is to establish a
or more relational tables, the most important feature is a two-dimensional table that accesses data through SQL structure query statements, keeping the data
Very strong consistency.

Interview 002: Please state the typical product, feature and application scenario of relational database.
MySQL Internet enterprise commonly used
Oracle Large Legacy Enterprise application software
such as data backup, complex connection query, consistent data storage, or the use of MySQL or other traditional relational database is the most appropriate

Interview 003: Please explain the concept and main features of non-relational database.
Non-relational databases are also known as NoSQL databases, and data storage does not require a unique fixed table structure
Characteristics: High performance, high concurrency, easy installation

Interview 004: Please name the non-relational database typical products, features and application scenarios?
memcaced Pure Memory
Redis Persistent Cache
MongoDB Document-oriented
If you need a short-response query operation, a data store with no good schema definition, or a data store with frequent schema changes or NoSQL

Interview 005: Please describe in detail the SQL statement classification and corresponding representative keywords.
The SQL statements are categorized as follows
DDL data definition language, used to define database objects: libraries, tables, columns
Representative keywords: create alter drop
DML data manipulation language, used to define database records
Representative Keywords: insert Delete update
DCL Data Control language for defining access and security levels
Representative Keyword: Grant deny revoke
DQL data Query Language, used to query the record data
Typical 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 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 [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 a number of service processes, they are independent of each other, non-impact external services, easy to save server resources and post-schema expansion
There are two ways to configure multiple instances:
1, one instance one configuration file, different port
2. Configure different instances under the same configuration file (MY.CNF), based on the Mysqld_multi tool

Interview 010: How to enhance MySQL security, please give feasible concrete measures?
Delete the default user that the database does not use
Configure the appropriate permissions (including remote connections)
Do not enter the password for the database at the command line interface
Regularly change passwords and enhance the complexity of passwords

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 delete data can be restored, it is a slow deletion speed
The latter is physically deleted, unrecoverable, and it is the overall removal speed fast

Interview question 013:mysql How to solve the sleep thread too much?
1, can kill the sleep process, kill PID
2, modify the configuration, restart the service
[Mysqld]
Wait_timeout = 600
Interactive_timeout=30
If the production server is not ready to restart, you can use the following method to resolve
Set Global wait_timeout=600
Set global 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
Set Global sort_buffer_size = 2M

Interview 015: How to properly clean up MySQL binlog online?
The Binlog log in MySQL records data changes in data to facilitate point-in-time and location-based recovery of data
However, the size of the log file will grow larger, with a large amount of disk space, so you need to periodically clean up some of the log information
Manual removal:
First look at the Binlog file name that the master/slave library is using
Show Master (slave) Status\g
Be sure to back up before deleting
Purge master logs before ' 2017-09-01 00:00:00 ';
#删除指定时间前的日志
Purge master logs to ' mysql-bin.000001 ';
#删除指定的日志文件
Automatic deletion:
Let the system automatically delete logs by setting the Binlog expiration time
Show variables like ' expire_logs_days ';
Set global expire_logs_days = 30;
#查看过期时间与设置过期时间

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 being modified, and then the same data is modified on the slave side
2.Statement (statement mode)
Each modified data is fully recorded in the Binlog of the Master Library master, and the SQL statement executed at Master is fully executed on slave
3.mixed (Mixed mode)
In combination with the previous two modes, if there is a need for special functions such as functions or triggers in the work, the blending mode
When the amount of data is higher, it chooses the statement mode instead of the row level row 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 points of the full-ready)
2. Merge and export the Binlog file from 0 o'clock to the Binlog file that is fully available during the failure period to the SQL statement
Mysqlbinlog--no-defaults mysql-bin.000011 mysql-bin.000012 >bin.sql
3. Delete the drop statement from the exported SQL statement and restore it 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 (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 turns on the Binlog feature and authorizes connection from the library to the main library, from the library through the change master to get the relevant synchronization information of the main library
Then connect to the main library to verify that the main library IO thread is based on the request from the library slave thread, starting from the Master.info location point
Start fetching information, and send the location and the latest location to the Binlog message from the library IO thread.
The SQL statements from the library are then stored in the Relay-log, and finally from the library's SQL thread, the Relay-log is applied
To from the library, so that the entire synchronization process is complete, and then the process will be infinitely repeated
The complete steps are as follows:
1, the main library opens the Binlog function, and carries on the full preparation, pushes the full file to the slave library server
2, show master Status\g record the current location information and the binary file name
3, log in from the library to recover full files
4. Execute 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-bin
Log_bin_index=slave-bin.index
Need to restart Service

Interview question 021:mysql How to achieve two-way mutual master from replication, and explain the application scenario?
Bidirectional synchronization is mainly used to solve the pressure of the single main library write, the specific configuration is as follows
Main Library Configuration
[Mysqld]
Auto_increment_increment = 2 #起始ID
Auto_increment_offset = 1 #ID自增间隔
Log-slave-updates
Configure from Library
[Mysqld]
Auto_increment_increment = 2 #起始ID
Auto_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?
Cascade synchronization is primarily used in the main library from which libraries need to be made as other databases
Add the following configuration to the database configuration file that requires cascading synchronization
Log_bin=slave-bin
Log_bin_index=slave-bin.index

Interview question 023:mysql master-slave replication failure how to solve?
1, login from the library, execute stop slave; stop master-Slave synchronization
Then set global sql_slave_skip_counter = 1; skip One-step error
Finally, start slave is executed and the master-slave synchronization status is checked
2, need to re-master and Slave synchronization operation
Go to the main library, complete the database and refresh the Binlog to see the status of the main library
Recover the full files from the library and then perform the change master
Turn on master-slave sync start slave and view 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
By judging the number of Yes to monitor the master-slave replication status, the normal condition equals 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?
Execute stop slave or stop service
Fix it from the library database.
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 let others synchronize their data from the library
2, repair the main library, the new operation master-slave synchronization of the steps can be
#需要注意的新的主库如果之前是只读, you need to turn off this feature to make it writable
#需要在新从库创建与之前主库相同的同步的用户与权限
#其它从库执行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 old data)
2, intranet test environment server suddenly power outage caused master-slave synchronization failure

What are the causes of replication delays in interview questions 029:mysql? How to solve?
1. Too many data to be synchronized from the library
2, from the library of poor hardware resources, need to improve
3, network problems, need to improve network bandwidth
4, the main library data write a large amount, need excellent configuration and hardware resources
5, SQL statement execution is too long, need to optimize

Interview 030: Give the enterprise to produce large MySQL cluster architecture feasible backup scheme?
1, dual master multiple slave, master-slave synchronization of the architecture, and then implement some from the library professional as a backup server
2, write the script to implement the sub-database sub-table for backup, and join the scheduled task
3. Finally push the backup service to the intranet Professional server and keep the database server locally for one week
4, Backup server according to the actual situation to retain backup data (generally 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
Execution is either successful or failed.
Features: 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, backing up all data from the current database
Add: Back up to now all new data on the basis of the last backup
Cold: Backup operation based on stop service
Hot-Standby: Perform online backup operations without affecting the normal operation of the database
Fully prepared in the enterprise is basically a weekly or a day, the other time is incremental backup
Hot standby is a situation where two databases are in service at the same time, the database for archiving mode
Cold standby use of enterprises in the initial stage, the volume of data is not large and the number of servers, you may perform some library, 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?
Cluster architecture can adopt dual master and multi-slave mode, but the actual dual master only one main online service, between the two master to do the mutual preparation
Additional slave-readable load balancing, 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, execution error causes the process to break
2, also need to pay attention to whether the execution time of the statement is too long, will have 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 table structure of the library-D only export the table structure, and then bulk replace
2. Export all data in the library (without generating new data)
3. Then globally replace set names = xxxxx
4, delete the original library and table, and create a new, and then import the database and build the 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 configuration file
3. Turn on the slow query log and analyze the problem

Interview question 039:mysql What are the high-availability programs, their respective characteristics, and how do companies choose?
High-availability scenarios include: Master-slave architecture, MMM MHA MYSQL+HAPROXY+DRBD Mysql+proxy+amoeba

Interview 040: How do I bulk change the engine of a database table?
Back up an SQL file with the mysqldump command and replace it with the SED command
or execute the following script to modify
#!/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 indump -e "show tables from $databses;"|sed ‘1d‘
Do
$cmd "ALTER TABLE $database. $tables engine = MyISAm;"
Done
Done

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?
You can use commands such as top free to analyze system performance issues
If this is caused by a database, you need to look at the slow query log to find and analyze the problem

Linux operations must be the MySQL Enterprise face examination questions Daquan