ISPconfig 3 - Mirror System / Two Server Setup !?!

Discussion in 'ISPConfig 3 Priority Support' started by danhansen@denmark, Jul 8, 2013.

  1. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    Hi,


    5. Is there others that have problems doing what I am trying to? Shouldn't it be possible on Ubuntu when it is on debian?
    If this is the case, I've got a serious problem.. I'm not good enough to find new solutions, was only trying this because of the guide in the ISPconfig3 Manual...



    5.1 Maybe I misunderstood you, but please explain or make an example regarding:

    "/etc/mysql/my.cnf"

    from the section [mysqld] "commenting out the conflicting" - I added the text as shown above, but what is to be "commented out"?



    5.2 "/etc/mysql/my.cnf":

    master-user = slaveuser (ok? no change in this line)
    master-password = slave_user_password (am I suppose to enter the password here, doesn't think so, but I'm not sure)



    5.3 Do you know how to remove this swap file:

    E325: ATTENTION
    Found a swap file by the name "/etc/mysql/.my.cnf.swp"
    owned by: root dated: Tue Jul 23 15:21:45 2013
    file name: /etc/mysql/my.cnf
    modified: YES
    user name: root host name: server1.ispserver.tld
    process ID: 21658
    While opening file "/etc/mysql/my.cnf"
    dated: Tue Jul 23 16:07:38 2013
    NEWER than swap file!

    (1) Another program may be editing the same file. If this is the case,
    be careful not to end up with two different instances of the same
    file when making changes. Quit, or continue with caution.
    (2) An edit session for this file crashed.
    If this is the case, use ":recover" or "vim -r /etc/mysql/my.cnf"
    to recover the changes (see ":help recovery").
    If you did this already, delete the swap file "/etc/mysql/.my.cnf.swp"
    to avoid this message.

    Swap file "/etc/mysql/.my.cnf.swp" already exists!
    [O]pen Read-Only, (E)dit anyway, (R)ecover, (D)elete it, (Q)uit, (A)bort:



    .
     
    Last edited: Jul 23, 2013
  2. till

    till Super Moderator

    Some users reported that the mysql replication configuration for mysql 5.5 has been changed from 5.1. I havent setup a replication system with 5.5. yet so I can neither confirm nor deny that. You should be able to see the mysql version with the command:

    mysql -V

    5.1: Conflictin lines mean that you have the same variables defined already. The variable name is the part in fron of the =. So if there are the same variables defined, then add a # in front of the "old" lines.

    5.2: yes.

    5.3:

    rm -f /etc/mysql/.my.cnf.swp
     
  3. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    MySQL version and other stuff ;)

    Hi Till,

    5.0: MySQL version is: mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (i686) using readline 6.2

    5.1: Great, thanks.... variables alike! Got it!!!

    5.2: OK, this was badly expressed. To questions in the same line..

    "/etc/mysql/my.cnf"
    master-password = slave_user_password

    In my.cnf "slave_user_password " is not a variable being set! We enter the password directly in this file? It's just that it doesn't say so in the guide. I was only guessing.

    5.3: and thank you for that one :eek:


    New issue:
    1.: Page 49 in the manual, "Two Server Setup" guide:

    Having suncronized the two sql servers I'm trying to restart mysql by running:
    /etc/init.d/mysql start
    or
    /etc/init.d/mysql restart

    But this is what happened... I've seen this before, but in those cases it did start. Here it doesn't:

    root@myispserver1:/home/administrator# /etc/init.d/mysql start
    Rather than invoking init scripts through /etc/init.d, use the service(8)
    utility, e.g. service mysql start

    Since the script you are attempting to invoke has been converted to an
    Upstart job, you may also use the start(8) utility, e.g. start mysql
    start: Job failed to start
    root@myispserver1:/home/administrator#


    It's the same for both servers... Status for service is: stop/waiting

    What can be the reason for it not to be able to restart? I've checked every single line in the "my.cnf" and theres no conficts.. There were two conflicting variables which I corrected. It was:
    expire_logs_days = 10
    max_binlog_size = 100M
    But they were commented out as we are told to ;) No other conlicting variables!

    Here's the services managed by upstart:
    Code:
    root@myispserver1:/home/administrator# initctl list
    mountall-net stop/waiting
    passwd stop/waiting
    rc stop/waiting
    rsyslog start/running, process 434
    screen-cleanup stop/waiting
    tty4 start/running, process 807
    udev start/running, process 329
    upstart-udev-bridge start/running, process 327
    ureadahead-other stop/waiting
    whoopsie start/running, process 840
    apport start/running
    console-setup stop/waiting
    hwclock-save stop/waiting
    irqbalance stop/waiting
    plymouth-log stop/waiting
    tty5 start/running, process 813
    failsafe stop/waiting
    atd start/running, process 833
    dbus start/running, process 427
    mounted-var stop/waiting
    plymouth stop/waiting
    resolvconf start/running
    ssh start/running, process 559
    udev-fallback-graphics stop/waiting
    control-alt-delete stop/waiting
    hwclock stop/waiting
    mounted-proc stop/waiting
    module-init-tools stop/waiting
    setvtrgb stop/waiting
    shutdown stop/waiting
    cron start/running, process 832
    mountall stop/waiting
    mounted-debugfs stop/waiting
    console stop/waiting
    mounted-run stop/waiting
    acpid start/running, process 831
    plymouth-stop stop/waiting
    rcS stop/waiting
    ufw start/running
    wait-for-state stop/waiting
    flush-early-job-log stop/waiting
    friendly-recovery stop/waiting
    rc-sysinit stop/waiting
    upstart-socket-bridge start/running, process 685
    tty2 start/running, process 818
    udevtrigger stop/waiting
    container-detect stop/waiting
    mounted-dev stop/waiting
    tty3 start/running, process 819
    udev-finish stop/waiting
    hostname stop/waiting
    mountall-reboot stop/waiting
    mysql stop/waiting
    dovecot start/running, process 13658
    mountall-shell stop/waiting
    mounted-tmp stop/waiting
    network-interface (lo) start/running
    network-interface (eth0) start/running
    plymouth-ready stop/waiting
    plymouth-splash stop/waiting
    plymouth-upstart-bridge stop/waiting
    tty1 start/running, process 915
    udevmonitor stop/waiting
    dmesg stop/waiting
    network-interface-security (network-interface/eth0) start/running
    network-interface-security (network-interface/lo) start/running
    network-interface-security (networking) start/running
    networking stop/waiting
    procps stop/waiting
    tty6 start/running, process 821
    network-interface-container stop/waiting
    ureadahead stop/waiting
    
    


    I will try to get trought the guide never the less and if it works everything is great, if not I'm open to suggestions ;)

    .
     
    Last edited: Jul 24, 2013
  4. till

    till Super Moderator

    Thats ok and just a info message, you can still use the normal start / stop scripts on ubuntu. This will not cause a problem.

    So the reason for the failed mysql start is something different, most likely a incompatibility of the mysql version 5.5 with the setup described in the multiserver guide which is for mysq. 5.1. You should find the reason for the failed mysql start in the mysql log file.
     
  5. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    MySQL Log file / failure.... How to?

    Hello Mr. Brehm,

    Thanks for your reply...
    Please guide me when you have the time.. location of the file and a command to view it? I can VI the log right, but I guess theres a right way for this too.. I consulted my ubuntu manual, but the samples does not describe anything for this issue...
    e.g.: okular "filename" , xdg-open "filename" !!!???

    Thanks for your help.. I'll try a little longer to make this work, because it's a wonderfull way to have a webserver running and helps us in many ways. So thank you for that ;)

    .
     
  6. till

    till Super Moderator

    You can view the last lines of a log file with the tail command. e.g. to show the last 100 lines o the syslog:

    tail -n 100 /var/log/syslog
     
  7. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    Hi Till,

    Thanks for your reply..

    Do yo know the path to the mysql logfile? Haven't been able to locate it. In MySQL FAQ's we are told the log files are individually placed according to OS..
    Can you help? or is this the way:

    http://stackoverflow.com/questions/5441972/how-to-see-log-files-in-mysql

    Tried tail -n 100 /var/log/mysql/error.log but the error.log was empty! viewed the log after trying to restart mysql...

    Then there is "log_bin" and "general_log_file" Is it one of these? Or do I have to "activate" another log function? I can't find the line with "mysql.log"


    Had to ask you before trying more on my own...

    .
     
    Last edited: Jul 29, 2013
  8. till

    till Super Moderator

    The mysql log on Debian and Ubuntu is normally /var/log/mysql/mysql.log, so the tail command would be:

    tail -n 100 /var/log/mysql/mysql.log

    to view the last 100 entries from that file. But it might be that mysql changed the name with the 5.5 version. The log_bin file should be something different, but the general_log_file might be worth to take a look at.
     
  9. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    Enable log file and viewing the log file.

    Hi Till,

    Thanks for your reply...

    1.
    Tried to enable the general log file and then restart the service. After that I tried to view the log file..
    No such file or directory...
    Its called the "general_log_file" in "my.cnf" but is to be stored in the "/mysql.log" just like you said.

    Heres my "my.cnf" if you would take a look and help me get the log file running. I've been reading MytSQL faq's for information and it seems to be the right file we are talking about: http://dev.mysql.com/doc/refman/5.5/en/query-log.html

    "my.cnf":
    Code:
    root@myispserver1:/home/administrator#[COLOR="Red"] tail -n 100 /var/log/mysql/mysql.log[/COLOR]
    [COLOR="Red"]tail: cannot open `/var/log/mysql/mysql.log' for reading: No such file or directory[/COLOR]
    
    root@myispserver1:/home/administrator# [COLOR="Red"]vi /etc/mysql/my.cnf[/COLOR]
    
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    a escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    [client]
    port            = 3306
    socket          = /var/run/mysqld/mysqld.sock
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket          = /var/run/mysqld/mysqld.sock
    nice            = 0
    
    [mysqld]
    # ***** ADD'S TWO SERVER SETUP PAGE 48 *****
    server-id = 1
    replicate-same-server-id = 0
    auto-increment-increment = 2
    auto-increment-offset = 1
    master-host = 192.168.0.102
    master-user = slaveuser
    master-password = ********
    master-connect-retry = 60
    expire_logs_days = 10
    max_binlog_size = 500M
    log_bin = /var/log/mysql/mysql-bin.log
    # ***** END ADD'S TWO SERVER SETUP PAGE 48 *****
    #
    # * Basic Settings
    #
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    #bind-address           = 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer              = 16M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    general_log_file        = /var/log/mysql/mysql.log
    general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries       = /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                        = /var/log/mysql/mysql-bin.log
    #expire_logs_days       = 10
    #max_binlog_size         = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer              = 16M
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/
    
    
    2.
    Don't you think that there's others who uses ubuntu and uses this "Two Server System"? Or is it not that normal/custom???

    Looking forward to hear from you... ;)

    .
     
  10. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    Enable log file and viewing the log file.

    Hi Till,

    Thanks for your reply...

    1.
    Tried to enable the general log file and then restart the service. After that I tried to view the log file..
    No such file or directory...
    Its called the "general_log_file" in "my.cnf" but is to be stored in the "/mysql.log" just like you said.

    Heres my "my.cnf" if you would take a look and help me get the log file running. I've been reading MytSQL faq's for information and it seems to be the right file we are talking about: http://dev.mysql.com/doc/refman/5.5/en/query-log.html

    "my.cnf":
    Code:
    root@myispserver1:/home/administrator#[COLOR="Red"] tail -n 100 /var/log/mysql/mysql.log[/COLOR]
    [COLOR="Red"]tail: cannot open `/var/log/mysql/mysql.log' for reading: No such file or directory[/COLOR]
    
    root@myispserver1:/home/administrator# [COLOR="Red"]vi /etc/mysql/my.cnf[/COLOR]
    
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    #
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    a escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    [client]
    port            = 3306
    socket          = /var/run/mysqld/mysqld.sock
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket          = /var/run/mysqld/mysqld.sock
    nice            = 0
    
    [mysqld]
    # ***** ADD'S TWO SERVER SETUP PAGE 48 *****
    server-id = 1
    replicate-same-server-id = 0
    auto-increment-increment = 2
    auto-increment-offset = 1
    master-host = 192.168.0.102
    master-user = slaveuser
    master-password = ********
    master-connect-retry = 60
    expire_logs_days = 10
    max_binlog_size = 500M
    log_bin = /var/log/mysql/mysql-bin.log
    # ***** END ADD'S TWO SERVER SETUP PAGE 48 *****
    #
    # * Basic Settings
    #
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    #bind-address           = 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer              = 16M
    max_allowed_packet      = 16M
    thread_stack            = 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit       = 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    general_log_file        = /var/log/mysql/mysql.log
    general_log             = 1
    #
    # Error log - should be very few entries.
    #
    log_error = /var/log/mysql/error.log
    #
    # Here you can see queries with especially long duration
    #log_slow_queries       = /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    #server-id              = 1
    #log_bin                        = /var/log/mysql/mysql-bin.log
    #expire_logs_days       = 10
    #max_binlog_size         = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    #
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet      = 16M
    
    [mysql]
    #no-auto-rehash # faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer              = 16M
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/
    
    
    A "User" and a "Master-User" at the same time - "user = mysql" is no problem, or????

    2.
    Don't you think that there's others who uses ubuntu and uses this "Two Server System"? Or is it not that normal/custom???

    Looking forward to hear from you... ;)

    .
     
  11. till

    till Super Moderator

    User is the username of the Linux shell user that mysql is running under, master_user is the name of the mysql user on the master server that is used for replication. So there is no conflict here.

    The mirror guide is written for debian squeeze, debian sueeze uses a different mysql version (5.1) then The latest ubuntu that you use (mysql 5.5), the mysql replication configuration seem to have changed from mysql 5.1 to 5.5, so the way mysql has to be configured is different.

    You can always try to use a guide for a linux distribution and version were it is not written for but this can cause problems or may require different configurations like with mysql here.
     
  12. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    Solution? Use Debian, switch to old MySQL or wait for new "tutorial"?

    Hi Till,

    So, whats the solution do you think?
    Use Debian, switch to old MySQL or wait for new "tutorial" here on HowToForge??? ;)

    "Single Server System"
    I read the manual, of course.. several times, but would you please tell me this:

    1. If I use "Single Server System" what do I need to backup to be able to restore the system fast, in case of a hardware crash?????

    MySQL database, individually?
    HMTL files for websites?
    System configuration???
    ISPconfig3 setup/configuration???? Is it possible or do you have to start from scratch?
     
    Last edited: Aug 6, 2013
  13. till

    till Super Moderator

    If you like to use debian, then you must use squeeze (old-stabkle) as described in the guide and not wheezy as wheezy has the same new mysqld. We will publish a new mirror tutorial for wheezy here at howtoforge.

    Please see here for a backup script:

    http://www.howtoforge.com/forums/showthread.php?t=41609
     
  14. danhansen@denmark

    danhansen@denmark HowtoForge Supporter

    Over and out....

    Hi Till,

    Thanks for your reply ;)


    If you like to use debian, then you must use squeeze (old-stabkle) as described in the guide and not wheezy as wheezy has the same new mysqld. We will publish a new mirror tutorial for wheezy here at howtoforge.

    I like UBUNTU! Any chance of a mirror tutorial any time soon? I'll stop trying to make this work now. Both you and I have used a month trying to solve the issue.. I can't take more of your time and theres no more time for "development" now, I need the system up and running within 40 hours. Therefore I will go back to the "single server system" and then I'll cross my fingers in hope of a "Ubuntu Mirror Tutorial" some time in the near future ;)
    So, thanks for all your help - to bad it didn't work out ... But thanks anyroad... :eek:

    1. If I'm using a Windows 7 HP Workstation and want to place my backup from "Ubuntu-Server.12.04.2-ISPconfig3" "Single Server System" - then I must make a "SAMBA" share and mount the location where I want the backup to be stored. But, is there a "right way" to setup SAMBA on a UbuntuServer12.04.2-ISPconfig3 "Single Server System" ???

    2. If a VIP website on the "Ubuntu-Server.12.04.2-ISPconfig3" "Single Server System" contains both webfiles and a MySQL database is it then possible to make a backup which contains everything ? Or do I have to make separate backup's of the MySQL database?
    In ISPconfi3 there is a backup funktion which is able to make backups every day and up to 10 different copies, i think it is... But, This only backup's the website and the website MySQL and not the ISPconfig3 files/databases. But this is where I get lost! I just can't figure out exactly what I have to cabkup to be able to "restore" the backup back in to a "New computer" or "a blank harddrive" and then make ISPconfig3, the websites and database run again in a short time..
    .
     
    Last edited: Aug 9, 2013
  15. till

    till Super Moderator

    The next guide that I will write is for debian wheezy. I guess that it will work for current Ubuntu then as well as whezy and ubuntu 12.x use the same mysql major version.

    I wont setup a samba server if I were you. Why dont you download the backup with a ftp or scp client? You can also use the other way and upload the backup from ubuntu server to your windows desktop with a cronjob.

    In Post #33 of this thread I posted a link to a backup script that backs up all data of your server inkl. the ispconfig files and database.
     

Share This Page