How to set up MySql master slave replication

On master server you must enter in your configuration:

# replication master 
server-id=1
log-bin=www0-bin
expire_logs_days = 30
sync_binlog=1
# end replication

On slave server you must enter in you configuration:

# replication slave 
relay-log=/data/database/mysql/mysqld-relay-bin
server-id=2
# end replication

Be careful to set the unique id for the server-id (must be numeric)!

On master dump the DB in single transaction with current master position (on top of the dump file):

mysqldump -pyourpass --single-transaction --quick --master-data=2 schema_name > db_dump.sql

Using master-data=2 option will add change master to statement on the top of your dump file (commented).

On slave server if replication is running, we must first clean slave state. Log in to mysql console and run:

STOP SLAVE;
RESET SLAVE;

Now we can import the database on slave:

mysql -uroot -p db_name <  db_dump.sql

Get the master position from the top of db_dump.sql:

head -50 db_dump.sql | grep CHANGE

Add some additional parameters to CHANGE MASTER command (master_user, ...):

CHANGE MASTER TO MASTER_LOG_FILE='www0-bin.001519', MASTER_LOG_POS=518850262, master_user='replslave';

Start the slave and check the status:

START SLAVE;
show slave status \G;

If by any chance there are some duplicates, do the following:

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

How to kill long lasting connections in mysql automatically

Although it is possible to use console and kill long lasting connections by hand, or we could set up MYSQL to do the same in config, wouldn't it be nice to have a procedure that will inspect the database for long lasting connections automatically. Well here it is:

drop procedure if exists purge_slow_queries;
drop procedure if exists purge_idle_connections;
drop event if exists auto_purge_slow_queries;
drop event if exists auto_purge_idle_connections;

delimiter //

create procedure purge_idle_connections()
deterministic
begin
    declare done boolean default false;
    declare max_time int default coalesce(@max_kill_time, 200);
    declare pid bigint;
    declare c cursor for
    SELECT id
    FROM information_schema.processlist
    WHERE command in ('Sleep')
        AND time > max_time;
    declare continue handler for not found
        set done = true;
    open c;
    set @q_kill = 'KILL ?';
    prepare q_kill from @q_kill;
    PURGELOOP: loop
        fetch c into pid;
        if done then
            leave PURGELOOP;
        end if;
        set @pid = pid;
        execute q_kill using @pid;
    end loop;
    deallocate prepare q_kill;
end//


create procedure purge_slow_queries()
deterministic
begin
    declare done boolean default false;
    declare max_time int default coalesce(@max_kill_time, 200);
    declare pid bigint;
    declare c cursor for
    SELECT id
    FROM information_schema.processlist
    WHERE state in ('executing')
        AND time > max_time;
    declare continue handler for not found
        set done = true;
    open c;
    set @q_kill = 'KILL ?';
    prepare q_kill from @q_kill;
    PURGELOOP: loop
        fetch c into pid;
        if done then
            leave PURGELOOP;
        end if;
        set @pid = pid;
        execute q_kill using @pid;
    end loop;
    deallocate prepare q_kill;
end//

delimiter ;

create event auto_purge_idle_connections
    on schedule every 10 second
    do call purge_idle_connections();

create event auto_purge_slow_queries
    on schedule every 10 second
    do call purge_slow_queries();

Please note that you must have information_schema.processlist table wich was added in MySQL 5.1.7.