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
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 -u database_name -p < /path/to/file.sql
#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)
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;
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 Dump
--opt database > database_backup.sqlMySql Dump without data (only structure)
--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
Show Database
Show Tables
or Show Table Status for more information
Describe Table
To fully support UTF-8 with mysql, make sure of the followings:
Related links:
Assuming 1 to M from Person to Address: