Remotely Accessing Mysql over SSH.
If you have a requirement to remotely access your mysql databases for example to create full backups read on.
Remotely Accessing your Mysql Databases or tables is incredibly useful if you would like to create backups from remote servers in Datacentre with cronjobs.
Be warned though that a constraint of doing this tutorial will make your Mysql Database unavailable to be accessed whilst the backup is running, that is traffic from external networks to your webserver would be unavailable until the job is finished.
From my own experience, I have found a very small database around 3.9MB on a standard ADSL connection (upto 1.5MBps Down) from a Datacentre to lose connectivity for just under a minute (58 seconds)
The below tutorial outline the steps you require in order to achieve remote access using Debain Wheezy 7 - other Distributions will vary and are out of the scope of this tutorial.
Be aware there are however other ways of doing this.
Step 1 - Edit the my.cnf File
Summary - The my.cnf file is one of the configuration files for mysql. Here we must 'bind' the IP Address of the local server so that MYSQL can LISTEN on the local internet facing IP address.
Using your favourite Text editor, open the config file and look for the section [mysqld]
#~: emacs /etc/mysql/my.cnf
Now locate the line with 'bind-address'
Remove the '*' symbol and add the ipaddres of the LOCAL Server.
Example -
bind-address = 1.2.3.4
(where 1.2.3.4 is my internet facing IP.)
If you don't know your IP, form the console run
#~: curl ifconfig.me
1.2.3.4
Save the File and restart Mysql
#~: service mysql restart
[ ok ] Stopping MySQL database server: mysqld.
[ ok ] Starting MySQL database server: mysqld . ..
If you see this message dont worry about it
[info] Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..
Step 2 - Allow Remote IP
Next is to allow only the Remote Machine you choose to be able to connect access the MySQL Database / Tables.
Firstly Login to mysql as a different user to one you want allow access to. In my example i will use root.
#~: mysql -u root -p
Enter your password
We now have the mysql prompt. like this
mysql>
Now we need to change to the database we want to remotely connect to. In this example the databse will be called 'test' - if you cant remember its exact name you can type:
mysql> show databases;
mysql> use test;
Database changed
At this stage, it is assumed you already have a user and know the password for that user of wchih has access to the database you want to access.
Type the following:
mysql> grant ALL on database.* to 'user'@'4.5.6.7' identified by '123abc';
Where:
ALL - gives full access to the database - use with caution
database - is the name of the database you wish to connect to
.* - means every table in that database - change to specific tables to your requirement
user - is the database username
'123abc' - is the database password
If everything has been copied in correctly you should see the response
Query OK, 0 rows affected (0.01 sec)
Step 3
Add an IPTables Rule to allow the connection from your Remote IP.
If you already have a file with iptables rules inside it you can add the following, otherwise add the keyword 'iptables'.
File version
-A INPUT -i eth0 -s 4.5.6.7/32 -p tcp --dport 3306 -j ACCEPT
Or From the terminal just add the keyword 'iptables'
iptables -A INPUT -i eth0 -s 4.5.6.7/32 -p tcp --dport 3306 -j ACCEPT
That should be it!
Now test it using mysqldump from the Remote Machine.
The example below also then compresses the file and gives it a date and time format so that the file name will never be the same and therefore cant be overwritten.
mysqldump -P3306 -h 1.2.3.4 -u user '-p123abc' database | gzip > /home/test-user/5.6.7.8/db_backups/1website_test`date +\%Y-\%m-\%d_\%H-\%M-\%S`.sql.gz
No comments:
Post a Comment