this 2all

mysql replication by step

step 1:

on master(primary DB) server replication:

create user:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'slavehost’ IDENTIFIED BY 'slavesecret';

 

*useful:: change password

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
    -> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

 


set in my.ini/ my.conf file Configure server ID and bin log on master:

vim /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-bin.log

 

Restart MySQL Server

/etc/init.d/mysqld restart

 

Flush and Lock all Tables to Stop Writes

FLUSH TABLES WITH READ LOCK;

 

Note the Master Bin Log Position Details for later on the Slave

SHOW MASTER STATUS;

 

 

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |       98 |               |                    |

+------------------+----------+--------------+------------------+

 

Dump the databases that will be replicated

If all will be replicated:

mysqldump –uuser –psecret --all-databases > mysql-all.sql

 

If one or a few will be replicated do the following for each:

mysqldump –uuser –psecret  repdb > mysql-repdb.sql

 

Unlock all the tables so writes can continue

UNLOCK TABLES;


 

step 2:

on slave we eddit config file my.conf /my.ini:

be caful on wamp we got an error with "master-host" (if uoy wamp user remove lines marked in blue):


server-id = 2
master-host = {MASTER_SERVER_IP}
master-port = {MASTER_SERVER_PORT}
master-user = {MASTER_SERVER_USER}
master-password = {MASTER_SERVER_PASSWD}
replicate-do-db=generic4u_new
replicate-ignore-table=generic4u_new.StatisticsTbl
replicate-ignore-table=generic4u_new.OldStatisticsTbl
replicate-ignore-table=generic4u_new.MonthlyStatTbl
replicate-ignore-table=generic4u_new.statistic
replicate-do-db=general
replicate-ignore-table=general.online
replicate-ignore-table=general.ErrorsTbl
replicate-ignore-table=generic4u_new.test

 

 

Restart MySQL Server

/etc/init.d/mysqld restart

 

 

 

run this query on slave mashine:

CHANGE MASTER TO MASTER_HOST = '*ip_address_redacted*', MASTER_USER = 'repl', MASTER_PASSWORD= '*redacted*', MASTER_LOG_FILE= ' MySQL-bin.000001', MASTER_LOG_POS = 4236;

 

more info about set:

CHANGE MASTER TO master_def [, master_def] ...

master_def:
    MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = count
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'

 

 

 

 

 

file and position set from master server file& position

 

after that run slave: SLAVE START

 

 

Start the Slave IO Process

START SLAVE;

 

Check the Slave Status

SHOW SLAVE STATUS

 

 

Incrementing the slave bin log position after fixing a replication problem

SET SQL_SLAVE_SKIP_COUNTER = 1;

SLAVE START;

 


Windows bat file for mysql bd backup


schedule this file in windows "cantrol pannel">schedule update file pathes...

 

@echo off
cls
for /f "tokens=2,3,4 delims=/- " %%x in ("%date%") do set d=%%x%%y%%z
for /f "tokens=1,2,3 delims=:. " %%x in ("%time%") do set t=%%x%%y%%z
rem echo   %d%%t%
echo on
rem move E:SQL*.sql E:BU
"C:Program Filesxamppmysqlbinmysqldump.exe" -u{SERVER_USER} -p{SERVER_PASSWD} --extended-insert --single-transaction --quick --all-databases> E:BU%d%%t%.sql
rem copy E:SQL*.sql E:BU

 

Linux check mysql ping:

 

#!/bin/bash
mysqladmin -u root -h dbserver1.mycorp.com -p 'MyPASSWORD' ping
if [ $? -ne 0 ]; then
  echo "Send email, mysql not running"
else
 echo "Do nothing everything is working"
fi
done

 

 

IPtables firewall add port 3306:

[[email protected] ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 25 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 10000 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

*Usefull

A heartbeat check for the master

Figure 5. A heartbeat check for the master

# in the master CREATE TABLE who ( server_id int ) ENGINE=MyIsam # in each slave CREATE TABLE master_who ( server_id int ) ENGINE=Federated CONNECTION='mysql://username:[email protected]:3306/replica/who';

The leading character in this scenario, as shown in Figure 5, is a recurring event, set to occur every 30 seconds, called check master_conn.

create event check_master_conn on schedule every 30 second enable do call check_master();

It's as simple as setting a rule in crontab (even easier, I daresay). Every 30 seconds, this event calls a stored procedure that tests the status of the master.

create procedure check_master() deterministic begin declare master_dead boolean default false; declare curx cursor for select server_id from replica.master_who; declare continue handler for SQLSTATE 'HY000' set master_dead = true; open curx; # a failure to open the cursor occurs here # setting the master_dead variable to true if (master_dead) then stop slave; change master to master_host='172.16.1.40', master_log_file='mysql-bin.000001', master_log_pos=0; start slave; alter event check_master_conn disable; end if; end

To see if the master is alive, this procedure opens a cursor on master_who (a federated table pointing to a remote one). If the cursor fails to open, a handler enabled on the specific state related to such failure will set a variable master_dead to true. When this happens, the procedure will stop the replication, change the master to the waiting candidate, restart the replication, and finally disable the event, which has fulfilled its role (Figure 6).

Detecting a master failure through federated tables
Figure 6. Detecting a master failure through federated tables

Within 30 seconds of the master failure (or less, if you decide to set a lower interval), the old master is forgotten and the new one has happily taken its place (Figure 7).

The slaves have attached to a new master
Figure 7. Following a master failure, the slaves have attached to a new master