Skip to content

Tag Archives: server

MySQL – A simple script to truncate all MySQL tables in a Database

Wednesday, December 22, 2010
The official logo of the database query MySQL

Want a quick and dirty way to Truncate all the tables in a MySQL DB?  You can use the following one line script.

mysqldump -ppassword YourDBName –no-data dumpfile | mysql dumpfile

What this does is uses mysqldump to output all the tables in the database “YourDBName” into a dump file.  This dump file contains drop statements, and create table statements for each table in the database.  What this effectively does is drops every table, and recreates it WITHOUT any data inside.  Be very careful, since this is a very powerful script, it has the ability to completely wipe out your database with no hope of recovery unless you have a current backup.

For additional ways to execute specific commands against all tables in a DB, look into using mk-find in Maatkit, it is an excellent tool for advanced MySQL scripting.

Related articles
Filed in MySQL, Scripting | Also tagged , , , , , , , , , , , , , , , , , | Comments (0)

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)

MySQL- Allowing Access to Root Remotely

Thursday, August 19, 2010

MySQL includes all the basic methods needed to secure your user accounts.  However, the syntax and style used to manage it is often confusing to MySQL novices.  Here are a couple tips to get you started, however I strongly suggest your read the MySQL User Account Management documentation.

If you need to change your password for your localhost root account, here is the proper syntax.

mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST' = PASSWORD('new_password');

You can also grant access to all remote servers as well by using the following syntax.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

By default, MySQL only allows access by the ‘root’ account from the host running the database server (‘localhost’).

The percent symbol ("%") in the notation root@"%" means “any host”, but it doesn’t imply localhost. You need to repeat the commands above with root@localhost in order to grant/revoke permissions for localhost.

If you are setting up your server and require NO remote access, add skip-networking to your my.cnf file.

skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

 

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

Show Processlist and Show Full Processlist – MySQL Tip

Thursday, August 19, 2010

Everyone knows that using the MySQL command Show Processlist will display all current connections, like the following :

mysql> show processlist;
show processlist;
+—-+————-+—————–+——+———+——+———————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+—————–+——+———+——+———————————-+——————+
| 1 | root | localhost:32893 | NULL | Sleep | 0 | | NULL |
| 5 | system user | | NULL | Connect | 98 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 5018 | Reading event from the relay log | NULL |
+—–+——+———–+———+———+——-+——-+——————+
3 rows in set (0.00 sec)

What is little known however is that the Show Full Processlist command will display the full query in the Info column.  By default, Show Processlist will only display the first 100 characters. One thing to keep in mind about the Info line is that while the statement (query) might be the one sent to the server, it will display the innermost statement if the statement executes other statements.

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

MySQL – DateTime vs. TimeStamp – When to use?

Saturday, June 12, 2010

This question often comes up, and while there is often a heated debate, these are my thoughts on the subject.

I typically use a timestamp when I need to record a fixed point in time. For example when a record was inserted into the database or when some useraction took place that resulted in a row being updated.   The Timestamp data type has various features that allow it to automatically perform this function.   The default value for the Timestamp data type results in it being set to the current_date when a new row is added.  If you as the On Update syntax, this value will also be updated whenever the row is updated.

I use a datetime field when the date/time can be set and changed arbitrarily. For example when the field can be updated based on specific events or actions, besides a simple row Update.  In addition, a TimeStamp field can only store dates since 1970, so if you need to store dates in the past, such as a birthday, you must use DateTime.

A couple other things to keep in mind, TimeStamp fields support the TimeZone setting on your server.  For example, if I have a database in Europe, and take a dump of that database to syncronize/populate a database in America, then the timestamp would update to reflect the real time of the event in the new time zone, while datetime would still reflect the time of the event in the European timezone.  By default, the current time zone for each connection is the server’s time, however the time zone can be set on a per-connection basis.

Lastly, TIMESTAMP stores its value in 4 bytes, while DATETIME uses 8 bytes.  This is the main reason why TimeStamp has a lower limit of 1970.

Filed in Uncategorized | Also tagged , , , , , , , , , , , , , , , , | Comments (0)

MySQL Removing duplicate rows – Part II

Tuesday, May 11, 2010

Using ALTER IGNORE TABLE [TABLENAME] ADD UNIQUE INDEX `UNIQUE_INDEX` ([FIELDNAME]) to remove duplicate rows in a table is a fast an efficient process, however on large tables where the physical size is larger than server memory, the ALTER statement can take a long time to run in a production environment.

If you need to remove duplicates on a very large table (we recently used this on a table of 77 million rows), try this method :

delete t1 from table t1, table t2
where t1.duplicate_field= t2.duplicate_field (add more if need ie. and t1.duplicate_field2=t2.duplicate_field2)
and t1.unique_field > t2.unique_field
and breakup into ranges to run faster

If you use an auto-incrementing ID field as the primary key, use this as your unique field, and in the Where clause to run on a range of records to break into smaller operations.

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