* 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)
