MySQL Masters Binary Logs & Replication?

shaggly

Hi guys,

I wonder if you can help me on this one...

One of the sites I work with is a commercial site. I keep a fully functional version on my PC herre, along with a complete web server model to test with.

I'm in the process of moving hosts with my client, so I hosted her site up on my space here, pending the final move. While it's here I came across another question that might interest others, as well as prove pretty useful for people wanting to use their hostpace for commercial purposes.

I wanted to set up replication on her database (or rather the one I set up here), but in order to do that I would need to enable the binary logs on that database.

Question One: Is that possible. It needs to be enabled in the my.ini, or my.cnf file prior to actually running the sql commands.

Question Two: Has anyone else ever tried to set up MySQL database replication here, for the purposes of keeping a local DB copy up to date, and is there a work around?

Bondings, Noobie, or one of the other admns, your input on thsi would also be invaluable. Wink

At present, I am updating via export/imports, which is a major pain in the ass TBH. Obviously with a commercial DB, which is constantly updated, then replication is the most ideal method, but is it possible without being able to have further control over the particular database instances that we set up?

Any input would be welcomed and appreciated.

Bondings

First of all, you're most likely the first and only one using/asking for the mysql replication, so I doubt that you'll get much information from anyone else. However, let's hope you're lucky. Wink

I can't change the my.ini or my.conf, because that's for the whole server. There might be a possibility to set some values yourself like you can do with php settings, but I have no idea how.

Most likely what you want to do is possible, I just have never done it myself so I really can't help you much with this one.

shaggly

Well thanks Bondings, I like to be different if nothing else m8 Laughing

I'm amazed I'm the only one that's asked... Doesn't anyone else want a "live" updating local security version of their DB that means not having to manually update it? Ok, I guess not.

Well, I'll research it further and let you know what I come up with. There must be a local way of effecting or "duping" the MySQL settings. I fully appreciate that the global settings are a no-no and why. This only deepens the challenge. I know you can set user only PHP settings, so in theory the same should be possible for MySQL.

In actual fact, it will be beneficial on her new perm host. Like most hosting companies, they too only have global MySQL settings. I'm only moving the commercial main site due to SSL requirements and b/w (it's nothing personal). My other sites and testing environments will most definitely remain here. Very Happy

If I find any further info, I'll post it up in here so that the knowledge is shared around.

Cheers. Very Happy

shaggly

Ok, after some extensive searching, I found a few solutions which obviously charged a fair amount of money.

No interest there - my pockets ache with frequent usage as it is.

Then I found this, which is freeware:

http://www.tucows.com/preview/278984

It's MYdbPAL for MySQL. Their homepage is here:

http://www.it-map.com/index.html

The version for MySQL database is free, and it was only updated in September of this year (yeah, something free AND up to date). They, of course, also do a version that is cross-platform, but that costs a lot of money.

First impressions are good. It comes with a 28 day license, but you can extend that to an unlimited license for FREE. All you have to do is go to their site and provide contact information.

I'm testing it this morning, so I'll update in here as far as results go, but first impressions are definitely good. It appears to be a very professional package, complete with a full help system, pdf manual, and even tutorials. Certainly can't grumble about lack of help. Plus this also DOES NOT need to have binary logging enabled on the host....

shaggly

Ok, progress so far (or lack of it).

Well, I've learnt one hell of a lot about why it won't work, but despite hours of searching, I can't find a single workaround yet....

The MYdbPAL seems to be a wonderful package, but the problem is that in order to connect you need to set up the MySQL schema object as an ODBC source.

Problem there is trying to set up MyODBC to see the localhost sql server on the frihost servers. I can't seem to find any way around the connection problem there. Obviously, you can't access the localhost via IP address, which would be the easiest workaround.

Well, that's the update for the moment. I'll carry on searching. Of course, I could always just go the easy route and make a backup, then restore the backup locally, but I can't seem to grasp why no one has made an easy solution for this problem. Rolling Eyes

After all, most hosts, even the ones that charge, all call their sql server instances localhost, so this isn't a problem that in any way is to do with Bondings, or the wonderful services we get here.

More later........(assuming I haven't thrown my PC's out of the window)

shaggly

Ok, so I couldn't get that program to work, but I did try SQLyog. It seems to work very well, but it's not free, and then on top of that, the last install I did on my development machine here was XAMPP.

XAMPP at the moment is using MYSQL 5, which I have to say seems quite nice and all, but most servers are running ver 4, or even ver 3. Unfortunately, you can't synchronise backwards databases. It does give it a god try, but the structure of the tables seems different with the Null Fields coming up as an error.

Stil researching..........

steveadams617

Hi. I'm interested to see if you got SQLyog working with your frih.net account. I've been trying without success. phpMyAdmin is cool, but SQLyog is easier and has some great features. I know not every host allow people to connect remotely to MySQL through port 3306. I get "access denied for user myusername@blah.blah.verizon.net (Using password: YES)" for the following settings.

MySQL Host address:myusername.frih.net
Username: myusername
password: mypassword
Databases: myusername_mydatabasename

Are there other settings I should use or just give up on this.

shaggly

Yes, I did manage to get SQLyog working.

First, to your access problem. In general you're using the wrong user name to connect to SQL. Remember that your user name needs to be your account name_DB user name. So, an example for me would be shaggly_dbusername. Secondly, the service name for SQL here is localhost. That's key if you're not connecting via an ftp connection (in the same way that Dreamweaver connects, for example).

SQLyog uses a direct connection, not an FTP connection so if you're trying to connect through SQLyog, you need to remember that the SQL service name on FRihost is also localhost, so you need to use the HTTP Tunnel setting. Here's mine (minus the blanked ones, obviously)

On the "Connect to MySQL Host" screen:

<Server>

MySQL Host Address: localhost
User Name: "Yourfrihostaccountname_theusernamewhenyousetupthedb"
Password: "theusernamepasswordforyourDB"
Port: 3306
Database(s): Blank in my case, as I want to select.

then you need to set up HTTP tunneling, so that the connection won't point to your localhost, if you're running an apache server like me on my PC.

Now for tunneling, you'll need to copy the SQLyogTunnel.php file across to your domain under the root html folder, and inside a new folder called tunnel.

<Tunnel>

URL: http://myfrishostaddress/tunnel/SQLyogTunnel.php
Timeout: 30

and off you go.

The next thing you need to remember is that MySQL versions are not 100% compatible. I found this out earlier.

I managed a workaround for this though. It's still a bit of a pain, but it's still a fairly quick way to compare 2 versions of a database running on different versions of MySQL.

Simply export a full gzipped copy of your master DB. Import that into a dummy DB on your development network. Now the two DB's are on the same MySQL version. Now you can run your synchronisation and update your local DB without any hassles.

One extra step, but still a lot faster for a store based DB than doing a manual compare, or going through a dated backup to check for modification dates.

Hope this helps some other people out there who may have had the same problem.

Cheers.

steveadams617

Thanks. I'll give this a try on Tuesday when I get this project done... Smile

shaggly

You're very welcome m8.

mySQL help needed please.
Upload Script (Read the 3rd post)
connect to different mysql databases site.
MySQL in Visual Basic?
How did you find this Site?

Problem launching phpmyadmin under Ubuntu
.htaccess problem
Installing Apache Php and MySQL [newbie tutorial]
build Universal Binary version of mysql administrator
Ill deal with your HijackThis logs for you.

[Frihost] External connections to MySQL VPS1 & Server 2
php admin and mysql admin console
[php scripts ] phpweather&email
E-Cards with PHP & MySQL
HTTP AUTH with PHP and mySQL