SQL / MySQL Tips and Tutorials

MySQL log files are taking a lot of disk space – How to disable and delete them?

I have WAMP server installed on my local computer for PHP and MySQL development. After using it for a while, the MySQL installation folder has run up some seriously huge log files that are taking enormous amount of disk space. We are talking 10s of GB here. The file names are like ibdata1, mysql-bin.000017, etc.. It appears it’s because I frequently have large databases in and out by the ‘mysql’ and ‘mysqldump’ command, building up large stacks of data importing and exporting logs for data rollback and recovery or something.

Simply deleting the log files may result in problems such as being unable to start MySQL again. Tried that and I had to reinstall it to make it work again.

After some searching, I’ve found this query to solve my problem. Just execute it after you have logged in MySQL via command line – of course you will need the root privileges:

PURGE BINARY LOGS BEFORE '2036-12-12 12:12:12';

Something like that would purge and delete all the huge logs to free the disk. Just make the date time to be as sci-fi as possible so that all log files are purged.

SQL / MySQL Tips and Tutorials

MySQL: Change Default Character Set or Default Collation in phpMyAdmin

It can be annoying when MySQL imports your UTF8 database (which contains exotic characters other than those in English) in the default character set of latin1_swedish_ci, jeopardizing the text content. It is also annoying when phpMyAdmin does the same and when you forgot to set the collation to utf8_general_ci for the new database which is very probably going to store utf8 characters.

Let’s fix this once and for all.

Just locate and open the MySQL configuration file my.ini and find the section [mysqld]. Add the following directive:


Save to my.ini and restart MySQL demon. Now MySQL will use utf8 as the default character set when importing databases or creating new databases. The default collation of phpMyAdmin has also changed to utf8_general_ci.

SQL / MySQL Tips and Tutorials

MySQL: Check if a table exists

When I was creating an installation script, I needed to check if a table exists to make sure the installation had not been performed yet. How did I do that?

I use this simple query to get whether a table exists in the specified database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'db_name'
AND table_name = 'table_name'

Just fill in the ‘db_name’ as well as ‘table_name’. If this query returns 1 row, the table db_name.table_name does exist, otherwise it does not.

SQL / MySQL Tips and Tutorials

MySQL logic operators: How to use AND, OR together in WHERE clauses in one query?

AND and OR are common logic operators across all programming languages and in MySQL, they mean the same thing. A statement (a.k.a, a condition in any WHERE clauses such as id > 100) can be either true or false. A group / combination of statements can be true or false, depending on their own values (true or false) and the logic operators that have joined them.

AND and OR each can join 2 statements.

AND – both statements must be true

For example, in this MySQL query:

SELECT * FROM student WHERE age > 10 AND grade < 4

All students who are graded less than 4 and older than 10 will be selected. The logic operator AND commands that both of the conditional statements (age > 10 and grade < 4) must be true for the whole statement (age > 10 AND grade < 4) to be true. You can add more conditions and ask them all to be true to narrow down the selection:

SELECT * FROM student WHERE age > 10 AND grade < 4 AND sex = 'female'

Which effectively selects all the female students who are older than 10 and have a grade of lower than 4 because the AND operators mean that all 3 statements must be true for the whole, joined statement (age > 10 AND grade < 4 AND sex = ‘female’) to be true.

OR – at least one statement must be true

Consider this example,

SELECT * FROM student WHERE grade > 4 OR grade < 2

This simply selects all the students who have a grade higher than 4 or lower than 2. All the students who satisfy one or more of these statements are selected. Students who satisfy none of these statements are neglected. You can as well use more than one OR in a series:

SELECT * FROM student WHERE grade > 4 OR age < 9 OR surname = 'Bush'

So that any student who has a grade higher than 4 or is less than 9 years old or has a surname of Bush will be selected.

How to use AND and OR together?

Consider the following example,

SELECT * FROM student WHERE grade > 2 AND grade < 5 OR age > 8 AND age < 12

As AND has a higher priority (effectively so in almost all programming languages) than OR, it will be evaluated before any OR statements. Therefore, this query will select all those students whose grade is higher than 2 and lower than 5 or those whose age is older than 8 and younger than 12.

You can add parenthesis but because AND has a higher priority in parsing the logic statement, it’s the same:

SELECT * FROM student WHERE (grade > 2 AND grade < 5) OR (age > 8 AND age < 12)

However, as parenthesis has the highest priority, if you add them in a different way such as this:

SELECT * FROM student WHERE (grade > 2 AND grade < 5 OR age > 8) AND age < 12

It would give completely different results. Firstly, grade > 2 AND grade < 5 must be true or age > 8 must be true, secondly, age < 12 must be true. For example, these students will satisfy this WHERE clause:

  1. grade = 4, age = 7
  2. age = 9
  3. grade = 3, age = 11

These students don’t satisfy this WHERE clause:

  1. age = 14
  2. grade = 5, age = 7
Apache Web Server Tutorials & Tips PHP Tips & Tutorials SQL / MySQL Tips and Tutorials

How to bring down / optimize memory usage in your unmanaged Linux VPS box and avoid OOM (Out Of Memory) errors?

The other day I was very upset about some extraordinary down times of my unmanaged VPS box at Linode. As it’s unmanaged, support staff at Linode are not responsible for the failures. I contacted them and they told me it’s OOM (Out Of Memory), pointing me to the right documentation to figure out how to get the problem sorted out myself. After a few tweaks and observations for a week, so it seems that I have successfully optimized my VPS server to take on more traffic with less resources such as RAM.

The problem almost always lies in where the user is free to feed stuff to your website or program. Sometimes Convert Hub spikes in memory usage and forces my box to use swap that relies on disk I/O to work. This happens when someone uploads an ultra large picture to be processed or converted. While I may restrict the size of the picture that is allowed to be uploaded, I may also do the following settings to optimize the entire LAMP environment so the other websites enjoy it as well.

Apache 2 Low-Memory Optimization

Use this command to identify the MPM you are using:

apache2 -V | grep 'MPM' # for Debian-based systems
httpd -V | grep 'MPM' # for Fedora/CentOS systems

Find and change these settings in your Apache 2 configuration file (usually found at /etc/apache2/apache2.conf):

StartServers 1
MinSpareServers 3
MaxSpareServers 6
ServerLimit 24
MaxClients 24
MaxRequestsPerChild 3000

Switch to Lighttpd or Litespeed if possible.

MySQL Low-Memory Optimization

Same as above, find and change these settings of the MySQL configuration file (may be at /etc/mysql/my.cnf) accordingly:

key_buffer = 16K
max_allowed_packet = 1M
thread_stack = 64K
table_cache = 4
sort_buffer = 64K
net_buffer_length = 2K

PHP Low-Memory Optimization

Find your PHP configuration file (php.ini) and modify the PHP script memory limit to 32M or less (default is 128M):

memory_limit = 32M
Information Security SQL / MySQL Tips and Tutorials

A few database security tips – things to do to effectively protect MySQL databases

I’d like to share with you some tips about hardening the database part of your application. Here are a few things you can do in protecting the databases from being compromised in security:

  1. Create separate users with ONLY necessary privileges (as few as possible) to connect to the database for common daily tasks. Never use the database owner / creator or even MySQL root user in your PHP scripts to perform routine tasks.
  2. Protect against SQL injection attacks by escaping ALL incoming input after ensuring data types with a variety of PHP variable type and character type validation functions.
  3. The sprintf() function is both useful and secure in constructing SQL queries because of the built-in type checking. Better yet, use PDO.
  4. Turn off error messages MySQL or PHP outputs when things go wrong so crackers know nothing about the technical details of your build such as database schema. As a matter of fact, a good rule of thumb in web application security is that the less people know about your application’s internal structure, the better.
  5. For advanced SQL developers, extra abstraction layer in SQL in the form of stored procedures can benefit security because you implement yet another depth of defense and hide the schema of the database from the outside world.
  6. For mission critical applications, it goes without saying that custom logging of database accesses can help a lot in identifying security risks.
  7. If the database contains very sensitive data such as credit card information, you are strongly recommended to encrypt these tables or fields. Just use PHP cryptography extensions such as Mcrypt to encrypt any data that are to be stored and decrypt them when they are being retrieved.
Hosting Tips & Deals SQL / MySQL Tips and Tutorials

MySQL: How to backup ALL databases as root with mysqldump at once?

When you have a VPS or dedicated server to manage, typically you’d have a bunch of different mysql users granted the privileges of every particular database for the sake of security. While this works well in segregating the privileges and preventing hackers from gaining access to all databases upon the compromise of only one mysql user account, it means much more work when you are backing up all those databases on the server.

The solution is to use the mysqldump command as root. To backup all databases on the mysql server no matter which users they belong to, try the following via SSH:

mysqldump -u root -p --all-databases > all.sql

It’d then prompt you for root password. After successful authentication, the mysql server will start dumping all databases into a text SQL file: all.sql. After it’s done, you can compress (“tar zcf all.tar.gz all.sql”) and put all.sql somewhere safe for backup.

To restore all.sql into the entire MySQL:

mysql -u root -p < all.sql

Can’t be any simpler.

SQL / MySQL Tips and Tutorials

MySQL: Select and Show all MySQL Users

To show all MySQL databases, you can simply type SHOW DATABASES; or SHOW TABLES; to show all tables of the current database. However, if you want to list and display all the MySQL users, SHOW USERS; won’t work.

So how to display a list of the MySQL users?

As all user information such as user name and privilege data are stored in the table user of the database mysql that comes automatically created and populated after MySQL installation. You can show all the MySQL users of the current host by the following select query:

SELECT user FROM mysql.user;

Which would output a list of rows of all the active users, in the same format of what a SHOW DATABASES; would do.

SQL / MySQL Tips and Tutorials

MySQL: How to change or convert MyISAM to InnoDB or vice versa?

After we have discussed the advantages and disadvantages of MyISAM and InnoDB, you might want to convert them back and forth and see the performance differences yourself. For small website, the difference may be minor but for a large popular application,

  • MyISAM tables yield better performance results in selecting data records
  • InnoDB tables give better performance results in inserting and updating data records

To change a MyISAM table to an InnoDB table:

ALTER TABLE table_name ENGINE = InnoDB;

To convert an InnoDB table to a MyISAM table:

SQL / MySQL Tips and Tutorials

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  3. InnoDB is more strict in data integrity while MyISAM is loose.
  4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
  5. InnoDB has transactions while MyISAM does not.
  6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  8. MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.

Advantages of InnoDB

  1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
  2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
  2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
  3. No full-text indexing.

Advantages of MyISAM

  1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  3. Full-text indexing.
  4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
  3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.