Archive for September, 2009

MacOSX – Burn a DMG or ISO image to disc.

Bring up the Disk Utility in Applications/Utilities.
Drag and drop the image file to the left window pane. The image file will be listed along with your other disk drives.
Click on the newly listed image file to select it in the Disk Utility.
Click on the Burn icon on the top toolbar.

MySQL Commands

I intend to add to this as I have time.

To display the schema of a table:

mysql> desc tablename;

To restore a database from backup:

mysql -u root -p
mysql> create database databasename;
mysql> use databasename;
mysql> source databasebackupfile;
Note: I back my databases up to a file name of databasename.sql.

To update all records in a column of a table:

mysql> update tablename set columnname=’value‘;

To update one record in a column of a table. Note: othercolumnname needs to be unique to the record.:

mysql> update tablename set columnname=’value‘ where othercolumnname=`somevalue‘;

To create an account:

GRANT ALL PRIVILEGES ON databasename.* TO ‘username‘@’hostname‘ IDENTIFIED BY ‘password‘;
GRANT ALL PRIVILEGES ON databasename.* TO ‘username‘@’localhost’ IDENTIFIED BY ‘password‘;
FLUSH PRIVILEGES;

To make mysqld listen only localhost:

vi /etc/my.cnf

bind-address=127.0.0.1

To count the number of rows in a table:

SELECT COUNT(*) FROM tablename;

To remove formatting and heading from a SELECT statement and delimit using a TAB character:

mysql mydatabase -N -B -u myuser -p -e “myselect statement;”

How to convert timestamp to date from a SELECT statement:

SELECT column1,DATE_FORMAT(FROM_UNIXTIME(`column2`), ‘%b %e %Y’) AS ‘date_formatted’ FROM tablename;

Here is a simpler example, where timestampcol is the name of date column you want to convert:

SELECT FROM_UNIXTIME(timestampcol) FROM tablename;

How to get the time an hour ago:

SELECT TIME(DATE_SUB(NOW(), INTERVAL 1 HOUR));

To access a MySql database from a script without having the password stored in the script:
Add the information to the .my.cny file in the user’s home directory that the script will run under.

Edit or create the file:

# vi ~/.my.cny

Protect the file:

# chmod 600 ~/.my.cny

Sample file contents:

# cat ~/.my.cny
[client]
user=mysqlusername
password=mysqlpassword
database=defaultdatabase

Then, to use it:

# mysql < mysqlscript > my.output

Display table information:

show table status from databasename;

How to backup one table:

# mysqldump -u mysqlusername -h mysqlhostname -p databasename tablename > tablename.sql

To search or output of a query. This would also apply to a SELECT statement.:
SHOW TABLES LIKE ‘%whatever%’;

To limit the number of rows:
SELECT * FROM mytableLIMIT 100;

To sort output based on a particular column:
SELECT * FROM mytable ORDER BY mycolumn DESC;

To get the size of a databse:
SELECT table_schema “Database Name”, SUM( data_length + index_length)/1024/1024
“Database Size (MB)” FROM information_schema.TABLES where table_schema = ‘mydatabase‘;

To get the size of all the tables in a database:
SELECT table_name “Table”, table_rows “Number of Rows”, round(((data_length + index_length)/1024/1024),2)
“Size of Table” FROM information_schema.TABLES WHERE table_schema = “mydatabase“;

To allow an account to export data to a file:
GRANT FILE ON *.* TO ‘username‘@’hostname‘;

Configure Finder to display the hidden files and directories.

From a Terminal session, type in the following:

To display hidden files:
defaults write com.apple.finder AppleShowAllFiles TRUE
killall Finder

To hide hidden files:
defaults write com.apple.finder AppleShowAllFiles FALSE
killall Finder

Return top

INFORMATION