ESTRUCTURA
SERVIDOR MASTER
MYSQLSERVERS
IP 10.0.4.16
REDHAT 7
SERVIDORES SLAVE
MYSQLVIRTUAL MYSQLVIRTUAL2
IP 10.0.4.23 IP 12.26.10.3
UBUNTU 14.04 REDHAT 7
-------------------------------------------------------------------------------------------------------------------------
INTRODUCCION
Demos configurar un Servidor como MASTER (MysqlServers) y otro como SLAVE (MysqlVirtual en TECO, MysqlVirtual2 en CENTRAL).
PASOS PREVIOS PARA SERVIDORES MASTER Y SLAVE:
Antes que nada debemos chequear la variable "open_files_limit" :
mysql > show global variables like 'open%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+
1 row in set (0,00 sec)
Este valor no es el ideal pero por ahora quedara asi, para conseguirlo debemos saber que Sistema Operativo tenemos.
Para el caso de REDHAT 7 debemos agregar en:
[root@MysqlServers ~]# cd /usr/lib/systemd/system
[root@MysqlServers system]# vi mysqld.service
Y agregamos las siguientes lineas al final del archivo mysqld.service:
LimitNOFILE = infinity
LimitMEMLOCK = infinity
Pero ademas debemos hacerlo para el sistema operativo:
[root@MysqlServers ~]# cd /etc/security/
[root@MysqlServers security]# vi limits.conf
Aqui agregamos las siguientes lineas:
definimos aqui tambien para el servicio de mysql
* soft nofile 65536
* hard nofile 65536
* soft nproc 65536
* hard nproc 65536
mysql hard nofile 65536
mysql soft nofile 65536
Echo esto agregamos en el archivo de configuracion del mysql la variable global open_files_limit con el valor definido anteriormente:
[root@MysqlServers ~]# cd /etc
[root@MysqlServers etc]# vi my.cnf
Dentro de las etiquetas [mysqld] y [mysqld_safe]
[mysqld]
...
open_files_limit = 65536
...
[mysqld_safe]
...
open_files_limit = 65536
...
Y para evitar varios problemas chequen los permisos del my.cnf, por default tiene los permisos 600, necesitamos modificar a 644
Default
-rw-------. 1 root root 1793 abr 20 10:28 my.cnf
Modificado
-rw-r--r--. 1 root root 1793 abr 20 10:28 my.cnf
-------------------------------------------------------------------------------------------------------------------------------------------------------
CONFIGURANDO MASTER my.cnf
[root@MysqlServers etc]# vi my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
user = mysql
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin = "/var/lib/mysql/mysql-bin"
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
server-id = 1
skip-external-locking
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
binlog_format=MIXED
log_bin_index = "/var/lib/mysql/mysql-bin.index"
sync_binlog=1
general-log
general_log_file = "/var/lib/mysql/MysqlServer.log"
innodb_flush_log_at_trx_commit=1
Lo importante para el mnaster
server-id = 1
log_bin = "/var/lib/mysql/mysql-bin"
log_bin_index = "/var/lib/mysql/mysql-bin.index"
sync_binlog=1
binlog_format=MIXED
Todos los demas parametros son necesarios, pero para el motor de base de datos, no para la replicacion en si.
Luego chequeamos que este andando
1.- reiniciamos el mysql: service mysqld restart
2.- ingresamos a mysql: mysql -u root -ppassword
3.- Ejecutamos el comando show para ver el estado del master
mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 213040583 | | | |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0,00 sec)
4.- Si queremos reiniciar el MASTER, ejecutaremos:
mysql> reset master;
5.- Debemos hacer dump de todas las bases de datos para deployarlas luego en el Slave, sino hacemos estos no replicara nunca.
Este dump tiene la caracteristica siguiente:
[root@MysqlServers root]# mysqldump -u root -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 --all-databases > Dump-TOTAL.sql
Luego de terminado ejecutamos el siguiente comando:
head DumpTotal-20160420.sql -n80 | grep MASTER_LOG_POS
Debemos tener encuenta estos valor para luego en el slave utilizarlos.
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=120;
6.- Copiamos el dump al servidor slave
[root@MysqlServers root] scp DumpTotal-20160420.sql root@MysqlVirtual:/root/
---------------------------------------------------------------------------------------------------------------------------------------------------------
CONFIGURANDO SLAVE
Para que realmente pueda replicar debemos tener que restaurar un dump del master en el slave, (ste es el caso donde se replican todas las bases del Master), pero una vez que empezo a repplicar, cuando creamos una nueva base en el master, se copia en los slave, sin tener que hacer un dump del master o tener que crear por consola la base en el slave.
Restauramos el Dump:
root@MysqlVirtual:~# mysql -u root -ppassword < DumpTotal.sql
Echo la restauracion abrimos el archivo de configurarcion del mysql (my.cnf) del slave:
PARA UBUNTU 14.04
root@MysqlVirtual:~# cd /etc/mysql/
root@MysqlVirtual:/etc/mysql# vi my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file = /var/log/mysql/mysql.log
general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
# Agregar este parametro para solucionar este error: ERROR 2006 (HY000): MySQL server has gone away, que se produce cuando estamos restaurando el dump del master en el slave
max_allowed_packet = 64M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
open_files_limit = 65536
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
[mysqld_safe]
open_files_limit = 65536
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
Los parametros que son importantes para el servidor slave (UBUNTU 14.04)
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
max_allowed_packet = 64M
PARA REDHAT 7
[root@MysqlVirtual2 ~]# cd /etc/mysql
[root@MysqlVirtual2 mysql]#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
server-id = 3
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet = 64M
# GLOBAL VARIABLES
explicit_defaults_for_timestamp
open_files_limit = 65536
# VARIABLES RELAY
relay-log = mysqld-relay-bin
max-relay-log-size = 500M
relay_log_purge = 1
# REPLICATIONS
report-host = 10.0.48.160
report-user = replicar
report-password = r3pl1c4c10n
report-port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
auto_increment_increment = 2
auto_increment_offset = 1
sysdate-is-now
symbolic-links
skip_external_locking = off
plugin_dir = /usr/lib64/mysql/plugin/
general-log = 1
log-error = mysqlservers.err
general_log_file = mysqlservers.log
log_slave_updates
show-slave-auth-info
sync_binlog = 1
enforce_gtid_consistency
gtid-mode = OFF
slave_allow_batching
slave-sql-verify-checksum
slave_exec_mode = STRICT
binlog-format = MIXED
relay_log_info_file = relay-log.info
Los parametros que son importantes para el servidor slave (REDHAT 7):
server-id = 3
# VARIABLES RELAY
relay-log = mysqld-relay-bin
max-relay-log-size = 500M
relay_log_purge = 1
# REPLICATIONS
report-host = 10.0.4.16
report-user = replicar
report-password = r3pl1c4c10n
report-port = 3306
general-log = 1
log-error = mysqlservers.err
general_log_file = mysqlservers.log
log_slave_updates
show-slave-auth-info
sync_binlog = 1
enforce_gtid_consistency
gtid-mode = OFF
slave_allow_batching
slave-sql-verify-checksum
slave_exec_mode = STRICT
binlog-format = MIXED
relay_log_info_file = relay-log.info
Realizado a configuracion del archivo my.cnf debemos ingresar al motor mysql
[root@MysqlVirtual2 mysql]# mysql -u root -ppassword
Debemos decirle al slave donde tiene que buscar el servidor MASTER, en este momento vamos a utilizar los datos que obtuvimos del master
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=120;
Y lo agregamos en el siguiente comando:
mysql> CHANGE MASTER TO MASTER_HOST='10.0.4.16', MASTER_PORT=3306, MASTER_USER='replicar', MASTER_PASSWORD='r3pl1c4cion', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=120;
Query OK, 0 rows affected (0,03 sec)
Luego iniciamos el servidor slave:
mysql> start slave;
Query OK, 0 rows affected (0,00 sec)
Y chequeamos que todo este funcionando adecuadamente:
mysql> show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.4.16
Master_User: replicar
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000042
Read_Master_Log_Pos: 1963620
Relay_Log_File: mysqld-relay-bin.000081
Relay_Log_Pos: 493146
Relay_Master_Log_File: mysql-bin.000042
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1963620
Relay_Log_Space: 1964120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f82504d7-fb73-11e5-b535-005056bb4337
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0,00 sec)
ERROR:
No query specified
Esta salida del comando nos esta diciendo que todo esta normal y funcionando, sabiendo que los siguientes parametros estan incializados con el valor "YES"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDIMIENTO UNA VEZ SOLUCIONADO EL ERROR EN LA REPLICACION
Ahora bien, si se produce un error, luego de solucionarlo debemos hacer lo siguiente:
1.- Paramos el servicio de slave
mysql> stop slave;
Query OK, 0 rows affected (0,00 sec)
2.- Reseteamos el servicio de slave, osea limpiamos el error
mysql> reset slave;
Query OK, 0 rows affected (0,00 sec)
3.- Ejecutamos nuevamente el comando CHANGE MASTER TO como lo detallamos arriba
4.- Reiniciamos nuevamente el slave
mysql> start slave;
Query OK, 0 rows affected (0,00 sec)
---------------------------------------------------------------------------------------------------------------------------------------------------------
ERRORES: CASOS SOLUCIONADOS
1.- Caso: EQUAL MySQL server UUIDs;
mysql> show slave status\G
*************************** 1. row ***************************
...
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because
master and slave have equal MySQL server UUIDs;
these UUIDs must be different for replication to work.
...
Solucion
Dentro del directorio donde estan las bases de mysql /var/lib/mysql se encuentra un archivo llamado auto.cnf este se autogenera, este error nos avisa que
tenemos el mismo uuid entre el master y slave, por lo tanto borramos en el slave el archvio y reiniciamos el mysql
No hay comentarios:
Publicar un comentario