www.zygmund.net

Occasionally I have found a need to connect to certain MySQL tables or Databases remotely. Obviously such a connection presents a security risk and generally it is best to restrict remote users to a specific ip address. This can be done with a firewall rule and/or MySQL provides a way to restrict the user by host. This is how I set up a remote MySQL user. login to your mysql and issue the following command:

SSH into your server if you are working remotely. Create an empty database

mysql -u username -p -e 'CREATE DATABASE database

Now use the following command from your ssh session

mysql -u username -p database < path/to/backup.sql

Backing-up your sql databases can be done effectively with the mysqldump facility. You will need to login as the MySQL server root administrator. The following command will backup ALL your databases.

mysqldump -u myusername -pmypassword -A > backup.sql

To dump a single database

mysqldump -u myusername -pmypassword databasename > backup.sql

Having been notified by go-daddy that all my vhost servers were going to need to be rebuilt ( what a major PAIN!), I decided the time had come to move to a dedicated server. Part of this process was to create a back-up archive of the important parts of the server. To archive all my web data I simply create a tar archive of my vhosts directory.

Using FC6 and SSH into the server, I installed phpMyAdmin using yum. Very straight forward. To access phpMyAdmin you need to enter https://my-server-name/phpMyAdmin This failed with "Access Forbidden". It turns out that the rpm creates a 'phpMyAdmin.conf' file in /etc/httpd/conf.d/ and this file only permits access to 127.0.0.1 by default for security. Because I only ever access remotely, simply modifying the allow directive to my remote ip was sufficient for my needs. Alternatively you could secure your connection using SSL.