Restore compressed backup files (MYSQL)

the commandline is :

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

** if you dump file bz2 you can use bunzip2

Script to rename multiple table in MySQL

Baru-baru ni aku faced problem untuk rename table prefix secara pukal...kat database tu ada 186 tables...
kalau nak tukar satu-satu...pergghh...memang lebam...setelah google punya google...aku jumpa solution ni...tapi kena pakai ruby script dan tukar ke sql statement..tapi kalau korang nak pakai convert ke php pun x de hal...guna concept yg sama jek...

Ikut ni :

1. masuk mysql...pastu show tables;
2. copy semua tables yg korang nak rename tu...
3. paste kat dlm script ni (dlm cth ni aku bagi nama file ni batch_rename.sh ) :
   
   OLD_PREFIX = "mdl"
   NEW_PREFIX = "mdlsirap"
   data=<<-EOD | mdl_assignment | | mdl_assignment_submissions | | mdl_backup_config | | mdl_backup_courses | | mdl_backup_files | | mdl_backup_ids | | mdl_backup_log | | mdl_block | | mdl_block_instance |
   | mdl_block_pinned |
   EOD
   tables = []
   data.each_line do |line|
   if line=~/(#{OLD_PREFIX}\w+)/
   puts "RENAME TABLE #{$1} TO #{NEW_PREFIX}#{$1};"
   end
   end
   
   
4. Pastu runkan script ni pakai ruby punya command-line (aku assume korang dah install ruby) :
   #ruby batch_rename.sh
5. Dia akan kluar output :
   RENAME TABLE mdl_assignment TO mdlsirap_assignment;
   RENAME TABLE mdl_assignment_submissions TO mdlsirap_assignment_submissions;
   RENAME TABLE mdl_backup_config TO mdlsirap_backup_config;
   RENAME TABLE mdl_backup_courses TO mdlsirap_backup_courses;
   RENAME TABLE mdl_backup_files TO mdlsirap_backup_files;
   RENAME TABLE mdl_backup_ids TO mdlsirap_backup_ids;
   RENAME TABLE mdl_backup_log TO mdlsirap_backup_log;
   RENAME TABLE mdl_block TO mdlsirap_block;
   RENAME TABLE mdl_block_instance TO mdlsirap_block_instance;
   RENAME TABLE mdl_block_pinned TO mdlsirap_block_pinned;
6. just masukan sql statement ni dlm satu file lain...tapi kalau nak senang...buat step no. 4 tu camni :
   #ruby batch_rename.sh > a.sql
7. Runkan sql file tu guna mysql...:
   #mysql namadatabase <>
8. Selesai....
   

reference :
http://zerokspot.com/weblog/e/676/

Repair table MYSQL

Today i face 1 problem when i want mysqldump my D. my error is

Incorrect key file for table 'mdl_block_instance'; try to repair it

This command to help you to auto-repair your table on mysql

mysqlcheck -uusername -p --auto-repair namedatabase

Netcat Tar

This is suitable if you don't have enough space on the source machine.

On sender :

tar -czf - directory-name | nc -l 3333

on receiver :

nc sender-ip-address 3333 > mybackup.tar.gz

Courtesy : Sifoo Zaman.

Change Login Directory (FreeBSD)

today i learn something.

How to change directory when login.

Login as root
setenv EDITOR ee (i need this because i don't know how to use VI in BSD)
type vipw
edit the directory at user you want to edit
save & exit

DONE

Hidden Apache directory listing

If you are on an RPM installation of Apache (which i dont really recommend) you will find the apache configuration file probably here:

/etc/httpd/conf/httpd.conf

If you are using apache from the source tar balls ( like real men ) probably you will find the configuration file here:

/usr/local/apache/conf/httpd.conf

Using an editor like vi , edit the httpd.conf file and scroll until you find a line like this:

Options All Indexes FollowSymLinks MultiViews

To disable directory browsing carefully remove the line that says: Indexes and leave the line like this:

Options All FollowSymLinks MultiViews

Restart your apache webserver and thats it

reference http://www.felipecruz.com/blog_disable-directory-listing-browsing-apache.php

Setting Clock on Centos

what im doing to configure clock on centos

install ntp

yum install ntp

after finish run

ntpdate ntp.asia.pool.org

done!

Search using mysqlbinlog (troubleshoot)

simply go to or where your mysqlbinlog located :

after you find it run this command
mysqlbinlog mysql-bin.0000xx | grep nametosearch > namefile.txt

Dump using sed & mysqldump

Dump table mysql using sed

sed -n '/^-- Table structure for table \`nametable\`/,/^-- Table structure for table/p" namedb > namatable.sql

using mysqldump

mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

restore DB / table

mysql -u username -ppassword databasename < /tmp/databasename.sql Dump table mysql using sed

sed -n '/^-- Table structure for table \`nametable\`/,/^-- Table structure for table/p" namedb > namatable.sql

using mysqldump

mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

restore DB / table

mysql -u username -ppassword databasename < /tmp/databasename.sql

Mysqldump only the structure

run this command for mysql backup only the data structure

mysqldump -d -h localhost -u root -pmypassword databasename > dumpfile.sql