Mysql – Innodb_buffer_pool_reads ,Innodb_buffer_pool_read_requests reset

MySQL

Except from restart,when Innodb_buffer_pool_reads , Innodb_buffer_pool_read_requests parameters reset to zero?

Best Answer

I don't think you reset two specific status variables.

You can only reset all of them using FLUSH STATUS;

This option adds the current thread's session status variable values to the global values and resets the session values to zero. Some global variables may be reset to zero as well. It also resets the counters for key caches (default and named) to zero and sets Max_used_connections to the current number of open connections. This information may be of use when debugging a query. See Section 1.7, “How to Report Bugs or Problems”.

Try running FLUSH STATUS; and see if this works.

Related Solutions

MySQL – How to Reset Root Password in LAMPP Server on Ubuntu

The skip-grant-tables solution is not a recommended one, for a couple of reasons:

It makes the database vulnerable (even with skip-networking)
It requires taking your database down twice.

A solution which requires taking the database down just once is as follows:

Create a temporary SQL text file, say /tmp/init.sql
Within this file, write:

SET PASSWORD FOR root@localhost = PASSWORD('the_new_password');
Add the following to your MySQL config file (on Ubuntu this is on /etc/mysql/my.cnf), under the [mysqld] section:

init-file=/tmp/init.sql
Restart MySQL once. The init file is read and executed upon startup. The password is reset.
Proceed to remove the init-file=/tmp/init.sql entry from my.cnf (do not forget this). Even as the server is up and running.
Remove the /tmp/init.sql file.

There are even more solutions! Please refer to a past blog post of mine. Make sure to check out comment #4 by strcmp

Mysql – reset thesql root password on ubuntu

Here is a more secure way to reset password without skip-grant-tables

Suppose you want root to have myn3wp@ssw0rd as the password

Step 01 : Create a script to execute when mysqld first start up

Create a file called /var/lib/mysql/init-file.sql with these two lines

GRANT ALL PRIVILEGES ON *.* to root@localhost
IDENTIFIED BY 'myn3wp@ssw0rd' WITH GRANT OPTION;

Step 02 : Run these three(3) lines in the OS

chown mysql:mysql /var/lib/mysql/init-file.sql
service mysql restart --init-file=/var/lib/mysql/init-file.sql
rm -f /var/lib/mysql/init-file.sql

Step 03 : THERE IS NO STEP 03. YOU ARE DONE !!!

Give it a Try !!!

CAVEAT

I used to recommend two restarts of mysql that required editing my.cnf
I learned a better approach from @ShlomiNoach
This answer improves on it more by not editing my.cnf at all

UPDATE 2013-06-17 07:05 EDT

OK Since mysqld for Ubuntu does not like init-file on the command, you must edit the my.cnf. Please do these next two steps

STEP 01) Add these lines to my.cnf under the [mysqld] group header

[mysqld]
init-file=/var/lib/mysql/init-file.sql

STEP 02) Allow mysql to read files from /etc/mysql

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the line:

/etc/mysql/*.sql r,

STEP 03) Restart MySQL

This should work for you. Give it a Try !!!