Tag Archives: MySQL

MySQL: How do you enable sphinxse (Sphinx Storage Engine) in your mysql installation?

As you may know mysql fulltext search is not highly scalable.  One of the options to get around this scalability limitation, which I prefer, is to use Sphinx.  You can use Sphinx with out having to alter your mysql installation.  But, if you would like to use from within mysql and not have to worry about how to pass data between Sphinx and MySQL, you can enable sphinxse (sphinx storage engine).  It is not included with mysql by default so you will have to compile it yourself.

Here are the instructions on how to get sphinxse compiled with your mysql installation on CentOS x64.  I am sure same instructions will work for other flavors but I have not tested it.  I will be compiling the most current version of sphinx (0.9.8) with most current stable version of mysql (5.0.51b) at the time of the writing.  Let’s get the appropriate packages first:

wget http://www.sphinxsearch.com/downloads/sphinx-0.9.8.tar.gz
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.51b.tar.gz/from/http://mysql.he.net/
tar zxpf sphinx*
tar zxpf mysql*

You will also need “bison”, “patch”, “automake” and “libtool” installed.  Let us just do a yum install for it.

yum -y install bison patch automake libtool

NOTE:  if you don’t install bison, you will get the following error:
sed '/^#/ s|y\.tab\.c|sql_yacc.cc|' y.tab.c >sql_yacc.cct && mv sql_yacc.cct sql_yacc.cc
sed: can't read y.tab.c: No such file or directory
make[2]: *** [sql_yacc.cc] Error 2

Let us continue with patching mysql source with sphinx storage engine (sphinxse) code and compile/install our new binaries.

cd mysql*
patch -p1 < ../sphinx-0.9.8/mysqlse/sphinx.5.0.37.diff #Make sure everything succeeded.
BUILD/autorun.sh
mkdir sql/sphinx
cp ../sphinx-0.9.8/mysqlse/* sql/sphinx
./configure --prefix=/usr/local/mysql --with-sphinx-storage-engine
make
make install

Now start your mysql installtion and check if engine support is compiled in:

mysql> show engines\G
Engine: SPHINX
Support: YES
Comment: Sphinx storage engine 0.9.8

To read more about how to use Sphinx storage engine, please refer to:  Sphinx documentation for using sphinx storage engine

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL: Fix Microsoft Word characters. Shows weird characters on the web page.

As a consultant, I do a lot of content migrations for clients. One issue I run into quite often is the encoding of databases, tables, columns differs between source and destination. Most clients do not want me to go and change the way their encoding is to fix issues since they are too afraid about messing with production data. Of course amongst other issues, it creates weird characters for data which is copied/pasted from Microsoft Word. You see weird characters like: ’ … – “ †‘

So if you just want to replace these with appropriate symbols, you may do it with a simple sql query. Note that below queries are without where clause. You may what to test it with one of your rows before making changes to the whole table. Of course, you should always backup your data before you try this out. If you have a dev system, that is even better. I put all my sql queries into a file ex: fix.sql and sourced it with mysql client.

vi fix.sql

update table_name set fieldname = replace(fieldname, '’', '\'');
update table_name set fieldname = replace(fieldname, '…','...');
update table_name set fieldname = replace(fieldname, '–','-');
update table_name set fieldname = replace(fieldname, '“','"');
update table_name set fieldname = replace(fieldname, '”','"');
update table_name set fieldname = replace(fieldname, '‘','\'');
update table_name set fieldname = replace(fieldname, '•','-');
update table_name set fieldname = replace(fieldname, '‡','c');

Save/exit.

# mysql
mysql> source fix.sql;

I am not sure if I am missing any other chars. If you know of any other chars, please comment with them and I will add on to the script here.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL: How do you install innotop to monitor innodb in real time?

Innotop is a very useful tool to monitor innodb information in real time. This tool is written by Baron Schwartz who is also an author of “High Performance MySQL, Second edition” book. [Side note: I highly recommend getting this book when it comes out (in June, 08?). Other authors include: Peter Zaitsev, Jeremy Zawodny, Arjen Lentz, Vadim Tkachenko and Derek J. Balling.] Quick summary of what innotop can monitor (from: http://innotop.sourceforge.net/): InnoDB transactions and internals, queries and processes, deadlocks, foreign key errors, replication status, system variables and status and much more.

Following are the instructions on how to install innotop on CentOS x64/Fedora/RHEL (Redhat enterprise). Most probably same instructions can be used on all flavors of Linux. If not, leave me a comment and I will research a solution for you. Let us start with downloading innotop. I used version 1.6.0 which is the latest at the time of writing.

wget http://internap.dl.sourceforge.net/sourceforge/innotop/innotop-1.6.0.tar.gz

Now let us go ahead and unzip and create the MakeFile to get it ready for install

tar zxpf innotop-1.6.0.tar.gz
cd innotop-1.6.0
perl Makefile.PL

At this point if you get the following output, you are good to continue:

Checking if your kit is complete...
Looks good
Writing Makefile for innotop

If you get something similar to following, you will need to take care of the prerequisites:

Looks good
Warning: prerequisite DBD::mysql 1 not found.
Warning: prerequisite DBI 1.13 not found.
Warning: prerequisite Term::ReadKey 2.1 not found.
Writing Makefile for innotop

Just because they are warnings does not mean you ignore them. So let us install those prerequisites. We will use perl’s cpan shell to get this installed (visit my post on how to install perl modules for more details). If it is your first time starting this up, you will have to answer some questions. Defaults will work fine in all cases.

perl -MCPAN -eshell
install Term::ReadKey
install DBI
install DBD::mysql

Note: you must install DBI before you can install DBD::mysql.

If you get an error like following when you are installing DBD::mysql:

Error: Can't load '/root/.cpan/build/DBD-mysql-4.007/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.15: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.

You will have to create a symlink to the object file in your lib64 (or lib if you are not using x64 version) folder:

ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.15 /lib64/

Once all prerequisites are done, type perl Makefile.PL and you should have no warnings. Continue the install:

make install

At this point you should have innotop installed on your system. Let us do some quick set up so you can start using innotop. We start with configuring your .my.cnf to include connection directives.

vi ~/.my.cnf

Add the following (edit to reflect your install) and save/exit

[mysql]
port = 3306
socket = /tmp/mysql.sock

Start up innotop by typing innotop at your shell prompt. First prompt will ask you to “Enter a name:”. I just put localhost since this will be used to connect locally. Next prompt asks you about DSN entry. I use: DBI:mysql:;mysql_read_default_group=mysql

This tells innotop to read .my.cnf file and use group [mysql] directives. Next prompt is optional (I just press enter). Next two prompts you enter information if you need to.

At this point your innotop installation / testing is complete. You can read man innotop to get more details on how to use innotop.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL: How do I dump each record from a table to a separate files in csv format?

I honestly do not know why somebody would want to export each record from a table in to its’ own files in a csv format. I am sure people have their own reasons. But since I got request from couple people, I figure I would post a solution here. Same script can be used to dump the whole table in to one csv file as well, with little tweaking. I will start with creating database with a table. I then insert three rows with test data into the table just to show three separate files creation.

mysql> CREATE DATABASE testdump;
mysql> USE testdump
mysql> CREATE TABLE `testtable` (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`testfield` VARCHAR( 15 ) NOT NULL ,
`testfield2` VARCHAR( 15 ) NOT NULL
) ENGINE = innodb COMMENT = 'test table for dumping each row to file';
mysql> INSERT INTO `testtable` values ('','test1','test2'),('','test3','test4'),('','test5','test6');
mysql> select * from testtable;
+----+-----------+------------+
| id | testfield | testfield2 |
+----+-----------+------------+
| 1 | test1 | test2 |
| 2 | test3 | test4 |
| 3 | test5 | test6 |
+----+-----------+------------+
3 rows in set (0.00 sec)

Now we create a php script which we can use to select data from MySQL database and save it to a file under /tmp directory. You can easily modify this script to save it somewhere else, pull from multiple tables and/or save with different delimiter.

<?php
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$db = mysql_select_db('testdump',$link);
//specify what query to use to pull data.
$select = "SELECT * FROM `testtable`";
$result = mysql_query($select);
while($myrow = mysql_fetch_array($result))
{
extract($myrow);
//specify what query to use for create text files with csv data
//in our case we create files with testtable_ and ID which is unique
//per row. We use the same ID in where clause to limit one record at a time
//modify this part to your needs. Eg change delimiter from , to - or something else
$export = "
SELECT * INTO OUTFILE '/tmp/testtable_$id.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM testtable where id=$id";
mysql_query($export);
}//loop until we are done exporting all records.
mysql_close($link);
?>

You can run this script from command line if you have php cli installed by issuing: php filename.php

Above example is a very simple example. t is merely a proof of concept. You can modify it to do whatever you need it to do. I would also put some error checking, not use extract() function, etc before using it in production environment.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

MySQL: How do you use symlinks with MySQL tables?

Creating table symlinks in MySQL is very easy and it is probably one of the features a lot of MySQL users overlook or never think about. You may ask, why would I want to use symlinks? Some of the reasons you would want symlinks is if you are running low on disk space on the partition where your data generally is and/or you want to move one (or more) table(s) on to a different disk/partition for performance reasons.

One of the things worth mentioning is that MySQL documentation states: “Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.” Keeping that in mind, if you have a innodb table and would like to create symlinks for, you should change the engine type to myisam before symlinking. Although, I have heard and personally used symlinks for innodb databases with no side affects.

First let us find out if your MySQL configuration allows symlinks:

mysql> SHOW VARIABLES LIKE 'have_symlink'\G
*************************** 1. row ***************************
Variable_name: have_symlink
Value: YES

I am going to make following assumptions:

  • MySQL installation is in /usr/local/mysql.
  • You want to create new tables under /mnt/another_partition/mysql/var
    1. you must create the dir: mkdir /mnt/another_partition/mysql/var -p
    2. Make it owned by mysql user: chown mysql /mnt/another_partition/mysql/var

We will create some test data. Following are the steps:

mysql> create database db1;
Query OK, 1 row affected (0.05 sec)
mysql> use db1;
Database changed
mysql> CREATE TABLE `users` ( `id` int(11) default NULL, `name` varchar(255) default NULL ) ENGINE=myisam, data directory="/mnt/another_partition/mysql/var", index directory="/mnt/another_partition/mysql/var";
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| users |
+---------------+

Drop back to command prompt and “ls” your db1 directory as shown below. Here you can see that users table’s data file (users.MYD) is a symbolic link to the location we specified. You can see that users index file (users.MYI) is symlinked to the location we specified as well.

# ls -al /usr/local/mysql/var/db1/
total 24K
drwx------ 2 mysql mysql 4.0K May 2 00:03 ./
drwx------ 23 mysql mysql 4.0K May 1 23:59 ../
-rw-rw---- 1 mysql mysql 65 May 1 09:33 db.opt
-rw-rw---- 1 mysql mysql 8.4K May 2 00:03 users.frm
lrwxrwxrwx 1 mysql mysql 42 May 2 00:03 users.MYD -> /mnt/another_partition/mysql/var/users.MYD
lrwxrwxrwx 1 mysql mysql 42 May 2 00:03 users.MYI -> /mnt/another_partition/mysql/var/users.MYI

Here are the real files which above symlinks are pointing to:

# ls -al /mnt/another_partition/mysql/var/
total 12K
drwxr-xr-x 2 mysql root 4.0K May 2 00:03 ./
drwxr-xr-x 3 root root 4.0K May 1 23:39 ../
-rw-rw---- 1 mysql mysql 0 May 2 00:03 users.MYD
-rw-rw---- 1 mysql mysql 1.0K May 2 00:03 users.MYI

If you go back in to mysql prompt and insert couple rows, you can see the size of users.MYD increase:

mysql> insert into db1.users values (1,"test1");
Query OK, 1 row affected (0.01 sec)
mysql> insert into db1.users values (2,"test2");
Query OK, 1 row affected (0.01 sec)
# ls -al /mnt/another_partition/mysql/var/
total 16K
drwxr-xr-x 2 mysql root 4.0K May 2 00:03 ./
drwxr-xr-x 3 root root 4.0K May 1 23:39 ../
-rw-rw---- 1 mysql mysql 40 May 2 00:08 users.MYD
-rw-rw---- 1 mysql mysql 1.0K May 2 00:08 users.MYI

If you already have data file and index file you would like to move, I recommend shutting down your MySQL server and moving the files manually and create symbolic links by using “ln -s” command. You can read more about ln by doing man ls

NOTE: Even though MySQL documentation says this is only supported 100% for myisam tables, I have read few posts where people say they have done this with innodb tables without any problems. If anybody else can comment on this, I would greatly appreciate it.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.