Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 18th August 2010, 02:45
scottrill2 scottrill2 is offline
HowtoForge Supporter
 
Join Date: Dec 2009
Posts: 93
Thanks: 21
Thanked 2 Times in 1 Post
Default MYsql replication questions.

Have a question about replication. I read this statement on MYsql's website:

"•Data security - because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data. "


Question 1: Does this mean everytime a backup script is run in ispconfig 2 there is a chance I could corrupt my databases? Is it a minute possiblity or is it fairly common?



Question 2: I found a few different replication tutorials on HowToForge, one covering master-slave and one covering master-master. Is a master-master vulnerable to the same corruption? Is a master-slave scenario safer all the way around?



Question 3: In falko's tutorial "http://www.howtoforge.com/mysql5_master_master_replication_debian_etch" he mentions:


"The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master"

Does this mean if I use a master-slave replication I have to tell ISPConfig 2 to only wrtite to the master or something like that?



Thanks as always folks,

Scott

Last edited by scottrill2; 18th August 2010 at 03:13. Reason: Had another question.
Reply With Quote
Sponsored Links
  #2  
Old 18th August 2010, 10:31
Mark_NL Mark_NL is offline
Senior Member
 
Join Date: Sep 2008
Location: The Netherlands
Posts: 912
Thanks: 12
Thanked 100 Times in 96 Posts
Default

Quote:
Originally Posted by scottrill2 View Post
Have a question about replication. I read this statement on MYsql's website:
"•Data security - because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data. "
Question 1: Does this mean everytime a backup script is run in ispconfig 2 there is a chance I could corrupt my databases? Is it a minute possiblity or is it fairly common?
No, because you're just stopping the slave to get a consistent snapshot of the database at that exact moment. when you do that on a master, inserts/deletes still work so you could get incosistent data (corrupt).

Quote:
Question 2: I found a few different replication tutorials on HowToForge, one covering master-slave and one covering master-master. Is a master-master vulnerable to the same corruption? Is a master-slave scenario safer all the way around?
master-slave is much safer .. if you make one mistake with master-master you can mess up your complete database, besides that i'm not a fan of m-m setup's .. it can give you a lot of problems (think of collisions when writing the same data/record at the exact same time, no locking through replication) .. i rather have a mysql cluster when a master-slave is not enough.

Quote:
Question 3: In falko's tutorial "http://www.howtoforge.com/mysql5_master_master_replication_debian_etch" he mentions:

"The advantages of master-master replication over the traditional master-slave replication are that you don't have to modify your applications to make write accesses only to the master"

Does this mean if I use a master-slave replication I have to tell ISPConfig 2 to only wrtite to the master or something like that?
Yes, you make the slave read-only (my.cnf: 'read-only' setting)
now you tell your applications to shoot inserts, deletes, updates etc to the master, and selects to the slave .. or use your master for everything, and just keep your slave solely for backing up your data.


Quote:
Thanks as always folks,

Scott
Your welcome !
Reply With Quote
  #3  
Old 18th August 2010, 22:54
scottrill2 scottrill2 is offline
HowtoForge Supporter
 
Join Date: Dec 2009
Posts: 93
Thanks: 21
Thanked 2 Times in 1 Post
Default Thanks!

Thanks alot for the answers Mark,

I think I will start off with just a slave for backups only. Then as I get better with Linux, I will try the whole splitting commands between the master and slave thing lol


Scott
Reply With Quote
  #4  
Old 19th August 2010, 08:50
Mark_NL Mark_NL is offline
Senior Member
 
Join Date: Sep 2008
Location: The Netherlands
Posts: 912
Thanks: 12
Thanked 100 Times in 96 Posts
Default

You could write a mysql class like i did .. what it does it checks if the query starts with SELECT or something else, depending on what it runs the query on the correct server.

good luck!
Reply With Quote
  #5  
Old 31st August 2010, 21:03
scottrill2 scottrill2 is offline
HowtoForge Supporter
 
Join Date: Dec 2009
Posts: 93
Thanks: 21
Thanked 2 Times in 1 Post
Default A few more questions if I could.

Hello all,

I encountered an error on my slave. A few questions I would ask if possible:

1. Error was this:



Aug 31 12:11:16 server2 mysqld[1933]: 100831 12:11:16 [ERROR] Slave: Error 'Duplicate entry 'random data' for key 1' on query. Default database: 'web4db1'. Query: 'INSERT INTO `jos_session` ( `session_id`,`time`,`username`,`gid`,`guest`,`clie nt_id` ) VALUES ( 'random data','random data','','0','1','0' )', Error_code: 1062



Aug 31 12:11:16 server2 mysqld[1933]: 100831 12:11:16 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 8683



I several sites installed on ISPConfig some playing around with Drupal, some with joomla etc etc. For instance web1db1 and web4db1 both are Joomla so both have a table "jos_session" since they are completely different databases that shouldn't be a problem correct? What usually causes this error and where should I investigate first?


2. While researching this error some sites have said to insert "slave-skip-errors = 1062" into config file for slave. Is this wise or just mearly a bandaid type of thing?


Thanks as always for the help folks,

Scott
Reply With Quote
  #6  
Old 31st August 2010, 23:08
Mark_NL Mark_NL is offline
Senior Member
 
Join Date: Sep 2008
Location: The Netherlands
Posts: 912
Thanks: 12
Thanked 100 Times in 96 Posts
Default

skipping slave errors should only be used if you know the problem that occured won't be of any problem for the rest of the replication .. the error tells you that there is already a record in that table with the same key ..

1. Do you have "read only" in your slave config?
2. Remember that a SUPER user is exempt for "read only" (they can always edit data on a read only server)
3. if you're sure you can continue do:

SLAVE_SKIP_ERRORS=1;
START SLAVE;
SHOW SLAVE STATUS;

and check if the "Seconds Behind master" field is decreasing..

good luck.
Reply With Quote
  #7  
Old 2nd September 2010, 14:10
scottrill2 scottrill2 is offline
HowtoForge Supporter
 
Join Date: Dec 2009
Posts: 93
Thanks: 21
Thanked 2 Times in 1 Post
Default Man this is sad.

Well I think I must have horrid mysql karma or something lol



So I followed your instructions, all seemed well. I then saw a new tutorial by falko of setting up master slave replication with ssl. Now I'm just on a private network playing around and stuff so I don't NEED security lol but figured what the hell lets play around with it.

Now I get this error in syslog:



Sep 2 05:00:05 server2 mysqld[4567]: 100902 5:00:05 [ERROR] Slave I/O thread: error connecting to master 'slave_user@10.1.10.123:3306': Error: 'Access denied for user 'slave_user'@'10.1.10.124' (using password: YES)' errno: 1045 retry-time: 60 retries: 86400


After searching the forums I found a post where it was possibly a firewall problem. Now before the ssl the slave was working just fine until it hit that duplication error, so I wouldn't think it is a firewall issue. I found this command as well:

mysql -u slave_user@10.1.10.123 -pmypassword

This returns with this error:

ERROR 1045 <28000>: Access denied for user 'slave_user@10.1.'@'localhost' <using password: YES>

It cuts off the host after 5 digits, don't know if that is normal either?


So any ideas folks?



Thanks as always for your time,



Scott
Reply With Quote
  #8  
Old 3rd September 2010, 17:53
scottrill2 scottrill2 is offline
HowtoForge Supporter
 
Join Date: Dec 2009
Posts: 93
Thanks: 21
Thanked 2 Times in 1 Post
 
Default lol alls well that ends well.

Well when I followed the tutorial I copied and pasted falko's commands into notepad then made my changes to db names, ip's etc and saved the file. I went back through it and there were no typos.



I went through both master and slave and reversed all changes I had made. then opened up notepad and repasted the commands from the tutorial, the error went away lol


I'm thinking maybe I grabbed an extra space or something copying and pasting back and forth between notepad, thats the only thing I can think of.


Thanks as always folks,

Scott
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Squirrelmail login failure Cracklefish Installation/Configuration 9 30th June 2010 20:38
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 13:58
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
ISPConfig server also a MySQL replication slave jamesh Installation/Configuration 1 2nd August 2006 09:59
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 16:04


All times are GMT +2. The time now is 07:02.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.