In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Linux operation and maintenance will be the MySQL enterprise interview questions Daquan recommendation (1) basic written test command inspection 1. Open the MySQL service / etc/init.d/mysqld startservice mysqld startsystemctl start mysqld2. Check whether the port is running lsof-I: 3306netstat-lntup | grep 33063. 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
Command for changing default password in msyql version 5.7 or later
Alter user 'root'@'localhost' identified by' root'4. Log in to MySQL database mysql-uroot-ppassword5. 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-in user select user (); 8. Create the database mingongge of the GBK character set, and view the complete statement create database mingongge DEFAULT CHARSET GBK COLLATE gbk_chinese_ci;9. Create a user mingongge so that it can manage the database mingonggegrant all on mingongge.* to 'mingongge'@'localhost' identified by' mingongge';10. Check what permissions the created user mingongge has show grants for mingongge@localhost11. Exe. Check which users are in the current database, select user from mysql.user;12. Access to the mingongge database use mingongge13. Create an innodb GBK table test with fields id int (4) and name varchar (16) create table test (id int (4), name varchar (16)) ENGINE=innodb DEFAULT CHARSET=gbk;14. View the table structure and the SQL statement desc test;show create table test\ G15 of the table structure. Insert a piece of data "1jinmingongge" insert into test values ('1jinghemingge'); 16. Insert 2 rows of data in batches: "2, migrant worker", "3jinmingonggeedu" insert into test values, ('3Jingminminmingonggeedu'), ('3Mingonggeedu'). Query the record named mingongge select * from test where name = 'mingongge';18. Change the name mingongge where the data id equals 1 to mggupdate test set name = 'mgg' where id =' 1 alternate 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 of mingongge database system mysqldump-uroot-pMgg123.0. -B mingongge > / root/mingongge_bak.sql21. Delete all the data in 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 the database to restore the above deleted data system mysql-uroot-pMgg123.0. Bin.sql
3. Delete the drop statement from the exported sql statement and restore it to the database
Mysql-uroot-pmysql123 < bin.sql
The 018:mysqldump backup of the interview question uses the-A-B parameter, how to restore the single table?
-A this parameter is used to backup all databases (equivalent to-- all-databases)
-B databasename backup specified data (for single database backup)
Interview question 019: describe in detail the principle of MySQL master-slave replication and the complete steps of configuring master-slave
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 the relevant synchronization information of the master library through change master, and then connects to the master library for verification. According to the request of the slave library slave thread, the master library IO thread starts to take the information downwards from the location point recorded by master.info, and sends the location point and the latest location to the slave database IO thread together with the binlog information. The slave library stores the relevant sql statements in the relay-log. Finally, the sql statement in relay-log is applied to the slave library from the SQL thread of the library, and the whole synchronization process is completed, after which the above process will be repeated infinitely.
The complete steps are as follows:
1. Enable the binlog function in the master library, and provide all the necessary files to the slave server.
2. Show master status\ G records the current location information and binary file name
3. Log in and recover complete files from the library
4. Execute the change master to statement
5. Execute start slave and show slave status\ G
Interview question 020: how to turn on the binlog function of the slave library?
Modify the configuration file plus the following configuration
Log_bin=slave-binlog_bin_index=slave-bin.index
Need to restart the service to take effect
Interview question 021:MySQL how to achieve two-way master-slave replication, and explain the application scenario?
Two-way synchronization is mainly used to solve the writing pressure of a single main library. The specific configuration is as follows
Main library configuration
[mysqld] auto_increment_increment = 2 # start IDauto_increment_offset = 1 # ID self-increasing interval log-slave-updates
Slave library configuration
[mysqld] auto_increment_increment = 2 # start IDauto_increment_offset = 2 # ID self-increasing interval log-slave-updates
Both master and slave library servers need to restart the mysql service
Interview question 022:MySQL how to achieve cascade synchronization and explain the application scenario?
Cascading synchronization is mainly used in the slave database as the master database of 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 questions how to solve the 023:MySQL master-slave replication failure?
Login slave library
1. Execute stop slave; to stop master-slave synchronization
2. Then set global sql_slave_skip_counter = 1; skip one step error
3. Finally, execute start slave; and check the master-slave synchronization status.
The master-slave synchronization needs to be redone as follows
Enter the main library
1. Perform a full database and refresh the binlog to view the status of the main database.
2. Restore the complete files to the slave library, and then execute change master
3. Enable the master-slave synchronization start slave; and check the master-slave synchronization status
Interview question 024: how to monitor whether master-slave replication fails? mysql-uroot-ppassowrd-e "show slave status\ G" | grep-E "Slave_IO_Running | Slave_SQL_Running" | awk'{print $2}'| grep-c Yes monitors the status of master-slave replication by judging the number of Yes. How does the 025:MySQL database achieve read-write separation when the normal situation equals to two-sided questions?
1. Realized through the development program.
2. Implement it through other tools (such as mysql-mmm)
Interview question 026: if the production of one master and multiple slaves is down, how to recover it manually?
1. Execute stop slave or stop the service
2. Repair the slave database
3. Then re-operate the main library synchronization
Interview question 027: if the production of one master and multiple slaves is down, how to recover it manually?
1. Log in to each slave library to stop synchronization, and check whose data is up to date, and set it as the new master library to allow other slave libraries to synchronize their data.
2. After the master library has been repaired, the steps of master-slave synchronization of new operations will be fine.
# Note: if the new master library was read-only, you need to disable this function to make it writable # you need to create the same synchronization users and permissions in the new slave library as the previous master library # other slave libraries execute the port of the new change master to master_port= master library. Start slave interview question 028: what database failures have you encountered in your work? please describe two examples?
1. Developers use root users to write data on the slave database, resulting in inconsistency between master and slave data, and the front end does not show what needs to be modified (it is still old data)
2. Master-slave synchronization failure caused by sudden power outage of the server in the intranet test environment
What are the reasons for the replication delay in 029:MySQL? How to solve?
1. Too much slave data needs to be synchronized.
2. The hardware resources of the slave library are poor and need to be improved.
3. Network problems, need to improve the network bandwidth
4. The amount of data written in the main database is large, which requires optimal configuration and hardware resources.
5. Due to the long execution of sql statements, optimization is needed.
Interview question 030: give a feasible backup scheme for enterprises to produce large-scale MySQL cluster architecture?
1. Double master and multi-slave, master-slave synchronous architecture, and then implement a slave database specialty as a backup server
2. Write a script to back up the database and table, and add scheduled tasks.
3. Finally, the backup service is pushed to the private network professional server, and the database server is retained locally for a week.
4. The backup server retains backup data according to the actual situation (usually 30 days)
Interview question 031: what is a database transaction and what are its characteristics? How do enterprises choose?
A database transaction is a logical set of sql statements that make up the set of operations that either succeed or fail.
Features: atomicity, isolation, persistence, consistency
Interview question 032: please explain the concept of complete equipment, additional equipment, cold standby, hot standby and practical experience of the enterprise?
Complete: a complete backup of all the data in the database, that is, all the data in the current database
Add: back up all the new data from the last backup to now
Cold backup: backup operation on the basis of stopping service
Hot backup: carry out online backup operation without affecting the normal operation of the database
Complete in the enterprise is basically once a week or every day, the rest of the time is incremental backup
Hot standby is used when there are two databases providing services at the same time, for databases in archive mode
The use of cold backup is in the early days of the enterprise, when the amount of data is small and the number of servers is small, and some major operations such as libraries and table structures may be performed.
How to optimize the SQL sentence of 033:MySQL?
Establishing primary key and adding index
Interview question 034: how to design backup scheme for enterprise production MySQL cluster architecture?
1. The cluster architecture can adopt the dual-master and multi-slave mode, but in fact, only one master provides services online, and the two masters provide each other.
2. other load balancers that can be read, and then take one of them out for professional backup.
Interview question 035: the developer has a pile of data sent to dba for execution, what should be paid attention to in DBA implementation?
1. You need to pay attention to whether there is a format error in the statement. The execution error will lead to the interruption of the process.
2. We also need to pay attention to whether the execution time of the statement is too long and whether it will exert pressure on the server load and affect the actual production.
Question 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 replace it in batches
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, create a new one, and then import the database and table statements and all the data
Interview question 037: please describe the principle of garbled Chinese data in MySQL, how to prevent garbled code?
Unified characters are required due to the inconsistency of the tripartite character sets of the server system, database and client.
Interview question 038: how to optimize the production MySQL of enterprises (please describe it from multiple angles)?
1. Improve server hardware resources and network bandwidth
2. Optimize mysql service profile
3. Open the slow query log and analyze the problem
What are the 039:MySQL high availability programs, their respective characteristics, and how do enterprises choose them?
The highly available solutions are
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 engine of database tables in batches?
Backup a sql file through the mysqldump command, and then replace it with the sed command
Or execute the following script to modify it
#! / bin/shuser=rootpasswd=123456cmd= "mysql-u$user-p$passwd" dump= "mysqldump-u$user-p$passwd" for database in `$ cmd-e "show databases;" | sed '1Magna 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 question 041: how to change the database character set in batches?
Back up a sql file through the mysqldump command, and then use the sed command to replace sed-I's Universe
Interview question 042: the website opens slowly, please give the troubleshooting method, if the database is slow, how to check and solve, please analyze and give an example?
1. You can use commands such as top free to analyze system performance and other problems.
2. If it is caused by the database, you need to check the slow query log to find and analyze the problem.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.