master master setup

[mjh5598]

I have seen the tutorial regarding master slave mysql setup - but can anyone assist in pointing me to a setup on how to get master master setup functioning - with a sync utility between servers if the servers go out of sync and i wish to use my functioning server to sync to the out of sync server ?

thanks

[federated_life]

check out pt-table-checksum and pt-table-sync to check for and maintain consistency. If you have a lot of data, you'll have to throttle checksum'n.

useful options Ive found is with pt-table-sync, the print and test options. There's also a detailed section about master-master.

for pt-table-sync, you'll have to query the checksum tables to see where checksum's do not match

[mjh5598]

thanks - but do you know where there is a tutorial on how to run those utilities ?
I have downloaded them and getting an error.

I have entered the the username/password for master1 as m1/pas and for master2 as m2/pas in the [client] sections of both my.cnf files.

I get this error:

pt-table-sync --execute h=192.168.62.25,D=mailserver,t=virtual_users h=192.168.62.121
DBI connect('mailserver;host=192.168.62.25;mysql_read_ default_gr oup=client','',...) failed: Access denied for user 'm1'@'bbmachine.local' (using password: YES) at /usr/bin/pt-table-sync line 1382

I think my issuse is for some reason it adds the 'bbmachine.local' ending to the name - do not know how to stop this. Further - i do not think it should matter because i have 'm1'@'%' -- so that user can login from anywhere - I did not really give the user any further rights then the article - http://www.howtoforge.com/mysql_mast...er_replication - gave the user.

thanks -- any tutorial you can point me to would be appriciated.

[mjh5598]

i am begining to think my problem is that i do not understand replication that well.
On master1 - i should have m1 and pass user
On master2 - i should have m2 and pass user

But i am finding out that it is the exact opposite - that user info should only be in the /etc/my.cnf for the partner machine - the real breakdown is :


On master1 - i should have m2 and pass user
On master2 - i should have m1 and pass user

But confused as to what privilages should they be granted - can you help with that - i think i just gave them replicate privilage - thanks

[scott.nemes]

Running a master-master setup can be tricky, and it sounds like your experience is limited, which will likely be an issue especially if this is a production setup.

What are your actual requirements and goals for this setup? You may be able to accomplish them in a simpler fashion.

[mjh5598]

I am running master master setup because i have 5 large databases (6G each) that i need up running constantly.
I would like to load balance between both physical servers.
I am running mostly MyISAM databases on debian squeeze.
I really need to be able to recover quickly - that is why i am looking at master master setup as well as the fault tolerance of the two physical servers.
I do not know what I will be using as a load balancer for apache, postfix,bind,and asterisk.

If you can recommend anything for a fault tolerance setup - I would appriciate it.
thanks

[federated_life]

create a user with localhost as the host for your user on that local machine. you shouldn't have to sync your data if you just setup master master. and youll want to check it first with pt-table-checksum anyways. myisam dual active masters could be troublesome depending on your application, youll probably not want primary keys on the replicated tables. mysql proxy could load balance.

you might want to check out xeround or rds to save yourself some trouble

[mjh5598]

but should i create this user on the server that is out of sync and run the utility from there? or run it from the true master server with the updated information ?
What rights should i assign - i guess this user would have full rights to the tables, yes?

[scott.nemes]

If your write traffic is minimal enough to not require splitting those out between multiple servers, I would recommend a simpler master-slave setup. Have the writes go to the master, and balance reads between the master and the slave. Then if the master fails, you can point the writes to the slave as well as all the reads until you get the master fixed up.



If you want to balance the writes as well as reads without having to deal with duplicate key issues, you could have all 6 databases on each server, but have all the writes for 3 of the databases go to Server A, and then all writes for the other 3 databases go to Server B, and then have them replicate their changes to the other server. So each server ends up with full copies of all databases, but you do not have to worry about collisions. Then if one server fails, you just have to re-point the reads and writes for the failed server back to the remaining server.

Then since both servers would have full copies of all six databases, you could balance the reads across both of them.

That setup would give you the redundancy you want along with balancing both writes and reads, with a less complicated master-master setup since you are not writing to the same database on two servers.



For the replication user, you need to create the user on the master server. So if you end up doing master-master, you'd create the user on both. The user needs the REPLICATION SLAVE privilege.

If you already know that your second server is out of sync, your probably better off just taking a backup of your main server and creating the second server with that.

[mjh5598]

thanks for the input - so you are not recommending master master setup at all? even with the utilities provided by percona ? when would you use a master master setup?

do you know how to setup an easy dsn for executing these utilities? i have tried to put the username and password in the my.cnf file - but does not work. i thought i gave replication slave to the user, but i get replication safety warnings that the changes are not going through.
Is there a simple example of master master that i can follow with the percona utilities as far as replication and dsn is concerned ?

thanks

[federated_life]

replication safety warnings ? can you provide specifics...


master to master replication works. two active masters also works. however your application needs to take this into account, and you need to know at least a little bit of what your doing.

[scott.nemes]

There is nothing wrong with master-master specifically, it is just a more complex setup, and often not necessary. If you combine that with lack of general knowledge about MySQL and/or replication, you can get yourself into trouble pretty quick.

As far as setting up master-master, you need to set a few configuration options on each (auto_increment_increment and auto_increment_offset), and then you would follow the same steps for setting up a normal slave, except you do that on both. So setup Server A as a slave of Server B, and setup Server B as a slave of Server A.

I'd suggest reading up on MySQL replication in general before getting too far into this, as unless you fully understand what is going on there will likely be issues down the road.

[mjh5598]

i think i narrowed down my issue -
I have setup master master replication as per :
http://www.howtoforge.com/mysql_mast...er_replication -
Replication works fine - i created records and they are getting transfered well..form both servers.

I am getting access denied errors when i run:
pt-table-sync --execute h=m1,D=mailserver,t=virtual_users h=m2

Specifially this in the log:

120608 15:50:32 417 Connect slave2@bbmachine.local on mailserver
417 Init DB Access denied for user 'slave2_user'@'%' to database 'mailserver'
418 Connect slave2_user@bblue.local on mailserver
418 Init DB Access denied for user 'slave2_user'@'%' to database 'mailserver'

Do i have to give these utilities more rights then what the article gave the replication user ??

[scott.nemes]

Yes the user for pt-table-sync would need more permissions as it will be attempting to modify data.

I'd still recommend just reloading your slave with a new snapshot however as if you already know it's out of sync that is not a great way to start a new setup like this. Then just start using pt-table-checksum regularly to check for slave drift and you'll be good to go.

If you are still going to be using pt-table-sync, make sure to read the documentation carefully, especially regarding using it in a replication setup. Otherwise you could end up destroying your master as well if you are not careful.

http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync .html

[mjh5598]

sorry..the link you gave me does not have any permission settings.
Does it need full permissions to the other master table ?