The Full Monty- Part 3

October 21, 2011

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!

 

 

posted in Commentary by Mark Grennan

Follow comments via the RSS Feed | Leave a comment | Trackback URL

1 Comment to "The Full Monty- Part 3"

  1. parwita wrote:

    is the full monty part 4 not published yet?

    it’d be great to know the HA failover work.
    thanks.

Leave Your Comment

You must be logged in to post a comment.

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.