Rollback with MySQL Transaction Log

Yesterday was a Grey Monday (almost turning into Black Monday)..

It happened that one of the MySQL DB was not backed-up and to make things more thrilling, one the table on the db was compromised.
Alhamdulillah, again, Zaman came to save the day.

Here what we did to rollback the transaction using the transaction log :

1. Read the transaction log in /var/db/myql/hostname-bin.xxxxxx
   This is binary format. In order to read the file, you have to use mysqlbinlog command :
   
   mysqlbinlog mysql-bin.000001
   
   This will show every single transaction happened on that particular server.
   
   
2. Then setup new MySQL server (I strongly advise you to setup a new database since we do not want to mess the existing one.)
   
   
3. Dump the compromised db structure only (without data) then restore it in the new server.
   
   
4. Then simply run the mysqlbinlog command and extend it to restore back to the server :
   
   mysqlbinlog mysql-bin.xxxxxx --database=yourdbname | mysql -uroot -p yourdbname
   
   If you want to exclude the last transaction which normally will be the culprit, you can use --stop-datetime option. You should know when is the last point/time (before the culprit transaction) of the transaction.
   
   
   
   By reading the transaction log, you will find the timestamp provided by MySQL. For example : 1263759884
   
   Since mysqlbinlog format needs human readable format, you have to convert the timestamp to human readable format. Here is one of the online date converter :
   
   http://www.epochconverter.com/
   
   Once converted, append it into the command :
   
   
   
   mysqlbinlog mysql-bin.000024 --database=yourdbname --stop-datetime="2010-01-17 22:11:16" | mysql -uroot -p yourdbname
   
   That's it.

Add HDD on VMWARE (linux)

- choose vm that you wish to add the HDD
- edit setting
- add and choose HDD
- follow the instruction
- fdisk -l to see all hdd status
- you will noticed /dev/sdb (not partitoned)
- format new partition : [root@localhost ~]# mkfs -t ext3 /dev/sdb1
- Create mount point and set up automatic mount when system start up [root@localhost ~]# mkdir /newdisk
- Add the following line into fstab
/dev/sdb1 /newdisk ext3 defaults 1 1

reboot machine

Add HDD on VMWARE (FreeBSD)

I just finish adding HDD onto my Vmware server.

the step :

- choose vm that you wish to add the HDD
- edit setting
- add and choose HDD
- follow the instruction
- after adding go to server (freebsd)
- type sysinstall
- choose fdisk
- BEWARE! please make sure your HDD before you format it!
- after you choose your HDD, choose A and W (write the HDD)
- after finish,exit and choose label
- choose C (create) , edit the label and W (write) the HDD.
- edit the fstab (ee /etc/fstab)

example :
but beware too remember the path your HDD before edit the fstab.

/dev/da1s1d /data ufs rw 2 2

- save and reboot.

Setting in FSTAB add HDD in FreeBSD or Unix

ee /etc/fstab

/dev/da2s1 /studentdata ufs rw 2 2

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....

Credit to Sirap.Dol.

Enable the query cache in MySQL to improve performance

from mysql server

$ mysql -u root –p

set cache inside mysql shell



mysql> SET GLOBAL query_cache_size = 16777216;

mysql> SHOW VARIABLES LIKE 'query_cache_size';

exit and edit my.cnf

(on ubuntu)
cd /etc/mysql/my.cnf

# nano /etc/my.cnf

add

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

SUDO in FreeBSD / Unix

as root install

sudo located at /usr/ports/security/sudo
make configure
make && make install

edit conf at

ee /usr/local/etc/sudoers

example and edit :

# Runas alias specification

# User privilege specification
root ALL=(ALL) ALL
kenan ALL=(www) ALL

example :
usename ALL=(group) ALL


** note : !/usr/bin/su (this will prevent user to sudo su)

Set environment : setenv EDITOR ee


General sudoers File Record Format
usernames/group servername = (usernames command can be run as) command

example using sudo :

-list the dir-
sudo -u www ls -lah /usr/local/www/apache22/data/

-create a file-
sudo -u www touch /usr/local/www/apache22/data/test

- edit a file-
sudo -u www ee /usr/local/www/apache22/data/test

Can't create table (errno: 708) (Mysql BUGS)

I recently face my mysql problem like this.

mysql> alter table 19m4t_enrol_authorize engine=ndbcluster;
ERROR 1005 (HY000): Can't create table './lmsdb/#sql-2ece_a.frm' (errno: 708)

when i want to cluster my tables on my other nodes this error appear.

The Solution is go to my mysql management node and i increase

increase "MaxNoOfAttributes"

into my ndb_mgm.conf

example :

DataMemory = 24G
IndexMemory = 4G
MaxNoOfOrderedIndexes = 1024
MaxNoOfAttributes = 10000
MaxNoOfTables = 2500
MaxNoOfConcurrentOperations = 250000
MaxNoOfConcurrentIndexOperations = 250000

reboot all machine.

DONE!

Check RAM on FreeBSD

Install :

# cd /usr/ports/sysutils/freecolor
# make install clean

$ freecolor -m -o