Replicacion MySQL

* INSTALAMOS EL MYSQL EN AMBOS SERVIDORES

# yum -y install mysql mysql-server

* EN EL SERVIDOR 1

[root@servidor1 ~]# vim /etc/my.cnf
------------------------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#Insertamos un ID
server_id = 1   
#Lugar donde se almacenara los logs de la replica
log-bin = /var/lib/mysql/bin/mysql-bin

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
------------------------------------
[root@servidor1 ~]# mkdir /var/lib/mysql/bin
[root@servidor1 ~]# chown mysql.mysql /var/lib/mysql/bin/
[root@servidor1 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
* CREAREMOS UNA BD PARA PROBAR
[root@servidor1 ~]# mysql
mysql> create database db_site1_pro;
Query OK, 1 row affected (0.00 sec)

mysql> use db_site1_pro;
Database changed
mysql> create table user(id int(4));
Query OK, 0 rows affected (0.03 sec)

mysql> insert user(id)values(4);
Query OK, 1 row affected (0.00 sec)

mysql> create database db_site2_pro;
Query OK, 1 row affected (0.00 sec)

mysql> quit
Bye
* REVISAMOS LOS LOGS DE LA REPLICA, LA TERMINACION PUEDE CAMBIAR
[root@servidor1 ~]# tail -f /var/lib/mysql/bin/mysql-bin.000001
@db_site2_procreate database db_site2_pro
* TENEMOS LOGS, POR LO QUE HASTA AHORA TODO BIEN

* EN EL SERVIDOR 2

[root@server2 ~]# vim /etc/my.cnf
---------------------------------------------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#Establecimiento ID
server-id=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
---------------------------------------------------------
[root@server2 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

* EN EL SERVIDOR 1

* CREAMOS EL USUARIO DE REPLICACION Y ASIGNAMOS PERMISO
[root@servidor1 ~]# mysql
mysql> CREATE USER 'usr_replica'@'%' IDENTIFIED BY 'Pa$$w0rd';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'usr_replica'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

* BLOQUEAMOS LA BD
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.06 sec)

* OBTENER LAS COORDENADAS DEL LOG DEL MASTER
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      812 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

* AHORA HACEMOS UN BACKUP (Asegurar que user mysql pueda escribir)
[root@servidor1 ~]# cd /var/lib/mysql/ && mysqldump --all-databases --master-data > dbdump.db

* EN EL SERVIDOR 2

[root@server2 ~]# mysql < /var/lib/mysql/dbdump.db
[root@server2 ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bin                |
| db_site1_pro       |
| db_site2_pro       |
| mysql              |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mysql> quit
Bye
* LE DECIMOS QUIEN SERA EL MASTER y ACTIVAMOS LA REPLICA
mysql> slave stop
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.232.131',
    -> MASTER_USER='usr_replica',
    -> MASTER_PASSWORD='Pa$$w0rd',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=812;
Query OK, 0 rows affected (0.00 sec)

mysql> slave start
    -> ;
Query OK, 0 rows affected (0.02 sec)

* DESBLOQUEAMOS EN SERVIDOR 1
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.02 sec)

* AHORA DEBEMOS PROBAR QUE TODO VA BIEN

*EN EL SERVER 1
mysql> insert into db_site1_pro.user(id) values (12);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db_site1_pro.user(id) values (13);
Query OK, 1 row affected (0.00 sec)

mysql> insert into db_site1_pro.user(id) values (14);
Query OK, 1 row affected (0.01 sec)

* VEMOS LOS LOGS
[root@servidor1 ~]# tail -f /var/lib/mysql/bin/mysql-bin.000001
@db_site1_proinsert into user(id) values (12)çRg¸
@db_site1_proinsert into user(id) values (13)ÁçRg
@db_site1_proinsert into user(id) values (14).

* EN EL SERVER2 
mysql> select * from db_site1_pro.user;
+------+
| id   |
+------+
|    4 |
|    5 |
|    9 |
|   10 |
|   11 |
|   12 |  <----VALORES QUE INGREAMOS
|   13 |  <----Y ESTAN REPLICANDO
|   14 |  <----
+------+
8 rows in set (0.00 sec)