the command was executed on server 1, (master) to connect to server 2 (slave) it was my understanding that this was ultimately a Master-Master type scenario... The instructions Till gave me earlier in this thread gave me the impression that I needed to execute on server 1,(maybe I misunderstood?) Just to make sure we are all on the same page Server #1 (Webserver0 - 10.3.192.20) Server 2 (WebServer2 10.3.192.21) As far as I am aware Server 1 is the true master Thanx Next step please
Ok, the connection test with "mysql -u slaveuser -h xxxxx" has to be executed on the SLAVE to test it's connection to the MASTER, not the other way. The slave needs to be able to connect to the master to get it's updates.
so I executed the command on server #2 specifying the IP of server #1 and I was able to connect... I get the following.. root@WebServer2:/home/dgeho1# mysql -u slaveuser -h 10.3.192.20 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.1.72-2-log (Debian) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show master status; ERROR 1227 (42000): Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation mysql>
What I don't understand is: In your first post, you posted the SHOW SLAVE STATUS command. This is normally issued on the slave server. Yours states: Code: Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400 So this would mean, the server you executed this, tries to connect to master 10.3.192.21 but you assumed the ip .20 is the master. I really don't get what your configuration really is
the install guide informs to check slave status from the master (server #1) 10.3.192.20 http://www.howtoforge.com/installing...th-ispconfig-3 On server 1: Log into MySQL on the shell with... mysql -u root -p ... and enter the MySQL root passord that you had choosen during mysql install. Then execute this commnd on the MySQL shell: GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'slave_user_password'; FLUSH PRIVILEGES; quit; Replace 'slave_user_password' with a secure password that you want to use for the slave to connect to the master server. Replace this placeholder in the next steps with the password that you had choosen wherever the placeholder occurs. Now let's configure our 2 MySQL nodes: On server 1: vi /etc/mysql/my.cnf Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options): [...] [mysqld] server-id = 1 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = 192.168.0.106 master-user = slaveuser master-password = slave_user_password master-connect-retry = 60 expire_logs_days = 10 max_binlog_size = 500M log_bin = /var/log/mysql/mysql-bin.log [...]Then stop MySQL: /etc/init.d/mysql stop Now do nearly the same on server2... On server 2: vi /etc/mysql/my.cnf Search for the section that starts with [mysqld], and put the following options into it (commenting out all existing conflicting options): [...] [mysqld] server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 master-host = 192.168.0.105 master-user = slaveuser master-password = slave_user_password master-connect-retry = 60 expire_logs_days = 10 max_binlog_size = 500M log_bin = /var/log/mysql/mysql-bin.log [...]Then stop MySQL: /etc/init.d/mysql stop Now we have to sync the two mysql servers. We do this by copying over the mysql data directory from the master to the slave and also the debian configuration file that contains the debian-sys-maint user. This can be done as we stopped mysql before on both servers. On server 1: scp -pr /var/lib/mysql/* [email protected]:/var/lib/mysql/ scp -pr /etc/mysql/debian.cnf [email protected]:/etc/mysql/debian.cnf Now we start MySQL on the master server again: /etc/init.d/mysql start Log into the MySQL shell as root user... mysql -u root -p ... and execute this command in the MySQL shell... SHOW MASTER STATUS; ... to get the MySQL master status: mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) The information that we need for the next step is the binlog file mysql-bin.000002 and the binlog position 106. We need the same details for server2 later below. Now execute this command in the MySQL shell on the master to connect it to the slave: STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106; START SLAVE; Then check the slave status: SHOW SLAVE STATUS \G It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output. On server 2: Log into the MySQL shell as root user... mysql -u root -p ... and execute this command in the MySQL shell: STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.0.105', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106; START SLAVE; Then check the slave status: SHOW SLAVE STATUS \G;
This tutorial describes the installation of a clustered web, email, database and DNS server to be used for redundancy, high availability and load balancing on Debian 6 with the ISPConfig 3 control panel. MySQL Master/Master replication will be used to replicate the MySQL client databases between the servers and Unison will be used to Sync the /var/www (websites) and /var/vmail (email account data) folders
Ok, sorry. overlooked the information about master-master replication. Have you compared both slave status commands on both servers? does it work the other direction?
here is the result from server number 1 10.3.192.20 mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.3.192.21 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000046 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000019 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000046 Slave_IO_Running: No 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: 106 Relay_Log_Space: 262 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) here is the result from server 2 10.3.192.21 ysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.3.192.21 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000042 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000042 Slave_IO_Running: No Slave_SQL_Running: No 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: 106 Relay_Log_Space: 0 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: NULL 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)
Ok, so: 1.) is it correct, that the user is "slave" instead of "slaveuser" on the second server? Maybe that's one reason it can't connect to replicate. 2.) may it be that binary logging is not enabled on the second server? "Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'"
Repeat the steps with "CHANGE MASTER TO....." for the second server. Check the mysql config if binary logging is enabled on both. After that - restart both mysql servers and check the slave status again.
I Looked at the .conf files and it looks like bin logging should be turned on (not exactly sure what I'm looking for) When trying to change master on server 2 (10.3.192.21) I get the following result (I got the .binlog filename by showing master status on server #1 per the instructions) mysql> CHANGE MASTER TO MASTER_HOST='10.3.192.20', MASTER_USER='slaveuser', MASTER_PASSWORD='nc3mx-bagxlrblk', MASTER_LOG_FILE='mysql-bin.000068', MASTER_LOG_POS=106; ERROR 29 (HY000): File './mysqld-relay-bin.000019' not found (Errcode: 13)
It seems your installation is quite messed up somehow. I have no idea how to fix it other than completely reconfigure the slave mysql (second master server).
Id first try just uninstalling mysql on the second server that has the problems, delete the /var/lib/mysql and reinstall and reconfigure mysql after that. Maybe you can avoid reinstalling everything in this way.
BACKUP first if you have any data to save. They might and probably will be lost If you want to make a complete uninstall of mysql you might use apt-get purge mysql-server* But be cautious! This deletes all files for mysql, all databases and config. Check if /var/lib/mysql doesn't contain any more data and move away (backup) /etc/mysql if it still exists. Then reinstall with apt-get install mysql-server-5.0 (or 5.1, am not sure what's on debian 6). And configure it following the howto. I hope it works out this time.
I tried the method you mentioned last and it didn't work.. so I formatted disks and started it over. still not working.. when I get to the section of the guide that tells me to make sure that I/o is running 1 row in set (0.00 sec) The information that we need for the next step is the binlog file mysql-bin.000002 and the binlog position 106. We need the same details for server2 later below. Now execute this command in the MySQL shell on the master to connect it to the slave: STOP SLAVE; CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='slaveuser', MASTER_PASSWORD='slave_user_password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106; START SLAVE; Then check the slave status: SHOW SLAVE STATUS \G It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output I get the following error mysql> SHOW SLAVE STATUS \G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.3.192.21 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No 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: 106 Relay_Log_Space: 106 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2013 Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400 Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) I've done everything exactly as the guide says..
