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.
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.
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. For example, 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.
I am often asked if it makes sense to place an index on a Boolean field in order to improve query performance. In general, because a boolean value can only have three values (True, False, Null), this low cardinality would suggest that adding an index will not help performance, as the query optimizer will still usually perform a table-scan if you have an even distribution of values within your DB.
One situation in which an index on a boolean field (or other low cardinality field) might be useful is if there are relatively few of one of the values, for example 5 True values in a table of millions of records and you are searching for those few values on a regular basis.
However, you might index a boolean value on a combination of fields. Indexing on a single Boolean might be pointless, because there’s only 2 (or 3) values. However, indexing on 16 boolean values has the potential of 2^16 values. It might help to make a combined index but you should understand how the combined index can and cannot be used and be aware that the order of the columns matters.
In general, you should always profile your system to see if there are queries that are too slow and consider adding another index to handle those queries. Sometimes a single combined index can be used for multiple queries and others time you will need to make an index for each type of query. Remember that adding indexes slows down modifications to the data so it is possible to have too many indexes. There is always a trade-off when creating multiple indexes.
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.
SQL injection vulnerabilities are often been described as the most serious threat for Web applications, regardless of what language they are written in . Web applications that are vulnerable to SQL injection may allow an attacker to gain complete access to their underlying databases, regardless of if they are using MySQL Server, SQL Server, or Oracle.
SQL-injection attacks are those in which data provided by the user is included in an SQL query in such a way that part of the user’s input is treated as SQL code that is executed on the server. By using this technique, an attacker can submit SQL commands directly to the database. These attacks are a serious threat to any Web application that receives input from users and passes it into SQL queries to an underlying database server. If user input is not santised properly, web applications may result in SQL Injection attacks that allow hackers to view information from the database and/or even wipe it out.
To defend against SQL Injection attacks, user input must not directly be embedded into SQL statements that are executed on the server. Instead, you must use parameterized statements, and Escaping functions to check user input.
Various resource for addressing SQL Injection are as follows:
Bobby Tables provides real-world practical code for addressing SQL Injection – http://bobby-tables.com/
Michal Daw’s Blog Page outlines various SQL Injection vectors – http://michaeldaw.org/sql-injection-cheat-sheet
