Change mysql timezone

June 14th, 2015 § 0 comments § permalink

The MySQl server will use the same timezone as its system, if we didn’t specify the time zone in /etc/mysql/my.cnf file. If you want to specify a different timezone you need to edit the /etc/mysql/my.cnf file.

You need to add default_time_zone=’+00:00′ below [mysqld] section to set timezone to GMT.


[mysql]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
default_time_zone='+00:00'

mysql : change root password

May 24th, 2013 § 0 comments § permalink

Please note that this post doesn’t cover How to reset mysql root password if you are forgotten.

Recently I noticed that, its quite simple to change the root password of the mysql. mysqladmin command is quite handy for this.

mysqladmin -u<username> -p password <new password>

Here substitute <username> with root and <new password> with your new root password. Since we are passing -p option to the command, it will prompt for your old password.

Prompt for old password

Prompt for old password

Enter your old password and you are done.

You are done.

Thanks for reading.

Rename a Table or Column in mysql

August 18th, 2011 § 0 comments § permalink

If you are a PHPMyAdmin or SqlBuddy user (like me 😉 ), then mostly probably you won’t be familiar with sql queries for rename a table or rename a column. So here I share the sql query to do those things via command line.

To rename the column:
To rename the table:
Add new column after a specified field:

Edit view in phpMyAdmin

July 12th, 2011 § 1 comment § permalink

Are You a phpMyAdmin user? If so have you created views in it? I think most of you are. But phpMyAdmin doesn’t have a direct way to edit the views.
So what we do? Here is the easy steps to edit the MySQL-views in phpMyAdmin.This solution is just re-shared from the stackoverflow

Let our view name be “MySampleView”. Don’t forget to substitute your view name where ever you see “MySampleView”. 😉

    1. Run the Query SHOW CREATE VIEW MySampleView
    2. Click on the options just above the query result and change partial text to full text.
    3. Copy the content of create view column after “SQL SECURITY DEFINER VIEW `MySampleView` AS” and paste it in a query editor
    4. Make the changes you need for the view and run it.(Hope your desired output has came)
    5.  Now Scroll until you see create view after the query result and click on it
    6. You will be taken into the create view page.There you give your old view name ie.,MySampleView in the VIEW name field  and check in the OR REPLACE field.
    7. I hope the As textarea will be filled with your edited query if not please paste your new edited query
    8. Press GO to update the view.

Hooray. You have done it.

change default storage engine in mysql

June 6th, 2011 § 1 comment § permalink

Here is the small tip to change the default storage engine in mysql from MyISAM into InnoDB.
You need to edit the configuration file of MySql in order to change the default storage engine.
In ubuntu you can see the file in /etc/mysql/my.cnf. You need admin privilage for editing this file.

So the simple way to edit this file is to run the following command.

step 1 :

sudo gedit /etc/mysql/my.cnf

if you are using wamp the you need to edit

 path\to\wamp\bin\mysql\mysql[your mysql version]\my.ini

Eg: C:\wamp\bin\mysql\mysql5.5.8\my.ini

step 2:

find “[mysqld]

Step 3:

add this line below the [mysqld]

default-storage-engine=innodb

step 4 :

restart the mysql server using the following command

sudo service mysql restart

if you are using wamp the you can restart the mysql from the notification area.

step 5 :

check the status of the engines using the following command in mysql prompt

show  engines;