This is more boring parts but you must lay a good foundation.
MySQL Setup:
All the resources must be in place before you configure Pacemaker. The default location for MySQL data is /var/lib/mysql. You will be moving this to the DRBD file system in /data/mysql. The default MySQL configuration is in /etc/my.cnf. I move it to /data/mysql so updates will move with system fail overs. To keep life simple I use sym-links to point from /var/lib/mysql and /etc/my.cnf to their new locations.
Install MySQL of your choise on DB1, DB2 and DB3.
# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm # yum -y install Percona-Server-server-51.x86_64 Percona-Server-client-51.x86_64 \ Percona-Server-shared-51.x86_64 xtrabackup.x86_64 # service mysql start # mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" # mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" # mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" # service mysql stop # chkconfig mysql off
ON DB1, move MySQL data to the DRBD file system.
# df
Filesystem 1K-blocks Used Available Use% Mounted on /dev/md2 11903664 1633608 9655620 15% / /dev/md1 505508 218159 261250 46% /tmp /dev/md0 256586 25967 217371 11% /boot tmpfs 2037412 16348 2021064 1% /dev/shm /dev/drbd1 30477552 176200 28753152 1% /data
On DB1 and DB3
# mkdir /data/mysql # cp -prv /var/lib/mysql/* /data/mysql # mv /var/lib/mysql /var/lib/mysql-empty # ln -s /data/mysql /var/lib/mysql # touch /data/mysql/my.cnf # ln -sf /data/mysql/my.cnf /etc/my.cnf # chown -R mysql.mysql /data/mysql # chmod 644 /data/mysql/my.cnf
On DB2, make it ready for a fail over.
# mv /var/lib/mysql /var/lib/mysql-empty # ln -sf /data/mysql /var/lib/mysql # ln -sf /data/mysql/my.cnf /etc/my.cnf
Configure MySQL (/data/mysql/my.cnf)
I have a documented my.cnf file I start with. You need to change the server-id number on each server. The minimum setting you will need are:
[mysqld] log-bin=mysql-bin server-id=1 innodb_flush_log_at_trx_commit=2 sync_binlog=1
on DB1
# service mysql start
Now we need to add a couple of user before we install the replicator. We also need to set a password for the root user. The root password is blank so just it return.
If you have skip-name-resolve set you will need to substitute the host names for IP addresses.
# mysql -h localhost -u root -p
mysql> DROP USER ''@'db.grennan.com';
mysql> DROP USER ''@'localhost';
mysql> GRANT ALL on *.* TO 'root'@'%' IDENTIFIED by 'P@ssw0rd' with GRANT option;
mysql> GRANT ALL on *.* TO 'root'@'localhost' IDENTIFIED by 'P@ssw0rd' with GRANT option;
mysql> GRANT ALL ON *.* TO 'tungsten'@'127.0.0.1' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'tungsten'@'localhost' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'tungsten'@'db.grennan.com' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'tungsten'@'db2.grennan.com' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'tungsten'@'db3.grennan.com' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;
mysql> flush privileges;
Now that we can access MySQL from any of the servers we need to prepare each as a master and replication. We can fix DB1 and then copy all of the data tables to the other servers.
mysql> reset master; reset slave; mysql> exit # service mysql stop # rsync -rog --delete /data/mysql root@db2:/data # rsync -rog --delete /data/mysql root@db3:/data
Remember we put the my.cnf file in the /data/mysql directory. We need to make sure each has a unique server_id number.
# vi /etc/my.cnf server_id = 1 # ssh db2 # vi /etc/my.cnf server_id = 2 # exit # ssh db3 server_id = 3 # exit
Rather than type the password for MySQL each type we connect. We can also setup a private my.cnf to prevent this. This should connect you to the master (RW) server from each host.
# vi ~/.my.cnf
[Client] user=root password=P@ssw0rd host=db.grennan.com socket=/data/mysql/mysql.sock
# scp .my.cnf db2:. # scp .my.cnf db3:.
Next up, Configuring Heartbeat and making failover work!
The big questions was “Where is Oracle taking MySQL.”