The Ji Village News

季庄新闻–Haidong Ji’s Blog

A couple of weeks ago, a friend asked about replication on MySQL 4.1.7. I’ve worked with replication in the past, just a quick and dirty job on MySQL 5, and soon forgot about it. This time, I wanted to do it on MySQL 4, and make sure I take good notes for my own benefit. If it can help somebody else, all the better. The official documentation is here. It took a little time to wade through it.

The process below is used to replicate all databases on the master to the slave(s). I will talk about replicating only certain selected databases in a future post.

1. At the master server, check if binary logging is on. Use show variables like ‘%bin%’ to check if binary logging is on or not. If it is not on, turn it on by adding log-bin under [mysqld] section in /etc/my.cnf. Also remember to add server-id=Number, where the number is a unique number for all MySQL servers within the network. It looks something like this:

[mysqld]
log-bin=mysql-bin
server-id=1

If mysql restart is needed, do:
shell> mysqladmin -u root shutdown
shell> mysqld_safe &

2. Set up an account on the master server that the slave server can use to connect. This account must be given the REPLICATION SLAVE privilege. If the account is used only for replication (which is recommended), you don’t need to grant any additional privileges.

mysql> GRANT REPLICATION SLAVE ON *.*
-> TO ‘repl’@'%’ IDENTIFIED BY ’slavepass’;

3. Issue ‘flush tables with read lock’. While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and position on the master. Take note of those 2 values, ’cause they will be used later:

mysql > SHOW MASTER STATUS;
+—————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| mysql-bin.003 | 73 | test | manual,mysql |
+—————+———-+————–+——————+

4. I used mysqldump, documented here, to dump out the database I am interested in. Then do ;

mysql> UNLOCK TABLES;

5. Stop the slave server, add the following lines to its my.cnf file:

[mysqld]
server-id=slave_id

The slave_id value, like the master_id value, must be a positive integer value from 1 to 232 – 1. In addition, it is necessary that the ID of the slave be different from the ID of the master. For example:

[mysqld]
server-id=2

When setting up multiple slaves, each one must have a unique server-id value that differs from that of the master and from each of the other slaves. Think of server-id values as something similar to IP addresses: These IDs uniquely identify each server instance in the community of replication partners.

Now start MySQL on the slave;

6. Create the database with the same name of the database where the dump was taken,

shell> mysql -u root -p -D MyDbNameHere < dump_file.sql

7. Execute the following statement on the slave, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
-> MASTER_HOST=’master_host_name’,
-> MASTER_USER=’replication_user_name’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;

8. Start the slave threads:

mysql> START SLAVE;

It should work. One thing that got me during my process was that I had firewall enabled on both the master and slave, therefore I had to open those ports up for MySQL communications. From this point on, EVERYTHING will be replicated. Notes on replicating only selected databases to follow…

Here is a script I wrote to generate a script to drop all indexes that are not primary keys. This works for Sql Server 2005. The main thing it uses is sp_MSForEachTable, an undocumented stored procedure. Run the generated script, and you will be good to go.

This is especially useful before a bulk load or bulk insert, where you load a lot of data into Sql Server. It is best and faster to drop all those indexes, load the data, and then recreate them.

I am assuming that you have saved the index creation scripts in a separate file, so they can be easily recreated. If you don’t want to bother, uncomment the commented line, and all indexes will be dropped, except primary keys.

Enjoy!

declare @RETURN_VALUE int
declare @command1 nvarchar(2000)
set @command1 = 'DECLARE @indexName NVARCHAR(128)'
set @command1 = @command1 + ' DECLARE @dropIndexSql NVARCHAR(4000)'
set @command1 = @command1 + ' DECLARE tableIndexes CURSOR FAST_FORWARD FOR'
set @command1 = @command1 + ' SELECT name FROM sys.indexes'
set @command1 = @command1 + ' WHERE object_id = OBJECT_ID(''?'') AND index_id > 0 AND index_id < 255 AND is_primary_key = 0'
set @command1 = @command1 + ' ORDER BY index_id DESC'
set @command1 = @command1 + ' OPEN tableIndexes FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' WHILE @@fetch_status = 0'
set @command1 = @command1 + ' BEGIN'
set @command1 = @command1 + ' SET @dropIndexSql = N''DROP INDEX ?.['' + @indexName + '']'''
--set @command1 = @command1 + ' EXEC sp_executesql @dropIndexSql'
set @command1 = @command1 + ' print @dropIndexSql'
set @command1 = @command1 + ' FETCH NEXT FROM tableIndexes INTO @indexName'
set @command1 = @command1 + ' END'
set @command1 = @command1 + ' CLOSE tableIndexes'
set @command1 = @command1 + ' DEALLOCATE tableIndexes'
Print '-----------------------------------------'
exec @RETURN_VALUE = sp_MSforeachtable @command1=@command1

And here is a script to confirm the name and number of indexes that will be dropped by the above script:

SELECT name, object_name(object_id) TableName FROM sys.indexes
WHERE index_id > 0 AND index_id < 255 AND is_primary_key = 0
and object_name(object_id) not like 'sys%' and object_name(object_id) not like 'queue%'

After my last failed Oracle 10g Express install due to insufficient memory, I purchased some memory from eBay, and started it over.

This time, the first thing the installer noticed was that the swap file is not big enough. I fixed that by doing a total re-install of Fedora Core 2. Yeah, I know that was kind of lame, but I got a few machines with me, so I didn’t waste time waiting for it.

Started again, this time the install went through, but I got some error messages at the end. The message was “groupadd: unable to lock group file”. The problem was that there were passwd.lock and gshadow.lock files under /etc. I then removed those files.

I tried to reinstall the RPM package. The message I got back was that it was already setup. I then ran:

rpm -e oracle-xe-10.2.0.1-1.0.i386.rpm

to remove it.

Finally, I was able to install it correctly.

It was a good exercise. I’ve done some Oracle database administration stuff, but never installation and setup stuff. I would like to bring my Oracle skill to the next level, so this is a good way to get started. 11g will come out within a week or so, but hopefully the skills I learned here can be transferred. I will give 11g a run when it comes out.

A few things learned:

1. yum is worth looking into. It is probably a better way to install or update dependencies than compiling binary or doing manual rpm install;
2. Got to be careful when typing. During profile setup, when I tried to put . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh into /etc/profile, I missed the blank between the dot and forward slash. This shell script sets up $ORACLE_HOME and $ORACLE_SID for you;
3. Here is a good site for 10g Express setup on Linux.

I downloaded and installed Katmai, code name for Sql Server 2008, a few weeks ago. My friend Scott Whigham has a great post on it. Here are some of my own observations:

1. The installation process is very similar to that of Sql Server 2005. Here is one screen shot of feature selections:

2. If Advanced button is clicked, you will see the screen shot below:

Interestingly, you will see the legacy DTS runtime component is still there. I don’t know if this is an oversight, or if Microsoft decides to carry the legacy DTS runtime to Sql Server 2008. If I have to guess, my bet would be that DTS runtime would be dropped in the fully baked version;

3. The UI for Sql Server Management Studio is pretty much the same as that of Sql Server 2005. I am a command line kind of guy, so I typed sqlwb to run it, as the executable file for Management Studio in 2005 is called sqlwb.exe. It didn’t work. By the way, Management Studio was called Sql Server Work Bench, hence sqlwb, when Sql Server 2005 was in beta. That name was quickly dropped, I wonder if that has anything to do with Oracle’s Migration Workbench.

Anyway, the new executable name for Management Studio is now ssms.exe, finally consistent with its official name.

I will play with some features when I get a chance. I may even write about it, time permitting…

Below is something I wrote a couple of months ago for my son, to help him prepare for his Japanese speech. The teacher presented a list of interview questions to me, which I replied in writing. I was organizing my files and came across it. I made slight editing to the original.

Why do I want to come to the US?

I don’t think I had real clear purpose when I decided to come to the US. Part of it is that a lot of people wants it. Part of it is America is believed to be such a powerful and prosperous country.

My brother, your uncle, came to the US first. He wrote back that America was really great, and encouraged me to come. I don’t think I would ever get here without his push and help. My life would have been much different then.

However, knowing what I know now, I wouldn’t necessarily encourage people to come and stay in the US, blindly believe that they would be happier. There are a lot of misconceptions about the US. In my opinion, some of those misconceptions were caused by America’s own cultural and media export, since it is in such a dominant position. At the same time, a lot of immigrants, legal or illegal, don’t always give an accurate account of life in the US to people back home, mostly out of a false pride, worrying of being viewed as a loser back home. If one combines the false pride with the inability or unwillingness to learn and adopt, that is a recipe for an American nightmare. That is true for living in any country, even living in different region within one’s own country. (I should add that this applies to anybody, including Americans living abroad. Benjamin Ross has a great entry on this. The comments there are also very interesting.)

Having said that, I think it is great for people to visit foreign countries when they can. It is a great way to learn about, not only the foreign culture, but, maybe paradoxically, you own culture and yourself. It will be even better if one could live in the foreign country for a while. I highly, highly recommend it.

However, don’t even try to deny or suppress your own heritage and identity, though, just to be able to “fit in”. That is another great recipe for a miserable life.

What did you do when you missed Chinese food?

I go to a Chinese restaurant in Chinatown. I am a lousy cook, therefore I cannot cook to satisfy my own cravings. I do make some futile attempts at it, though. Gradually over the years, I first learned to tolerate, and now enjoy cuisine from different culture and countries.

What else did you miss?

I miss my family and friends, my hometown, my university and university city. The people I miss most are my parents and my nephew and niece, your cousins in China. Like one report about Yao Ming I read awhile ago, I miss the familiarity associated with the environment where I grew up. The place I miss most is my university town, Xiamen City in Fujian province.

Were there any other funny stories?

I have mentioned the Christmas party story with my funny dress. Another story I mentioned was the poker game. Playing cards is translated into Chinese as pu ke pai (扑克牌). The first 2 Chinese characters sound very much like Poker, and the last character means card(s). This Chinese translation may have gotten its inspiration from the game poker, but I didn’t know that. So one Thanksgiving at friend’s house, I proposed that we play some poker. What I meant was that we play some card games, not the game poker where people bet money on it, which was the way my friends took. My friends are nice people and not the kind that would gamble with a poker game. So it made an awkward moment, which is funny in retrospect.

As I mentioned before, it is great to be different, as long as you have good attitude and good intentions. Don’t isolate yourself because you are different. Engage with people, appreciate and learn from their unique perspectives, and we all benefit as a result.

Skateboarding

Fishing at Mom’s hometown in Sweden

We did the fish showing off thing. Not too sure how I feel about it now. Benjamin caught this big one. He didn’t want to hold it. The carp (?) was then quickly released.

This bluegill is one of his first catches ever. He caught it at Morton Arboretum in Lisle, Illinois. That was also my first ever fishing experience.

Man, how time flies. After summer my son will go to third grade. Below is a picture of the two of us in Sweden, before the start of first grade.