Réplication MySQL 5.1, créer un nouveau slave depuis un slave existant
Par PlaceOweb le mardi, octobre 27 2009, 07:08 - SQL - Lien permanent
Au lieu de couper ou bloquer le le serveur MySQL master, le temps du dump, si vous avez déjà un esclave à jour, vous pouvez vous en servir pour créer un nouvel esclave MySQL.
Procédure de duplication du slave vers un autre expliquée dans le man de mysqldump
· --master-data[=value]
Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump
output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master
server coordinates from which the slave should start replicating after you load the dump file into the slave.
If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is
reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified,
the default value is 1.
This option requires the RELOAD privilege and the binary log must be enabled.
The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case,
a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on
logs happens at the exact moment of the dump.
It is also possible to set up a slave by dumping an existing slave of the master. To do this, use the following procedure on the existing slave:
1. Stop the slave´s SQL thread and get its current status:
mysql> STOP SLAVE SQL_THREAD;
mysql> SHOW SLAVE STATUS;
2. From the output of the SHOW SLAVE STATUS statement, the binary log coordinates of the master server from which the new slave should start replicating are the
values of the Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote those values as file_name and file_pos.
3. Dump the slave server:
shell> mysqldump --master-data=2 --all-databases > dumpfile
4. Restart the slave:
mysql> START SLAVE;
5. On the new slave, load the dump file:
shell> mysql < dumpfile
6. On the new slave, set the replication coordinates to those of the master server obtained earlier:
mysql> CHANGE MASTER TO
-> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;
The CHANGE MASTER TO statement might also need other parameters, such as MASTER_HOST to point the slave to the correct master server host. Add any such
parameters as necessary.
Notes
Nommons les serveurs
- mysql51.master (server_id 1)
- mysql51.slave (server_id 2)
- mysql51.new.slave (server_id 3)
Mémorisation des infos de réplication depuis le slave
# Sur le slave mysql> SHOW SLAVE STATUS; +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Waiting for master to send event | mysql51.xxxxxxxxxx | replication | 3306 | 60 | mysql-bin.000019 | 425272148 | PMP021-relay-bin.000054 | 951112 | mysql-bin.000019 | Yes | Yes | | | | | | | 0 | | 0 | 425272148 | 951268 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ 1 row in set (0.00 sec) mysql> SHOW SLAVE STATUS; +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Waiting for master to send event | mysql51.xxxxxxxxxx | replication | 3306 | 60 | mysql-bin.000019 | 425372539 | PMP021-relay-bin.000054 | 1051098 | mysql-bin.000019 | Yes | Yes | | | | | | | 0 | | 0 | 425372134 | 1051659 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ 1 row in set (0.00 sec) mysql> STOP SLAVE SQL_THREAD; Query OK, 0 rows affected (0.06 sec) mysql> SHOW SLAVE STATUS; +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Waiting for master to send event | mysql51.xxxxxxxxxx | replication | 3306 | 60 | mysql-bin.000019 | 425488485 | PMP021-relay-bin.000054 | 1135003 | mysql-bin.000019 | Yes | No | | | | | | | 0 | | 0 | 425456039 | 1167605 | None | | 0 | No | | | | | | NULL | No | 0 | | 0 | | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ 1 row in set (0.00 sec) mysql> SHOW SLAVE STATUS; +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Waiting for master to send event | mysql51.xxxxxxxxxx | replication | 3306 | 60 | mysql-bin.000019 | 425509662 | PMP021-relay-bin.000054 | 1135003 | mysql-bin.000019 | Yes | No | | | | | | | 0 | | 0 | 425456039 | 1188782 | None | | 0 | No | | | | | | NULL | No | 0 | | 0 | | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ 1 row in set (0.00 sec) # Donc nous allons reprendre la réplication depuis ces positions : # Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote those values as file_name and file_pos. # Relay_Master_Log_File : mysql-bin.000019 # Exec_Master_Log_Pos : 425456039
Obligation d'activer les logs binaires sur le slave
mysqldump --host="mysql51.master" --user="monUserAvecDroits" --password="xxx" --all-databases --opt --routines --triggers --master-data=2 > mysql51.slave.sql mysqldump: Error: Binlogging on server not active
Vous devez activez les logs binaires dans le fichier de configuration my.cnf avant de relancer le serveur slave.
SET GLOBAL log_bin = ON #1238 - Variable 'log_bin' is a read only variable
/etc/mysql/my.cnf
# Enable binary logging. This is required for acting as a MASTER in a # replication configuration. You also need the binary log if you need # the ability to do point in time recovery from your latest backup. log-bin=mysql-bin # binary logging format - mixed recommended binlog_format=mixed
Relancement du slave
# /etc/init.d/mysql restart
Relancement du new.slave et configuration en tant qu'esclave
# Vérification que le server_id est le bon mysql> SHOW VARIABLES LIKE 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 3 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW SLAVE STATUS; Empty set (0.00 sec) # Configuration de la position de reprise de cet esclave mysql> CHANGE MASTER TO MASTER_HOST='mysql51.master', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=425456039; Query OK, 0 rows affected (0.02 sec) mysql> SHOW SLAVE STATUS; +----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | | mysql51.xxxxxxxxxx | replication | 3306 | 60 | mysql-bin.000019 | 425456039 | VMP035-relay-bin.000001 | 4 | mysql-bin.000019 | No | No | | | | | | | 0 | | 0 | 425456039 | 106 | None | | 0 | No | | | | | | NULL | No | 0 | | 0 | | +----------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ 1 row in set (0.00 sec) # Activation/lancement de la réplication mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> SHOW SLAVE STATUS; +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ | Waiting for master to send event | mysql51.xxxxxxxxxx | replication | 3306 | 60 | mysql-bin.000019 | 515983985 | VMP035-relay-bin.000002 | 4956004 | mysql-bin.000019 | Yes | Yes | | | | | | | 0 | | 0 | 430411792 | 90528353 | None | | 0 | No | | | | | | 9401 | No | 0 | | 0 | | +----------------------------------+--------------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+ 1 row in set (0.00 sec)
Vous voila avec 2 slave mysql, qui se synchronisent depuis le même master (dont on a pas coupé les services). Enjoy !
Ressources
- Commandes mysql
- SHOW SLAVE STATUS SHOW SLAVE STATUS
- START SLAVE START SLAVE thread_type: IO_THREAD | SQL_THREAD
- STOP SLAVE STOP SLAVE thread_type: IO_THREAD | SQL_THREAD
- CHANGE MASTER TO CHANGE MASTER TO
- Replication Master Thread States
- Replication Slave I/O Thread States
- Replication Slave SQL Thread States
- Replication Slave Connection Thread States