Mysql Braindump = Tips & Tricks !

Note: These are braindumps !
While learning any topic I make notes, at first they are very basic and progressively they get advanced.
Don't expect super wisdom ("Gyan") out of these.
As I said just braindumps 8-)

If you have some tips you'd like to share, post in comments and I'll include them here for everybodys benefit.

MySQL

See all tables - "show tables;"

Configuring mysql for the first time after installation - The documentation from mysql is pretty good. I've just followed them, and have shown what the output of those commands are, just to satiate any paranoid developers like me 9-)

Remove unique constraint on MySql Table
This post describes how to do it, copied from there -
alter table TABLE_NAME drop index COLUMN_NAME;

Backup your MySQL database
mysqldump -u root -p DatabaseName > backuped_up_DB_DATE.sql
This will prompt you for your password.
The above is for the complete backup, including recreate statements & data.
Other variations are in the link.

Convert string to Date select column_name
from table
where date_column > str_to_date('31-jan-2010', "%d-%b-%Y")
and date_column< str_to_date('10-feb-2010', "%d-%b-%Y"); Drivers/connectors for Mysql for various platforms

Execute queries via mysql command prompt -
mysql db_name < text_file

Running mysql on Solaris for the first time

All commands are from the mysql installation directory.

default for Solaris is /opt/mysql

1.) Run the mysql_install_db command to create the basic grant tables

./mysql_install_db --user=mysql --basedir=/opt/mysql/mysql --datadir=/opt//mysql/datadir --log-error=/opt//mysql/logs/mysql_install_db_log_file.txt

Output

Installing MySQL system tables...

OK

Filling help tables...

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/opt/mysql/mysql/bin/mysqladmin -u root password 'new-password'

/opt/mysql/mysql/bin/mysqladmin -u root -h password 'new-password'

Alternatively you can run:

/opt/mysql/mysql/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /opt/mysql/mysql ; /opt/mysql/mysql/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd /opt/mysql/mysql/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /opt/mysql/mysql/scripts/mysqlbug script!

The latest information about MySQL is available at http://www.mysql.com/

Support MySQL by buying support/licenses from http://shop.mysql.com/

2.) Start the mysql database now

mysqld_safe --user=mysql --basedir=/opt/mysql/mysql --datadir=/opt//mysql/datadir --log-error=/opt//mysql/logs/mysql_error_file.txt

Output on screen

100209 14:31:21 mysqld_safe Logging to '/opt//mysql/logs/mysql_error_file.txt'.

100209 14:31:21 mysqld_safe Starting mysqld daemon with databases from /opt//mysql/datadir

Output in the log file (log-error)

100209 14:31:21 mysqld_safe Starting mysqld daemon with databases from /opt//mysql/datadir

100209 14:31:21 [Note] Plugin 'FEDERATED' is disabled.

100209 14:31:21 InnoDB: Started; log sequence number 0 44233

100209 14:31:21 [Note] Event Scheduler: Loaded 0 events

100209 14:31:21 [Note] /opt/mysql/mysql/bin/mysqld: ready for connections.

Version: '5.1.42' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)

3.) Shut it down from another prompt now

Shutdowm mysql deamon

mysqladmin -u root shutdown

Output in the log file (log-error)

100209 14:35:23 [Note] /opt/mysql/mysql/bin/mysqld: Normal shutdown

100209 14:35:23 [Note] Event Scheduler: Purging the queue. 0 events

100209 14:35:23 InnoDB: Starting shutdown...

100209 14:35:24 InnoDB: Shutdown completed; log sequence number 0 44233

100209 14:35:24 [Note] /opt/mysql/mysql/bin/mysqld: Shutdown complete

100209 14:35:24 mysqld_safe mysqld from pid file /opt//mysql/datadir/.pid ended

4.) Start again, this time with nohup so it can run in the background.

This is the actual command to start MySQL deamon

nohup mysqld_safe --user=mysql --basedir=/opt/mysql/mysql --datadir=/opt//mysql/datadir --log-error=/opt//mysql/logs/mysql_error_file.txt &

Output in the log file (log-error)

100209 14:38:00 mysqld_safe Starting mysqld daemon with databases from /opt//mysql/datadir

100209 14:38:00 [Note] Plugin 'FEDERATED' is disabled.

100209 14:38:00 InnoDB: Started; log sequence number 0 44233

100209 14:38:00 [Note] Event Scheduler: Loaded 0 events

100209 14:38:00 [Note] /opt/mysql/mysql/bin/mysqld: ready for connections.

Version: '5.1.42' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)

5.) Change the password of the mysql root user

/opt/mysql/mysql/bin/mysqladmin -u root password ''

6.) Secure the mysql database, by running this command and remove/disble all unsecure options

/opt/mysql/mysql/bin/mysql_secure_installation

The questions asked are -

Change the root password? [Y/n]

Remove anonymous users? [Y/n]

Disallow root login remotely? [Y/n] Y <-- Select yes for this whether this is dev OR production

Remove test database and access to it? [Y/n]

Output you will see on the screen towards the end when everything needs to be saved

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] Y

... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL

installation should now be secure.

Thanks for using MySQL!

7.) Convert timezone in SQL query

Use this function, where "last_update_date" is the column name.
Below the timezone is being converted from UTC to EST.
convert_tz(last_update_date, '+00:00','-05:00')

8.) Sysdate equivalent in MySQL
Want to get records 5 days back ?
In oracle that sysdate-5 or sysdate-10 came so handy.
In MySQL, you can do date arithmetic by functions DATE_ADD & DATE_SUB.
Yes, as you would've guessed, DATE_SUB would subtract a certain amount.
Here is an example :

SELECT DATE_SUB(NOW(), INTERVAL 5 day);

The DATE_SUB takes the time you want to act on (NOW() gives the current date/time) and INTERVAL 5 day is giving the amount of time it wants to subtract from it.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

9.) Auto increment your integer column
When you want one of the columns to auto increment :

ALTER TABLE your_table

MODIFY some_column INT NOT NULL AUTO_INCREMENT;