MySQL Forums - Synchronization

Synchronizing mobile databases with MySQL in Amazon cloud (AWS EC2 & RDS) (no replies)

Alice Qiao — Mon, 20 May 2013 07:30:54 +0000

Following is a tutorial for setting up Pervasync server in the Amazon cloud so that you can sync your mobile devices with Oracle, MySQL or SQL Server databases hosted in Amazon cloud.

Preparing the AWS Env

1. Create an AWS account at http://aws.amazon.com/ec2/

2. Create a Beanstalk env to have a running Tomcat instance and a RDS database instance: http://aws.amazon.com/elasticbeanstalk/.
Setup the DB security group so that the DB instance can be accessed from the EC2 instance where Tomcat is hosted.

3. Create your app database, schema and tables. For MS SQL Server, connect to the DB server and create a database for your application’s DB schemas and tables. The DB name will be used as value of pervasync.server.db.database.name during Pervasync setup. For Oracle you supply the DB name during DB instance creation. The DB name will be the Oracle SID to be used in DB JDBC URL. For MySQL the RDS DB name is really the schema name.

4. For MySQL, create a DB Parameter Group and set the value of parameterlog_bin_trust_function_creators to 1. Modify the MySQL instance and set its parameter group to the one with log_bin_trust_function_creators set to 1. Reboot the DB instance.

Alternately to Beanstalk, you could also directly create an EC2 instance and RDS instance. To install Java & Tomcat on your EC2 instance, see
http://www.excelsior-usa.com/articles/tomcat-amazon-ec2-basic.html

Installing Pervasync in the AWS Env

Note: You need to substitute the host name, password etc. with yours in the following.

1. Download pervasync_server-5.0.3.zip from
https://www.dropbox.com/s/4uwhvo99j7ra2xc/pervasync_server-5.0.3.zip

2. Copy the zip file to your Amazon EC2 instance home folder:
scp -i keypair1.pem pervasync_server-5.0.3.zip ec2-user@ec2-54-214-122-102.us-west-2.compute.amazonaws.com:/home/ec2-user

3. Log on to your Amazon EC2 instance
ssh -i keypair1.pem ec2-user@ec2-54-214-122-102.us-west-2.compute.amazonaws.com

4. From now on act as root user. Un-package the zip in /usr/share/:
sudo su root
cd /usr/share/
unzip /home/ec2-user/pervasync_server-5.0.3.zip

5. Go to non-GUI install folder for your DB type, for example for SQL Server:
cd pervasync_server-5.0.3/util/mssql/
Edit pervasync_server_mssql.ini to supply your DB connection info:

#=========================================================================
#== Pervasync server for MS SQL Server ==
#== ini file for setup/uninstall ==
#== ==
#== Property values are needed and only needed when you run ==
#== the setup/uninstall scripts. After you are done with ==
#== setup/uninstall, you can erase sensitive info from this file. ==
#=========================================================================
# The JDBC URL to the Pervasync server repository database.
# Replace "localhost" with the DB host name or IP if DB is not on the same host
# For AWS RDS, replace "localhost" with the DB Endpoint

pervasync.server.db.url=jdbc:sqlserver://db1.cinuuz0ecpzz.us-west-2.rds.amazonaws.com:1433

# The database name. Replace "master" with the database name you created for your app

pervasync.server.db.database.name=myappdb

# Name and password of a DB user with root privileges.
# This user/account should be pre-existent.
# For AWS RDS, use the master user
pervasync.server.db.system.user=master
pervasync.server.db.system.password=welcome1234!

# PervaSync server admin user name and password. You would need to use this
# user/password pair to login to the web-based Pervasync admin console.
# If not already exist, a database user/schema with this name will be created
# in Pervasync server DB repository at setup time. At un-install time, the user/schema
# will be dropped.
pervasync.server.admin.user=pvsadmin
pervasync.server.admin.password=welcome1234!

6. Run the setup
./pervasync_server_mssql_setup.sh

7. Install Pervasync server web app to Tomcat
chown -R tomcat:tomcat /usr/share/pervasync_server-5.0.3
cp /usr/share/pervasync_server-5.0.3/config/Catalina/localhost/pervasync.xml /usr/share/tomcat7/conf/Catalina/localhost/
vi /usr/share/tomcat7/conf/Catalina/localhost/pervasync.xml

`
8. Log on to Pervasync web admin console

http://default-environment-ddffdffd.elasticbeanstalk.com/pervasync
If not working, check Tomcat log:

vi /usr/share/tomcat7/logs/catalina.out
Check Pervasync log:

vi /usr/share/pervasync_server-5.0.3/log/pervasync_server_mssql.log

MySql replication with Master-Master and master to slaves (no replies)

Nils Kariya — Tue, 05 Mar 2013 06:40:16 +0000

I want to implement replication in MySQL.

My topology for replication are as below:

I have different branch and all branches are connected with replication to each other. This way replication I have done with my current system.

But now I want to replicate branch's database to different till connected with it.

For example: I have branches: Ho, B1, B2, B3.

Currently I have done replication is following flow: Ho->B1, B1-> B2, B2->B3, B3->Ho and its working fine.

But now I want to replicate My branch database to tills connected with it.
So for example: I have 3 Tills at Branch B1 than Brnach and Head Office should work as above scenarios but My Brnach B1 should replicate like

B1 - > Till 1, B1- > Till 2 , B1-> Till 3.

And same for other brnach having till than branch's main Database will replicate with Till database.

But HO and Branches repliaction should work as currently working scenarios as above.

Please let me how I can work with such a scenarios.

Thanks in Adavnce.

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' not working (1 reply)

Neeru Sharma — Fri, 01 Mar 2013 13:46:37 +0000

Hi,

I'm trying to install semi synchronous replication in my mysql 5.5 and trying to execute the below command:

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so'

but this command is throwing the below error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''rpl_semi_sync_master' SONAME 'semisync_master.so'' at line 1

Please help regarding this. We are using Win7 Professional SP1 32-bit OS.

Thank You

Break in synchroniztion on system shutdown (no replies)

Neeru Sharma — Fri, 18 Jan 2013 06:49:21 +0000

In our project we are using two systems(Slave and Master) which are connected using VPN connection for MYSQL database synchronization.

When the master system is shutdown, the synchronization breaks and to start the synchronization again the slave machine's MYSQL services has to be restarted manually.

Is there any wayout for the synchronization to start without any manual work?

Pervasync 5.0 is available (1 reply)

Alice Qiao — Thu, 21 Feb 2013 04:58:14 +0000

This new version added SQLite sync client for Windows and Linux. This is in addition to the support of SQLite for Android and Blackberry.

Another enhancement is the support of Big Data -- fast and interruptable syncing of large amount of data. One customer has successfully synced hundreds of Giga bytes of data from MySQL in the cloud to MySQL in local offices.

multiple updates from tables in synchronisation (no replies)

seema joshi — Thu, 04 Oct 2012 09:00:41 +0000

I HAVE A DATABASE WORKING OFFLINE .WHEN I CONNECT THE INTERNET I WANT THE DATA FROM MY DATABASE TABLE TO GO TO ANOTHER TABLE OF THE SAME DATABASE.
PLZ HELP ME OUT

Master-Master Resyncronization Problem (no replies)

j sarma — Sun, 23 Sep 2012 05:48:29 +0000

I have two InnoDB's set up in "circular" replication so that each is a slave to the other. I'm doing this for failover purposes, so clients will only write to one at a time which should prevent sync problems. It seems to work well in most scenarios, but I'm trying to really kick the tires and find out if I can recover from synchronization errors before I go live. Here's the test I'm doing that results in the sync error that I'm having trouble recovering from:

1) Assume we have the following simplified table definition and we're properly synced:

table clients (
id INT(11) PRIMARY KEY,
name VARCHAR(255)
);

2) insert record (id=1, name=X)

3) stop slave on both servers so communication stops between them.
4) On server A, update clients set name='A' where id=1
5) On server B, update clients set name='B' where id=1
6) start slaves on both machines.
7) Amusingly enough they've swapped records. ie: server A has id=1, name='B'. Server B has id=1, name='A'.

I don't find this sync error surprising, because they both read each other's relay messages. But now I'm trying to create a procedure to recover from this scenario.

Recovery Procedure:

1) issue "service mysql stop" on both servers.
2) scp -pr user@serverA:/var/lib/mysql/dbname user@serverB:/var/lib/mysql/
3) fix permissions on the new files on server B with chown mysql:mysql
4) issue service mysql start on both machines

Strangely enough, after doing this, server B still has name='A'. You would think overwriting the data files would cause them to both have name='B'. My suspicion is it's rereading the binlogs, but I don't understand why it would do that since it's already read them once, and I haven't reset their positions. I've tried deleting the relay logs on both machines before restart mysql, but that didn't help either

The only thing I did try that worked is using mysqldump instead of copying the data files. But I'd rather not do this because it needs to be an automated process, and I don't want to have to feed mysqldump the password at the cmdline, because that's insecure.

I have innodb_file_per_table enabled.

Can someone please tell me what is wrong with this procedure and how it can be corrected?

Thank you all for your time.

-Justin

Client-Server DB Synchronization (no replies)

Dhanya CM — Fri, 03 Aug 2012 06:29:31 +0000

We have a data synchronization scenario wherein we want to send the data from master DB to client. The clients will have only a subset of master data depending on some rules which are calculated at the server level.
Here we have cases where the tables have relation among them and there can be "n" levels of such relations. ie I can have Table-1 whose data will determine what data from Table-2 should be sent which in turn will determine data of Table-3 be sent and so on. So if there is any change in any of these tables then all tables down the hierarchy should be recalculated and synchronized.
Some limitations we have are:
a) We are currently using Pervasync(http://www.pervasync.com/) for synchronization and modeled the queries for such cascading cases but is facing issues there like DB lock wait time out.
b) This is also limiting the usage of DB triggers as pervasync internally uses the triggers.
c) Since we have already zeroed in on pervasync, it is difficult to take a code based calculation approach wherein we identify at the code level what changes to be propagated and do data to client mapping.

Has anyone handled such cases? Any suggestions on how to approach this case will be very helpful.

Replication Tool (1 reply)

ramkumar vm — Wed, 18 Jul 2012 17:16:54 +0000

Hi Team,

i tried to find out the best and easy tool for my sql replication from one server to other server.

please provide me the tool list.

thanks in advance.

Update Database on Hourly Basis (no replies)

Nitin Deopura — Thu, 17 May 2012 14:02:10 +0000

Hello,

I have as server serving 40 Production Database. All these database are SELECT only to the Customer and hence i have used MyISAM db engine. Now i have to update All these database on Hourly Basis with new data Set. Since these database are production and consumers are access these database, while loading the dump to the existing db, it locks up all the select queries to that db.? Is there any way that i can update the database without affecting the current customer on the site.

Regards,
Nitin

auto_increment_offset for only specific Database (no replies)

bhavesh modi — Mon, 23 Apr 2012 11:37:39 +0000

Hi,

Is it possible to use auto_increment_offset for specific set of database/tables on one mysql server or is there any other options to set the auto increment offset to the columns.

Regards,
Bhavesh

Attendance system - syncing records - best strategy? (1 reply)

Robert Stone — Sat, 19 May 2012 00:39:15 +0000

We are developing an attendance system using barcode readers, php, and mysql for a university. There is one central db running on a shared MySQL server accessed over the Internet (not on the university LAN).

Because the db is not on the LAN we cannot operate in "real time" with a direct connection between each station and the central db. We are constrained to operate in "store and forward" mode where each barcode reader/computer station records attendance data and provides appropriate confirmation of valid scans to the users locally.

Once every 24 hours we need to upload the locally collected attendance records from each station to the central db and simultaneously to download any changes (new users, deactivated users, etc.) from the central db to the stations.

There are 10 locations and 14 reader/computer stations - two locations have enough traffic that more than one station is required. These stations are on the university LAN.

The question is how best should we keep the list of valid barcodes and other data synchronized with the central db?

Also what do you recommend for storing the data locally: MySQL, a spreadsheet, a flat db, or . . . ?

Synchronisation from HQ to multiple slave sites (no replies)

Dan Lay — Thu, 22 Dec 2011 10:35:08 +0000

Morning all,

I notice that there is a lot of talk in this forum on different ways to implement data synchronisation between multiple locations. I can't seem to find an answer to my problem.

I have an application that traditionally runs on one site with one database. My client is now expanding and is adding 2 additional branches.

Currently I am using MySQL 5.5 community at the HQ site, all communication with the database is done through MySQL connector.net.

What I plan to do is serialise any UPDATE/INSERT/DELETE queries including parameters passed into XML in the database tier, then make the XML data available to other sites on a publish/subscribe framework.

I think this will work fine as I am currently doing exactly the same thing for other parts of the system with files. I poll a web service for items in a queue.
When the remote site gets a database sync job, it deserialises the details and re-constructs the statement exactly as it was.

My issue lies with unique ID's - presently I use Auto Increment columns to give each record in each table a unique id. This is fine when there is just one Database but when there are multiple databases it creates an issue.

Scenario 1
1. Add product to HQ db (master), unique id is assigned to the item.
2. Sync job is created to sync this query to all remote sites.
3. Remote site gets the sync job and applies it to the db (slave).
So far so good

The problem comes when a product record is added to the remote site db (slave). The identity column then increments by one. Then, when another sync job comes in, it applies another INSERT statement however this time the product ID is different and does not match master.

Then, any subsequent UPDATE statements for this product will update the wrong record as the id's are one out.

I've considered adding another ID to the product table that will remain consistent across all sites but this then prevents other UPDATE statements I have collected from processing normally as the statement will update by the master's id.

I am aware that MySQL does support replication but I cant seem to find a solution that will work across multiple sites like this. The connection between the site can go up and down and I would prefer to synchronise by http web services.

Any possible suggestions would be great.

Thanks,
Dan

SYNCRONIZATION OF 2 DBS (1 reply)

VINCENT BORRELLI — Thu, 24 Nov 2011 12:50:31 +0000

I'A NEWBIE ABOUT MYSQL.
I HAVE THIS NEED:

1 DB ON MY LOCAL PC, AND 1 ON MY WEBSITE SERVER.

HOW CAN SYNCRO THEM? any build in functionalists to synchronize data ?
PLS HELP ME!
REGARDS
VINCENT

Scenario on synchronizing database from multiple locations to a central database and vice versa (2 replies)

shiva Kumar — Fri, 04 Nov 2011 15:01:18 +0000

I have several databases server located in different locations and a central database which is in a data center. All have the same schema. All of them are changed(insert/update/delete) in each location with different data including the central database.

I would like to synchronise(manully at midnight using some scripts) all the data in the central database. I would also like all data in the central database synchronise to all locations. What I mean is that database change in location 1 should also be reflected in location 2 database.

Any ideas on how to go about this using the procedure scripts ?

Thanks in Advance

Michael SopRaj

synchronization of Database Manually (1 reply)

shiva Kumar — Fri, 04 Nov 2011 15:03:15 +0000

Hi,
I have a ERP Web based Application that going to run on mysql,it consists of one central database located in one location and other 4 sub centers databases location placed in different location. Here the 4 locations access the master data from central database and also central database sync(synchronization) from the four location database transactions data with regular time interval, here i need to sync the transactions data from sub location by manually sync that is script put linux or other operating system Cron.
and i tried the replication and cluster it is not suitable for transactions,So

is there is way to do sync manually by using the sql scripts

Thanks in Advance,

Siva Kumar

Announcing Pervasync Support for Android and Blackberry SQLite Databases (no replies)

Alice Qiao — Wed, 24 Aug 2011 07:33:42 +0000

We are pleased to announce the release 4.0 of Pervasync, the leading synchronization software for SQLite, MySQL and Oracle databases.

Pervasync enables mobile users to work offline and have their offline data automatically synced with a central server when a network connection becomes available. In 4.0.0, we added the following new features:

Android sync client for device native SQLite databases
Blackberry sync client for device native SQLite databases
Resume of a sync session after network interruptions

Now you can synchronize your business data in central Oracle or MySQL databases to Android and Blackberry SQLite databases and instantly see the data on device using the built-in database browser. Build your mobile applications on top of the SQLite databases and let Pervasync do the data synchronization!

Download and test drive Pervasync today! We encourage you to compare Pervasync with other solutions in terms of easiness of use, robustness, performance and scalability.

http://pervasync.wordpress.com/2011/08/23/doing-synchronization-using-pervasync-client-for-android-and-blackberry/
http://www.pervasync.com

Apparent replication error, unsure how to fix it. (no replies)

David Hicks — Fri, 03 Jun 2011 19:35:18 +0000

I'm fairly new to replication with MySQL, but I'm managing a system that contains a master with several slaves. They are all running on MySQL 5.0.86 Community Edition. Some slaves have a limited amount of replication filtering on them. I've got some errors kicked out complaining of a duplicate key error on two of the slaves. So, I whipped out the MaatKit documentation and started checking up on it. When I run mk-table-checksum on the specific table that is in error, I can clearly see that the checksums between the master and slave do not match. However, if I then run mk-table-sync with --dry-run, it reports no changes. This doesn't make sense to me, but, like I said, I'm new to this.

Can anyone shed any light on this for me?
Thanks,
Dave

closed (no replies)

fumihide nario — Sat, 21 May 2011 12:04:23 +0000

closed

Synchrnization Tables (1 reply)

wagner souza — Fri, 15 Jul 2011 16:36:23 +0000

Hi folks.

I've had a problem to do a synchronization.

My scenario is: One server with two database A and B.

The database A has the tables G,H,I.

The database B has the tables H,J,K.

The table H in both database need have the same data, but they really need be in a diferent database. How can I do a synchronization with databases?

Multiple slave and one master db (no replies)

ROHIT GUPTA — Tue, 29 Mar 2011 07:45:55 +0000

Hello All,

I am looking for proper solution for my problem. my problem is

I have one Head Office and multiple branches and franchise. Now i have to export all data from branch system to server on realtime basis. and server database should not be replicated to local system as i need one way replication or syncronization.

Kindly please suggest me proper solution what should i use replication or syncronization. i am totaly condused between this two technologies.

Thanks in Advance.

ROHIT GUPTA

me.rohitgupta@yahoo.in

Multi Master - Single Slave - Configuration? (no replies)

Max Ziegler — Fri, 11 Mar 2011 12:05:00 +0000

Hello,

i try to build up a configuration where databases (with different names) on different Servers will be syncronised (only in one direction) with one Server.
My question: is this possible with the replication feature?

overview:

Server 1 [db1]
Server 2 [db2] --> Server 4 [db1, db2, db3]
Server 3 [db3]

the arrow shows the data flow. On Server 4 only read processes will be used.

My thoughts:
I configure Server 1 - 3 as replication masters and the server 4 as slave, and use a script which switch the connection to the server each 5 minutes with the command slave stop; CHANGE MASTER TO ... slave start;
with the PARAMETER
MASTER_LOG_FILE='server<1-3>-log', RELAY_LOG_FILE='server<1-3>-relay';

But this doesnt work.
Error msg: ERROR 1380 (HY000): Failed initializing relay log position: Could not find target log during relay log initialization
error_log on slave:
110311 11:57:04 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236)
110311 11:57:04 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236

If I remove the Parameters, he will start at position 0 at the bin-log file and re-enter all entries.

So do i have to write a script which remebers the position from the *.index file and set the parameter MASTER_LOG_POS?

Or is it easier to start 3 instances of slave-servers on server 4?

Or is there any other tool which fits better? (like clustering?)

Thanks a lot, max

An interesting blog on MySQL synchronization (no replies)

Alice Qiao — Wed, 19 Jan 2011 22:30:48 +0000

http://www.unorganizedmachines.com/site/software-and-technology/34-software-development/87-pervasync

Replication shutsdown the slave mysql. (1 reply)

Rodis Alvarez — Tue, 25 Jan 2011 04:30:17 +0000

I have successfully setup replication on mysql, data is going into the slave just fine. But for some reason, the slave shuts down the mysql database. Below is the log file, is there anything that i can do to prevent the salves mysql from shutting down?

101227 14:29:54 mysqld started
101227 14:29:54 InnoDB: Started; log sequence number 0 8643281
101227 14:29:54 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.91-community-log' socket: '/var/lib/mysql/mysql.sock' port: 37475 MySQL Community Edition (GPL)
101227 14:29:54 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 12959095, relay log './christina-relay-bin.000032' position: 10663
101227 14:29:54 [Note] Slave I/O thread: connected to master '**@**:**', replication started in log 'mysql-bin.000003' at position 12959095
101227 14:32:09 [Note] /usr/sbin/mysqld: Normal shutdown

101227 14:32:09 [Note] Slave I/O thread killed while reading event
101227 14:32:09 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000003', position 13141902
101227 14:32:09 [Note] Error reading relay log event: slave SQL thread was killed
101227 14:32:09 InnoDB: Starting shutdown...
101227 14:32:09 InnoDB: Shutdown completed; log sequence number 0 8643281
101227 14:32:09 [Note] /usr/sbin/mysqld: Shutdown complete

101227 14:32:09 mysqld ended

101227 14:32:27 mysqld started
101227 14:32:27 InnoDB: Started; log sequence number 0 8643281
101227 14:32:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.91-community-log' socket: '/var/lib/mysql/mysql.sock' port: *** MySQL Community Edition (GPL)
101227 14:32:27 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 13170036, relay log './christina-relay-bin.000036' position: 28369
101227 14:32:27 [Note] Slave I/O thread: connected to master '**@**:**', replication started in log 'mysql-bin.000003' at position 13170036
101227 14:37:08 [Note] /usr/sbin/mysqld: Normal shutdown

101227 14:37:08 [Note] Slave I/O thread killed while reading event
101227 14:37:08 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000003', position 13463249
101227 14:37:08 [Note] Error reading relay log event: slave SQL thread was killed
101227 14:37:08 InnoDB: Starting shutdown...
101227 14:37:08 InnoDB: Shutdown completed; log sequence number 0 8643281
101227 14:37:08 [Note] /usr/sbin/mysqld: Shutdown complete

101227 14:37:08 mysqld ended

101227 14:37:26 mysqld started
101227 14:37:26 InnoDB: Started; log sequence number 0 8643281
101227 14:37:26 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.91-community-log' socket: '/var/lib/mysql/mysql.sock' port: *** MySQL Community Edition (GPL)
101227 14:37:26 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 13469766, relay log './christina-relay-bin.000040' position: 6752
101227 14:37:26 [Note] Slave I/O thread: connected to master '**@**:**', replication started in log 'mysql-bin.000003' at position 13469766
101227 14:42:08 [Note] /usr/sbin/mysqld: Normal shutdown

101227 14:42:08 [Note] Slave I/O thread killed while reading event
101227 14:42:08 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000003', position 13817775
101227 14:42:08 [Note] Error reading relay log event: slave SQL thread was killed
101227 14:42:08 InnoDB: Starting shutdown...
101227 14:42:09 InnoDB: Shutdown completed; log sequence number 0 8643281
101227 14:42:09 [Note] /usr/sbin/mysqld: Shutdown complete

101227 14:42:09 mysqld ended

101227 14:42:27 mysqld started
101227 14:42:27 InnoDB: Started; log sequence number 0 8643281
101227 14:42:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.91-community-log' socket: '/var/lib/mysql/mysql.sock' port: *** MySQL Community Edition (GPL)
101227 14:42:27 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000003' at position 13834397, relay log './christina-relay-bin.000044' position: 16857
101227 14:42:27 [Note] Slave I/O thread: connected to master '**@**:**', replication started in log 'mysql-bin.000003' at position 13834397

Sync on entire master database (2 replies)

greg rouf — Mon, 06 Dec 2010 14:14:15 +0000

Hello,

The question is pretty simple, if i want to synchronise/replicate the whole master database, would i need to specify into master's my.cnf the "binlog-do-db" for every existing database?
As far as I know, if I start to specify database using the binlog-do-db options, the slave won't build up new databases if some are created later on the master.

Wouldn't just selecting the right bin-log on the master enough?

Let me explain my question through an example.
My master "my.cnf" doesn't specify any binlog-do-db. Therefore binlog-do-db is empty while doing a show master status.

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000467 | 4067704 | | |
+------------------+----------+--------------+------------------+

If i still pick up the right mysql-bin.00x file with the correct pointer position, would the slave synchronise the whole database or would it just pick up nothing?

Many thanks for your help.
Greg

Slave socket crashed and reboot (no replies)

greg rouf — Fri, 03 Dec 2010 10:40:51 +0000

Hi,

I've set up a master-slave synchronisation between 2 mysql database. It looks like everything is working fine (both pointer are synchronized) but it doesn't properly. The slave seems to reboot every time he gets some data from the master.
Moreover it might be ok but when i do a "show master status" on the slave server, the pointer and the mysql-bin.0x file is changing all the time?! I tried to stop it through "stop master" with no effect.
After 3h, i got like 20 000 mysql-bin.0x files in my /var/log/mysql/.

Here some logs from Syslog:

101202 20:40:59 [Note] Slave I/O thread: connected to master 'user_slave@172.22.1.193:3306', replication started in log 'mysql-bin.000467' at position 63837695
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: Error: trying to access page number 17266432 in space 0,
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: space name ./ibdata1,
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: which is outside the tablespace bounds.
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: Byte offset 0, len 16384, i/o type 10.
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: If you get this error at mysqld startup, please check that
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: your my.cnf matches the ibdata files that you have in the
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: MySQL server.
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: 101202 20:41:00InnoDB: Assertion failure in thread 1141287264 in file fil0fil.c line 3959
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: We intentionally generate a memory trap.
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: If you get repeated assertion failures or crashes, even
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: immediately after the mysqld startup, there may be
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: corruption in the InnoDB tablespace. Please refer to
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: InnoDB: about forcing recovery.
Dec 2 20:41:00 ORA-S-BK02 mysqld[20828]: mysqld got signal 11;

By the way I didn't specify any "binlog-do-db=" on the master, do we really need it if we use the bin-log ?

Many thanks in advance
Greg

Sync 6000 specific tables with 15GB of data to a target database (no replies)

Steve Wayne — Sun, 28 Nov 2010 06:32:56 +0000

Hello,

Maybe someone can help me with this. I manage a database with about 7000 tables and about 18 GB of data. Out of the 6000 tables, the biggest tables have up to a million records. I have a test environment and a live environment. Currently, I do data imports on the test environment, and when the imports are inspected and deemed correct, I then do a sqldump with a php script to get 6000 specific tables and dump them to the live server. Then I import this data on the live server.

I think this is a pretty clunky way to do it as the sqldump takes about an 30 minutes and the import takes about 3 hrs during which time, the server is slowed down a bit.

Ideally, I'd like to be able to click a button that would copy just the delta between the 6000 tables on the test and the 6000 target tables on the live machine and also create any new tables that I specify on the live that were newly created on the test since the last update.

What is the best way to do this?

Sync of 10-12 Gig DBs (no replies)

Huu Da Tran — Fri, 12 Nov 2010 17:17:22 +0000

This may not be 100% sync question, but I think it's the best place to ask it. I have a mix of MyISAM-only databases, InnoDB-only databases and a mix of both.

I have a question to know what would be the best way to transfert (for each setup) the databases from one host to another (local network). For smalls databases, I just go with mysqldump and load it on the other server.

My problem is more what to do with the 10-12 Gigs DBs? On those DBs, developer would truncate tables in a given DB, do their magic and populate the DB on their workstation. So basically, it's all new data. Now, I want to transfert that on a test server.

At the moment, for ease of procedure, I ask them to mysqldump, put the dump on the test master and a DBA source the file (set sql_log_bin=0) and then run it once more on the test slave at a latter time (delay is not a problem). This is done in two steps because the snapshots are not important to keep in the binlogs and will not slow down both master and slave at the same time. When data is satisfactory, the same dump file is used on production master (and again on the production slave).

Are there other ways? We used to tar the MyISAM-only DBs when all hosts were 32-bit machines. Now, all hosts are running the same version of MySQL 5.1.46-log on Suse 11.3. About 80% of the hosts are 64-bit machines.

Thanks,

Synchronization whit view (no replies)

michele amodeo — Mon, 08 Nov 2010 08:34:36 +0000

Hello,
I need to read from a view mysql the records from a dbms oracle.
Can you help me?
Best regards
michele

Announcing Release Of Pervasync 3.0.0 And The Support Of Sync-Based-On-Network-Characteristics (no replies)

Alice Qiao — Sun, 03 Oct 2010 22:19:04 +0000

We are pleased to announce the availability of Release 3.0.0 of Pervasync, the leading synchronization software for MySQL and Oracle databases.

Pervasync enables mobile users to work offline and have their offline data automatically synced with a central server when a network connection becomes available. With the support of sync based on network characteristics, administrators can control what data to sync based on which network is being active on end user’s devices. This is another example of Pervasync’s “innovation based on customer needs”.

New feature in Pervasync version 3.0.0 include:

- support of sync based on network characteristics
- support of client groups and group subscriptions
- support of syncing data and files in a single transaction
- support of sync tables with no primary key
- support user interfaces in Chinese

Download and test drive Pervasync today! We encourage you to compare Pervasync with other solutions in terms of easiness of use, robustness, performance and scalability.

Pervasync Software
http://www.pervasync.com

P.S.

Sync Based on Network Characteristics

A mobile computer usually has multiple network interfaces, e.g. cable, Wi-Fi and cellular wireless (mobile data), to connect to the Internet, through which to connect to the central sync server. Some of the network interfaces are slower or more expensive compared with others. On the other hand, of all the subscribed publications for synchronization, some may involved much larger amount of data than others. One example is a folder of media files (photos or videos) or a database table of blobs of data items. When you are on a slow or expensive network, you may want to exclude these publications from synchronization. The “Sync Based on Network Characteristics” feature was implemented for this purpose.

Defining Network Characteristics Using a Matching String

First we need to find a way to identify the network interfaces. Use the utility listnetworks.bat (or listnetworks.sh for Linux) in the bin folder of Pervasync home to list all the attributes of all the network interfaces. Pay attention to the display names, hardware addresses and IP addresses.

C:\pervasync\bin>listnetworks.bat

C:\pervasync\bin>java -classpath ../classes pervasync.util.Util listNetworks

Network Name: lo
Status: Active
Display Name: MS TCP Loopback interface
Hardware Address:
IP Address: 127.0.0.1
IP Address: 0:0:0:0:0:0:0:1
IP Address: fe80:0:0:0:0:0:0:1%1

Network Name: eth0
Status: Active
Display Name: Intel(R) PRO/Wireless 3945ABG Network Connection - McAfee NDIS Intermediate Filter Miniport
Hardware Address: 0018DE157BA9
IP Address: 182.138.0.102
IP Address: fe80:0:0:0:218:deff:fe15:7ba9%4

Network Name: eth1
Status: Non-Active
Display Name: Broadcom NetXtreme 57xx Gigabit Controller - McAfee NDIS Intermediate Filter Miniport
Hardware Address: 0015C54B210F

According to the network attributes, admin comes up with strings that match the display name, hardware address and/or IP address. The matching string defines a specific network card or a type of network. The matching string may contain operators “&&” and “||”. For example, the following matching string will match network eth0 in the above listing:

Intel && Wireless && Network Connection

The following matching string will match network cards that have an IP address that starts with “182.138” or “193.111.2”:

182.138. || 193.111.2

Specifying No-Sync Lists Associated with Sync Schemas and Sync Folders

Pervasync adds two no-sync-list attributes to the sync schema and sync folder definition: NO_INIT_SYNC_NETWORKS and NO_SYNC_NETWORKS. The values of the attributes are the matching strings that identify a specific network card or a type of network. Values can be specified at publication level, group subscription level and client subscription level. Publication level values are inherited and can be overridden by group subscription values, which in turn are inherited and can be overridden by client subscription values.

Pervasync client will match the active network with the no-sync list matching strings before starting a synchronization. If there is match, the sync schema/folder will be excluded from synchronization. For example, if schema “schema1” has a NO_SYNC_NETWORKS value of “182.138. || 193.111.2” and the current active network card has an IP of 193.111.222.111, “schema1” will be excluded from the synchronization.

NOTE: NO_INIT_SYNC_NETWORKS only apply to initial sync of a publication while NO_SYNC_NETWORKS apply to both initial and incremental syncs.
On the Pervasync admin console’s publications and subscriptions UI, sync folders and sync schemas are displayed on same pages. Admins have a complete view of all the available schema and folder publications. Admin could easily make sure related folders and schemas are either included or excluded together for a certain network type. At subscription time, admin again could easily make sure related folders and schema are either included or excluded together in a group or client subscription.

No-sync lists are part of the sync metadata that admin can update at any time. When a sync session detects new metadata changes, it will postpone data and file syncs and will instead sync these metadata changes to device. Device will then use these new metadata in following syncs.