Modify parameter variables without restarting mysql
Https://www.cnblogs.com/huidaoli/p/3232265.html
Everyone knows that updating mysql configuration my.cnf requires a restart of mysql to take effect, but sometimes when mysql is online, it does not necessarily allow you to restart, what should I do?
Look at an example:
1 2 3 4 5 6 7 8 9 10mysql > show variables like 'log_slave_updates' +-+-+ | Variable_name | Value | +-+-+ | log_slave_updates | OFF | +-+-+ 1 row in set (0.00 sec) mysql > set global log_slave_updates=1 ERROR 1238 (HY000): Variable 'log_slave_updates' is a read only variable
Did you see that? Wrong report!
Later, I checked the information and found that there is something called gdb, which feels quite awesome and can change the mysql parameters online. Please see the example:
1 2 3 4 5 6 7 8mysql > system gdb-p $(pidof mysqld)-ex "set opt_log_slave_updates=1"-batch mysql > show variables like 'log_slave_updates' +-+-+ | Variable_name | Value | +-+-+ | log_slave_updates | ON | +-+-+ 1 row in set (0.00 sec)
But for some repeatable parameters, you can't change them directly with set, so what do you do at this time? The foreigner gave a solution:
1 2 3 4 5 6 7 8 9 10mysql > 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,hehehe...
=
Many parameters of mysql need to be restarted to take effect. Sometimes conditions are not allowed, so you can use gdb as a last resort.
Let's take a look before the revision.
Mysql > show global variables like'% connection%'
+-+ +
| | Variable_name | Value |
+-+ +
| | character_set_connection | latin1 |
| | collation_connection | latin1_swedish_ci |
| | max_connections | 151 | |
| | max_user_connections | 0 | |
+-+ +
4 rows in set (0.01sec)
Use gdb to modify
[root@asm ~] # gdb-p $(pidof mysqld)-ex "set max_connections=1500"-batch
Other parameters can be modified accordingly.
Then check the current configuration
Mysql > show global variables like'% connection%'
+-+ +
| | Variable_name | Value |
+-+ +
| | character_set_connection | latin1 |
| | collation_connection | latin1_swedish_ci |
| | max_connections | 1500 | |
| | max_user_connections | 0 | |
+-+ +
4 rows in set (0.00 sec)
You can see that the modification was successful, but there are risks in using gdb, especially in the production environment, which may cause the process down to drop, only as a last resort.
