Skip to content

MySQL – Best way to remove all data from a table

Monday, November 22, 2010
MySQL's logo. Converted from EPS with Scribus ...

There are multiple ways in which to remove all data from a MySQL Table.

The first is to use the DROP TABLE command, which will remove the entire table with data.  This basically removes the entire table, you will not be able to recover the structure or data.

The second is DELETE * FROM Table which will remove the data,  leaving the auto-increment values alone, as well as the rest of the table structure.  This method is very slow on large tables using Innodb.  This is because each record has to be written to the log.  In general, you should never use this method to remove all data from a table.

The third method is to issue a TRUNCATE TABLE command which will quickly remove all the data from the table, reset the auto-increment values (but leave them as auto-increment columns, so they will just start at 1 and go up from there again).

Lastly, you can use the nuclear option, which is as follows :

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

This will drop the table, and then recreate it all from scratch.

Related articles
Filed in MySQL, Scripting | | 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 | | 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 | | Comments (0)

MySQL Collate and Case Sensitivity – Flexible Compare

Tuesday, August 31, 2010

In a typical MySQL installation, the default character set and collation are latin1 and latin1_swedish_ci.  This means that all string comparisons will be performed in a case insensitive manner.  So, ‘Search String’='search string’.  This is great for most WHERE expressions, however there are occasions where performing a case-sensitive search is required.  Luckily MySQL provides the collate clause which allows you to override whatever the default collation is for a comparison.   As an example :

select * from mytable where first_name collate latin1_general_cs=’damon’

This will only return rows will the first_name matches damon exactly. Damon will not match.

You can also use latin1_bin which performs an exact binary match.

If you are unsure of a field or strings collation, you can use the COLLATION(str) function to return the collation of the string argument.

Filed in MySQL | | 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 | | 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 | | Comments (0)

MySQL Delimiters – Or why I hate stored procedures and Error Code : 1064 You have an error in your SQL syntax.

Thursday, August 19, 2010

MySQL 5.0+ gave DBA’s the ability to write stored procedures and functions. This is a great addition, and one that all “real” DB engines should provide. But, as many DBA have found out, writing stored procedures using most MySQL clients can be a bit of a challenge. Usually after writing their first attempt, they are greeted with, “Error Code : 1064 You have have an error in your SQL syntax;”.

So, what is going on?  Well, by default the MySQL statement delimiter is the semi-colon (;).  However, the mysql command-line utility also uses a semi-colon as a delimiter. So, if the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.

The solution is to temporarily change the command-line utility delimiter using the DELIMITER command, as seen here:

DELIMITER //

CREATE PROCEDURE sku_pricing( OUT low_price DECIMAL(8,2), OUT high_price DECIMAL(8,2), OUT avg_price DECIMAL(8,2) )

BEGIN

SELECT Min(prod_price) INTO low_price FROM sku_items;

SELECT Max(prod_price) INTO high_price FROM items;

SELECT Avg(prod_price) INTO avg_price FROM items;

END; //

DELIMITER ;

Here, DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine, rather than being interpreted by the client. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;.   Any character may be used as the delimiter except for \, as it is an escape character in mySQL.

Related articles
Filed in MySQL, Stored Procedure | | Comments (1)

How do I store an IP Address in MySQL? INET_AtoN!

Thursday, August 19, 2010

One of the most popular questions out there is, “How do I store an IP Address in MySQL?”.   The fastest and most compact way to store an IP address in the format “10.51.1.10″ is as an unsigned integer.  If you store the actual values as a char(15), you will require 11 extra bytes per row.  In addition, using IP as a condition when querying is much faster when using an unsigned integer.  Testing has shown that range queries can result in as much as a 35 million times improvement over storing an IP address as a character string.  Yes, that is 35 million!

Simply define IP as an INT UNSIGNED.

Use MySQL’s built-in function INET_ATON() to convert the string representation of the IP Address into an unsigned Integer.  INET_NTOA() will perform the reverse translation.

This will result in space savings, and faster queries, who could ask for more than that?

Filed in MySQL | | 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 | | Comments (0)

How to increase MySQL performance when loading a Dump File

Thursday, August 19, 2010

In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.

Filed in MySQL, Restore Backup | | Comments (0)