Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the knowledge points of mysql". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the knowledge points of mysql"?

1. Synchronization mode

Binlog and pos synchronous, asynchronous, semi-synchronous, GTID-based synchronization, single-thread synchronization, multi-thread synchronization

2. Configuration of key parameters

Innodb_buffer_pool_size, key_buffer_size, innodb_flush_log_at_trx_commit, bin, sync_binlog, innodb_file_per_table, character_set_server, max_connections, transaction_isolation isolation level, default is Repeatable Read, if binlog is row mode, it is also often set to Read Committed level

3. Database environment collection tools

Pt-mysql-summary

4. Database performance

Instance load (CPU load, IO load, system load)

Slow query situation

SQL delay situation

Lock condition

Dirty page situation

Access model: the access model is whether the database is responsible for reading more or writing less, and whether it is high concurrency, and so on.

To solve the above problems, you can use the pt-mysql-summary tool to obtain and analyze them, or you can view them in real time through the following two tools:

Innotop

Orzdba

5. Data security

Permission security: (1) the database must set a user password that conforms to the password complexity. (2) it is forbidden to set% login machine to the user. (3) only the account with minimum permission is given to the business, and the login machine is restricted.

Data consistency: to ensure data consistency, remember to use pt-table-checksum periodically to check whether the master-slave data is consistent, and if it is inconsistent, you can use pt-table-sync to fix it.

Data security: backup strategy (database backup, binlog backup). Backup validity and availability

6. General operation

General operations: general operations generally include the following

Start and stop.

General database changes.

Index optimization.

Configuration modification.

Backup of the database.

Migration of data.

Switch.

Common failure plans: 1, master failure, how do we deal with it?

The plan in the extreme case: the situation in which the master and slave of the database are dead.

Regular exercises!

7. Architecture:

Understand the principles of MHA, MMM, LVS, keepalived, not just building. Advantages and disadvantages and troubleshooting are important.

MHA, for example, how does it detect an instance exception? How do the components work together? How does MHA ensure data consistency when switching? If there are multiple slave at the back end, how does it choose which slave to switch, and how do other slaves handle it?

8. Online operations: twelve operations that can easily lead to online failures and techniques to avoid misoperation

Back up before modifying or deleting data, back up first (say important things three times).

There must be a fallback plan for online changes.

Add sleep in the middle of batch operation.

DDL operations should be cautious, and it is best to use pt-online-schema-change for alter operations on large tables.

The change operation is tested in the test environment first.

Brush the dirty page before restarting the database.

Batch deletion of a large number of binlog is prohibited.

For the change operation, be sure to write detailed steps, and review.

Confirm the environment again before pressing enter.

If your action may make the situation worse, please stop the operation.

Quickly deal with disk fullness and use tune2fs to free file system reserved blocks.

Modify the memory variable instead of restarting when the number of connections is full, as follows:

Gdb-p pid-ex "set max_connections=1000"-batch#pid is the corresponding pid of mysqld

At this point, I believe you have a deeper understanding of "what are the knowledge points of mysql?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.