Category Archives: MySQL

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

8 Replies

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.

13 Replies

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?

4 Replies

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.

Oh dear MySQL slave, where did you put those rows?

9 Replies

I need help from my fellow mysql users.  I know some of the people who read this are alot better then me with mysql so hopefully you can help 🙂

So today we decided that we are going to migrate one of our master database servers to new hardware.  Since we got the hardware this morning and we wanted to move on to it asap, we decided that we will take our slave down, copy data from it, and bring it up on future master server.  At that point, we will let it run as slave to the current master server until its time for us to take it down.  Reason we did that instead of mysqldump/import was to avoid the lag mysqldump creates on our server.

After we did all this and put up the new master server, we started to notice odd issues.  After looking around and comparing old db with new, we found out that new db was missing data.  How it happened is beyond me and is the reason why I am writing this.  We never had issues with the slave which would cause data to be lost; so what happened to those missing rows?  Is this something which is common?  Can we not trust our slave enough to use it as master if master died?  Can we not run backups off the slave with confident that our data is protected and up to date so to keep load down on our master?  All these questions which keep me awake and wondering…

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

2 Replies

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.