Step by step process for how to set the mk-heartbeat monitoring

Process For setting mk-heartbeat mechanism.

Take a case you have two servers(Master and Slave1 and Slave2)

replication is as Master->Slave

We want to set monitoring for slave.

Steps:

1.Download mk-heartbeat script

from terminal you can download as:

wget http://www.maatkit.org/get/mk-heartbeat

2.create a database Heartbeat and a table heartbeat on the master.It will automatically be replicated on the slave.

create database Heartbeat;

use Heartbeat;

CREATE TABLE heartbeat (

id int NOT NULL PRIMARY KEY,

ts datetime NOT NULL

)Engine = Memory;

Insert one row in the table heartbeat as

INSERT INTO heartbeat (id, ts) VALUES (1, NOW());

If the user has permissions on the Heartbeat we dont need to insert the row manually in the table,The mk-heartbeat will itself insert the row in it.

3.Now create a user heartbeat with some password as

Grant all on Heartbeat.* to heartbeat@'%' identified by 'maatkit';

the user heartbeat should have permissions on the Database Heartbeat, We have created the user heartbeat for our sake otherwise we have to start mk-heartbeat by any other or current user.mk-heartbeat takes the default values from the my.cnf it did not pass them at the script execution time.

4.Now we have to make mk-heartbeat to update the master and monitor the slave.

For master:

mk-heartbeat -D Heartbeat --update -h 10.167.29.58 -u heartbeat -p maatkit --daemonize

where 10.167.29.58 is IP for master

For Slave:

mk-heartbeat -D Heartbeat --monitor -h 10.166.162.231 -u heartbeat -p maatkit --daemonize --log /home/slave.log

where 10.166.162.231 is IP for slave.

It will generate the log in slave.log file generated by above command

Issue tail -f slave.log

It should be working fine.

MySQL replication set up

there are some basic steps to set mysql replication.

wee start from MASTER

1.see that binary logs are enabled.and also set server id in my.cnf file

If not then do as:

log-bin=mysql-bin ##THIS SHOULD BE THE SAME AS HERE

server-id=1

Restart the server

2.in the mysql shell issue a command

mysql > show binary logs;

If the binary logs are enabled it should not return the empty result set.If return empty set you are wrong somewhere pls check that step 1 is completed...??

otherwise you will face problem in replication

If done come to step 3.

3.In mysql shell write

mysql > FLUSH TABLES WITH READ LOCK;

Now just generate the dump of your master as or any other method u want

mysqldump -uroot -proot --all-databases --master-data > dbdump.db

4. mysql > show master status;

note down the binary log and position.

5.now In mysql shell

mysql > UNLOCK TABLES;

6.mysql > CREATE USER 'repl'@ '%' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@ '%' IDENTIFIED BY 'password';

NOW ON THE SLAVE DO SOME WORK

1.IN my.cnf

server-id=2

#and also try to write relay-log

restart the mysql server

2.

mysql > stop slave;

mysql > CHANGE MASTER TO

MASTER_HOST=’X.X.X.X’,

MASTER_USER=’user’,

MASTER_PASSWORD=’password’,

MASTER_PORT=3306,

MASTER_LOG_FILE=’mysql-bin.000001?,

MASTER_LOG_POS=98,

MASTER_CONNECT_RETRY=10;

mysql > start slave ;

mysql > show slave status\G

Now It should be working fine

MySQL startup issue

If you are facing problem in starting the mysql server try to start mysqld with the option –verbose it will tell what is the problem or you can also check the problem by your error log file.

Take a case here

abdul@system:~$ mysqld --verbose --user=mysql –datadir=/var/lib/mysql/ &

[1] 2975

abdul@system:~$ 111013 10:42:35 [Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.

111013 10:42:35 [Warning] Can't create test file /var/lib/mysql/OSS-240.lower-test

111013 10:42:35 [Warning] Can't create test file /var/lib/mysql/OSS-240.lower-test

mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)

111013 10:42:35 [ERROR] Aborting

111013 10:42:35 [Note] mysqld: Shutdown complete

Above wee see that we are getting error code 13.means its permission issue login as root and now try.

root@system:~$mysqld --verbose --user=mysql --datadir=/var/lib/mysql/ &

now mysql is running & working properly.

But one point you should consider in mind is that we have written –user=mysql.

Always try to start your mysql server as mysql user if you start it as root its not a good practice.

Restore your data using mysqlbinlogs

Its a simple article on How you can restore your data using mysql binary logs.

mysql binary logs contain a record of each SQL query executed against the database that changes data since the logs where last flushed (new log file started).MySQL Binary Logs must be enabled by passing the –log-bin option to mysqld.

The log contains queries like UPDATE and DELETE. The log does not record SELECT or SHOW queries for example because they do not change the database.

MySQL Binary Logs are some times confused with the InnoDB binary transaction log. InnoDB uses a binary log to journal changes to the InnoDB table space file(s) as a protection from crashes to protect table space data integrity.

When --log-bin is enabled even InnoDB transactions are written to the binary transaction log used for replication or restores.

At this time take a scenario you has binary logs as follows:

see the location where binary logs are stored.

localhost-bin.000001

localhost-bin.000002

localhost-bin.000003

localhost-bin.index

firstly lock the tables as

LOCK TABLES

then flush the logs as

FLUSH LOGS

At this time take a scenario you has binary logs as follows:

see the location where binary logs are stored.

localhost-bin.000001

localhost-bin.000002

localhost-bin.000003

localhost-bin.000004

localhost-bin.index

after flushing the logs a new bianry log generated as localhost-bin.000004.

Now upto binary log localhost-bin.000003 you can take the complete backup.

take the backup using any method that you want.

This is you complete backup upto the log localhost-bin.000003

Then unlock the tables as

UNLOCK TABLES

Restoring with Binary Logs

To restore from a point in time using binary logs you would:

1. Restore the database from the last SQL Dump completed before the desired recovery point.

2. Use mysqlbinlog to restore to the desired point in timewhere N is the log entry number you want to restore up to.

mysqlbinlog –start-position=N    localhost-bin.000004   | mysql N is the position of binary log. 

How to reduce the ibdata space in Mysql

Dump the Whole Database

This is the easiest method How to reclaim the ibdata space

In fact, this is the method I used to solve the problem. It requires much more space and time but it’s maybe the easiest one. So here it is:

1. Dump all databases by using:

   mysqldump -uroot -proot --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > all-databases.sql

2. Stop the MySQL server;

/etc/init.d/mysql stop

3. Rename or remove the MySQL data directory and create an empty one with the same name and permissions;

i.e In case the datadir is /var/lib/mysql then remove the all content of that.

else remove /var/lib/mysql and create other one with the same permission and same name.

4. Make the appropriate changes in my.cnf.

5. Re-initialize the database with the following command.

   sudo -u root mysql_install_db

6. Start the MySQL server;

/etc/init.d/mysql start

7. Get into the ‘mysql‘ console and type:

   SET FOREIGN_KEY_CHECKS=0;
   SOURCE all-databases.sql;
   SET FOREIGN_KEY_CHECKS=1;

8. Restart the MySQL server.

At this point of time everything should be running fine.

Running Multiple Mysql server

The Complete Installation Process is not listed here.You Can take that from official webpage :dev.mysql.com

Just what we have to do is the start the server (mysqld_safe) with different option's and also this applies for the client(mysql). I have installed the two MySql servers Now i want them to be started with different basedir,datadir,socket etc..



1. I have Installed the One Mysql Server with the apt-get and other as binary.
2. Install the first one Simply as apt-get install mysql-server.
3. The binary Installation is as from
/usr/local/mysql/support-files/$./mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql


Now start the server as running below with options.( mysqld_safe with the various options)

root@administrator-desktop:/etc/mysql# ps -ef | grep mysql
root 6910 6887 0 15:14 pts/5 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf
mysql 7005 6910 0 15:14 pts/5 00:00:00 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/administrator-desktop.err --pid-file=/var/lib/mysql/administrator-desktop.pid --socket=/var/run/mysqld/mysqld.sock --port=3307
root 7020 6887 0 15:16 pts/5 00:00:00 mysql -p
root 7046 6356 0 15:19 ? 00:00:00 /bin/sh ./mysqld_safe --defaults-file=/etc/mysql/my-medium.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --socket=/tmp/mysql.sock
mysql 7150 7046 0 15:19 ? 00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my-medium.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/administrator-desktop.err --pid-file=/usr/local/mysql/data/administrator-desktop.pid --socket=/tmp/mysql.sock --port=3308
root 7220 7193 0 15:20 pts/4 00:00:00 grep mysql



We also need to edit our my.cnf file for the as we have to start the server with the options of --defaults-file as it include the socket and port.
The socket and port for both the cnf file should different and also all other datadir, basedir .......


Now run your mysql client with --port and --socket option.