Monthly Archives: February 2016

Get MySQL Tabel size sorted

Recently to dump a database with lots of large table which are not needed, I had to find out the large tables first and then use sequel pro to export the database with small tables and only structure of large table. I have used the following query to find out the large tables:

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Enable Content Compression in nginx

To enable content encryption in nginx, put the below configuration inside server block of nginx configuration file (usually in /etc/nginx/nginx.conf):

       gzip on;
       gzip_disable "msie6";
       gzip_http_version  1.1;
       gzip_comp_level    5;
       gzip_min_length    256;
       gzip_proxied       any;
       gzip_vary          on;
       gzip_types
           application/atom+xml
           application/javascript
           application/x-javascript
           application/json
           application/rss+xml
           application/vnd.ms-fontobject
           application/x-font-ttf
           application/x-web-app-manifest+json
           application/xhtml+xml
           application/xml
           font/opentype
           image/svg+xml
           image/x-icon
           text/css
           text/plain
           text/x-component;

You should get content size reduction unto 80%!

Upgrade PHP version to 5.5 in amazon linux

If you have older version of php in your amazon linux instance, follow the below steps to upgrade it to php 5.5:

  1. Find the list of old php packages and remove those:
    sudo yum list installed | grep "php"
    
    sudo yum remove php php-cli php-common php-devel php-fpm php-gd php-imap php-mbstring php-mcrypt php-mysql php-odbc php-pdo php-pear php-pecl-apc php-process php-xml (adjust according to your list from previous command)
    
    sudo yum remove httpd*
  2. Install new packages
  3. sudo yum install php55 php55-devel php55-common php55-cli php55-pecl-apc php55-pdo php55-mysql php55-xml php55-gd php55-mbstring php-pear php55-mcrypt mysql55 php55-server php55-mysqlnd php55-fpm
    
  4. Restart servers
  5. sudo service php-fpm restart
    sudo service nginx start
    sudo service mysqld start
    
  6. Check php-fpm config:
    sudo vim /etc/php-fpm-5.5.d/www.conf
    <blockquote> Enter the following content:
    listen = /var/run/php-fpm/php-fpm.sock
    listen.owner = nginx
    listen.group = nginx
    listen.mode = 0664
    
    user = nginx
    group = nginx