MySQL Installation
Main MySQL server on clondb1
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 '********'
MySQL server on Intel-based VME controllers
To run standalone DAQ on Intel-based VME controller, MySQL server must be local. Following steps were performed to activate it:
- add 'mysql:x:27:' to the /etc/group file if it is not there
- add 'mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash' to the /etc/passwd file if it is not there
- mkdir /var/lib/mysql
- chown mysql:mysql /var/lib/mysql
- mkdir /var/run/mysqld/
- chown mysql:mysql /var/run/mysqld/
- yum install mysql mysql-devel mysql-server
- mysql -h localhost (should be able to enter mysql)
- create /var/lib/mysql/ directory in /root area and place appropriate line into /snapshot/files
- reboot controller, mysql directory must be copied into custom area of the controller
- /etc/init.d/mysqld start
- cd /var/lib/mysql/
- mysqldump --master-data --databases daq_daq daq_clasdev -h clondb1 -u clasrun > mirror.sql
- mysql --force -h localhost -u root -p******** < mirror.sql (mysql -h localhost < mirror.sql)
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, for example:
CHANGE MASTER TO MASTER_HOST='clondb1.jlab.org', MASTER_USER='replica', MASTER_PASSWORD='clonreplica', MASTER_LOG_FILE='clondb1-bin.000790', MASTER_LOG_POS=9815913;
NOTE: MASTER_LOG_FILE and MASTER_LOG_POS are defined in the begining of mirror.sql file, get it from there.
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.