Summary of methods for modifying parameter variable settings in mysql

The most common method to modify mysql parameters is directly in my. in cnf, modify and restart mysql. You can also use commands to directly modify mysql without repeating mysql. However, if the latter restarts mysql, the variable set will be invalid.

Log on to the mysql database system and use show processlist to view the current running status.

Mysql> show processlist;

+ -- + ----- + ------- +

| Id | User | Host | db | Command | Time | State | Info

+ -- + ----- + ------- +

| 207 | root | 192.168.0.2: 51621 | mytest | Sleep | 5 | NULL

| 208 | root | 192.168.0.2: 51622 | mytest | Sleep | 5 | NULL

| 220 | root | 192.168.0.2: 51676 | mytest | Query | 84 | Locked |

 

In general, after modification, I. cnf needs to restart mysql to make this parameter take effect. Someone said that/etc/init. d/mysqld reload can be used.

However, after I reload the variable, the variable value remains unchanged.

The procedure is as follows:

The Code is as follows:	Copy code
Mysql-uroot-psss @ pas-e "show variables" | grep max_connection

The result is max_connections 1024.

Modify my. cnf

The Code is as follows:	Copy code
# Max_connections 2048

Then/etc/init. d/mysqld reload

The Code is as follows:	Copy code
Mysql-uroot-psss @ pas-e "show variables" | grep max_connection

The result is still max_connections 1024.

Therefore, I believe that the configuration parameters of mysql cannot be modified online. Therefore

The Code is as follows:	Copy code
Mysql-uroot-psss @ pas-e "set GLOBAL max_connections = 2048" Mysql-uroot-psss @ pas-e "show variables" | grep max_connection Result: # Max_connections 2048

Therefore, run the set GLOBAL variable name variable value command to modify the mysql configuration parameters online.

References

1> modify global variables

The Code is as follows:	Copy code
[Root @ admin root] # mysql Mysql> show variables like '% sort_buffer_size % '; + --------- + ---- + | Variable_name | Value | + --------- + ---- + | Sorting _ buffer_size | 6291448 | + --------- + ---- + 1 rows in set (0.00 sec)

Use the set GLOBAL command to set GLOBAL variables

The Code is as follows:	Copy code
Mysql> set GLOBAL sort_buffer_size = 7000000; Query OK, 0 rows affected (0.00 sec) Mysql> show variables like '% sort_buffer_size % '; + --------- + ---- + | Variable_name | Value | + --------- + ---- + | Sorting _ buffer_size | 6291448 | + --------- + ---- + 1 rows in set (0.00 sec)

The current value of this parameter does not change. Exit and then re-connect.
Mysql> exit
Bye
Reconnect to the mysql database

The Code is as follows:	Copy code
[Root @ admin root] # mysql Mysql> show variables like '% sort_buffer_size % '; + --------- + ---- + | Variable_name | Value | + --------- + ---- + | Sorting _ buffer_size | 7000000 | + --------- + ---- + 1 rows in set (0.00 sec)

The new parameter value takes effect.
2> modify session-level variables
Run the show variables command to view the value of the current parameter. like 'pattern' is used for pattern matching to find the specified parameter.

The Code is as follows:	Copy code
Mysql> show variables like '% sort_buffer_size % '; + --------- + ---- + | Variable_name | Value | + --------- + ---- + | Sorting _ buffer_size | 6291448 | + --------- + ---- + 1 rows in set (0.00 sec)

Use the set SESSION command to set the new value of the SESSION-level variable

The Code is as follows:	Copy code
Mysql> set SESSION sort_buffer_size = 7000000; Query OK, 0 rows affected (0.00 sec)

Modifying session-level variables takes effect immediately for the current session

The Code is as follows:	Copy code
Mysql> show variables like '% sort_buffer_size % '; + --------- + ---- + | Variable_name | Value | + --------- + ---- + | Sorting _ buffer_size | 7000000 | + --------- + ---- + 1 rows in set (0.00 sec) Mysql> exit Bye

This parameter restores the original value after you exit the reconnect operation.

The Code is as follows:	Copy code
[Root @ admin99 root] # mysql Mysql> show variables like '% sort_buffer_size % '; + --------- + ---- + | Variable_name | Value | + --------- + ---- + | Sorting _ buffer_size | 6291448 | + --------- + ---- + 1 rows in set (0.00 sec)

Foreigners gave a solution

The Code is as follows:	Copy code
Mysql> show slave status G ... Replicate_Do_DB: test ... Mysql> system gdb-p $ (pidof mysqld) -Ex 'call rpl_filter-> add_do_db (strdup ("hehehe") '-batch Mysql> show slave status G ... Replicate_Do_DB: test, hehehehe ...