Archive for the ‘MySQL’ Category.
December 18, 2009, 8:29 pm
$ awksqldump dbname.dump.sql tblname
#!/bin/bash
### check parameter
E_NO_ARGS=65
if [ $# -eq 2 ]
then
# Outputs string of specified table only
awk "/Table structure for table .$2./,/UNLOCK TABLES/{print}" $1 > $1.EXTRACTED.sql
elif [ $# -eq 3 ]
then
# Outputs string starting from table1 and ends on beginning of table2
awk "/Table structure for table .$2./,/Table structure for table .$3./{print}" $1 > $1.EXTRACTED.sql
else
echo "Awk Sql Dump
Usage: awksqldump dumpfilename tablename1 [tablename2]
"
exit $E_NO_ARGS
fi
November 18, 2008, 5:44 pm
To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
August 26, 2008, 11:53 pm
ALTER TABLE `attributes` DROP FOREIGN KEY `attributes_ibfk_1`;
MySQL ERROR 1005 (HY000): Can’t create table ‘Table.frm’ (errno: 150). Here’s the solution.
Example:
ALTER TABLE `race` DROP FOREIGN KEY `race_ibfk_1`;
ALTER TABLE `race`
ADD CONSTRAINT `race_ibfk_1` FOREIGN KEY (`specieId`) REFERENCES `specie` (`specieId`) ON DELETE CASCADE;
July 25, 2008, 7:19 pm
Here are the SQL commands:
create database newdb;
grant CREATE,INSERT,DELETE,UPDATE,SELECT on newdb.* to newuser@localhost;
SET PASSWORD FOR 'newuser'@'localhost' = password(’newuserpass’);
– or –
SET PASSWORD FOR 'newuser'@'localhost' = old_password(’newuserpass’);
flush privileges;