MySQL handly commands


To prevent duplicate entry issue - replace INSERT into with REPLACE INTO .
DEF: REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

REPLACE into table (id, name, age) values(1, "A", 19)

Run Optimize of All tables of a database:

mysqlcheck -o db_to_optimizeĀ  -udb_user -p

All databases

Find the Largest Table in a MySQL Database

SELECT CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
WHERE table_schema='database_name'

Update File Path in Drupal

We have a site that we had been migrating since Drupal 4. Few days back when we tried to migrate the same from Drupal 6 to Drupal 7. There was an issue with respect to file location that was present since previous upgrades. To resolve that a symlink was created called "files" with linked to sites/default/files during D6 upgrade. This led to first loss of many files as Drupal does not handle symlink based file saving well. Secondly the files created were saved under public://files/filename.ext instead of public:://filename.ext which caused created files not being accessible.

MySQL Tuning Best Practices


Based on video from lullabot, the following settings in my.cnf file in mysql for drupal performance and handling:

Before beginning to start using MySQL it is recommended to run the script provided by MySQL to provide some security features.
The link is: /usr/bin/mysql_secure_installation . The location can vary. It basically requests for root password, disabling remote root login, remove anonymous users and other security setting.

Character Set: utf8. Drupal mostly expects the default character set to be utf8.