Master / Master Asynchronous Replication
Currently we have a web based application that is mostly reads (4:1
r/w). It is using a single MySQL database server. Is there any way to
have two database servers in a master/master configuration such that
writes to either database server are replicated to eachother. Basically
even though we have a 4:1 ration of read/write the writes happen often
enought that when the database goes down the app stops working. I know
how to get this working in Oracle (insert big laugh here) but Oracle is
cost prohibitive. Any pointers?
View Complete Forum Thread with Replies
Related Forum Messages:
Master Master Replication, Truncate Table Fails, Version 5.0.22
I have set up a master master replication link between two databases, prototyping an idea. Anyhow, adding records and deleting works, both get updated, creating and dropping tables works, but truncating tables doesn't work. My main questions are, why doesn't truncate work, and if it doesn't work, what else doesn't work across a master-master replication link?
View Replies !
Backing Up A Circular Master-master Replication Setup
I have a running circular master - master replication running. I want to run a mysqldump on one of the servers but am worried what the impact of running a mysqldump will be on the replication. Will the transactions generated by the mysqldump get put into the binary log file? I see the option --delete-master-logs for mysqldump and am assuming that it deletes the previously inserted log items from the binary log file. If there is someone that can please just give me a little more clarification on how the --delete-master-logs option works.
View Replies !
Master/Master Replication Problems
I was wondering whether someone could help me. I have inherited set of replicated servers from a guy I used to work with, basic set up is: Master1/Slave2 - Server ID 1 Master2/Slave1 - Server ID 2 When I looked at the logs it looks as though Server ID 2 is well out of step. Having looked at the slave status it appears that that Slave_SQL_Running state is set to No and a particular piece of SQL is causing the problem. Having ran the SQL on the particular server it has now problem at all. Now my understanding of this type of set up is that Server ID 1 writes directly to Server ID 2, then Server ID 1 is in turn replicated to. But for the life in me, I cannot understand why this piece of SQL is causing the problem. Can anyone offer me any ideas on how I can best get to the bottom of this, I'm really struggling with this. There was me thinking replication just worked! Is there an ability to get MySQL to log all queries it is running, so I can get to the bottom of all queries currently being executed?
View Replies !
Replication From Master To Slave
I'm having quite a fuss with my two MySQL DB servers lately. I'm trying to establish replication between a MySQL 4.0.20 server (master) and a MySQL 4.1.11 server (slave). From what I read, replication from a 4.0.x server -> 4.1.x server will work, just not the other way around. However, that's not really my problem. After unsuccessfully completing the configuration myself, I used ....
View Replies !
Dual-master Replication
I would like to set up a dual-master replication setup for my MySQL database (running on RedHat 9). Could someone please point me to appropriate website that details this setup process and management as well (i.e. what should I do if one of the masters goes down?). Also, are there any solutions that allow auto-increment fields in this kind of setup?
View Replies !
Replication :: Run RESET MASTER
If I run RESET MASTER on the primary MySQL server (the master) will it wait till the slaves are up to date before deleting the bin files or do you think my slaves will get screwed up?
View Replies !
Master Slave Replication
I am setting up a Master/Slave system so that the data will always be backup up safely when any changes are done. Now - I am not sure to what extent the replication extends to. I know it is fine for Data but what about these scenario's 1. Master table structure changes - are these replicated 2. Trigger/Stored Procedure added or changes - are these replicated 3. View or exisiting view creates/modified - are these replicated.
View Replies !
Replication Master & Slave On The Same Machine
I have set up a master and a slave according to the manual. The master runs 4.0.12 and the slave runs 4.0.14, they are both actually on the same machine (it's a long story, but it's what I need). I did LOAD DATA FROM MASTER on the slave which completed successfully, and then I tried SLAVE START: ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO but.. it is! on the slave: Code:
View Replies !
Master/slave Replication Error
I have set up master/slave replication between 2 servers (1 master, 1 slave). However, when I change a table on the master (e.g. by inserting a row), the same table on the slave is not updated. The strange thing is that if I enter SHOW SLAVE STATUS on the client, it gives me the same offset in the binary log on the master as the master gives when issuing SHOW MASTER STATUS (both entered after the update). Code:
View Replies !
Replication Master Crashed After 6 Hours
I recently setup replication, the master and slaves appears to work fine initially. After about 6 hours, the master mysqld process will start to consume 99% CPU and the load (avg. load per minute) will go to 3, 4, 6, 15, 24, 56, 312... in a matter of seconds! The master then stops responding and needs a reboot. This problem is reproducible, I have tested a few times. This is a screenshot of "top" command just before the master died: http://choonkeng.hopto.org/temp/replication-hang.gif There are plenty of memory and no disk swapping when the master goes 99%. Error and slow logs don't show any useful information.
View Replies !
Master Replication Automatic Failover
I am having one master and three slaves. If my master fails suddenly then one of my slave has to become master automatically on any platform (windows/linux). Can you please suggest the process to do this. Note: Manually i know how to do this task, but i need to be do this automatic.
View Replies !
Multi-Master Cross OS Replication
I normally run two Linux MySQL servers in multi-master replication and it has worked fine. Now I have had one of the machines die on me. The problem is that I want to now have that same replication configuration between my Linux and Windows machine which both run MySQL. The configuration on the linux machine I don't really have trouble with at all, but editing the my.ini file on windows to setup the master host causes the service to fail to start. So my question is what is the appropriate configuration of my.ini on windows to facilitate multi-master replication? Many hours of googling produced limited results/tutorials, none of which were helpful (most of which were for Linux which again I am familiar with already).
View Replies !
Muti Master Server Replication
I need some help in setting up multi master server replication setup fpr my project. Till now I have set up single master replication, but had not been able to manage anythin on mulitimaster setup.
View Replies !
Master/slave Setup To Do Replication
using mysql 4.1.7, have a master/slave setup to do replication. Is there anyway to prevent the slavedb from accepting inserts other than the replication inserts? If rows do get inserted directly into the slave, there is an error because the two databases no longer match. Is there anyway to recover from this?
View Replies !
Dual Master Replication In MySQL 4.1
I have been charged with the task of setting up dual master replication with our company's MySQL servers. I know that their are inherent auto_increment problems with this set up, and the mechanisms to fix this problem weren't introduced until version 5.0. Can anyone give me some tips, pointers, or advice for safely setting up this replication topology in 4.1? Are there any steps I can take to make sure there are no auto_increment issues? Has anyone had experience setting this up?
View Replies !
Replication With Master And Several Slaves/Masters
At the company I'm working (a road survey centre) where about to start a new project. We're planning to create web-based application with php, javascript, maybe we're going to use AJAX but certainly combined with a MySQL database to store all of the data. Since users/researchers have to be able to work off-line on a laptop. Our idea was to install, via XAMPP or something like that, the complete package (i.e. Apache, PHP, MySQL) on each laptop. On a main server we have the same but probably a Linux system. Now, I know it is possible to replicate the DB on the laptops with the main DB. But is it also possible to update/replicate the main DB with results from the/a laptop after working a day off-line? So it becomes available on each laptop after they done a replication from the main DB? Maybe a long post for a small question, but I wanted to draw the "context" in wich it is going to be used. A question you are about going to ask me probably is: "why they have to be able to work off-line?" Quite simple: Not everywhere in the world you have wireless internet access + the onsite laptops need to communicate on site with serial devices GPS, Gyroscope, and such like. Wich is for a webbased application running on a server thousand miles rather difficult...
View Replies !
Replication :: Error Connecting To Master
Show master status on the master looks fine. When I start mysql on the slave, here's what I get: 071017 10:20:35 [Note] Slave SQL thread initialized, starting replication in log 'binary-log.000006' at position 3031, relay log './web1-relay-bin.000003' position: 98 071017 10:23:44 [ERROR] Slave I/O thread: error connecting to master 'replication@myotherserver': Error: 'Lost connection to MySQL server during query' errno: 2013 retry-time: 60 retries: 86400 Master my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/var/db/repl/binary-log binlog-do-db=mydatabase binlog-ignore-db=abunchofotherdatabases server-id=1 [mysql.server] user=mysql #basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Slave My.cnf: [mysqld]...........................
View Replies !
Replication :: Master Log File Position Changed
I have a simple master/slave replication environment that is working smoothly with one exception. If I restart the master server the Master Log File is incremented by one (e.g. the original master log is named data-bin.000001 and the new master log is named data-bin.000002) the slave server never notices that the Master Log File and the Master Log Position have changed. Instead, the slave sits there waiting for new entries in the old Master Log File. I can tell this by executing a SHOW SLAVE STATUS on the slave server. I am not sure about this because I can't find any documentation on the proper behavior of mysql during this scenario, however, I am under the impression that slave server should be able to figure this out and continue replicating, without requiring me to execute an "CHANGE MASTER..." command on the slave server every time the master is restarted. Of course, I assume this same problem will occur when the Master Log File grows to its maximum size and a new Master Log File is created. If anybody who has experience with mysql replication could just confirm that the behavior I am seeing is not correct, that would be great. Or if anybody has any suggestions as to what, if anything, is wrong with my setup. Code:
View Replies !
Replication :: Switching Master And Slave On Failure
I have 2 servers, A and B. A is the primary server and master in mysql. B is the redundant server and the slave in mysql. A is replicating to B. Now A fails and B becomes the primary server (still slave in mysql?). A finally comes back up, but instead of switch primary status from B back to A, A becomes the redundant server and B stays as primary. Now, I need B to be master in mysql and A to be slave in mysql. I don't know if this can be done the way it needs to. If it can I really don't need a step by step process unless someone is really willing to give that to me. I just would like to know if this can be or has been done.
View Replies !
Replication :: Understanding Master & Slave Servers
Can anyone share their experience with replication? In particular I'm wondering how fast (or how slow?) replication occurs. Could I pull off the configuration below? For example, if a person places an order on our site, the update is sent to the master server. Can that update be replicated to the slaves before the browser is taken to a reciept page (which is queried against the slaves)?
View Replies !
Replication :: Show Master Status Return Empty Set
I am trying to set up replication on 2 servers. I am following the procedure on mysql.com. After you have done the grant replication and the snapshot, you have to do SHOW MASTER STATUS; and it is suppose to return informations. But for me, it returns "empty set".
View Replies !
Replication :: Slave Thread: Error Connecting To Master: Access Denied For User
I have MySQL 3.23.41 on a RH-7.2 Linux used as local server. I'm trying to replicate the databases from server (192.168.0.1) to another linux PC (192.168.0.3). Following the manual, i grant to the master a user with file privileges, and edit both my.cnf files. The master starts fine but the slave mysql.log shows up an error: Slave thread: error connecting to master: Access denied for user: replica@localhost' (Using password: YES) (0), retry in 60 sec
View Replies !
Replication, Slave Thread: Error Connecting To Master: Access Denied For User:
I have MySQL 3.23.41 on a RH-7.2 Linux used as local server. I'm trying to replicate the databases from server (192.168.0.1) to another linux PC (192.168.0.3). Following the manual, i grant to the master a user with file privileges, and edit both my.cnf files. The master starts fine but the slave mysql.log shows up an error: Slave thread: error connecting to master: Access denied for user: replica@localhost' (Using password: YES) (0), retry in 60 sec
View Replies !
Master Slave Replication Working But Slave Log Name Stuck?
I have been checking SHOW SLAVE STATUS; and keeping an eye on it. It lags but seems to be still going. However. Today I notice that the Master_Log_File changed from domain-bin.000001 to domain-bin.000002 and the Read_Master_Log_Pos from a really high 6 dig number to a low number, but the Relay_Log_File didnt change. it still says slavedomain-relay-bin.000001 and a really large growing number for: Relay_Log_Pos
View Replies !
Relay-bin On Master?
I am getting relay-bin logs on my master server. They are all empty except for one line that reads: þbin I have no slave settings in my my.cnf file. Any idea where these are coming from and how to turn them off?
View Replies !
Two Master Servers
I am using two master servers that are synchronized with each other so that if one of them gets disconnected, the other one could take over and no data is lost. Now my problem is doing backup on the servers. Say I am doing backup on Master 1 and after backup, I will delete the data. So, since Master 2 is synchronized with Master 1, will the deleted data be written back to Master 1? My batch file is something like this. echo Executing MYSQL BACKUP mysqldump -uroot -proot --result-file=%SAVPATH%%FILENAME%".sql" database1 echo Executing MYSQL DELETE mysql -uroot -proot datalog < "C:Program FilesMySQLMySQL Server 5.0inscript.sql" Script.sql will call the command "DELETE from table1" for me.
View Replies !
Clustering And Master Down
I want to create replication databases with multiple servers, but i need more informations about consequences when the MASTER database crash : what happens ? And, if one slave crash : what happens ? Is it like RAID 5, mysql just work in degraded mode and continue to work, or is it fatal ? Can we compare the replication system by MySQL with the Grid Computing of Oracle ? I don't think MySQL offers a clustering mode for calculating and searching rows, am i wrong ?
View Replies !
Change Master
My query is that is their any possibility that i can change running slave as a master server. I want to switch running slave as a master.
View Replies !
Master-connect-retry
I installed MySQL 4.0.14 on a new Linux machine unde the form of two servers: one master and one slave. I met many unsynchronized events so it was appears conflicts from this late of the changes from master to slave. I put the master-connect-retry to 2. Now it seems to work clearly but I cannot see the master-connect-retry variable with "show variables" command. Is this bad? Shall I have another unsynchronized events in the future between master and slave? I manually repaired each conflict or I did RESET MASTER/SLAVE to escape from the errors' labirint.
View Replies !
MySQL Master/Slave
In the case of 2 3.23.X series mysql databases in a master/slave replication setup (myiasm tables), if a FLUSH TABLES WITH READ LOCK is put on the slave, does the master effectively lock too because it is waiting for the binlog update(s) to be confirmed from the slave, or does the master merrily carry on not caring in the least that the slave tables are locked?
View Replies !
Master/Slave Restoration
I have 2 servers (A and B) setup as Master and Slave respectively. Upon A's failure, all client's request will be directed to B. How can I set A to become master again after its been restored from its failure? In chapter 4.10 of mysql docs, it mentioned (vaguely) that A will become B's slave to sync all operations that had happened after the failure. Finally, upon full sync switch A to become Master again and B to slave. How can I setup A to be a slave of B? The problem I faced is that I can't find out what the bin log and position is from B since "show master status" is always empty. I guess B still thinks its a slave.
View Replies !
Master Child Reporting
Need to create a query that will pull a status field from a child table based on two seperate fields. One is date and the other is time. Can not use datetime field due to restictions in other legacy application. Want to select single related record with the highest date and time.
View Replies !
Master Servers Merge
I want to make query to merge table1 from master server A and table2 from master server B (ex: select * from table1, table2) because my site becomes heavy traffic and database gets bigger. It means I need to separate tables and create new database on other master server B. Do you know how to make "merge" from master server A and B?
View Replies !
Show Master Logs
When would SHOW MASTER LOGS be used? I've had mysql hang 4 times this week and we've done all we can to fix it - but it seems that what we have done has only reduced the backlog of queries that occur when it hangs - now it hung again this morning and the only thing of note in the mysqladmin process list was SHOW MASTER LOGS - I googled it and there seemed to be a few cases of crash related bugs with this particular command - now I know I didnt issue the command so what would have done
View Replies !
Different Errors On Master And Slave.
Replication stopped on slave servers when I executed the below query, and this query failed on master. mysql> UPDATE members set col1 = 1000, col2 = 10 where type = 2; ERROR 1030 (HY000): Got error 127 from storage engine Here is the error message on when I do SHOW SLAVE STATUSG Last_Errno: 0 Last_Error: Query caused different errors on master and slave. Error on master: 'Got error %d from storage engine' (1030), Error on slave: 'no error' (0). Default database: 'subscribers'. Query: 'UPDATE members set col1 = 1000, col2 = 10 where type = 2' members table contains 1485112 rows.
View Replies !
PROBLEM WITH PURGE MASTER LOGS
I have a Server Linux with mysql 4.0.13 version with qmail server. I run the command purge master logs but he return a message of error i/o. The table "relay" on db vpopmail is corrupt and the command repair or restore not function.
View Replies !
Master List Of Select Rows
I am creating a database as part of a service record for items. When an item is serviced, an entry is added to the database with the item serial number and some other info. That all works great. I also want to create a master list of all items. If I use the old "SELECT * FROM database WHERE 1" query, it will display all the rows in the database. Is there any way to retrieve the last row for each item number, instead of displaying all rows for all items? It seems I have worked myself into a corner with this table setup. If it can't be accomplished the way I have it set up, is there a better way to structure the table? I thought about creating another field "type" and have it be either "parent" or "child" and then only display the "parent" rows in the master list. That would involve two queries when updating an item's status (adding a new row and updating the "parent" row for that item), and seems to be less convenient. This seems like a common application so there has to be another way.
View Replies !
Privilege For Purge Master Logs
MySQL Version: 5.0.41 I have a user that has these privileges but is denied when trying to PURGE MASTER LOGS. GRANT SELECT, RELOAD, FILE, SHOW DATABASES, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.*
View Replies !
Procedures To Promote Slave To Master Server
What is the proper procedure for promoting a Slave into a Master replication server? We want to do some fail-over testing. When we tried this in the past, the former master would not replicate. We got an error: "master and slave have equal MySQL server ids". The fail-over procedure includes renaming the slave and exchanging IP addresses with the master server. We did not change the server-ids. I haven't found any information on promoting slaves to master servers.
View Replies !
LOAD DATA INFILE :: Master & Slave
After recently replicating using the stop databases and copy innodb and mysql files method, things were fine...Until the slave thread stopped complaining about duplicate rows after one of our developers did a LOAD DATA INFILE operation on the master. Unfortunately the insert's are into a table using AUTO_INCREMENT as well and this may be compounding the problem.I have read the docs, but it's sort of unclear, does LOAD DATA INFILE work in a replicated 3.23.56-1 setup, only under certain circumstances, or not at all?
View Replies !
Forwarding Insert/Updates From Slave To Master
Is there a way within MySQL to forward all Inserts/Update requests from the Slave to the Master, or much I rearchitect my application to carry two connection; one connection to the Master for updates and the other connection to the slave for Selects?
View Replies !
|
TRACKER
