Mysql – Using MySQL Workbench, what is a good way to do development on an alternate server and copy changes to the main server

MySQL

This is a noobie question. I'm taking over a database project from someone who recently left the company and I'm looking for advice on how to do some development on a test platform before making changes on the active server. I've got MySQL installed on both the server and my local machine, with MySQL Workbench as the interface to both. Apache and PHP are also installed on both platforms.

I think what I want is a local copy of the schema (including data) that I can occasionally synchronize with the server. So when I want to do any development I would:

pull any changes from the server
make changes locally and test them out
then copy the modifications back up to the server.

Is that what people do in practice?

If No: What is the recommended way to do development without messing up the existing database?
If Yes: Can you offer some details on how to do this with MySQL Workbench.

Thank you, and please let me know if you need any more information.

Best Answer

What I traditionally do, if I can't sync between databases, is take a copy of the schema, add dummy data, and develop on that locally. Then I save all my modification scripts and run them on the main server when I'm ready to push.

I would avoid moving data from development to production. Production -> Development may be fine if you don't have large amounts of data. Otherwise, use a dummy data generator if you can.

Ultimately, you'll have to develop your own methodology just like you do when you introduce version control or backups to a project. It is dependent on the situation.

Related Solutions

MySQL Workbench Error – Creating Foreign Key Issue

You will get this message if you're trying to add a constraint with a name that's already used somewhere else.

According to this post you can check your constraint like this:

If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

SELECT
  constraint_name,
  table_name
FROM
  information_schema.table_constraints
WHERE
  constraint_type = 'FOREIGN KEY'
  AND table_schema = DATABASE()
ORDER BY
  constraint_name;

Mysql – Trying to use MySQL Workbench with TCP/IP over SSH – failed to connect

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
Finally I have the following entered in MySQL WorkBench.
- SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
- SSH Username: sshuser
- SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
- MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
- MySQL Server Port: 3306 (also default)
- Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

Best Answer

Related Solutions

MySQL Workbench Error – Creating Foreign Key Issue

Mysql – Trying to use MySQL Workbench with TCP/IP over SSH – failed to connect

Related Question