Multiserver Squeeze MySQL 5.1 to 5.5 Suggestions

Discussion in 'Server Operation' started by fatmike, Mar 17, 2015.

  1. fatmike

    fatmike Member HowtoForge Supporter

    Hi,
    I have the current Multiserver Setup:
    Master Web,Mail Server
    Master/Master MySQL Replication, primary DNS
    Master/Master MySQL Replication, secondary DNS
    Slave Web
    All systems running Debian 6 Squeeze with MySQL 5.1.73 and ISPConfig 3.0.5p5(The first is the Master with web interface and all the others are slaves).
    I updated source list on all systems to:
    Code:
    deb http://packages.dotdeb.org squeeze all
    deb-src http://packages.dotdeb.org squeeze all
    deb http://packages.dotdeb.org squeeze-php54 all
    deb-src http://packages.dotdeb.org squeeze-php54 all
    
    I tried to update/upgrade in order to update mysql to 5.5 arrording to this-> https://www.dotdeb.org/2014/07/22/mysql-5-5-38-for-squeeze/
    But only mysql-common and php updated.

    So I found this article -> http://www.ovaistariq.net/490/a-step-by-step-guide-to-upgrading-to-mysql-5-5/
    and I'm thinking to update using that guide to the latest 5.5 version.
    Firstly update the Master and then the Web slave and after that stop the slaves from replicating in MASTER/MASTER and update those too one at the time.

    Any ideas on what I'm trying to achieve?
    How would you make that happen?
    I don't want to damage my installation.

    Thank you
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Why dont you update to wheezy? Squeeze is quite old already and does not has ull update support anymore. Yu will have to update to wheezy sooner or later anyway, if you want to keep your system secure.
     
    fatmike likes this.
  3. fatmike

    fatmike Member HowtoForge Supporter

    Very nice idea.
    I was thinking of the same thing.
    But isn't whezzy part of debian 7?
    All my systems run debian 6.
    I would have to reinstall the whole VPS installation on all systems, losing all settings and data, wouldn't I?
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Wheezy is debian 7. Like Ubuntu, each debian version has also a codename beside of the number, so squueze = 6, wheezy = 7

    Debian systems can be updated to the next distribution version, you dont have to reinstall the systems. I have some servers here that were updated from Debian 5 to 6 and now 7.
     
    fatmike likes this.
  5. fatmike

    fatmike Member HowtoForge Supporter

  6. fatmike

    fatmike Member HowtoForge Supporter

    I updated to wheezy but i got an error regarding php mysql and dovecot could not be started, maybe because i previously tried to update mysql to 5.5.
    Mail account could not connect.
    So I completed removed dovecot php and mysql services and reinstall.
    MySQL works fine under 5.5 on Web and mail servers and php under 5.4
    Dovecot starts fine but all mails cannot connect using imap pop or webmail.
    MySQL Master/Master cannot connect/start after reboot
    Help please??
     
    Last edited: Mar 18, 2015
  7. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    Check your logs regarding mail. For mysql use show slave stats \G in mysql. mysql 5.5 uses a different definition for the master (moved from my.cnf to mysql-console).
    I upgraded some servers from lenny to wheezy a few days ago - and this was not much fun. :)
     
    fatmike likes this.
  8. fatmike

    fatmike Member HowtoForge Supporter

    Mail logs point mainly to a database connectivity issue:
    Code:
    amavis[7140]: (07140-01) (!!)TROUBLE in process_request: connect_to_sql: unable to connect to any dataset at (eval 116) line 247.
    postfix/qmgr[3998]: error: unsupported dictionary type: mysql
    postfix/smtpd[11805]: fatal: no SASL authentication mechanisms
    postfix/smtpd[3173]: lost connection after CONNECT from localhost.localdomain[127.0.0.1]
    
    What do you mean by that?


    I got that so I'm currently backing everything up to start clean and setup the all the systems all over again.
    That was what i was trying to avoid.
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    There should be mysql errors in the /var/log/syslog file that show which config settings in the mysql my.cnf cause the start problem.
     
    fatmike likes this.
  10. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    With mysql 5.5 use
    Code:
    CHANGE MASTER TO MASTER_HOST="host.example.com", MASTER_USER='user', MASTER_PASSWORD='password'
    to set the the master (and remove the settings from my.cnf. Are you sure, that the latest apt-get upgrade installed and configured all packges?
     
    fatmike likes this.
  11. fatmike

    fatmike Member HowtoForge Supporter

    Well the error was because of what florian030 mentioned.
    Basically from 5.5 and forward properties of master-host, master-user, master-password have been deprecated and not allowed in my.cnf.


    Yes but now i started all over again anyway.
    I made it work without my.cnf on a clean installation(at least i think i did. I haven't had the chance to test yet but "Slave_IO_Running" and "Slave_SQL_Running" respond to "YES" on both MySQL servers).

    From what i understand MySQL 5.5+ takes care of the replication without my.cnf master-* properties.
    You should set the server-id in my.cnf
    Create user in all servers and grant slave privileges
    Change master to master_host, master_user, master_password, master_log_file, master_log_pos on all servers accordingly
    Start slaves on all servers

    I don't know if the collision issue that was bypassed (by increasing auto_increment_increment and auto_increment_offset based on number of servers) has been fixed in MySQL 5.5+

    If someone know about that, is worth mentioning.
    I will test in near future with and without auto_increment_increment properties if anyone wants to know.
     
  12. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    You don´t need to adjust the log-positions. Just remove the client-settings for the master from my.cnf and set them in the mysql-console. You will get an inconsistence replication if you changed the log-pos to a higher value.
    You can dump the databases on one master including --master-data and import this dump on the slave (but do not include any databases that you excluded from your replication ;) ). Maybe you find some more here or here.
     
    fatmike likes this.
  13. fatmike

    fatmike Member HowtoForge Supporter

    Well it was a clean installation without any databases. So i reset the masters on both servers and set the correct master_log_file, master_log_pos on both(it is a Master/Master Replication).
    It seems that is replicating just fine an example_db.

    Does 5.5+ needs me to specify databases that i don't want to be replicated?(binlog_ignore_db?? Replicate_Ignore_DB??)
     
  14. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    You can set the ignore-db and ignore-tables in the my.cnf and / or within the mysql-shell.
     

Share This Page