SMF Optimization & Backup Scripts

.htaccess

# Turn it on
ExpiresActive On

# Set a default expiry time. One hour is fine, but a day or longer may be appropriate.
ExpiresDefault A3600

# Turn expiry off for dynamic content (or potentially dynamic content).
ExpiresByType application/x-httpd-php A0
ExpiresByType application/x-python-code A0
ExpiresByType text/html A0

<FilesMatch "\.(php|py|pyc|pyo)$">
  ExpiresActive Off
</FilesMatch>

backup.bat

#!/bin/bash
cp /home/user/public/example.com/public_html/forum/Settings.php /home/user/scripts/Settings.orig.php
cp /home/user/scripts/Settings.lock.php /home/user/public/example.com/public_html/forum/Settings.php
/usr/bin/nice /usr/bin/mysqldump -q -u dbusername -p"dbpassword" --ignore-table=smf_forums.smf_log_search_words smf_forums > /home/user/backups/smf_forums.`date +%a`.sql
/usr/bin/nice /usr/bin/mysqlcheck -ao -–auto-repair -u dbusername -p"dbpassword" --databases smf_forums
chmod o-r /home/user/backups/smf_forums.`date +%a`.sql
cp /home/user/scripts/Settings.orig.php /user/public/example.com/public_html/forum/Settings.php
/usr/bin/nice /bin/bzip2 -f9 /home/user/backups/smf_forums.`date +%a`.sql

optimize.sql

DELETE FROM smf_log_topics;
DELETE FROM smf_log_boards;
DELETE FROM smf_log_mark_unread;

ALTER TABLE smf_attachments ENGINE=InnoDB;
ALTER TABLE smf_collapsed_categories ENGINE=InnoDB;
ALTER TABLE smf_log_actions ENGINE=InnoDB;
ALTER TABLE smf_log_boards ENGINE=InnoDB;
ALTER TABLE smf_log_errors ENGINE=InnoDB;
ALTER TABLE smf_log_karma ENGINE=InnoDB;
ALTER TABLE smf_log_mark_read ENGINE=InnoDB;
ALTER TABLE smf_log_online ENGINE=InnoDB;
ALTER TABLE smf_log_topics ENGINE=InnoDB;
ALTER TABLE smf_members ENGINE=InnoDB;
ALTER TABLE smf_sessions ENGINE=InnoDB;
ALTER TABLE smf_settings ENGINE=InnoDB;
ALTER TABLE smf_topics ENGINE=InnoDB;
ALTER TABLE smf_log_activity ENGINE=InnoDB;

CREATE TABLE `smf_log_floodcontrol` (
  `ip` char(16) NOT NULL default '',
  `log_time` int(10) unsigned NOT NULL default '0',
  `log_type` varchar(8) NOT NULL default 'post',
  PRIMARY KEY  (`ip`,`log_type`)
) ENGINE=MEMORY;

ALTER TABLE smf_pm_recipients ENGINE=InnoDB;
ALTER TABLE smf_pm_rules ENGINE=InnoDB;
ALTER TABLE smf_log_spider_stats ENGINE=InnoDB;

ALTER TABLE `smf_boards` ADD UNIQUE KEY (`id_board`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `board_order`, `id_board` ), ENGINE=InnoDB;

ALTER TABLE `smf_admin_info_files` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_admin_info_files` CHANGE `path` `path` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_admin_info_files` CHANGE `parameters` `parameters` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_admin_info_files` CHANGE `filetype` `filetype` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_custom_fields` CHANGE `field_desc` `field_desc` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_custom_fields` CHANGE `field_options` `field_options` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_custom_fields` CHANGE `mask` `mask` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_custom_fields` CHANGE `default_value` `default_value` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_comments` CHANGE `recipient_name` `recipient_name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_errors` CHANGE `file` `file` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_member_notices` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_packages` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `package_id` `package_id` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `version` `version` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `member_installed` `member_installed` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `member_removed` `member_removed` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_packages` CHANGE `themes_installed` `themes_installed` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_reported` CHANGE `membername` `membername` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_reported` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_reported_comments` CHANGE `membername` `membername` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_reported_comments` CHANGE `comment` `comment` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_spider_hits` CHANGE `url` `url` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_log_subscribed` CHANGE `vendor_ref` `vendor_ref` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_mail_queue` CHANGE `recipient` `recipient` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_mail_queue` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_openid_assoc` CHANGE `handle` `handle` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `url` `url` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_permission_profiles` CHANGE `profile_name` `profile_name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `poster_name` `poster_name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_spiders` CHANGE `spider_name` `spider_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_spiders` CHANGE `user_agent` `user_agent` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_spiders` CHANGE `ip_info` `ip_info` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_subscriptions` CHANGE `description` `description` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_personal_messages` CHANGE `from_name` `from_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_personal_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `real_name` `real_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `personal_text` `personal_text` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `website_title` `website_title` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `website_url` `website_url` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `location` `location` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `icq` `icq` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `msn` `msn` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `member_ip` `member_ip` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `secret_question` `secret_question` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `additional_groups` `additional_groups` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `member_ip2` `member_ip2` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `poster_name` `poster_name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `poster_email` `poster_email` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `poster_ip` `poster_ip` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `modified_name` `modified_name` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL, CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL, CHANGE `url` `url` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL, CHANGE `realName` `realName` VARCHAR(255) NOT NULL, CHANGE `emailAddress` `emailAddress` VARCHAR(255) NOT NULL, CHANGE `personalText` `personalText` VARCHAR(255) NOT NULL, CHANGE `websiteTitle` `websiteTitle` VARCHAR(255) NOT NULL, CHANGE `websiteUrl` `websiteUrl` VARCHAR(255) NOT NULL, CHANGE `location` `location` VARCHAR(255) NOT NULL, CHANGE `icq` `icq` VARCHAR(255) NOT NULL, CHANGE `msn` `msn` VARCHAR(255) NOT NULL, CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL, CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL, CHANGE `memberIP` `memberIP` VARCHAR(255) NOT NULL, CHANGE `secretQuestion` `secretQuestion` VARCHAR(255) NOT NULL, CHANGE `additionalGroups` `additionalGroups` VARCHAR(255) NOT NULL, CHANGE `memberIP2` `memberIP2` VARCHAR(255) NOT NULL;
ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL, CHANGE `posterEmail` `posterEmail` VARCHAR(255) NOT NULL, CHANGE `posterIP` `posterIP` VARCHAR(255) NOT NULL, CHANGE `modifiedName` `modifiedName` VARCHAR(255) NOT NULL, CHANGE `fromName` `fromName` VARCHAR(255) NOT NULL, CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

ALTER TABLE `smf_ban_groups` CHANGE `notes` `notes` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_actions` CHANGE `extra` `extra` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_comments` CHANGE `body` `body` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_errors` CHANGE `url` `url` VARCHAR(8000) NOT NULL;
ALTER TABLE `smf_log_errors` CHANGE `message` `message` VARCHAR(8000) NOT NULL;
ALTER TABLE `smf_log_group_requests` CHANGE `reason` `reason` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_member_notices` CHANGE `body` `body` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_online` CHANGE `url` `url` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_reported` CHANGE `body` `body` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_log_subscribed` CHANGE `pending_details` `pending_details` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_membergroups` CHANGE `description` `description` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_sessions` CHANGE `data` `data` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_settings` CHANGE `value` `value` VARCHAR(16000) NOT NULL;
ALTER TABLE `smf_themes` CHANGE `value` `value` VARCHAR(16000) NOT NULL;