Mysql : mettre en place une répli (pour confirmé)

Valide sur 5.1.

Sur le master faire un dump des bases avec :

mysqldump -uroot -p –single-transaction –routines –triggers <database> > <database>.sql

Sur le master faire un :

show master status; (récupérer le info sous le coude)

Sur le slave , réimporter les bases :

mysql -p -h127.0.0.1 <database> < <database>.sql

Sur le slave dans le fichier my.cnf :

# Master parameter

#log-bin                        = mysql-bin
#binlog_format                  = mixed
#max_binlog_size                = 256M

# Slave parameter

skip-slave-start
server-id                       = 3  #doit être unique
relay-log                       = <slave_hostname>-relay-bin
master-host                     = <master-hostname>
master-info-file                = master.info
master-port                     = 3306
master-user                     = <replicauser>
master-password                 = <pwdreplicauser>
master-connect-retry            = 60

replicate_do_db                  = <database1>
replicate_do_db                  = <database2>

…
replicate_do_db                  = <databasen>

read-only

innodb_data_home_dir             = /var/lib/mysql_3306
innodb_data_file_path            = ibdata1:10M:autoextend
innodb_log_group_home_dir        = /var/lib/mysql_3306
innodb_buffer_pool_size          = 256M
innodb_additional_mem_pool_size  = 20M
innodb_log_file_size             = 64M
innodb_log_buffer_size           = 8M
innodb_file_per_table

Sur le master créé le user de réplication :

mysql>CREATE USER ‘replicuser’@’ip_slave’ IDENTIFIED BY ‘<pwdreplicauser>’;

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘replicuser’@’ip_slave’ IDENTIFIED BY PASSWORD ‘MDP EN HEXA’;

mysql> flush privileges;

Sur le slave créé le user de réplication :

mysql>CREATE USER ‘replicuser’@’ip_master’ IDENTIFIED BY PASSWORD ‘pwd en Hexa’;

mysql>GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO ‘replicuser’@’ip_master’ IDENTIFIED BY PASSWORD ‘pwd en Hexa’;

mysql> flush privileges;

Synchroniser le slave :

mysql> change master to MASTER_HOST=’ip_master’, MASTER_PORT=3306, MASTER_USER=’replicuser’, MASTER_PASSWORD = ‘<pwdreplicauser>’, MASTER_LOG_FILE=’numéro de binlog’, MASTER_LOG_POS=position dans le binlog;

mysql> start slave;

 enjoy~