Tag Archives: MySQL

Removing All Triggers in a Database

We were re-structuring our current database here in work and there was a need of removing triggers. Here is how it is done in bash for mysql:

mysql -u<dbuser> -p<dbpassword> –skip-column-names <dbname> -e ‘SHOW TRIGGERS;’| cut -f1|sed -r ‘s/(.*)/DROP TRIGGER IF EXISTS \1;/’| mysql -u<dbuser> -p<dbpassword> <dbname>

MySQL Proc Crashed

One day when I powered up my machine, I had a trouble with accessing my database. After tailing on the logs, I found this error : Table ‘./mysql/proc’ is marked as crashed and should be repaired. Based on replies from forums, my database has been corrupted and needed fix by myisamcheck, assuming mysql data directory is on /var/lib/mysql/mysql:

myisamchk -q -r /var/lib/mysql/mysql/proc

 

 

Mysql: Editing only the date in datetime

Recently I was looking for a way to do data testing in my project which involves data with date time. Since it was querying for the current day and my data was older, I wanted to change the date part of the datetime column. Anyways, here is how I did it:

UPDATE tablename SET fieldname = concat('2015-04-14 ', time(fieldname)) WHERE DATE(fieldname) = '2015-04-10'

‘2015-04-14’ is the new date we want to apply and ‘2015-04-10’ is the old date that we want to look for.

Select Previous and Next Entry based on ID in MySQL

Here is an easy way to select the previous and next entry based on a given ID in MySQL, good for paginations in PHP.

To get the previous entry, query is “SELECT * FROM table_name WHERE id $current_id ORDER BY id ASC LIMIT 1”

You might think it’s easier to use (id – 1) or (id + 1) but it might return errors when id’s are deleted and the count is broken.

Changing MySQL root password

There are several ways of changing the root password of MySQL. Initially, when mysql in first accessed, there are no passwords for the root access, which we all know is not always a good practice. The user root is MySQL’s admin user account, same as in linux/unix.

Using mysqladmin

$ mysqladmin -u root password NEWPASSWORD

If you already have an existing password to root, you may change it by:

$ mysqladmin -u root -p OLDPASSWORD password NEWPASSWORD

This can also be used in changing passwords for normal users:

$ mysqladmin -u username -p OLDPASSWORD password NEWPASSWORD

Using mysql command

Another way is by changing the stored password of users within mysql command.

Login to mysql command:

$ mysql -u root -p

Change to mysql database, since this is where we need to do some changes.

mysql> use mysql;

Use mysql commands to update the password of a user, eg. lhan:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='lhan';

Reload privileges:

mysql> flush privileges;