MySQL Installation

clondb1 is the primary MYSQL machine for CLAS Online databases, it is running MYSQL server and has database on its local disk in /database. All other CLON cluster machines have MYSQL installed but server should not be run there

Installation for RHEL4 machines with satelite service (type following as 'root'):

 up2date mysql
 up2date mysql-server

version 4.1.20 was obtained August 19, 2006; newer version can be installed if 'up2date' will be used in future; repeat that procedure on all Linux_i686 and Linux_x86_64 machines.

Installation for all other machines: Get tarball 'mysql-4.1.20.tar.gz' from http://www.mysql.com/ and place it to /usr/local/downloads on CLON fileserver. Then do following:

cd /usr/local/src
cp ../downloads/mysql-4.1.20.tar.gz .
gunzip mysql-4.1.20.tar.gz
tar xvf mysql-4.1.20.tar
rm mysql-4.1.20.tar
cd mysql-4.1.20

Linux:

./configure --prefix=/usr --without-server

Solaris:

bash
CC=cc CFLAGS="-xO5 -KPIC -mt" CXX=CC CXXFLAGS="-xO5 -KPIC -mt" ./configure --prefix=/usr --without-server
exit

gmake
gmake install

Repeat for all SunOS_i86pc, SunOS_sun4u, Linux_ppc machines. Login as 'root' and do:

cd /usr/local/src/mysql-4.1.20
gmake install

It will install everything into /usr/bin, /usr/lib and /usr/include/mysql (same directories as on RHEL4).

NOTE: you may want to create symbolic links from /usr/lib to /usr/lib/mysql area for the libraries if it was not done by installation procedure:

cd /usr/lib
ln -s mysql/libmysqlclient.a libmysqlclient.a
ln -s mysql/libmysqlclient.so libmysqlclient.so
ln -s mysql/libmysqlclient.so.14 libmysqlclient.so.14

Clondb1 preparations:

Login as 'root' and execute following commands:

 id mysql

you should see something like this:

 uid=27(mysql) gid=27(mysql) groups=27(mysql)

If 'mysql' user or group does not exist, create it. Specify desired database directory in '/etc/my.cnf':

 ###datadir=/var/lib/mysql
 datadir=/database

Make sure '/var/lib/mysql' directory has appropriate ownership (in our case basedir=/var/lib):

 drwxr-xr-x   2 mysql     mysql    4096 Jun  1 23:42 mysql

Same about /database:

 chown mysql:mysql /database

Modify '/etc/my.cnf' if neseccary.

Starting mysql server:

 cd /var/lib/mysql
 mysql_install_db --user=mysql

some useful instructions will be printed:

 To start mysqld at boot time you have to copy support-files/mysql.server
 to the right place for your system
 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
 To do so, start the server, then issue the following commands:
 /usr/bin/mysqladmin -u root password 'new-password'
 /usr/bin/mysqladmin -u root -h clondb1.jlab.org password 'new-password'
 See the manual for more instructions.
 You can start the MySQL daemon with:
 cd /usr ; /usr/bin/mysqld_safe &
 You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
 cd sql-bench ; perl run-all-tests
 Please report any problems with the /usr/bin/mysqlbug script!
 The latest information about MySQL is available on the web at
 http://www.mysql.com
 Support MySQL by buying support/licenses at http://shop.mysql.com

Now start mysql deamon:

 /etc/init.d/mysqld start

NOTE: it did not worked, and wierd thing happened: when I tried to modify /etc/init.d/mysqld it started to work right after I touched it; no real changes were applied, just file date was changed ... It can be started as well as following: (cd /usr; /usr/bin/mysqld_safe &)

Make sure 'mysqld' is running:

 ps -ef | grep mysql
   root     23028 21947  0 20:54 pts/2    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf
      --pid-file=/var/run/mysqld/mysqld.pid
   mysql    23053 23028  0 20:54 pts/2    00:00:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf 
      --basedir=/usr --datadir=/database --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking
      --socket=/var/lib/mysql/mysql.sock

Set mysql root password:

 /usr/bin/mysqladmin -u root password '********'

Dumping

Dump is running as a cron job from clascron account on clondb1. See /database/dumpdb script. It accually locks table-by-table and dumps them, so it is not a whole database snapshot. Maybe better to run it on slave server locking whole database ...

Setting up replica

clondb2 is a slave MySQL replica for the master running on clondb1 machine.

Set up an account on the master server clondb1 that the slave can use to make its connection. You need to grant the account REPLICATION SLAVE privilege:

GRANT REPLICATION SLAVE ON *.* TO replica@clondb2.jlab.org IDENTIFIED BY 'clonreplica';

Take a snapshot of the data on the master server:

a) Lock all the tables on the master (flush to disk and lock):

FLUSH TABLES WITH READ LOCK;

b) Run

SHOW MASTER STATUS;

and note the File and Position (if blank, don't worry). Accually it can be found in mirror.sql file (see below).

IMPORTANT: do not exit MySQL session !!! LEAVE the MySQL client that you used for that running. This will prevent updates while you're copying to your mirror.

NOTE: when mysqldump started (see below) database will be locked, and it stays locked until dump is finished; when it is finished database will be unlocked; it seems not necessary to do (a),(b) and (d).

c) From another window, take a dump of the data on the Master server:

mysqldump --master-data --all-databases -uroot -p > mirror.sql

d) UNLOCK TABLES;

5. Check that the Master server has an ID set and is running with the log-bin option. This should be in the my.cnf file - for example

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

or just (as it is now on clondb1):

log-bin
server-id=1

Server ids (Master and Slave) must all be unique positive integers.

NOTE: to cleanup log-bin files do following:

 mysql> purge master logs before '2007-01-25 22:00:00';
 mysql> purge master logs before '2007-01-25';

or ask mysql to cleanup old files:

 mysql> SET GLOBAL expire_logs_days=10;
 mysql> show variables like 'expire_%';
   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | expire_logs_days | 10    |
   +------------------+-------+
   1 row in set (0.00 sec)

To make that setting permanent, add following to /etc/my.cfg in '[mysqld]' section:

expire_logs_days=10

It is also useful to add

set-variable = open_files_limit=1040400

Restart the server:

/etc/init.d/mysqld restart

6. Stop the slave server and configure its my.cnf file - for example

[mysqld]
server-id=2

Restart the slave server

7. Load the data onto the slave server; transfer the mirror.sql file created on master machine, then run

 mysql --force -hclondb2 -uroot -p******** < mirror.sql

Restart the slave server if you've transferred different account information over!

 /etc/init.d/mysqld restart

8. Tell the slave server to act as a slave server:

CHANGE MASTER TO MASTER_HOST='clondb1.jlab.org', MASTER_USER='replica', MASTER_PASSWORD='clonreplica'MASTER_LOG_FILE='clondb1-bin.000790', MASTER_LOG_POS=9815913;

9. Start the slave threads

START SLAVE;

That should be it ... your slave will contact your master for any updates since the backup copy was taken.

10. SHOW SLAVE STATUS;

NOTE: if you want to load data into slave server again, stop slave first, then load data and start slave again (it maybe necessary if slave server lost communication to the master):

 mysql> STOP SLAVE;
 unix> mysql --force -hclondb2 -uroot -p******** < mirror.sql
 mysql> START SLAVE;

OPTIONS ON SETUP

To reset slave (it will delete all log files) do following:

stop slave;
reset slave;
start slave;

Some alternatives:

a) If you want to use LOAD TABLE FROM MASTER or LOAD DATA FROM MASTER commands later, you need to give your slave SUPER and RELOAD global privileges, and also SELECT privilege over any tables that you want to load.

b) The data can also be transferred, more efficiently, in binary. In summary, you use tar, zip or similar software to squish the data directory in MySQL's data directory (extra steps needed if you're using InnoDb as well) and transfer those files - data files, log files, .frm definition files - and unpack them.

c) If you're going to be running different user accounts / passwords etc. on the slave server, or it's a significantly more recent version of MySQL, then do NOT transfer the database called mysql.

d) If you're copying to a slave that has previously functioned as a slave, restart it with the --skip-slave-start option in order to avoid it contacting its old master right away.

e) If you configure your slave server as a master too, then it can take over for data changes in the event of a prolonged failure of the master, and can feed data through to second layer slaves. With a properly configured network with homogeneous systems, the old master can be brought back in later as a slave to automatically catch up.

WHAT HAPPENS WHEN IT'S RUNNING?

The Master server keeps a binary log file of database updates for passing to the slaves as required.

The Slave server keeps data files called master.info and relay-log.info to keep track of how it's doing in processing the master log. It maintains a connection to the Master so that updates are virtually instant.

If a slave server looses connection to the master, it will retry connecting periodically (default 60 seconds, --master-connect-retry option to change), and when it gets back in touch it will catch up.

Master servers do not maintain a list of known slaves, let alone a note of whether or not they're up to date.

You are STRONGLY advised not to edit any of these files directly, but rather to use commands such as CHANGE MASTER TO ..

See also MySQL - our FAQ

Full manual

Complete MYSQL manual can be found at http://dev.mysql.com/doc/refman/4.1/en/index.html.

Problems observed

1. After up2date on August 28, 2007 mysqld did not started, error message in file /var/log/mysqld.log was:

070829 12:16:58  mysqld started
070829 12:16:58 [Warning] Can't create test file /database/clondb1.lower-test
/usr/libexec/mysqld: Can't change dir to '/database/' (Errcode: 13)
070829 12:16:58 [ERROR] Aborting
070829 12:16:58 [Note] /usr/libexec/mysqld: Shutdown complete
070829 12:16:58  mysqld ended

Found on web following recommendation:

its because rhel 4 has selinux installed with it You need to disable it
try
setsebool -P mysqld_disable_trans=1

Also:

the newer version of linux has selinux installed.
If that is it uses user mysqld_t which doesnt have permissions there.
You can either shut selinux off totally - use the command i said or you
will need to reflag files.

After running command setsebool -P mysqld_disable_trans=1 as root mysqld started fine. It is started fine on reboot as well.