June 14th, 2015 § § 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.
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
May 24th, 2013 § § 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
Enter your old password and you are done.
You are done.
Thanks for reading.
August 18th, 2011 § § 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:
July 12th, 2011 § § 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”. 😉
- Run the Query
SHOW CREATE VIEW
- Click on the options just above the query result and change partial text to full text.
- Copy the content of create view column after “SQL SECURITY DEFINER VIEW `MySampleView` AS” and paste it in a query editor
- Make the changes you need for the view and run it.(Hope your desired output has came)
- Now Scroll until you see create view after the query result and click on it
- 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.
- I hope the As textarea will be filled with your edited query if not please paste your new edited query
- Press GO to update the view.
Hooray. You have done it.
June 6th, 2011 § § 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
add this line below the [mysqld]
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