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
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). 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. 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. Your welcome !
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
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!
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`,`client_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
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.
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 '[email protected]: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 [email protected] -pmypassword This returns with this error: ERROR 1045 <28000>: Access denied for user '[email protected].'@'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
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
