Create temporary table in MySQL

Temporary tables are useful when you need to do something on the fly. Instead of writing full CREATE TABLE statement and then inserting from another table, you can do the following with one query: 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

Example:

CREATE TEMPORARY TABLE tmp_tbl as (SELECT * FROM tbl) 

Table with name tmp_tbl is created with the same columns and data is copied from tbl.

You can do the same with using LIKE:

CREATE TABLE tmp_tbl LIKE tbl

 But, again you will need another query for inserting data.

Create swap file in Linux

Before creating a swap file, we should check the size of the current swap:

#cat /proc/meminfo | grep SwapTotal
SwapTotal: 9233400 kB

Create a swap file

To create swap file, you will need to use dd command:

#dd if=/dev/zero of=/newswap bs=1024 count=1048576

if=/dev/zero - read from /dev/zero
of=/newswap -  write to /newswap
bs=1024 - blocksize in bytes for reading and writing
count=1048576 - swap file size

Create swap area

Type the following command to set up a Linux swap area in a file.

#mkswap /newswap

Set permissions for the file, so only the root user can use it:

#chmod 0600 /newswap
#chown root:root /newswap

Acticate swap

Next, we should activate the swap file:

#swapon /newswap

At the end, we want the new swap file to be active after reboot and we should add it in fstab file:

#nano /etc/fstab

add the following line at the end of the file:

/newswap swap swap defaults 0 0

Now check if swap is activated or not:

#cat /proc/meminfo | grep SwapTotal

or

#free -m

Add, Edit, Delete, List cronjobs in Linux

If you want to add cronjob or edit an existing one you can use the crontab command:

#crontab -e

The '-e' param stands for edit. This will open the crontab file for the current logged user. If you want to edit the crontab file for a specific user you need to specify the username:

#crontab -e -u username

  To delete user's crontab use the following command:

#crontab -r

or for a specific user:

#crontab -r -u username

To list cronjob:

#crontab -l

 or

 #crontab -l -u username

When you edit the crontab file, it will be open in your default editor. If you want to be open in a specific editor you can use the env command:

#env EDITOR=nano crontab -e

This will open the crontab file in nano editor.

MySql force index

Sometimes mysql may not use the index you created for some reason. To force mysql to use an index you can use FORCE INDEX.

| FORCE {INDEX|KEY} 

[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

Example:

SELECT * FROM table1 LEFT JOIN table2 FORCE INDEX FOR JOIN (`PRIMARY`) on table1.id = table2.id ...

How to delete user's password in Linux

If you want to remove a password for a specific user, you can use one of the following commands:

#passwd -d username

or

#passwd --delete username

Example:

#passwd -d ana

This will remove the password for the specified user. The user will not be able to login.

List all ip addresses on a linux system

With the ifconfig command you can list all network cards and ip addresses on a linux system.

#ifconfig

eth0     Link encap:Ethernet  HWaddr 00:00:00:00:00:00
  inet addr:192.168.1.2  Bcast:192.168.1.255  Mask:255.255.255.0
  inet6 addr: 0000::000:0000:0000:000/00 Scope:Link
  UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
  RX packets:54367 errors:0 dropped:0 overruns:0 frame:0
  TX packets:34504 errors:0 dropped:0 overruns:0 carrier:0
  collisions:0 txqueuelen:1000
  RX bytes:247741252 (247.7 MB)  TX bytes:127569007 (127.5 MB)
  Interrupt:28 Base address:0x0000

lo        Link encap:Local Loopback  inet addr:127.0.0.1  Mask:255.0.0.0
  inet6 addr: ::1/128 Scope:Host
  UP LOOPBACK RUNNING  MTU:16436  Metric:1
  RX packets:2394 errors:0 dropped:0 overruns:0 frame:0
  TX packets:2394 errors:0 dropped:0 overruns:0 carrier:0
  collisions:0 txqueuelen:0
  RX bytes:178041 (173.6 KiB)  TX bytes:178041 (173.6 KiB)

eth0 is my network card and because i m behind a router, my private ip address is: 192.168.1.2.
lo is the loopback address.

ERROR 1271 (HY000): Illegal mix of collations for operation...

The following MySql error occurs when you try to make operation on two fields with different collations. To check the field collation you can use the show create query:

mysql> show create table words;

CREATE TABLE `words` (
  `word` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Here, the collation is utf8_unicode_ci. To change the collation use the following query:

mysql> alter table words modify word varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

or

mysql> alter table words change word word varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL

Find currently logged users on a linux system

With the who command you can list the current logged users on a linux system:

# who

user     tty7         2013-05-07 09:18 (:0)
roy     pts/0        2013-05-07 14:47 (:0.0)
tony     pts/1        2013-05-07 14:49 (:0.0)
ana     pts/2        2013-05-07 15:18 (:0.0)

If the list is too long, and you want to search for a specific user, you can use grep:

# who | grep ana

ana     pts/2        2013-05-07 15:18 (:0.0)

Linux: check available space on each filesystem

To check used/available space on each filesystem, use the following command:

df -h

example output:

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             285G   16G  256G   6% /
none                  1.9G  288K  1.9G   1% /dev
none                  1.9G  3.6M  1.9G   1% /dev/shm
none                  1.9G  100K  1.9G   1% /var/run
none                  1.9G     0  1.9G   0% /var/lock

-h param stands for human-readable and if you don't specify it, sizes will be printed in bytes

Ubuntu: Add new domain / subdomain in apache

Navigate to /etc/apache2/sites-available. Create a file with your new domain name and add the following:

#file: mydomain.com
<VirtualHost *:80>
        ServerName mydomain.com
        DocumentRoot /root/directory/of/site
</VirtualHost>

Enable the configuration for the new domain with the following command:

a2ensite mydomain.com

 Restart Apache

service apache2 restart

change date.timezone in php

Open php.ini file and search for date.timezone directive. It should look something like this:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
date.timezone = 'America/New_York'

or you can use the ini_set() function. Just add at top of your script:

ini_set('date.timezone', 'America/New_York'); 

Here is a list of time zones: http://php.net/manual/en/timezones.php

create and delete symbolic links in linux

Create a symlink

ln -s /path/to/file/or/dir /path/to/symbolic/link

Delete a symlink

 unlink /path/to/symbolic/link

Example:

Create a symlink for file.gz which is placed in /home dir

ln -s /home/file.gz /home/user/file.gz

Delete the symlink for file.gz

unlink /home/user/file.gz

or simply delete the symbolic link with the rm command

rm /home/user/file.gz

PHP increase file upload size

 You can increase file upload size by setting upload_max_filesize to a higher value. Find the upload_max_filesize directive in php.ini and change the default value:

; Maximum allowed size for uploaded files.
; http://php.net/upload-max-filesize
upload_max_filesize = 8M

Restart apache so the new configuration can take effect:

/etc/init.d/apache2 restart

PHP Disable File Upload

Open php.ini file and find the file_uploads directive. It should look something like this:

; Whether to allow HTTP file uploads.
; http://php.net/file-uploads
file_uploads = On

To disable file uploads in php set the directive to Off:

file_uploads = Off

Restart apache with the following command

service apache2 restart

Note: In Ubuntu php.ini file can be found udner: /etc/php5/apache2

Linux: start / stop / restart apparmor daemon

Stop Apparmor

# /etc/init.d/apparmor stop

Start Apparmor

# /etc/init.d/apparmor start

Restart Apparmor

# /etc/init.d/apparmor restart

Apparmor status

# /etc/init.d/apparmor status

Restart Apache in all Linux distributions

Restart Apache

Ubuntu

service apache2 restart

/etc/init.d/apache2 restart

apache2ctl restart

CentOS

/etc/init.d/httpd restart 

service httpd restart

/sbin/service httpd restart

Debian

service apache2 restart

/etc/init.d/apache2 restart 

apache2ctl restart

Suse

/usr/sbin/rcapache2 restart

apache2ctl restart

Fedora

apachectl restart

/sbin/service httpd restart

RedHat

/etc/init.d/httpd status

service httpd restart

/usr/local/apache2/bin/apachectl restart

Updated Debian 6.0: 6.0.7 released

The Debian project is pleased to announce the seventh update of its stable distribution Debian 6.0 (codename squeeze). This update mainly adds corrections for security problems to the stable release, along with a few adjustments for serious problems. Security advisories were already published separately and are referenced where available.

Please note that this update does not constitute a new version of Debian 6.0 but only updates some of the packages included. There is no need to throw away 6.0 CDs or DVDs but only to update via an up-to-date Debian mirror after an installation, to cause any out of date packages to be updated.

source: http://www.debian.org/News/2013/20130223

Ubuntu 13.04 (Raring Ringtail) Beta 2 released

The Ubuntu team is pleased to announce the final beta release of Ubuntu 13.04 Desktop, Server, Cloud, and Core products.
Codenamed "Raring Ringtail", 13.04 continues Ubuntu’s proud tradition of integrating the latest and greatest open source technologies into a high-quality, easy-to-use Linux distribution. The team has been hard at work through this cycle, introducing new features and fixing bugs.

source: http://fridge.ubuntu.com/2013/04/05/ubuntu-13-04-raring-ringtail-beta-2-released/

Ubuntu 8.04 (Hardy Heron) server, 10.04 (Lucid Lynx) desktop and 11.10 (Oneiric Ocelot) reaching End of Life on May 9 2013

No more support for Ubuntu 8.04 (Hardy Heron) server, 10.04 (Lucid Lynx) desktop and 11.10 (Oneiric Ocelot)

source: http://fridge.ubuntu.com/2013/03/29/ubuntu-8-04-hardy-heron-server-10-04-lucid-lynx-desktop-and-11-10-oneiric-ocelot-reaching-end-of-life-on-may-9-2013/

MySQL's creator on why the future belongs to MariaDB

Some of the new code by Oracle is surprisingly good, but unfortunately the quality varies and a notable part needs to be rewritten before we can include it in MariaD.

source: http://www.computerworld.com.au/article/457551/dead_database_walking_mysql_creator_why_future_belongs_mariadb/

Date subtraction in MySQL

MySQL: select all records from a table using date subtraction.


SELECT tbldata.* FROM tbldata WHERE (tbldata.DateAdded <= DATE_SUB(now(), INTERVAL 3 HOUR));

source: http://www.daniweb.com/web-development/php/threads/448140/time-difference-in-mysql