Archive for the ‘Mysql’ Category

« Older Entries

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)

Posted in Mysql, OSX | No Comments »

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

Posted in Linux, Mysql | No Comments »

MySQL: Yahoo MySQL HA

Thursday, August 5th, 2010

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

Posted in Mysql | No Comments »

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

Posted in Linux, Mysql | No Comments »

MySQL: Load SQL file to MySQL

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

Posted in Mysql | No Comments »

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

				


				
 Posted in Mysql |   No Comments »


			


				

				
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;



				


				
 Posted in Mysql |   3 Comments »


			


				

				
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

				


				
 Posted in Hibernate,  Java,  Mysql |   No Comments »


			


				

				
MySql: Import dump file in MySql

				Friday, January 16th, 2009

				

					

mysql -u dbuser -p database < file-dump.sql


				


				
 Posted in Mysql |   No Comments »


			


				

				
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



				


				
 Posted in Mysql |   1 Comment »


			


		
		

			
« Older Entries