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

  gunzip mysql-4.1.20.tar.gz
 tar xvf mysql-4.1.20.tar
 mv mysql-4.1.20 mysql_SunOS_sun4u
 cd mysql_SunOS_sun4u
 ./configure --prefix=/usr
 gmake
 repeat for SunOS_i86pc, SunOS_sun4u, Linux_ppc

Login into every CLON cluster machine as 'root', goto /usr/local/clas/mysql_XXXXX_YYY directory and type 'gmake install'. It will install everything into /usr/bin, /usr/lib and /usr/include/mysql (same directories as on RHEL4)

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

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=3;
 mysql> show variables like 'expire_%';
   +------------------+-------+
   | Variable_name    | Value |
   +------------------+-------+
   | expire_logs_days | 3     |
   +------------------+-------+
   1 row in set (0.00 sec)

If necessary, stop and restart the server

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 -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='[recorded name or empty if none]',
 MASTER_LOG_POS=[recorded number or 4 if none];

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 -uroot -p******** < mirror.sql
 mysql> START SLAVE;

OPTIONS ON SETUP

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.