Re: Promoting MySQL 5.5 slave to master
2013/3/21 Miguel González Castaños miguel_3_gonza...@yahoo.es Dear all, I have a pretty simple setup. A LAMP server in production and a failover LAMP server in case the main server is down. I have been searching around to find out a clear answer of how to proceed when you need to promote a MySQL 5.5 slave server when the master (also 5.5) has crashed. So far I have read that if the master crashes I should issue an stop slave in the slave server and restart the mysql service (otherwise the master info is still kept in memory) with a skip-start-slave setting. Is that right? Is that all? Hi Miguel, Do you have a master-master replication between them? How is the failover over done? Do you just move a VIP between them? Anyways, there's no need to do stop slave or restar MySQL service in the new master...you just need to set it active (move the VIP, pointing your Apache manually to write to it, however you do it). What you have to make sure though is about the read_only parameter. Make sure the new master comes back to life with read_only = ON just to avoid any problems. The standby master should always have read_only = ON until it becomes active. Manuel.
Re: Promoting MySQL 5.5 slave to master
2013/3/21 Miguel Gonzalez miguel_3_gonza...@yahoo.es Can you elaborate about this? I thought that once you fixed the issues in the master server you needed to set it as slave of the new promoted master server, and do the other way round. That's why you might want to have master-master replication to avoid this manual process every time the active master dies. Anyways, you're right - you'd need to configure the dead master to become a slave of the new master. In order to do so, you need to now the binlog file and position the new master had before getting the VIP. You'd need to check the binlogs. By having both MySQL replicating from each other, you'd avoid this. Make sure you do reply all instead of replying only to me :-) Manuel.
RE: Promoting MySQL 5.5 slave to master
-Original Message- From: Miguel Gonzalez [mailto:miguel_3_gonza...@yahoo.es] Sent: 21 March 2013 08:29 To: Manuel Arostegui Cc: mysql@lists.mysql.com Subject: Re: Promoting MySQL 5.5 slave to master - Mensaje original - De: Manuel Arostegui man...@tuenti.com Para: Miguel Gonzalez miguel_3_gonza...@yahoo.es CC: mysql@lists.mysql.com Enviado: Jueves 21 de marzo de 2013 9:17 Asunto: Re: Promoting MySQL 5.5 slave to master 2013/3/21 Miguel Gonzalez miguel_3_gonza...@yahoo.es Can you elaborate about this? I thought that once you fixed the issues in the master server you needed to set it as slave of the new promoted master server, and do the other way round. By having both MySQL replicating from each other, you'd avoid this. I'm trying not to overcomplicate things. Also the server where the replica of the production server is is not active at all. I thought master-master configuration where more common when you were behind a web balancer. I thought also that replication had changed in MySQL 5.5. Is that difficult to find a clear procedure for performing a mysql failover when a server crashed? [AM] If you've the option to use MySQL 5.6 then managing replication is a lot simpler and more reliable... http://www.clusterdb.com/mysql-replication/mysql-5-6-ga-replication-enhancements/ MySQL 5.6 Failing over is described in section 5 of http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/ - note that the paper deals with a more complex scenario where there are multiple slaves and so you could simplify. Make sure you do reply all instead of replying only to me :-) Sorry about that, I thought the list was configured differently Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Promoting MySQL 5.5 slave to master
[AM] If you've the option to use MySQL 5.6 then managing replication is a lot simpler and more reliable... http://www.clusterdb.com/mysql-replication/mysql-5-6-ga-replication-enhancements/ MySQL 5.6 Failing over is described in section 5 of http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/ - note that the paper deals with a more complex scenario where there are multiple slaves and so you could simplify. I don't normally rush when it comes to upgrading services. MySQL 5.6 has been recently released and I prefer until the RPM package has been included in most YUM repos and it has been throughly tested. I still remember how painful was to install a new system with MySQL (5.5 I believe) under Ubuntu last May. I had to report a bug and make some tweaks in order to get the mysql service installed. Regards, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Promoting MySQL 5.5 slave to master
- Mensaje original - De: Manuel Arostegui man...@tuenti.com Para: Miguel Gonzalez miguel_3_gonza...@yahoo.es CC: mysql@lists.mysql.com Enviado: Jueves 21 de marzo de 2013 9:17 Asunto: Re: Promoting MySQL 5.5 slave to master 2013/3/21 Miguel Gonzalez miguel_3_gonza...@yahoo.es Can you elaborate about this? I thought that once you fixed the issues in the master server you needed to set it as slave of the new promoted master server, and do the other way round. By having both MySQL replicating from each other, you'd avoid this. I'm trying not to overcomplicate things. Also the server where the replica of the production server is is not active at all. I thought master-master configuration where more common when you were behind a web balancer. I thought also that replication had changed in MySQL 5.5. Is that difficult to find a clear procedure for performing a mysql failover when a server crashed? Make sure you do reply all instead of replying only to me :-) Sorry about that, I thought the list was configured differently Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key on multiple columns
On 2013-03-21 8:12 AM, Norah Jones wrote: I'm trying to create a foreign key on two columns but getting error... Here's what I tried: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)` Dupe reference column. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
compiling mysql-5.6.10 on solaris10 64bit sparc
Hi, Im trying to compile mysql-5.6.10 for Solaris10 for sparc, I used the following parameters for cmake to get it to work, but at 80% the compilation fails. WITH_INNOBASE_STORAGE_ENGINE=1 DEFAULT_CHARSET=utf8 CMAKE_C_FLAGS=-m64 CMAKE_C_COMPILER=/opt/csw/bin/gcc CMAKE_CXX_FLAGS=-m64 CMAKE_CXX_COMPILER=/opt/csw/bin/g++ Other options are default. The error which is thrown to me is the following [ 80%] Built target pfs_user-oom-t [ 80%] Built target mysqld_dtrace_all [ 80%] Built target mysqld [ 80%] Built target mysqlservices Linking CXX shared module semisync_master.so ld: fatal: file /export/home/builder/manual/mysql-5.6.10/mysql-5.6.10/plugin/semisync/CMakeFiles/semisync_master.dir/semisync_master_dtrace.o: wrong ELF class: ELFCLASS64 ld: fatal: file processing errors. No output written to semisync_master.so *** Error code 1 The following command caused the error: cd /export/home/builder/manual/mysql-5.6.10/mysql-5.6.10/plugin/semisync /opt/csw/bin/cmake -E cmake_link_script CMakeFiles/semisync_master.dir/link.txt --verbose= make: Fatal error: Command failed for target `plugin/semisync/semisync_master.so' Current working directory /export/home/builder/manual/mysql-5.6.10/mysql-5.6.10 *** Error code 1 The following command caused the error: make -f plugin/semisync/CMakeFiles/semisync_master.dir/build.make plugin/semisync/CMakeFiles/semisync_master.dir/build make: Fatal error: Command failed for target `plugin/semisync/CMakeFiles/semisync_master.dir/all' Current working directory /export/home/builder/manual/mysql-5.6.10/mysql-5.6.10 *** Error code 1 The following command caused the error: make -f CMakeFiles/Makefile2 all make: Fatal error: Command failed for target `all' From my understanding I have an issue with wrong ELF class: ELFCLASS64 and since the object is semisync_master_dtrace.o: ELF 64-bit MSB relocatable SPARCV9 Version 1 my guess would be there is some mixup with 32 and 64bit somewhere, but to be honest I have no clue where to start to fix this issue, which is why I came here ;). Any help is greatly appreciated, thanks in advance, cheers Felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key on multiple columns
On 3/21/2013 12:43 PM, Abhishek Choudhary wrote: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; i think error is because of referencing the same column in test1 table (ID,ID) . try to change the column name then run the code hope ur problem will solve out . Another solution would be to make two FK declarations, one for each column. CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1) REFERENCES test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT , CONSTRAINT fk2 FOREIGN KEY (col2) REFERENCES test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; This is the preferred syntax and it meets your original intent of associating both col1 and col2 to the ID column of the other table. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key on multiple columns
CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; i think error is because of referencing the same column in test1 table (ID,ID) . try to change the column name then run the code hope ur problem will solve out . Abhishek choudhary, www.tech4urhelp.blogspot.com - Original Message - From: Peter Brawley peter.braw...@earthlink.net To: mysql@lists.mysql.com Cc: Sent: Thursday, 21 March 2013 8:14 PM Subject: Re: Foreign key on multiple columns On 2013-03-21 8:12 AM, Norah Jones wrote: I'm trying to create a foreign key on two columns but getting error... Here's what I tried: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)` Dupe reference column. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
RE: MySQL Error#: 2002
Assuming you copied and pasted the error, it looks like the host made a typo in the config file: '/var/lib/myswl/mysql.sock' Should probably be mysql, not myswl. -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, March 18, 2013 3:32 PM To: Patrice Olivier-Wilson; mysql@lists.mysql.com Subject: RE: MySQL Error#: 2002 Check directory permissions, and check out the 'answers' in here: http://forums.mysql.com/read.php?10,284776,284936 -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: Monday, March 18, 2013 12:05 PM To: mysql@lists.mysql.com Subject: MySQL Error#: 2002 I have about 60 websites based on mysql and php. Suddenly they have all gone blank, just white pages. The files are still on the server and I can see the tables in all the databases via myphpadmin interfact. I'm not getting any response from hosting gods yet. When I try to connect to server via Dreamweaver, the error message is: MySQL Error#: 2002 Can't connect to local MySQL server through socket '/var/lib/myswl/mysql.sock'(111). I have one site that uses a different IP number that that site is ok. My static sites, ie, no database inclusion, are ok. Any ideas what to look for, most appreciated. Regards, Patrice Olivier-Wilson 828-628-0500 http://Biz-comm.com b...@biz-comm.com Everything will be alright in the end, so if it is not alright, it is not yet the end. - Quote from movie: The Best Exotic Marigold Hotel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
