Basically my webhost does not allow remote connections to the database on their server; that is I cannot link my script files on my "localhost" server (on my machine) to the mysql database on the webhost server. This means that I have to use a seperate database on my local machine for it to work with my localhost server. As a result, everytime I update something in my local database I have to re-upload the whole DB to the server, which is rather tediuos. So, I have two questions: 1) Is there any way I can connect to the database remotely? 2) If not, then is there a way of uploading JUST the changes made in the mysql database on my localhost server?
don't tell me you were reuploading the whole database each time you made a change? What about the content of the database/website? You loose everything each time you make a small change. Usually you make all the changes on localhost and write down each action you did on the database. After you tested everything and it works you do the same changes on the live server, possible write a script to do them all at once so you don't need to take it offline. SQL servers should not be accessible through the internet ever. This would be a huge security risk. You should avoid hosting companies that offer this.
Well yeah... Basically I could be making a fair few changes in the database and having tested it offline I just re-upload the whole DB - this would be quicker and less tedious than making the same changes again in the live DB. This is why I was wondering is there anyway MySQL can produce a script with the changes that were made since the last save?
Maybe i'm not following correctly, but by "changes in the database" do you mean changes to a table definition with ALTER TABLE or something like that? And you don't want to do every single alter on the remote system? If so, you can do a "SHOW CREATE TABLE your_table_name" in your mysql client on your local mysql instance to see what CREATE TABLE can be used to create the exact same table definition. But if want to just paste in that CREATE TABLE to the remote system, you'll have to do a "DROP TABLE your_table_name" first, loosing any data in the table.. which may not be what you want. However, if you are talking about changes in the data itself, like adding/removing rows, look at the "mysqldump" command line tool that came with MySQL. mysqldump will output a "CREATE TABLE" and an "INSERT" line for every single row in a table (or whole database) that can be used to populate another database with the same tables and data. It can be used something like this on your local server: mysqldump my_database_name my_table_name > my_data.sql Code (markup): The above will create a file named "my_data.sql" that contains the CREATE TALBE and INSERTs needed to populate another database. Now copy my_data.sql to your remote server and do the following on the remote command line: mysql my_database_name < my_data.sql Code (markup): This will populate the remote my_database_name with the same tables and data on the local one. You will run into problems if the tables already exist remotely unless you use the "--add-drop-table" to the mysqldump command line above so that the sql commands inside of my_data.sql will do a DROP TABLE before a CREATE TABLE to avoid conflicts. I hope this helps!
Hi Drunnels. Thanks for your input. I was talking about changes to the data in the database. I already use mysqldump to create the dump file and then I import this into phpmyadmin. The dump file puts in a "DROP TABLE IF EXISTS" command before every table definition so that's not a problem. I just think it's excessive having to re-upload the whole DB whenever I add or change any data in the database.
I'd say use replication.. but since it sounds like your hosting provider does not want to allow you remote access to mysql maybe you can write a script to run on your local machine in a cron job or something that checks for new entries and then does a POST or GET to a remote script at your webhost that will insert the new entries to the remote database?
Can you gain SSH access to the server? If so you could use something like MySQL Front (http://www.mysqlfront.de/) which supports SSH Tunneling for MySQL