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

I have thought about putting up forums on my blog for LONG time and even thought about writing my own. But after playing with phpBB3 for a while on my other site (http://totalplaystation.com/forum), I have decided to use it instead. Let’s just face it; I don’t have the time or resources to write all of the functionality phpbb3 provides. Isn’t it a big reason why we all love open source?

If any of the phpbb guys read this, Thank you and keep up the good work!

Check out the forums: http://crazytoon.com/forum/ and feel free to post  questions, comments, suggestions, rants, etc.

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'); …

In Linux, there is no command which will rename a user account. If you make a mistake creating a user account, user changes their name or if user does not like his user name, there is no real easy way of going and making the change. Only thing I know you can do is to go through some files and rename user manually. Let us say that we have a user who is named joe and we want to rename him to john.

Note: you must be logged in as root to do following.

vi /etc/passwd
find joe and change it to john, save/exit

vi /etc/group
find joe and change it to john, save/exit

vi /etc/shadow
find joe and change it to john. This file is read only and you have to force overwrite it. In vi it is :w! once saved, quit.

cd /home
mv joe john

And that should do the trick.

[Edited] Right after I posted this post, I was contacted and was told to look at utility called usermod. Read …

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 …

Buddy of mine asked me a question over chat today: “how do I show my machines’ IP at login prompt with out logging in?” He is referring to his Virtual Machine in this case. He does not want to have to log in to the server to see what ip it has (since its on dhcp) for him to ssh in or hit it from the browser. I could have answered him with a simple how to but what is the fun in that? So I decided to give some background on how login prompts are done and show what can be done.

When Linux server boots up, it calls a program called mingetty. This program creates that infamous login prompt as show in a screenshot:

You can see how server calls the mingetty program by looking at /etc/inittab. You will see a block like below:

# Run gettys in standard runlevels
1:2345:respawn:/sbin/mingetty tty1
2:2345:respawn:/sbin/mingetty tty2
3:2345:respawn:/sbin/mingetty tty3
…

Welcome to Tip #1 in MySQL Replication Series. In this tip we will go over what to do when you only want to replicate certain data to slave(s). Most general way to tell what is replicated to which slave is to include following configuration directive in my.cnf file depending on your environment and your goals. We will start with slave server side options since you have more flexibility on slave on what to replicate and what not to.

Slave server options:

• replicate-do-db = dbname (or) replicate-do-db = dbname1, dbname2, …, dbnameN
  This option is used on slave server to tell the server to only replicate dbname db on this particular host. You would want to use this if you have a master which is replicating to multiple slaves and each slave may contain different database for read performance reasons.
• replicate-ignore-db = dbname (or) …

Setting up replication is not hard and can be done by following simple directions. You can learn setting up two type of replication MySQL offers here: master-master replication and master-slave replication. Once you have replication set up, you can start playing with it without doing any more changes to the configuration. But let us face it, using replication in production environment and for sites which are either going live or are live, requires deeper knowledge of how replication can be used and what it can (or can not) do. We will start this “MySQL Replication Series” with briefly going over how MySQL replication works. Later in the series I will be posting some tips on …

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

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 [...] …