Category Archives: Enterprise level solutions

MySQL: How do you set up master-slave replication in MySQL? (CentOS, RHEL, Fedora)

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 up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware.

2) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

Ok let us dive into how to setup master-slave replication under MySQL. There are many configuration changes you can do to optimize your MySQL set up. I will just touch on very basic ones to get the replication to work. Here are some assumptions:

Master server ip: 10.0.0.1
Slave server ip: 10.0.0.2
Slave username: slaveuser
Slave pw: slavepw
Your data directory is: /usr/local/mysql/var/

Put the following in your master my.cnf file under [mysqld] section:

# changes made to do master server-id = 1 relay-log = /usr/local/mysql/var/mysql-relay-bin relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index log-error = /usr/local/mysql/var/mysql.err master-info-file = /usr/local/mysql/var/mysql-master.info relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info datadir = /usr/local/mysql/var log-bin = /usr/local/mysql/var/mysql-bin # end master

Copy the following to slave’s my.cnf under [mysqld] section:

# changes made to do slave server-id = 2 relay-log = /usr/local/mysql/var/mysql-relay-bin relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index log-error = /usr/local/mysql/var/mysql.err master-info-file = /usr/local/mysql/var/mysql-master.info relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info datadir = /usr/local/mysql/var # end slave setup

Create user on master:
mysql> grant replication slave on *.* to slaveuser@'10.0.0.2' identified by 'slavepw';

Do a dump of data to move to slave
mysqldump -u root --all-databases --single-transaction --master-data=1 > masterdump.sql

import dump on slave
mysql < masterdump.sql

After dump is imported go in to mysql client by typing mysql. Let us tell the slave which master to connect to and what login/password to use:
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.1', MASTER_USER='slaveuser', MASTER_PASSWORD='slavepw';

Let us start the slave:
mysql> start slave;

You can check the status of the slave by typing
mysql> show slave status\G

The last row tells you how many seconds its behind the master. Donâ€™t worry if it doesnâ€™t say 0, the number should be going down over time until it catches up with master (at that time it will show Seconds_Behind_Master: 0) If it shows NULL, it could be that slave is not started (you can start by typing: start slave) or it could be that it ran into an error (shows up in Last_errno: and Last_error under show slave status\G).

â€”â€”â€”â€”â€”â€”â€”â€”â€”â€”â€”â€”-
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.

Memcached: How do you install memcached? (CentOS 64 bit, Linux, Redhat, Fedora)

27 Replies

[updated 5/16/2010]

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 of the people who work with databases know it is costly to keep hitting database for same information over and over.

When you run the Memcached daemon, it runs and listens on a specific port. One of the things Memcached does lack is security. Memcached will let anybody who can make a connection to its port have full access to all objects. So you would have to run a firewall to block unauthorized access. It is usually wise to do put firewall on it even if you trust everybody on the same network since accidents do happen. That said, let’s get memcached installed!

Let’s get libevent which is required by Memcached:

wget http://monkey.org/~provos/libevent-1.3e.tar.gz
tar zxpfv libevent*
cd libevent*
./configure
make install

Now let’s download the newest Memcached source (at time of update to this post 1.4.5 was the latest)

wget http://memcached.googlecode.com/files/memcached-1.4.5.tar.gz
tar zxpfv memcached*
cd memcached*
./configure
make install

Let’s add Memcached user to run daemon as since we don’t need it to run as root privileges.

adduser memcached

We will start the server to use 48 megs of ram (-m 48), listen on ip 10.0.0.2 (-l 10.0.0.2) and run on port 11211 (-p 11211) as user memcached (-u memcached)

./memcached -u memcached -d -m 48 -l 10.0.0.2 -p 11211

If you get the following error (which you will get if you are doing this under CentOS 64 bit):

./memcached: error while loading shared libraries: libevent-1.3e.so.1: cannot open shared object file: No such file or directory

You can fix this by simply doing this:

ln -s /usr/local/lib/libevent-1.3e.so.1 /lib64/

That is all there is to it. You can see if daemon is running by telneting to the port.

PHP: How do I install phpsh, interactive shell prompt for php under CentOS or Fedora?

4 Replies

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 servers where it would not compile readline support in to python. I was able to compile reading support in to python by:

echo "readline readline.c -lreadline -ltermcap" >> Modules/Setup.local

Now let us continue with python installation.

./configure --prefix=/usr/local/python-2.5.1 --enable-readline make -i install

If you do not do “make -i install”, install may fail with following error (-i means ignore any errors):
Compiling /usr/lib/python2.5/zipfile.py ... make: *** [libinstall] Error 1

If you scroll up, you will find following error which seems to be the root cause:

Compiling /usr/lib/python2.5/test/test_multibytecodec.py ... Sorry: UnicodeError: ("\\N escapes not supported (can't load unicodedata module)",)

Once you do python install with make -i install, that library (unicodedata.so which is not built until later stage of build process) gets installed. If you want, you can type make install once again (without ignoring errors) and it will complete without errors.

Once you have python installed, you would want to use the new version. I like to keep a backup of old files in case I have to use older version for any reason. Run following which creates symbolic links and makes backups of current files:

for binaries in `find /usr/local/python-2.5.1/bin/*` ; do mv /usr/bin/`basename ${binaries}` /usr/bin/`basename ${binaries}`.bak ln -s ${binaries} /usr/bin/`basename ${binaries}` done

Now let us get phpsh and try it out:

wget http://www.phpsh.org/phpsh-latest.tgz tar zxf phpsh-latest.tgz cd ../phpsh chmod +x phpsh ./phpsh

At this point you should be at the shell: php>

Following is a snippet from README file which comes with phpsh. You should take a look since it has more details on how to use phpsh:

Type php commands and they will be evaluated each time you hit enter. Ex: php> $msg = "hello world" Put = at the beginning of a line as syntactic sugar for return. Ex: php> = 2 + 2 If you end a line with a backlash (\), you can enter multi-line input. For example, php> print "like \ ... this" like this php>

There we go. Now you have a great interactive php shell prompt. Note that there is interactive mode built into php as well. I personally do not like it as much but you can try it out for yourself by typing:

php -a Interactive mode enabled echo "Hello world"; Hello world

MySQL: How do I import individual table dump files in to MySQL using shell script?

12 Replies

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 typing:mysql db_name < table1.sql This will work as long as you are only importing few files. But if you need to import all of the files in the directory, which could be in 100’s, this method does not scale well. To achieve this I wrote a shell script which does the work for me. Of course, there are other ways to do this and I am only showing you one way of doing it. This works for me so here it is:

#!/bin/bash db=$1 if [ "$db" = "" ]; then echo "Usage: $0 db_name" exit 1 fi mkdir done clear for sql_file in *.sql; do echo "Importing $sql_file"; mysql $db< $sql_file; mv $sql_file done; done

MySQL: How do I dump all tables in a database into separate files?

20 Replies

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 had to deal with dumping more than few tables at a time. Today I had to do a dump of 181 tables. I was not going to sit there and type in that command with 181 table names. It is not just time consuming but it is also stupid. So I wrote this script to help me with this task. We still use mysqldump command as described above, except we do it programmatically to make it easier on us:

#!/bin/bash db=$1 if [ "$db" = "" ]; then echo "Usage: $0 db_name" exit 1 fi mkdir $$ cd $$ clear for table in `mysql $db -e 'show tables' | egrep -v 'Tables_in_' `; do echo "Dumping $table" mysqldump --opt -Q $db $table > $table.sql done if [ "$table" = "" ]; then echo "No tables found in db: $db" fi

You can also compress your files by adding bzip2, zip or any other compression commands after mysqldump command. Here is the same script with bzip2 command added:
#!/bin/bash db=$1 if [ "$db" = "" ]; then echo "Usage: $0 db_name" exit 1 fi mkdir $$ cd $$ clear for table in `mysql $db -e 'show tables' | egrep -v 'Tables_in_' `; do echo "Dumping $table" mysqldump --opt -Q $db $table > $table.sql bzip2 $table.sql done if [ "$table" = "" ]; then echo "No tables found in db: $db" fi

I do not recommend doing compression on a production server since most compression program put descent amount of load on the server. Also note that this will delay your dump considerably. You may also want to use different parameters for running mysqldump. Type man mysqldump in your shell to read more.

Technology: Learn and Share

Enterprise level solutions, LAMP, Linux, Apache, MySQL, PHP, Perl, Windows, Cache, Optimization