Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 21

Displaying posts with tag: Enterprise level solutions (reset)

sshfs: How do you install sshfs and fuse? [CentOS/Linux/Redhat]
+0 Vote Up -0Vote Down

One may wonder what is sshfs and why would you want it?  Well simply put, sshfs allows you to mount another server’s filesystem into a folder on your local system which in the background is doing ssh commands and transfers.  As a mounted folder, you are able to move about and copy files back and forth as everything was on local server.  As you can see this makes it very easy for you to work with files on multiple servers.

Note:  you only have to do the following installations on the server where you are doing the mounts on.

Let us download and install the filesystem framework which is a requirement for sshfs called fuse.

wget http://voxel.dl.sourceforge.net/sourceforge/fuse/fuse-2.7.4.tar.gz
tar zxpfv fuse-*.gz
cd fuse*

If you get the following error, you will either have to point to the

  [Read more...]
MySQL: How do you enable sphinxse (Sphinx Storage Engine) in your mysql installation?
+0 Vote Up -0Vote Down

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. 

  [Read more...]
MySQL: How do you use symlinks with MySQL tables?
+0 Vote Up -0Vote Down

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

  [Read more...]
MySQL: Solution for ERROR 1442 (HY000): Can’t update table ‘t1′ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+0 Vote Up -0Vote Down
Here is a sample table you can create to test following problem/solution on: CREATE TABLE `t1` ( `a` char(1) default NULL, `b` smallint(6) default NULL ); insert into t1 values ('y','1'); I have a table t1 which has column a and b, I want column a to be updated to ‘n’ when column b = 0. Here is the first version [...]
MySQL: How do you set up master-master replication in MySQL? (CentOS, RHEL, Fedora)
+0 Vote Up -0Vote Down
Setting up master-master replication in MySQL is very similar to how we set up master/slave replication. You can read up about how to setup master/slave replication in my previous post: How to set up master/slave replication in MySQL. There is obviously pros and cons about using master/master replication. But this is not [...]   [Read more...]
MySQL: How do you set up master-slave replication in MySQL? (CentOS, RHEL, Fedora)
+0 Vote Up -0Vote Down
Before we go into how to set up master-slave replication in MySQL, let us talk about some of the reasons I have set up master-slave replication using MySQL. 1) Offload some of the queries from one server to another and spread the load: One of the biggest advantages to have master-slave set [...]   [Read more...]
Memcached: How do you install memcached? (CentOS 64 bit, Linux, Redhat, Fedora)
+0 Vote Up -0Vote Down
Memcached is a very popular open source object caching server. It was developed to speed up livejournal.com by Danga Interactive.  We use memcached for a lot of our sites.  We use it for different purposes but one main purpose is to cache query results so we don’t have to keep hitting database.  As most [...]   [Read more...]
PHP: How do I install phpsh, interactive shell prompt for php under CentOS or Fedora?
+0 Vote Up -0Vote Down
phpsh requires readline support built into python. It also requires python version 2.4+. You can check which version of python you have installed by typing: python -V Let us download and install readline: wget ftp://ftp.cwru.edu/pub/bash/readline-5.2.tar.gz tar zxf readline-5.2.tar.gz cd readline-5.2 ./configure make install Now let us install python with readline support: wget http://www.python.org/ftp/python/2.5.1/Python-2.5.1.tgz tar zxf Python-2.5.1.tgz cd Python-2.5.1 I had some problems on one of the [...]   [Read more...]
MySQL: How do I import individual table dump files in to MySQL using shell script?
+0 Vote Up -0Vote Down
After I wrote the post: How do I dump all tables in a database into separate files? I got emails from couple people asking how to import the individual table files back in to MySQL. First way to import each sql file created by the post is to import each file individually by [...]   [Read more...]
MySQL: How do I dump all tables in a database into separate files?
+0 Vote Up -0Vote Down
There have been numerous occasions where I needed to make backups of individual tables from selected database. Usually I can achieve this by typing: mysqldump database_name table1 > table1.sql mysqldump database_name table2 > table2.sql This could be very painful if you have 10?s or 100?s of tables. Until today, I never ran into a situation where I [...]   [Read more...]
MySQL Cluster: Changing datamemory requires node restart with ?initial?
+0 Vote Up -0Vote Down
I ran into something with mysql cluster today which boggles my mind.  On http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-config-params-ndbd.html page, it is documented that if we you change datamemory parameter in the config.ini under mysql-cluster like below, you would have to restart nodes to reread the configuration. [NDBD] id=2 HostName=   # the IP of the first data node DataMemory=6G IndexMemory=512M But when I tried the: [...]   [Read more...]
MySQL: Slave lag behind master and data corruption.
+0 Vote Up -0Vote Down
I am reaching out to mysql experts out there! We just inherited a site which uses MySQL master/slave replication. We set up the master and slaves per MySQL documents and did some of our own tweaking in my.cnf to get things working. Things were great when we were testing the site but [...]   [Read more...]
MySQL: How do I reset MySQL to have only the databases when I first installed it? (CentOS, Redhat, Fedora, Linux)
+0 Vote Up -0Vote Down
One of my clients asked me today to make their MySQL installation go back to default database install. Basically they wanted me to get rid of all their databases (in this case test databases) so they can start fresh and go live with only the databases they needed. So here are the steps [...]   [Read more...]
MySQL: InnoDB: ERROR: the age of the last checkpoint is [number]
+0 Vote Up -0Vote Down
One of the mysql database servers I manage started to have issues with doing backups yesterday. mysqldump was running but nothing was happening on the backup side. I started to investigate to see why our full backups were failing. I opened up the mysql error log file (mine is at: /usr/local/mysql/var/hostname.err) [...]   [Read more...]
MySQL: ibdata files do not shrink on database deletion [innodb]
+0 Vote Up -0Vote Down
One very interesting thing I noticed with MySQL was that if you delete a database, ibdata file doesn’t shrink by that much space to minimize disk usage. I deleted the database and checked usage of /usr/local/mysql/var folder and noticed that ibdata file is still the same size. So the problem I face now [...]
MySQL database backup file compression: gzip vs bzip2
+0 Vote Up -0Vote Down
In one of my previous posts: MySQL backups, I talked about using a script for automating backups. I show that we can use gzip to compress backup file to compress and save. Since then, our backup file has been growing meg or two a day which is causing our backup files to get [...]
Windows Vista Ultimate installation (update 2)
+0 Vote Up -0Vote Down

Now since I reinstalled Vista 32bit version and its up and running, lets go through what works what don’t.

  • Daemon Tools (check)
  • Microsoft Office (check)
  • Trillian (check)
  • VMWare Server (CHECK!)
  • Diskeeper 10 (Nope, apparently I have to go download an update for Vista, postponed)
  • Ok now the fun part starts. How to get everything working the way I want to.

    I need to start my Fedora virtual machine so I can get some development/testing done. And a wall I hit. It says that I don’t have permissions to open the file. I am logged in as a user which belongs to Administrators group. So why do I not have permission? Well let me copy it to desktop and see if that works. Voila! it does! Ok.. I will let this one go since I have already wasted my whole weekend getting Vista going.

    OK.. so lets

      [Read more...]
    Windows Vista Ultimate installation (update 1)
    +0 Vote Up -0Vote Down

    While looking through my new Vista, I found out that my cpu is 64bit capable! I was like.. OMFG hell yeah! I whipped out the 64bit Vista DVD and nuked all my hard work of setting up software without even giving it a second thought… About 20 mins later, I was back in my new found love, except this time I dove right into installing software. One by one, I get basic stuff installed in following order:

  • daemon tools (went out and got the upgraded vista version) no problems
  • Microsoft Office 2007 ultimate, uh.. thank god no problems there
  • McAfee VirusScan, ofcourse problems.. knew that from 32 bit version but what the hell.. was worth a try
  • Trillian - another no problem
  • VMWare Server………………………………………….PROBLEMS! Sigh.. they don’t support
  •   [Read more...]
    Windows Vista Ultimate installation
    +0 Vote Up -0Vote Down

    There I was sitting and working and minding my own business when I heard bunch of noise outside of my office. So I took my headphones off to tune in to the conversation. One of the developers has gone out and bought Windows Vista Ultimate edition and was talking about installing it and trying it out. I have always tried out all of the Windows flavors since 3.1 as soon as they came out and even did beta runs for NT 4, XP, 2000, 2003 and even Vista. But for some reason after installing and running beta 2 of Windows Vista, I wasn’t very impressed. But when they were talking about installing Vista, I myself got curious to find out how good/bad is the released version. So I went and got myself a copy of Vista Ultimate as well.

    I didn’t want to destroy my laptop so I decided to install it on my old computer. The specs of that computer are:

    Intel Pentium 3.2 with

      [Read more...]
    What is this ?load average? I keep hearing about?
    +0 Vote Up -0Vote Down

    I have been asked numerous times what does “load average” means in top. If you don’t know what top is and you have access to linux machine, go type top now and see what it shows.

    load average: 2.05, 2.17, 1.93

    Quick answer is: first number (2.05) is 1 minute avg, second number (2.17) is 5 minute avg, third number (1.93) is 15 min avg. Generally system admins look at these #’s to see how is their server is doing. But now you wonder, if this is the #’s you look at, why is there cpu %? Isn’t that computer load also? Ofcourse it is. BUT, meaning of cpu % shown in [ Cpu(s): 14.2% us, 1.7% sy, 0.0% ni, 80.7% id, 3.1% wa, 0.0% hi, 0.3% si, 0.0% st ] actually just means how much % of time was spent doing stuff on cpu. On the other hand, load average takes other things such as how much cpu’s were

      [Read more...]
    Improve page load time and increase server capacity by doing simple DNS and server changes
    +0 Vote Up -0Vote Down


    One of the sites I maintain has been getting more and more traffic everyday. A very good thing for the site, not so good for the solo server which is serving those pages. The site is VERY dynamic with LAMP setup. We only have one server serving our web pages to our users. Since its a dynamic site with PHP and MySQL, it has a lot of load during peak times. Average load time of a page is between 1-2 secs during normal usage, 2-5 secs under average to heavy load.

    During heavy load, we started to see our mysql stop responding to requests which is a big concern for us since we don’t show content if there is no db connection. We had to come with a solution, fast, to prevent this issue from appearing.


    So here are couple things I ended up

      [Read more...]
    Showing entries 1 to 21

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.