Archive for the ‘Mysql’ Category

OSX: Install mysql on Mac

Wednesday, September 8th, 2010

1) Download and install the .dmg image

2) Fix the permission

sudo chgrp -R mysql /usr/local/mysql

You might need to do the following:

sudo chown -Rf :mysql /usr/local/mysql-5.1.50-osx10.6-x86_64/

This will set the _mysql group owner for all the subfolders of mysql

3) Start the MYSQL

sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

4) Create the supersecure root password

/usr/local/mysql/bin/mysqladmin -u root password supersecurepassword

5) symlink mysql

sudo ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

6) login via mysql

msyql -u root -p

7) Download and install Sequel Pro (the SQLyog for Mac)

Linux: MySQL Install (with yum) & Run

Sunday, August 15th, 2010

Install via Yum

yum install mysql
yum install mysql-server

Run

service mysqld start

Set Root Password

mysqladmin -u root password MYPASSWORD

Change root Password

mysqladmin -u root -p'oldpassword' password newpass

MySQL: Yahoo MySQL HA

Thursday, August 5th, 2010

http://mysqlguy.net/blog/2010/08/03/mysql-master-ha-yahoo

MySQL: Shell script to backup mysql database

Monday, September 7th, 2009
tdy=`date +%Y-%m-%d-%H-%M`
mkdir /var/backup/db_name/$tdy
mysqldump -u db_user -p --opt db_name > /var/backup/db_name/$tdy/db_name-$tdy.sql

MySQL: Load SQL file to MySQL

Saturday, July 25th, 2009
mysql -u database_name -p < /path/to/file.sql

MySql: Drop table, rename table, drop foreign key, drop column

Friday, July 24th, 2009
#switch to the right DB
USE database_name;

#drop a table
DROP TABLE table_name;

#rename a table
RENAME TABLE table_name TO new_table_name; 

#drop a foreign key
ALTER TABLE table_name DROP FOREIGN KEY FK_name;

#drop a column
ALTER TABLE table_name DROP COLUMN column_name;

#change a column to varchar(128)
ALTER TABLE table_name CHANGE column_oldName column_newName  varchar(128)

See MySQL Alter Table doc.

# Check the foreign key of a particular database
SELECT * FROM information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA='myDatabaseName';

Source

MySQL: Force Drop Table with Foreign Keys

Saturday, June 6th, 2009

If you try to drop a table that has some foreign keys, you will get an exception.

mysql> SET foreign_key_checks = 0;
mysql> drop table ...
mysql> SET foreign_key_checks = 1;

Java: c3p0.ComboPooledDataSource broken pipe Error With MySQL

Monday, April 13th, 2009

This usually happen when the client connection pool maintained connection longer than the database connection. Fix: set the maxIdleTime property:

 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass" value="${db.driverClass}" />
  <property name="jdbcUrl" value="${db.jdbcUrl}" />
  <property name="user" value="${db.user}" />
  <property name="password" value="${db.password}" />
  <!-- 1/2 hr. MySQL server default connection timeout is 43200 (12hrs) -->
  <property name="maxIdleTime" value="1800" />
 </bean>

Information Source: Broken pipe

MySql: Import dump file in MySql

Friday, January 16th, 2009
mysql -u dbuser -p database < file-dump.sql

MySql: backup, mysqldump nodata, no structure, no create table

Wednesday, January 14th, 2009

MySql Dump

# mysqldump -u dbuser -p[pwd] --opt database > database_backup.sql

MySql Dump without data (only structure)

# mysqldump -u dbuser -p[pwd] --no-data database > database_backup.sql

MySql Dump without structure (no drop/create table)

# mysqldump -u dbuser -p[pwd] --no-create-db --no-create-info database > database_backup.sql

Import SQL file

mysql -u user_name -p  database_name < /path/to/file.sql