StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Replication was working, now broken
text
Body
copied!<p>So a month+ ago I setup replication from one of my web servers to m local LAN servers. I had 2 local LAN servers that were slaves (quad and hex) to the master web server (falcon). For weeks the replication worked without issue.</p> <p>Then I noticed earlier this week that the I/O thread wasn't running on either quad or hex. So I restarted them over and over and over again because every so often it would look like everything would run normally, for at least one of the slave, for up to several minutes. Then the I/O thread would stop working again. I have attempted to reconfigure the master on both the LAN machines via phpmyadmin and phpmyadmin reports that the slave is now connected to the master. phpmyadmin gives me the option now (that it wasn't yesterday) of synchronizing the slave DBs with the master's. I run it and in a few seconds the page stops loading but no response (error or otherwise) is given.</p> <p>All of the machines involved are running a LAMP stack on Ubuntu 12.04. The web server is 12.04 server while the LAN machines are both 12.04 desktop. I suspected that I had run apt-get update/upgrade on the LAN machines while neglecting to do so on my falcon. So I ran it on all three machines this morning. Still the I/O thread won't run.</p> <p>The SQL thread is always running. Occasionally, the master shows that one of the slaves (hex) is connected. But it doesn't stay connected.</p> <p>Like I said this worked for weeks without issue. Then very recently stopped. So that is why I suspect something in a recent update/upgrade that broke it all.</p> <p>Anyone know how to ix this?</p> <p>Thanks</p> <p>Added per request - my.cnf from falcon:</p> <pre><code># # 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 # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = xxxx 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] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = xxxx 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 = xxx.xxx.xxx.xxx # # * 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 # Replication per phpmyadmin - falcon is the MASTER server-id=8503668 log-bin=mysql-bin log-error=mysql-bin.err # # * 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! # # 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/ </code></pre> <p>Also, added per request, the lines from my.cnf on quad that set it up for replication</p> <pre><code># Replication per phpmyadmin server-id=1369932901 report-host=quad replicate-ignore-db=visits </code></pre> <p>The rest of the my.cnf is default except for the bind address which is quad's LAN IP 192.168.1.70. The my.cnf on hex the same way the server-id is different, report-host is hex and it ignores the visits DB. And, like quad, the bind-address is the local LAN IP of 192.168.1.60.</p> <p><strong>Weekend Update:</strong> This question floated around for 4 days without an answer as to how to fix a broken replication that once worked. I've posted the my.cnf (or at least relevant sections of it) but no one has had any suggestions on what to fix. Of the 4 days this question has been around at this point 2 were weekend days so I didn't expect much.</p> <p>Oddly enough last night I did a full slave start on both slaves for the 100th time and it finally took. Both slaves ran all night without issue and are now reporting that they are caught up with the master (0 seconds behind). I created a junk DB on the master to test that everything was in fact working. And it showed up in both slaves almost immediately.</p> <p>I still don't have any idea why it stopped working in the first place. It was suggested that MySQL replications need be monitored frequently. Is there a good tool out there for this purpose? (The slaves are Ubuntu and I code primarily in PHP.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload