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 '********'
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).
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.
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 (?? not necessary ?? will be unlocked as 'mysqldump' is finished ??)
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;
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.