Skip to content

Tag Archives: mysql server

MySQL – Help, I lost the Root password! Error number 1045

Tuesday, September 7, 2010

Have you ever seen the dreaded MySQL Error number 1045 Access denied for user ‘root’@'localhost’ (using password: YES)?

Don’t worry, this is an easy to recover from situation. Just follow these basic steps to reset the root user password.

Stop the MySQL server process.

Start the MySQL server process with the –skip-grant-tables option. This option causes the server to start without using the privilege system at all, which gives anyone with access to the server unrestricted access to all databases. BEWARE! Anyone can access your server, so make sure off the Internet, and that you perform the following steps as quickly as possible.

Start the MySQL console client with the -u root option. Mysql -u root

SELECT * FROM mysql.user;

UPDATE mysql.user SET Password=PASSWORD(‘[password]‘) WHERE User=’[username]‘; Replace [username] with root to change root. You can also change any other user as well.

Stop the MySQL process

Start the MySQL Process normally (i.e. without the –skip-grant-tables option).

Some folks have reported that issuing a Flush Privileges command will prevent your having to perform the final Stop/Start of the MySQL server. However, this has not always worked for me, and I think to be safe, it is wise to recycle the server.

Filed in Errors, MySQL | Also tagged , , , , , , , , | Comments (0)

MySQL – Innodb Slow Shutdown – Dirty Buffer Pages

Friday, September 3, 2010

If you have a MySQL server that reserves a large amount of memory for the Innodb buffer pool, you may have seen that it can often take a very long time to shutdown.  During the shutdown process, the server will not be able to serve any requests.  From my experience, I have seen that it can take 1-5 minutes per GB of buffer to perform a shutdown.  So, for example, if you have a server with 50G of memory allocated to the Innodb Buffer Pool, and 50% of the buffer is dirty (unwritten to disk), it can take anywhere from 25-125 minutes to shutdown.  Multiple hour shutdowns on large, busy Innodb MySQL servers is not unheard of.

Luckily, there is a fairly easy way to speed up shutdown while still allowing the server to stay up and handle requests.  The secret is to lower the maximum percentage of dirty pages allowed.  This is controlled by the global variable innodb_max_dirty_pages_pct. On my servers I usually have this set at 75%. This variable can be changed at run-time, which allows us to modify this variable to speed up system shutdown.

First, enter the command :

set global innodb_max_dirty_pages_pct=0;

You can then enter the following to verify the new value:

show global variables where variable_name rlike ‘dirty’;

To monitor the number of dirty pages that have not been written to disk, enter the following:

show global status where variable_name rlike ‘dirty’;

You will see this number begin to drop, although it is unlikely to ever reach 0, once it drops to a low level and reaches a plateau, you can then shutdown the server. You will find that the server will shutdown very quickly, allowing you to minimize downtime.

Filed in Innodb, MySQL, Optimization | Also tagged , , , , , , , , , , , , , | Comments (0)

Top My.CNF Optimizations for Innodb

Friday, August 20, 2010

These are the four parameters which will have the most affect on the performance of MySQL 5.1x and Innodb.   If you are suffering from poor performance, try changing the following settings.  Remember, each individual situation will vary, and in many cases, the actual design of your queries will have more to do with your overall performance than any system tuning tricks.  The best tool I have found to capture, review, and analyse query performance for MySQL is Jet Profiler for MySQL.  If you need help optimizing your queries, let us know.

innodb_buffer_pool_size =8G
Set this to ~80% of free server memory.  If you have a dedicated MySQL server with 10G, set to 8G

innodb_flush_log_at_trx_commit =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

sync_binlog =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

innodb_flush_method=O_DIRECT
On many systems, this will provide a performance improvement.  However, this can actually have a negative affect, so make sure you test appropriately.
Related articles
Filed in MySQL, Optimization | Also tagged , , , , , , , , , , , , , , , , | Comments (0)