问题描述
我在webhotel上有一个远程服务器，我想与本地服务器同步.
I have a remote server on webhotel which i want to synchronize with my local server.
我的计划是通过主从关系执行此操作，但Webhost作为虚拟服务器运行，因此问题之一是，是否可以将服务器的一部分设置为主服务器? (主持人对此一无所知)
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)
我的另一个但不太出色的想法是通过主机或本地服务器上的phpmyadmin手动同步(它们都运行mysql/phpmyadmin，但我希望我可以从本地同步到主机)
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 from host or from local i get the following error:
我该如何解决?我是数据库互连的新手，所以我必须在本地或远程的任何地方启用任何功能吗?
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,
超时
由于您可能有权限问题，因此我从这里开始.我建议首先确保您的本地IP地址具有连接到远程mySQL数据库的权限，反之亦然，根据您的设置，您可以在phpMyAdmin，托管助手(如cPanel)或通过Shell访问中进行配置.我建议设置一个单独的用户并使用强密码进行远程访问.如果您没有本地IP地址，请与您的Internet服务提供商联系并安排一个IP地址.
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.
在phpMyAdmin中，如果您具有足够的访问权限，请通过用户权限"选项卡并在登录信息"下设置主机".出于明显的安全原因，我不建议您使用任何主机"设置.
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.
在cPanel中的数据库"下，您应该找到远程mySQL"，您可以在其中设置IP地址.
In cPanel, under "databases" you should find "remote mySQL" where you can set your IP address.
最后使用SSH
- 使用您喜欢的编辑器(例如VI或Nano)打开mySQL配置文件，在Ubuntu上，该文件位于"/etc/mysql/my.conf"，
- 注释掉"bind-address = 127.0.0.1"，因此看起来像是#bind-address = 127.0.0.1"，
- 通过"/etc/init.d/mysql restart"重新启动MySQL，
- 使用"mysql -u root -p"登录mySQL，
- 对于新用户，"CREATE USER'MY_USERNAME'@'localhost'IDENTIFIED BY'MY_PASSWORD'"
- 其后为在'MY_DATABASE'上授予所有特权.*到'MY_USERNAME'@'12.34.56.78'由'MY_PASSWORD'标识"； -请注意，将IP"12.34.56.78"替换为您的实际IP地址.
- 您现在应该按照查询ok进行确认.
- 最后使用"quit"命令退出mysql并使用"/etc/init.d/mysql restart"重新启动
注意:我建议您设置两个mySQL安装程序以进行远程访问.
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'];
}
}
?>
注意:您应该在数据库中有数据才能使脚本正常工作，没有数据的结果将显示一条消息，表明脚本无法检索数据，如果您现在可以访问数据库或不是，但不能确保它100％可操作.
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.
主或从
是的，有可能，只要您有足够的访问权限
Yes, it is possible, providing you have sufficient access
在phpMyAdmin中:
In phpMyAdmin:
- 在复制"选项卡下，您将找到一个向导，向您提供在mySQL配置文件中进行设置的代码(在Ubuntu上，该文件位于"/etc/mysql/my.conf")，
- 单击链接以配置主服务器
- 在表格中输入信息，复制代码
- 转到您的配置文件(位于上面的位置)，然后添加phpMyAdmin提供的代码
- 更新配置文件后，重新启动mySQL并单击phpMyAdmin中的执行"按钮，这将确认更改
- 现在再次在复制"标签下，单击链接以配置从属服务器
- 请确保您的配置文件中具有唯一的服务器ID，如果未将phpMyAdmin提供的行添加到文件的[mysqld]部分中
- 完成必填信息，然后单击执行"
这应该设置您的复制.
This should set up your replication.
同步
如果所有其他方法都失败了，则在phpMyAdmin中转到同步"选项卡，输入信息，然后就走了，您只需要在下一页确认同步，然后在2秒钟后完成同步即可，就是简单.
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.
对于生产服务器，我个人具有主/从复制设置，但是对于开发，当我需要实时"数据来复制错误或问题时，我进行同步，这是因为我仅需要每6-12个月进行一次同步.如果我需要本地实时数据，则可以设置主/从复制
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
这篇关于phpmyadmin同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！