Making a mirror server

Discussion in 'Installation/Configuration' started by djmostek, Oct 22, 2012.

  1. djmostek

    djmostek New Member

    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?
     
  2. till

    till Super Moderator

  3. djmostek

    djmostek New Member

    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?
     
  4. djmostek

    djmostek New Member

    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/* root@server2.example.tld:/var/lib/mysql/
     scp -pr /etc/mysql/debian.cnf root@server2.example.tld:/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!
     
  5. till

    till Super Moderator

    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.
     
    Last edited: Oct 28, 2012
  6. djmostek

    djmostek New Member

    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)
    
     
  7. djmostek

    djmostek New Member

    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?
     
  8. till

    till Super Moderator

    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.
     
  9. djmostek

    djmostek New Member

    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.
     
  10. falko

    falko Super Moderator

  11. djmostek

    djmostek New Member

    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 'slaveuser@94.23.39.14
    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
    [​IMG]

    slave
    [​IMG]

    Only two high usage moments in slave, while master encoutered 100% usage few times.
     
    Last edited: Nov 3, 2012
  12. djmostek

    djmostek New Member

    Any suggestions to this two issues mates? :)
     
  13. onastvar

    onastvar Member

    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
     
    Last edited: Jul 4, 2013
  14. florian030

    florian030 HowtoForge Supporter

    Set the replication-values in mysql.

    Code:
    CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='slave_user', MASTER_PASSWORD='some_password', MASTER_PORT=3307;
     
  15. onastvar

    onastvar Member

    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 'slave_user@192.168.1.106: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 'slave_user@192.168.1.105: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!
     
  16. florian030

    florian030 HowtoForge Supporter

    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
     
  17. onastvar

    onastvar Member

    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
     
    Last edited: Jul 7, 2013
  18. florian030

    florian030 HowtoForge Supporter

    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).
     
  19. onastvar

    onastvar Member

    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!
     
  20. florian030

    florian030 HowtoForge Supporter

    Check your mysql-config. It seems that at least the server-ids are wrong. auto-increment-offset and server-id must always be different.
     

Share This Page