Archive for the ‘Mysql’ Category

MySQL: Change root password

Wednesday, August 7th, 2013

1) Kill mysqld
2) Start like that.

sudo mysqld --skip-grant-tables --skip-networking &

3) Type “mysql”

UPDATE mysql.user SET Password=PASSWORD('pwd') WHERE User='root';
FLUSH PRIVILEGES;

4) Kill mysqld and restart it with “sudo service mysqld start”

Source: MYSQL doc

MySQL: Installing and starting MySQL On Mac

Sunday, June 30th, 2013
  1. Download latest native Package (i.e. .dmg) here: MySQL Download
  2. See installation doc (install MySQL and the startup items

Startup Items Info from doc

The Startup Item for MySQL is installed into /Library/StartupItems/MySQLCOM. The Startup Item installation adds a variable MYSQLCOM=-YES- to the system configuration file /etc/hostconfig. If you want to disable the automatic startup of MySQL, change this variable to MYSQLCOM=-NO-.

Start:

shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

Stop:

shell> sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop

See source

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