Set timezone in mysql server

Timezone of a mysql server can be set same as of the system in which mysql database is installed.
Login into the mysql console and type the following command.

mysql> SET GLOBAL time_zone = 'SYSTEM';

Offset values can be set as below.

mysql> SET GLOBAL time_zone = '+10:00';

Retrieve the timezone by following command.

mysql> SELECT @@global.time_zone;

Output:

+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM |
+--------------------+
1 row in set (0.00 sec)


Set password for mysql user

How do I set or update password for user of a database ?

….

Login to mysql console

Set:

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('password');

Update:

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='username' AND Host='localhost';


Mysql backup and restore

How to take backup of mysql database and restore it ?

….

Backup –

mysqldump -u user -p databasename > backupname.sql

Create – empty database should be created before restoring.Database has to be created in mysql console

create database databasename;

Restore –

mysql -u user -p databasename < backupname.sql

Ubuntu 14.04 – Mcrypt extension missing

Php5-mcrypt shows it installed properly, but xibo complains its missing.

Solution:

sudo updatedb

sudo locate mcrypt.ini

Output:
/etc/php5/mods-available/mcrypt.ini

sudo locate mcrypt.so

Output:
/usr/lib/php5/20121212/mcrypt.so

Edit mcrypt.init and add the above path for extension.

extension=/usr/lib/php5/20121212/mcrypt.so

Create the links

sudo ln -s /etc/php5/mods-available/mcrypt.ini /etc/php5/cli/conf.d/20-mcrypt.ini
sudo ln -s /etc/php5/mods-available/mcrypt.ini /etc/php5/apache2/conf.d/20-mcrypt.ini


Enable the module

sudo php5enmod mcrypt

Restart Apache

sudo service apache2 restart

Insert into mysql from text file

If you have text file with multiple columns separated by space(or something else). you want to take entries from each row and  insert into MySQL database from a text file.

Following steps should be useful.

input.txt is a text file with below entries

105  bill@<domain.com>
304  lance@<domain.com>
605  foo@<domain.com>
609  bar@<domain.com>

inputfile="input.txt"
cat $inputfile | while read id map; do
echo "INSERT INTO <tablename>(users_id,email,email_map) VALUES ('$id','$map', '$map');"
done | mysql -u<username> -p<password> <database>;

Mysql: Copying to tmp table

Copying to tmp table is a common thread state within MySQL which occurs when the MySQL server is copying to a temporary table in memory.

MySQL will need to create temporary tables under certain circumstances e.g.:

1. Sorting operations e.g. if a query contains an “Order By” clause, or “Group By” clause
2. If the query contains “distinct”

To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says “Using temporary“.

According to the MySQL site:

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

• Presence of a BLOB or TEXT column in the table
• Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes
• Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used

Queries which involve disk based Temporary tables are often slow, and a large number of queries of this type can result in a serious performance bottleneck.

 

taken from

http://www.dbtuna.com/article/55/Copying_to_tmp_table_-_MySQL_thread_states

Mysql Cheatsheet

1. Check mysql running queries

mysql>show processlist;

2. Command to kill the running query(helpful to kill long running query)

mysql>show processlist;
kill "number from first col";

3. Restore from mysql backup(.sql  or .mysql file)

mysql -u username -p password databasename < file.sql