phpmyadmin synchronize

I have a remote server on webhotel which i want to synchronize with my local server.

My plan was to either do this though the master - slave relation but the webhost is running as a virtual server, so question one is, is it possible to set my portion of the server up to master server? (The host didnt know much about this)

My other but less awesome idea was to manually synchronize through phpmyadmin on either the host or the local server (They are both running mysql/phpmyadmin, but i would prefer if i could sync from local to host)

When i try to sync either 开发者_如何学Gofrom host or from local i get the following error:

"Cannot connect to target/source"

How do I get around this? I am a novice with DB interconnections so do i have to enable anything anywhere on either local or remote?

You seem to have a couple of issues here,

TIME OUT

I would start here as you may have a permissions problem. I would recommend first ensuring that your local IP address has permission to connect to your remote mySQL database and vice versa, depending on your setup you may be able to configure this in phpMyAdmin, a hosting consule such as cPanel or via shell access. I would suggest setting up a separate user with a strong password for remote access. You should have a static IP address on your local internet connection, if you do not have one contact your Internet Service Provider and arrange one.

In phpMyAdmin, if you have sufficient access, via the "user privileges" tab and setting the "host" under "login information". I would not recommend the "any host" setting for obvious security reasons.

In cPanel, under "databases" you should find "remote mySQL" where you can set your IP address.

Finally in SSH,

1. open the mySQL configuration file with your favorite editor such as VI or Nano, on Ubuntu it is located at "/etc/mysql/my.conf",
2. comment out "bind-address = 127.0.0.1" so it looks like "#bind-address = 127.0.0.1",
3. restart mySQL with "/etc/init.d/mysql restart",
4. log into mySQL with "mysql -u root -p",
5. for a new user, "CREATE USER 'MY_USERNAME'@'localhost' IDENTIFIED BY 'MY_PASSWORD'"
6. followed with "GRANT ALL PRIVILEGES ON 'MY_DATABASE'.* TO 'MY_USERNAME'@'12.34.56.78' IDENTIFIED BY 'MY_PASSWORD';" - note, replace the IP "12.34.56.78" with your actual IP address.
7. You should now have confirmation along the lines of query ok.
8. Finally exit mysql with the "quit" command and restart with "/etc/init.d/mysql restart"

NOTE: I would recommend setting up both mySQL installations for remote access.

I would now recommend testing this by running a small script on your local machine, something like

<?php
$host = 'mysql_remoteip';
$user = 'mysql_username';
$pass = 'mysql_password';
$db = 'mysql_database';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("OOPS! I can not connect to the remote mySQL database, I was given the following error: ".mysql_error());
$sql = "SELECT column FROM table WHERE column='my_var'";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
if(empty($row['column']) {
echo "WHOOPS! I could not get information from the mySQL, was there data for me to retrieve in the database?";
} else {
echo "The data I retrieved is:<br />"
echo $row['column'];
}
}
?>

NOTE: you should have data in the database for the script to work correctly, a result with no data will display a message that the script could not retrieve the data, this will still establish if you now have access to the database or not but doesn't ensure it is 100% operational.

MASTER OR SLAVE

Yes, it is possible, providing you have sufficient access

In phpMyAdmin:

1. under the "replication" tab you will find a wizard to provide you with the code to set this in the mySQL configuration file (on Ubuntu it is located at "/etc/mysql/my.conf"),
2. click on the link to configure the master server
3. enter the information in the form, copy the code
4. go to your configuration file (location above) and add the code provided by phpMyAdmin
5. once you update the configuration file restart mySQL and click the go button in phpMyAdmin, this will confirm the changes
6. Now again under the "replication" tab click the link to configure the slave server
7. make sure, you have unique server-id in your configuration file, if not add the line provided by phpMyAdmin into [mysqld] section of the file
8. Complete the required information and click Go

This should set up your replication.

SYNCHRONIZE

If all else has failed then in phpMyAdmin go to the "synchronize" tab, enter the information and away you go, you just need to confirm the sync on the next page and 2 seconds later you're done, it is that easy.

For production servers I personally have a master/slave replication set-up but for development I sync when I need "live" data to replicate an error or problem, this is because I only need to sync once every 6-12 months. If I need live data locally then I would set up the master/slave replication