vicker313 tech blog

April 8, 2011

Turn on MYSQL Trace Feature

Filed under: MySQL — Tags: , — vicker313 @ 2:34 pm

To turn on MySQL trace feature, simply issue following command in MySQL console (OFF to turn off)

SET GLOBAL general_log = 'ON';

Or add following option to MySQL start up command (to turn off use 0):

mysqld --general_log=1

You can find the trace log at the database folder. The file name is something like host_name.log.

January 14, 2010

Change MySQL Database Location in Linux

Filed under: MySQL — Tags: , — vicker313 @ 8:00 am

Install MySQL in Linux doesn’t like in Windows, either install during OS installation or install separately, you can’t define the location you want the database to be located. Steps below is to change the location of the database (or reallocate):

  1. Stop MySQL service (the service name may be different)
    service mysql stop
  2. Copy MySQL data file to destination (usually the default database directory is /var/lib/mysql. Make sure the owner of the directory and files are mysql.
    cp -r /var/lib/mysql /destination
  3. Go inside the database directory and remove the log files (usually have 2).
    rm /destination/ib_log*
  4. Locate MySQL configuration file. You can find it at /etc/my.cnf. If no such file exists, copy a sample of configuration file from /usr/share/mysql/. There will be a few files to be selected (named as my-xxx.cnf). Copy any of them to /etc and rename to my.cnf.
  5. Edit MySQL configuration file. Go to section mysqld and look for the keyword datadir (add in if not exist). Change the location to the destination.
    datadir=/destination/
  6. Start MySQL service and done.
    service mysql start

September 1, 2009

Transfer MySQL Database Offline

Filed under: MySQL — Tags: , , — vicker313 @ 8:00 am

Transfer MySQL database offline means, stop MySQL service, copy the database directory and paste it to another location directly. However it will be difference when transferring MyISAM database and InnoDB database:

  • MyISAM: simply copy past the database directory (data\databasename) will do since all the data files are inside the directory
  • InnoDB: need to copy the whole data directory (data), which all the databases will be copied as well. After you paste the directory to the destination, you need to remove the ib_logfile0 and ib_logfile1 before start the MySQL service

Choose Between MyISAM and InnoDB in MySQL

Filed under: MySQL — Tags: , , — vicker313 @ 8:00 am

We can choose how the data store in table at MySQL, by defining the storage engine:

create table test (id int) engine = [myisam|innodb];

It will use the default storage engine stated at database if the engine option in create statement is not defined. Two commonly used storage engines:

MyISAM

  • Fast in select and insert
  • Less reliable
  • No foreign key checking, where user can inset foreign key that does not exist
  • All the files (FRM, MYI and MYD) are placed at database directory (data\databasename), which make the transfer of database is easy
  • Each database data is stored in different data files and directory
  • No need to maintain

InnoDB

  • Slow in select and insert
  • More reliable
  • Has foreign key checking
  • Only FRM files are placed at database directory (data\databasename), while other files like IB_LOGFILE0, IB_LOGFILE1, IBDATA1 and MYSQL-BIN files are placed at data directory, which you need to copy the whole data directory when transferring database
  • All databases data are mixed in a single data file
  • Need to maintain the database from time to time by purging the binary log
    PURGE BINARY LOGS TO 'mysql-bin.010';
    Or
    PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

Blog at WordPress.com.