A summary of some methods for MySQL modifying parameter variable settings

Login MySQL database system, with show processlist analysis to see the current running state.

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 |

Under normal circumstances, after the modified my.cnf, you need to restart MySQL to allow this parameter to take effect. Some people say that you can use/etc/init.d/mysqld reload

But after the author reload, again to see the value of the variable remains unchanged.

The following is the author's operating procedures:

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

Result is max_connections 1024

Modify MY.CNF to make

The code is as follows	Copy Code
Max_connections 2048

And 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, the author believes that this can not be online to modify the MySQL configuration parameters. So

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 The results are: Max_connections 2048

So, use the command set GLOBAL variable name variable value; This allows you to modify the MySQL configuration parameters online.

Some references

1> Modifying global variables

The code is as follows	Copy Code
[Root@admin root]# MySQL Mysql> Show variables like '%sort_buffer_size% '; + ————————— + ———— + | variable_name | Value | + ————————— + ———— + | Sort_buffer_size | 6291448 | + ————————— + ———— + 1 rows in Set (0.00 sec)

Setting global variables with the SET global command

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 | + ————————— + ———— + | Sort_buffer_size | 6291448 | + ————————— + ———— + 1 rows in Set (0.00 sec)

The current value of this parameter does not change, exit first, and then reconnect
Mysql> exit
Bye
Reconnect MySQL Database

The code is as follows	Copy Code
[Root@admin root]# MySQL Mysql> Show variables like '%sort_buffer_size% '; + ————————— + ———— + | variable_name | Value | + ————————— + ———— + | Sort_buffer_size | 7000000 | + ————————— + ———— + 1 rows in Set (0.00 sec)

New parameter values are in effect
2> Modify session-level variables
Use the show variables command to view the value of the current parameter, like ' patterns ' for pattern matching, to find the specified parameters

The code is as follows	Copy Code
Mysql> Show variables like '%sort_buffer_size% '; + ————————— + ———— + | variable_name | Value | + ————————— + ———— + | Sort_buffer_size | 6291448 | + ————————— + ———— + 1 rows in Set (0.00 sec)

To set a new value for a session-level variable with the Set sessions command

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

Modifying a session-level variable takes effect immediately for the current session

The code is as follows	Copy Code
Mysql> Show variables like '%sort_buffer_size% '; + ————————— + ———— + | variable_name | Value | + ————————— + ———— + | Sort_buffer_size | 7000000 | + ————————— + ———— + 1 rows in Set (0.00 sec) Mysql> exit Bye

This parameter restores the original value after the reconnection is exited

The code is as follows	Copy Code
[Root@admin99 root]# MySQL Mysql> Show variables like '%sort_buffer_size% '; + ————————— + ———— + | variable_name | Value | + ————————— + ———— + | Sort_buffer_size | 6291448 | + ————————— + ———— + 1 rows in Set (0.00 sec)

The foreigner gave a solution

code is as follows	copy code
mysql> Show slave status G ...      replicate_do_db:test ... Mysql> system Gdb-p $ (pidof mysqld)          -ex ' Call Rpl_fi lter->add_do_db (StrDup ("hehehe")) '-batch mysql> show slave status G ...       replicate_do_db:test,hehehe ...