Mysql – Replication of a remote MySQL database to local MySQL database

mirroringMySQLreplication

I'm the developer of my employers corporate private website, a HTML5/PHP/CSS powered site for company reports etc. The site, hosted on a shared hosting package, connects to a remote read-only MySQL DB server for authentication and report data queries. This remote DB is mainly used by a separate windows exe.

The remote DB goes down frequently which of course causes the private website to bomb out on a MySQL connection error. I've thought about replicating the remote DB to the site's local hosted MySQL DB and using the local DB as the site's primary and only DB connection.

Is there a way of synchronising the remote DB to the site's shared hosting DB in a simple and pain free way? Like every hour it updates or something?

Or is there a function where the site's hosting package MySQL DB functionality can cache the remote DB data and use that as a fallback.

Thanks in advance.

Best Answer

Check here (http://drcharlesbell.blogspot.cl/2011/06/comparing-databases-with-mysqldbcompare.html)

It explain how to Compare-Copy a DB to another DB, my case is the same as yours.

Depending on your access level you can:

Use a external client as middle man (to make de compare-copy)
Script in PHP to check new lines in the original DB and copy them
Use one of the many tools of MySQL to copy

Related Solutions

Mysql – What program to use to change settings on db servers

As a MySQL DBA, may I be so bold as to suggest my favorite tool?

The mysql client program itself

There are also a variety of startup options for the client program.

Examples:

-h hostname or host IP
-P port number
-u username
-p password
-e allows execution of a semicolon delimited list of SQL queries inside double quotes
--skip-column-names will suppress the column heading for output of SQL queries

You see all the options by doing this

mysql --help --verbose

You can see global variables and status in the mysql instance using SHOW GLOBAL VARIABLES; and SHOW GLOBAL STATUS; You can also see session variables and status in the db connection using SHOW VARIABLES; and SHOW STATUS;

Once you have successfully connected to the mysql instance, you have a wide variety of session and server options that you can set for the current session or permamnently for all new sessions.

Sql-server – Schedule replication of SQL Server views to MySQL remote database

You'll want to look at SQL Server Replication. If you just want a one time nightly feed then you'll probably want Snapshot Replication. If you want a more real time feed then you'll want to look at Transactional Replication.

Now replication doesn't support replicating the views, so you'll need to replicate the base tables, removing any columns which aren't needed by the website then create the views on the MySQL database.

Best Answer

Related Solutions

Mysql – What program to use to change settings on db servers

Sql-server – Schedule replication of SQL Server views to MySQL remote database

Related Question