Bonjour,
J’ai deux serveurs avec chacun un Mysql qui tourne. J’essaye de faire de la replication entre eux, mais visiblement ça ne fonctionne pas…
D’après mes multiples recherches, je pense avoir bien configuré l’ensemble, puisque tout se lance, je n’ai pas de problèmes lors d’un show slave status, mais la replication ne se fait pas…
Voici mes conf:
my.cnf du master :
# other settings you may need to change.
bind-address = 0.0.0.0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = replitest
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
my.cnf du slave :
[code]server-id = 2
master-host=172.16.18.67
master-user=slaveuser
master-password=password
master-connect-retry=60
replicate-do-db=replitest
slave_skip_errors=ALL
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = replitest
binlog_ignore_db = mysql
binlog_ignore_db = information_schema[/code]
Sur mon master j’ai un user qu’a les droits pour la repli et tout ce qu’il faut, testé et fonctionnel
j’ai fait ça sur mon slave (avec les valeurs données par le show master status du master):
CHANGE MASTER TO
MASTER_HOST='172.16.18.67',
MASTER_USER='slaveuser',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=98;
Quand je fais un show slave status \G :
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.18.67
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 243
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: replitest
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: 98
Relay_Log_Space: 536
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:
1 row in set (0.00 sec)
quand je fais un show processlist sur le master j’ai ça :
mysql> show processlist \G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 2475
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 2. row ***************************
Id: 36
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 3. row ***************************
Id: 87
User: slaveuser
Host: 172.16.18.60:59970
db: NULL
Command: Binlog Dump
Time: 120
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
3 rows in set (0.00 sec)
sur le slave :
mysql> show processlist \G
*************************** 1. row ***************************
Id: 52
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 53
User: system user
Host:
db: NULL
Command: Connect
Time: 12
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 54
User: system user
Host:
db: NULL
Command: Connect
Time: 12
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
et enfin quand je regarde le syslog du slave j’ai ça :
Nov 10 08:11:31 RADIUS /etc/mysql/debian-start[5506]: Checking for insecure root accounts.
Nov 10 08:11:31 RADIUS /etc/mysql/debian-start[5510]: Triggering myisam-recover for all MyISAM tables
Nov 10 08:11:39 RADIUS mysqld: 111110 8:11:39 [Note] Slave I/O thread killed while reading event
Nov 10 08:11:39 RADIUS mysqld: 111110 8:11:39 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000009', position 98
Nov 10 08:11:39 RADIUS mysqld: 111110 8:11:39 [Note] Error reading relay log event: slave SQL thread was killed
Nov 10 08:11:42 RADIUS mysqld: 111110 8:11:42 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000009' at position 98, relay log './mysqld-relay-bin.000008' position: 243
Nov 10 08:11:42 RADIUS mysqld: 111110 8:11:42 [Note] Slave I/O thread: connected to master 'slaveuser@172.16.18.67:3306',replication started in log 'mysql-bin.000009' at position 98
Ensuite quand je fais du select, j’ai des données en plus sur le master, qui ne sont pas repliquées sur le slave…
J’ai cherché pas mal sur le net, je vois pas pourquoi ça ne fonctionne pas…
Merci à tous ceux qui essaieront de m’aider !