How can i share mysql database between two computers?

Question

Connect to a database with MySQL Workbench on your shared, VPS, or Dedicated hosting account. MySQL Workbench is a free tool you can use to connect to your server databases from your personal computer. MySQL Workbench can be Downloaded for free at the following link:

Table of Contents Show

Add IP to your Remote MySQL
Steps to connect to your database remotely
2.11.13 Copying MySQL Databases to Another Machine
How do I share a SQL database between two computers?
How do I share a MySQL database?
How do I remotely access a MySQL database from another computer?
How can I share data between two computers?

You will want to use the Windows (x86, 64-bit), MSI Installer if you are on Windows. The file we are installing in this tutorial is the mysql-workbench-gpl-5.2.47-win32.msi file.

Once you have downloaded MySQL Workbench, you can install it onto your computer. After you have the program installed, follow the steps below to set up a remote connection.

Don’t have time to read the article? Watch our walk-through video.

Add IP to your Remote MySQL

In order for you to be able to connect to your databases remotely you will need to add your local computer IP address to the Remote MySQL in cPanel. You can get your IP address by going to the following link:

What’s My IP address?

Steps to connect to your database remotely

Open MySQL Workbench.
Click New Connection towards the bottom left of MySQL Workbench.
In the “Set up a New Connection Dialogue” box, Type your Database connection credentials. The credentials will be like the following:
Connection Name: You can name this whatever you like.
Connection Method: Standard (TCP/IP).
Hostname: You can use your domain our your cPanel IP address.
Port: 3306
Username: Your cPanel username or the user you created for the database.
Password: cPanel password or the password for the database user that was created.
Default Schema: This can be left blank.
Click Test Connection.
Type your password and click the “Save Password in Vault” check box.Click Ok.
MySQL Workbench should say “Connection parameters are correct“. Click Ok.
Click Ok again to accept the connection settings.
Now under the SQL Development section you will see your connection listed in the Open Connection to Start Querying box. Click your newly created account.
Now you will see the databases list in the area on the left.

2.11.13 Copying MySQL Databases to Another Machine

In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.

Use mysqldump --help to see what options are available.

The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located:

mysqladmin -h 'other_hostname' create db_name
mysqldump db_name | mysql -h 'other_hostname' db_name

If you want to copy a database from a remote machine over a slow network, you can use these commands:

mysqladmin create db_name
mysqldump -h 'other_hostname' --compress db_name | mysql db_name

You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:

mysqldump --quick db_name | gzip > db_name.gz

Transfer the file containing the database contents to the target machine and run these commands there:

mysqladmin create db_name
gunzip < db_name.gz | mysql db_name

You can also use mysqldump and mysqlimport to transfer the database. For large tables, this is much faster than simply using mysqldump. In the following commands, DUMPDIR represents the full path name of the directory you use to store the output from mysqldump.

First, create the directory for the output files and dump the database:

mkdir DUMPDIR
mysqldump --tab=DUMPDIR db_name

Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:

mysqladmin create db_name           # create database
cat DUMPDIR/*.sql | mysql db_name   # create tables in database
mysqlimport db_name DUMPDIR/*.txt   # load data into tables

Do not forget to copy the mysql database because that is where the grant tables are stored. You might have to run commands as the MySQL root user on the new machine until you have the mysql database in place.

After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.

Note

You can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) See Section 15.2, “The MyISAM Storage Engine”.

To connect to the Database Engine from another computer.

On a second computer that contains the SQL Server client tools, log in with an account authorized to connect to SQL Server, and open Management Studio..

In the Connect to Server dialog box, confirm Database Engine in the Server type box..

Export a MySQL database.

Log into your server via SSH..

Use the command cd to navigate to a directory where your user has write access. ... .

Export the database by executing the following command: mysqldump --add-drop-table -h internal-db.s00000.gridserver.com -u username -p dbname > dbname.sql..