Tuesday, April 27, 2010

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.

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
Installing MySQL system tables...
Filling help tables...
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
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:
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
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 :


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.


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

ALTER TABLE your_table


No comments:

Post a Comment