Linux operations must be the MySQL enterprise face question Daquan recommendation

Linux operations must be the MySQL enterprise face question Daquan recommendation

(1) Examination of the basic written order 1. Turn on MySQL service

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

2. Detect if the port is running

Lsof-i: 3306netstat-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 use r = ' root '; flush privileges;

MSYQL 5.7 or later modify the default password command

Alter user ' root ' @ ' localhost ' identified by ' root '

4. Log in to 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 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 ()) 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 (one); #默认就是在最后一列后面插入新增列

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# The following command can also view the index type 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. Build 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 test table to MyISAM

ALTER TABLE Test Engine=myisam;

35. Recover SELECT permissions for Mingongge users

Revoke select on mingongge.* from [email protected]t;

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. Shutdownlsof-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 privileges; "

(2) MySQL Operation basic knowledge interview question Question 001: Please explain the relational database concept and main features?

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.

1, MySQL Internet enterprises commonly used
2. Oracle large-scale traditional enterprise application software
3, 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?

1, memcaced Pure memory
2. Redis Persistent Cache
3. 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?

1. Delete the default user that the database does not use
2. Configure the appropriate permissions (including remote connections)
3. Do not enter the password of the database in the command line interface
4, regular change 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 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 = 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 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 ';
ET 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 opens the Binlog function and authorizes the main library to connect from the library, obtains the relevant synchronization information from the library through change master, and then connects the main library for verification, and the main library IO thread is based on the request from the library slave thread. Starting from the location of the record Master.info start to fetch information, at the same time, the location point and the latest location and the Binlog information sent from the library IO thread, from the library to the relevant SQL statements in Relay-log, and finally from the SQL thread of the library to apply the SQL statements in the Relay-log from the library , the entire synchronization process is complete, and then the process is repeated indefinitely
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-binlog_bin_index=slave-bin.index

Need to restart 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 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 #起始IDauto_increment_offset = 1 #ID自增间隔log-slave-updates

Configure from Library

[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?

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-binlog_bin_index=slave-bin.index

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

Login from Library

1. Execute stop slave; stop master-Slave synchronization
2, then set global sql_slave_skip_counter = 1; skip One-step error
3. Finally execute the start slave and check the master-slave synchronization status

The following steps are required for master-slave synchronization:
Go to the main library

1. Make a full database and refresh the Binlog to see the status of the main library
2. Restore the full file to the library and then perform the change master
3. Turn on master-slave synchronization 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 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 the database from the library
3. 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 this feature off so that it can write # users and permissions that need to be created in the new library with the same synchronization as before the main library # other ports from the library that perform the change master to master_port= 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, either successfully or unsuccessfully when executed.
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?

1, the cluster architecture can adopt dual-master multi-slave mode, but the actual dual master only one main online service, between the two masters to do mutual preparation
2, additional from can be 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, 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?

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 an SQL file with the mysqldump command and 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 system performance and other aspects of the problem
2, because of the database cause, you need to check the slow query log to find and analyze the problem

Linux operations must be the MySQL enterprise face question Daquan recommendation