How to adjust server variables to adapt to individual enterprise needs

Different enterprises may have different Personalized Requirements for databases. Such as the date display format. To meet the requirements of different enterprises in this regard, the concept of server variables is put forward in the MySQL database. By adjusting these variables, the database administrator can establish an application environment that meets the actual situation of the enterprise. Here, I will talk about how to adjust server variables and related precautions based on my work experience.

1. view the values of existing system variables

To adjust the server variables, the database administrator must first understand the existing variables and related values. You can use the show variables command to view available variables and default values in the system. However, there are more than 200 system variables, which makes it troublesome to search. Therefore, you can use the like Query condition and wildcard characters to perform quick search. As shown in, if we use 'date % ', the system will list all variable names starting with 'date. This is very similar to the query conditions in SQL statements.

 

Using wildcards and Like keywords can help database administrators quickly locate related variables. However, when using wildcards, you must note that the single quotation marks on both sides cannot be forgotten. Otherwise, the system reports an error. Second, in this command line environment, it is not case sensitive. That is to say, 'date % 'and 'date %' are equivalent. This is a good feature for some case-insensitive database administrators. However, when entering a conditional statement, you must note the details, that is, spaces. When querying, the system does not filter spaces by itself. What are the differences between 'date % 'and 'date %' statements? A rough look, it seems to be the same. In fact, the two are different content. The next one has a space before date, but the first one does not. The results obtained from the database are the opposite. Because there are no spaces in front of the system variables, using the following statement will not find any available variables. Therefore, when querying, pay attention to the influence of spaces on the query statement.

Ii. differentiate between global variables and session Variables

In the development environment, variables generally have a distinction between global variables and local variables. The core difference between the two is that they have different scopes. This definition also applies to MySQL databases. MySQL database variables can be divided into global variables and session variables. The main difference between the two lies in the different scopes.

Global variables, as the name implies, affect global operations on the server. When the database server is started, the system initializes all global variables to the default value. Of course, the database administrator can specify related options in the selection file or command line as needed to change these default values. Even after the server is started, the database administrator can change the dynamic Global variable by executing the Set Global variable name.

The session variable is only valid for a specific session and does not affect other sessions. The server also maintains session variables for each client connection. If no value is set for a specific session during connection, the system uses a global variable to initialize the session variable. Similarly, you can use the Set Session variable name to change the dynamic Session variable.

When changing global variables or session variables, pay attention to permission issues. If you change the global variables, you must have the Super permission. However, if you change the session variables, no special permissions are required by default. That is to say, users can change their own session variables, but cannot change other customers' session variables. However, we do not recommend that you change the relevant session variables.

Iii. Effective time after change

When will the change of global or local variables take effect? This is what the database administrator needs to pay attention. When explaining this knowledge point, I still need to emphasize that you should pay attention to the differences between global variables and session variables. Only after understanding the difference can we have a deeper understanding of the effective time of variable changes.

Generally, any client that accesses the global variables can see the changes to the global variables. When the global variable is changed, it only affects the Client Connected to which the corresponding session variable is initialized from the global variable after the change, and does not affect the session variables of the client that has been connected, this is true even when the Set statement is executed. This mainly describes two points. First, changes to global variables will take effect only after the system is restarted, not like other systems. It will also take effect if you do not restart the database system. Second, it will not take effect for connected customers, but will only be valid for new sessions after the change. To test whether the change of a global variable takes effect, the Database Administrator does not need to restart the server, but only needs to create a new session to perform the test. After the test is complete, if you are sure to use this change, you do not need to restart the server. You only need to close all current sessions and ask the user to re-establish the session.

Similarly, after the session variable is changed, the server does not need to be restarted to take effect. However, unlike global variables, session variables are only valid for their own sessions. This means that after the session variable is changed, it will be valid in the current session.

If the database administrator wants to synchronize all application environments, for example, the whole information application adopts the same time format, in this case, the author suggests using global variables instead of session variables. The scope of comparing session variables is relatively limited.

Iv. Test and backup after modification

Whether it is a global variable or a session variable, there will be a test process after the change. For the test of global variable change, we recommend that you use a small range of tests first. For example, after changing the date display format, in order to test its validity, the database administrator is recommended to create a new session and check whether the change takes effect. Do not rush to reset all sessions. In this case, if the change fails or is not ideal, it will not affect the connected sessions (only valid for the new session after the change ), the negative effects can be controlled within the minimum range. However, to do so, you still need to control the entire time. If the interval is long, for example, 24 hours a day, a problem may occur. For example, the finance department is linked before the change, and a user in the purchasing department is connected after the change. At this time, the time display format may be different for the tables exported by two different users. This may mislead you. This policy has both advantages and disadvantages. The database administrator should shorten the test time as much as possible. If the test takes a long time, it is best to build another server for testing instead of directly testing on the production server.

In addition, it is best to back up the changed configuration independently. The backup here is not an overall backup of the database, but a backup of the configuration file. In addition, a detailed description is required. For example, why is the adjustment and adjustment time.

In order to maintain the consistency before and after the application, the adjustment time is also exquisite. Generally, do not make adjustments within one working day. For example, make adjustments at 12 noon. In this case, the documents in the morning and afternoon may be in disorder. In some special applications, there may be more strict requirements on the adjustment time. For the background database of financial software, the adjustment of related global variables may need to take into account the problems during the accounting period. It can only be adjusted at the end of the month or at the end of the year. Therefore, when adjusting a global variable, it will affect all users. Therefore, you need to seek comments from all users before making the changes. Then, select an appropriate adjustment time based on the actual situation and user requirements of the enterprise. Generally, an experienced database administrator will know which global variables will affect the end user and which global variables will only affect system maintenance. Then, the impact scope of the variable will be changed as needed to determine whether the variable needs to be confirmed by the end user. If you can make such a judgment, it is the best. Minimize the impact on users.