Making a mirror server

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?

 

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 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?

 

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.

 

Any suggestions to this two issues mates?

 

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.