MySQL: Shell script to backup mysql database
Monday, September 7th, 2009tdy=`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
Archive for the ‘Mysql’ Category
MySQL: Load SQL file to MySQL
Saturday, July 25th, 2009mysql -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)
MySQL: Force Drop Table with Foreign Keys
Saturday, June 6th, 2009If 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, 2009This 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, 2009MySql Dump
# mysqldump -u dbuser -p[pwd]
--opt database > database_backup.sqlMySql Dump without data (only structure)
# mysqldump -u dbuser -p[pwd]
--no-data database > database_backup.sqlMySql 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
MySql: Show, Desc, Info on Database and Table
Wednesday, January 14th, 2009Show Database
mysql> show databases
Show Tables
mysql> use my_database
mysql> show tables
mysql> show tables
or Show Table Status for more information
mysql> show table status
Describe Table
mysql> desc my_table
Mysql: Java Web App and MySql 5.x UTF-8
Saturday, November 8th, 2008To fully support UTF-8 with mysql, make sure of the followings:
- Set HttpServletRequest UTF-8 character encoding.
request.setCharacterEncoding(”UTF-8″);
- Set HttpServletResponse UTF-8 character encoding
response.setContentType(”text/html; charset=UTF-8″);
- Create the Column, Table, or DB need to have the UTF8 charset and collation
CHARACTER SET utf8 COLLATE utf8_general_ci
- THE TRICK: Add the character encoding instruction in your JDBC URL
jdbc:mysql://localhost:3306/DATABASENAME?useUnicode=true&characterEncoding=UTF8
Related links:
Mysql: Create Foreign Key
Sunday, October 12th, 2008Assuming 1 to M from Person to Address:
ALTER TABLE address ADD
CONSTRAINT FK_address_person_id
FOREIGN KEY (person_id)
REFERENCES person(id) ON DELETE CASCADE
CONSTRAINT FK_address_person_id
FOREIGN KEY (person_id)
REFERENCES person(id) ON DELETE CASCADE
