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