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: 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.
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? Connection Name: You can name this whatever you like. Click Test Connection.Add IP to your Remote MySQL
Steps to connect to your database remotely
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.
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_nameIf 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_nameYou 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.gzTransfer the file containing the database contents to the target machine and run these commands there:
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_nameThen 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 tablesDo 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”.