Welcome I have made one machine runnig with The Perfect Setup with Ubuntu 12.04 and ISPConfig3 which supports my main portal. As my portal has recently developed (over 600 users online in peak time) my one machine (Quad 9550, 4GB RAM) is not working properly in peak times. I have one spare machine (with same parameters) and I would like to mirror primary machine in order to make better permormance for my portal. And now how am I supposed to do it? I must install spare copy of Perfect setup on new machine and what then? Is there any manual for this?
Did you take a look at ispconfig.org were all installation manuals are listed? There is a section for muliserver tutorials and there you find the cluster guide http://www.howtoforge.com/installin...tabase-cluster-on-debian-6.0-with-ispconfig-3
Thank you Till! As always useful replay from you! But I have one more question: in this tut there's said that two servers must be configured with the basic setup (only 1-8steps) - and there is a problem as my primary machine is running for few months and is fully configured - only the secondary machine will be setuped with the basci setup. Will it cause any problems during and after the installation?
Till can U give me some feedback one more time? Im doing my cluster with tutorial (http://www.howtoforge.com/installin...tabase-cluster-on-debian-6.0-with-ispconfig-3) and i am on the step when i am supposed to add in /etc/mysql/my.cnf Code: [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 and after adding it and then after running commands Code: scp -pr /var/lib/mysql/* [email protected]:/var/lib/mysql/ scp -pr /etc/mysql/debian.cnf [email protected]:/etc/mysql/debian.cnf I cannot start my mysql server. So summarising: after stopping mysql service and editing /etc/mysql/my.cnf there is no way to restart MySQL again. Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
The mysql server on the master or the slave and which error messages do you get in the syslog? A mirror setup were one server is in production is possible, but its not easy and may cause problems as the configuration exists already on one server and e.g. the system users and groups and also the records for the mirror database are missing on the second server.
The mysql server on both master and slave server act as I described it above (do not start/restart after adding those commands from tut to [mysqld] section). Output of syslog: Code: Oct 28 19:52:41 hasty kernel: init: mysql main process (4303) terminated with status 7 Oct 28 19:52:41 hasty kernel: init: mysql main process ended, respawning Oct 28 19:52:41 hasty kernel: init: mysql post-start process (4304) terminated with status 1 Oct 28 19:52:43 hasty kernel: init: mysql main process (4353) terminated with status 7 Oct 28 19:52:43 hasty kernel: init: mysql main process ended, respawning Oct 28 19:52:43 hasty kernel: init: mysql post-start process (4354) terminated with status 1 Oct 28 19:52:45 hasty kernel: init: mysql main process (4403) terminated with status 7 Oct 28 19:52:45 hasty kernel: init: mysql respawning too fast, stopped Oct 28 19:53:01 hasty CRON[4647]: (root) CMD (/usr/local/rtm/bin/rtm 46 > /dev/null 2> /dev/null)
Maybe this is the problem of mysql version. That in 5.5 in comparison to 5.1 "master" options are disabled? or am I wrong?
The resaon for the problem might be the different mysql versions, the setup assumes a identical setup on both servers. I havent tested if mirroring between different mysl versions works.
But on both: primary and slave server the mysql is in 5.5 version. But only after adding this to my.cnf [code [mysqld] server-id = 1 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = xxxxxxxxxxx master-user = slaveuser master-password = slave_user_password master-connect-retry = 60 expire_logs_days = 10 max_binlog_size = 500M [/code] The server do not start. When I erase this the server works properly.
I guess you need to use the Code: CHANGE MASTER TO statement (see http://www.howtoforge.com/how-to-set-up-database-replication-in-mysql-on-ubuntu-9.10-p2 ) instead of setting the master values in my.cnf.
Thank you Falko! I've done it the way u suggest but... another problem has occured.. I've searched almost whole the internet to get thorugh it but I do not have any sneaky idea how to cope with that. The problem is after CHANGE MASTER TO commands I get on my master server: Code: Slave_IO_Running: Connecting Slave_SQL_Running: Yes and on my slave server: Code: Slave_IO_Running: Yes Slave_SQL_Running: No I've tried to use on my slave server commend Code: mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; but nothing has chaned. What am I supposed to do to get this over finally... The output of Last_SQL_Error (in slave machine points : Code: Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1017 Last_SQL_Error: Error 'Can't find file: './c2filmy33/a33_wsc_goco des.frm' (errno: 13)' on query. Default database: 'c2filmy33'. Query: 'UPDATE a3 3_wsc_gocodes SET hitcount=hitcount+1 WHERE id='57'' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 The output of Last_SQL_Error (in master machine points : Code: Last_IO_Errno: 1045 Last_IO_Error: error connecting to master '[email protected] 1:3306' - retry-time: 60 retries: 86400 Last_SQL_Errno: 0 Last_SQL_Error: The output of Code: grep mysql /var/log/syslog for both master and slave server shows no errors. PS. I have continued the (http://www.howtoforge.com/installin...tabase-cluster-on-debian-6.0-with-ispconfig-3) setup and make the slave server a mirror of master server (every step apart this connected with synchronizing mysql i go smoothly) and I check options in ISPConfig that master server cares only for mysql and slave for the rest (web,mail etc.) but when i turn down the apache service in master server (supposing that the slave will support the website) the website goes down.. I've acknowledged some strange thing, about usage of CPU of two machines: master slave Only two high usage moments in slave, while master encoutered 100% usage few times.
I'm trying Setting Up The Two Base Systems using this tutorial. http://www.howtoforge.com/installin...tabase-cluster-on-debian-6.0-with-ispconfig-3 I'm having the same issues when I add following to /etc/mysql/my.cnf Code: [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 = mypassword master-connect-retry = 60 expire_logs_days = 10 max_binlog_size = 500M log_bin = /var/log/mysql/mysql-bin.log After I add above to /etc/mysql/my.cnf I cannot start mysqld "Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!" When I run: grep mysql /var/log/syslog I get: Jul 4 10:58:42 moon mysqld: 130704 10:58:42 [Note] /usr/sbin/mysqld: Normal shutdown Jul 4 10:58:42 moon mysqld: Jul 4 10:58:42 moon mysqld: 130704 10:58:42 [Note] Event Scheduler: Purging the queue. 0 events Jul 4 10:58:42 moon mysqld: 130704 10:58:42 InnoDB: Starting shutdown... Jul 4 10:58:43 moon mysqld: 130704 10:58:43 InnoDB: Shutdown completed; log sequence number 249666817 Jul 4 10:58:43 moon mysqld: 130704 10:58:43 [Note] /usr/sbin/mysqld: Shutdown complete Jul 4 10:58:43 moon mysqld: Jul 4 10:58:43 moon mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended Jul 4 10:58:44 moon mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql Jul 4 10:58:44 moon mysqld: 130704 10:58:44 [Note] Plugin 'FEDERATED' is disabled. Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: The InnoDB memory heap is disabled Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: Compressed tables use zlib 1.2.7 Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: Using Linux native AIO Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: Initializing buffer pool, size = 128.0M Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: Completed initialization of buffer pool Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: highest supported file format is Barracuda. Jul 4 10:58:44 moon mysqld: 130704 10:58:44 InnoDB: Waiting for the background threads to start Jul 4 10:58:45 moon mysqld: 130704 10:58:45 InnoDB: 5.5.31 started; log sequence number 249666817 Jul 4 10:58:45 moon mysqld: 130704 10:58:45 [ERROR] /usr/sbin/mysqld: unknown variable 'master-host=192.168.0.105' Jul 4 10:58:45 moon mysqld: 130704 10:58:45 [ERROR] Aborting Jul 4 10:58:45 moon mysqld: Jul 4 10:58:45 moon mysqld: 130704 10:58:45 InnoDB: Starting shutdown... Jul 4 10:58:46 moon mysqld: 130704 10:58:46 InnoDB: Shutdown completed; log sequence number 249666817 Jul 4 10:58:46 moon mysqld: 130704 10:58:46 [Note] /usr/sbin/mysqld: Shutdown complete Jul 4 10:58:46 moon mysqld: Jul 4 10:58:46 moon mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended Jul 4 10:58:58 moon /etc/init.d/mysql[14199]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Jul 4 10:58:58 moon /etc/init.d/mysql[14199]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed Jul 4 10:58:58 moon /etc/init.d/mysql[14199]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Jul 4 10:58:58 moon /etc/init.d/mysql[14199]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Jul 4 10:58:58 moon /etc/init.d/mysql[14199]: Both server are identical (Debian 7.0 and MySQL 5.5) What was the solution, could you please share? Thank You
Set the replication-values in mysql. Code: CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='slave_user', MASTER_PASSWORD='some_password', MASTER_PORT=3307;
Thanks Florian! I cannot make a connection. When I issue SHOW SLAVE STATUS \G on server 1 I get the following: Code: *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.1.106 Master_User: slave_user Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Connecting 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: 107 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 [COLOR="Red"] Last_IO_Errno: 2003 Last_IO_Error: error connecting to master '[email protected]:3307' - retry-time: 60 retries: 86400[/COLOR] Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) When I issue SHOW SLAVE STATUS \G on server 2 I get the following: Code: *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.1.105 Master_User: slave_user Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Connecting 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: 107 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/newcerts/client-key.pem Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No [COLOR="red"] Last_IO_Errno: 2003 Last_IO_Error: error connecting to master '[email protected]:3307' - retry-time: 60 retries: 86400[/COLOR] Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) Anyone knows how I could fix this issue? Thank You!
Verify the (master-)port. Maybe you´re using 3006 in your mysql-config. You can test the sql-connections/permissions with Code: mysql -u USER -p PASS -h HOST -P PORT
I fixed the previous issue - now I have another issue - any ideas how to fix it? server 1 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' server 2 Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error log Jul 6 11:18:25 sun mysqld: 130706 11:18:25 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236) Jul 6 11:18:25 sun mysqld: 130706 11:18:25 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236 Jul 6 11:18:25 sun mysqld: 130706 11:18:25 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000017', position 107
I would restart the replicatoin. See here for a master-master-replication. If you´re running a master-slave-replication, you can dump the data on the master with --master-data and import the dump on the slave. Don´t forget to dump databases on the slave, that are not identical (maybe mysql - depends on your setup).
I did mysql dump export from server 1 and imported mysql dump file to server 2 which is working as expected. I'm still getting Last_IO_Errno: 1236 Slave_IO_Running: No Any suggestions how to troubleshoot server 1? server 1 Slave_IO_State: Master_Host: 192.168.1.106 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 213991 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: No Slave_SQL_Running: Yes Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000003' at 213991, the last event read from '/var/log/mysql/mysql-bin.000003' at 4, the last byte read from '/var/log/mysql/mysql-bin.000003' at 4.' server 2 Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.105 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 96244 Relay_Log_File: mysqld-relay-bin.000004 Relay_Log_Pos: 96390 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Thanks!
Check your mysql-config. It seems that at least the server-ids are wrong. auto-increment-offset and server-id must always be different.
