Allowing Remote Connections to MySQL Instance

You do not need to specially create a user and allow remote connections to a MySQL Instance. While adding a MySQL Instance to the Policy, you can use the credentials of any MySQL user with full access to the MySQL server from {{localhost}}. For example, you can use a MySQL 'root@localhost' account typically created during the MySQL server installation. See [Adding a MySQL Instance to a Policy].

Customers with both 3.14 Server _and_ Agent can use {{localhost}} for their MySQL instance hostname. The CDP Server does not connect directly to MySQL and instead *connects to MySQL through the agent connection*.
{note:title=Notice}There is no relationship between a MySQL user and a CDP user.
{note}
{note:title=Notice}Admin access to the server and MySQL installation is required.
{note}
{info:title=Tip}In CDP, end-user level MySQL backup is not supported. Only super-users can conduct MySQL backup. Sub-users are limited by agent user permission.
{info}

h5. 3.14 Version Limitation

*Prerequisites*:
* Multiple databases per MySQL Instance
* MySQL user with full access to one of the databases

The MySQL user can backup only databases that belong to him and cannot backup databases that do not belong to him.

Use the following command to allow the user to backup all databases:
{code}GRANT ALL PRIVILEGES ON *.* to 'user1'@'localhost'
IDENTIFIED by 'some-pass';{code}

{note:title=Notice }The command will allow you to backup databases, but not restore.
{note}

h2. Allowing Remote Connections to MySQL Instance (Version 3.12 or Earlier)

By default, remote access to the MySQL database server is disabled for security reasons. Follow the instructions below to provide remote access to the database server.
{note:title=Important Notice}For the MySQL Add-on to work, *both the CDP Server and the Agent* should have administrative permissions for the entire server. The CDP Server will backup the database data, while the Agent will lock and flush tables. Also, the CDP Server needs administrative permissions for restoring data. You can create a special user for backups in MySQL and limit access to MySQL from the CDP Server IP address. Also, you should provide access to this user from localhost and the 127.0.0.1 address for the Agent.
{note}

----
{toc:location=top|maxLevel=3|minLevel=3|type=flat|separator=pipe|style=border:1}

----

Then you will be asked about an untrusted key. Enter "yes" and press <Enter>. Then enter the password.

When you are asked about an untrusted key, click on the "Yes" button.

!3.png!

Then enter the login and password.

{code}# vim /etc/my.cnf{code}

3. Open the file in a text editor and find the section starting with the line:

{code}[mysqld]{code}

Make sure the line skip-networking is commented (or remove the line) and add the following line:

{code}bind-address = *{code}

The entire section should look like this:

{code}[mysqld]
character-set-server=utf8
collation_server=utf8_general_ci
skip-character-set-client-handshake
#skip-innodb
innodb_data_file_path=INNODBDATA1:4096M
innodb_log_file_size=512M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address = *
#skip-networking
skip-external-locking
skip-name-resolve....
..
....{code}

!mysql-restart.png!

5. Grant access to the MySQL server from the IP address of the CDP Server. You should also grant access from IP address 127.0.0.1 and localhost, since the lock and flush operations during the snapshot will be performed by the agent running on the same host as MySQL service. The CDP Server will connect to MySQL as "r1soft" user.

Connect to the MySQL server by executing the following command:

{code}$ mysql -u root -p mysql{code}

!7.png!

Enter the MySQL root password when prompted.

CDP should be able to backup all databases, so you should grant global privileges to the "r1soft" user.
{note:title=Notice}There is no relationship between a MySQL user and a CDP sub-user.
{note}
{note:title=Notice}Admin access to the server and MySQL installation is required.
{note}
{info:title=Tip}In CDP, end-user level MySQL backup is not supported. Only super-users can conduct MySQL backup. Sub-users are limited by agent user permission.
{info}
In the following example, it is assumed that the IP address of the CDP Server is 202.202.200.20 and that the CDP Server will connect with username "r1soft" and password "r1soft" without quotes.

{code}GRANT ALL PRIVILEGES ON *.* TO r1soft@'202.202.200.20' IDENTIFIED BY 'r1soft';
GRANT ALL PRIVILEGES ON *.* TO r1soft@'127.0.0.1' IDENTIFIED BY 'r1soft';
GRANT ALL PRIVILEGES ON *.* TO r1soft@'localhost' IDENTIFIED BY 'r1soft';{code}

!8 copy.png!

6. Log out of MySQL by typing the following command:

{code}exit{code}

If you have several CDP Servers that reside on IP subnet 202.202.200.0/24 and the MySQL server can be backed up by any of them, you can allow access to TCP port 3306 from the entire subnet in one command:

{code}iptables -A INPUT -s 202.202.200.0/24 -m tcp -p tcp --dport 3306 -j ACCEPT{code}

Finally, save firewall rules so they will be automatically reloaded when the server reboots:

{code}# /etc/init.d/iptables save{code}

!10_10.png!

8. Test that your firewall settings and MySQL security settings allow you to connect to the MySQL server from the CDP Server. Execute the following command:

{code}$ mysql -h mysql.server.com -u r1soft -p{code}

!11_11.png!

You can also test if port 3306 is reachable through the firewall using telnet:

{code}$ telnet mysql.server.com 3306{code}

!12_1.png!

If the port is reachable, you will see the following message:

{code}Connected to mysql.server.com Escape character is ^]{code}

Then some garbage characters along with the MySQL version string (here MySQL version is 5.1.51-community). To close the telnet, press the <Ctrl> + <\]> keys or just wait until the connection is closed on timeout.

----
h3. Using GUI Tools

{note:title=Notice} Using GUI tools described in this document only allows you to grant permissions on the MySQL server level. You still have to make sure that the MySQL server listens for network connections and that the firewall does not block its port. There are web applications that allow you to do this in GUI. For example, the popular hosting management panel Webmin allows you to edit firewall rules and text files as well as restart services. Another popular hosting management panel - Parallels Plesk - also allows you to edit firewall rules. Covering all available tools goes far beyond the scope of this documentation.
{note}
\\

h4. Granting Permissions Using phpMyAdmin

PhpMyAdmin ([http://www.phpmyadmin.net/]) is an open source web application written in PHP. It is very popular among web developers because of its intuitive interface and ease of use. A lot of web hosting management panels include phpMyAdmin in their interface.

!phpmyadmin-login.png!

2. Click the "Privileges" link. Depending on the version of phpMyAdmin, this link can be found in different parts of the window.

!phpmyadmin-privileges.png!

3. Click the "Add a new User" link.

!phpmyadmin-add.png!

!phpmyadmin-enteruser.png!

Scroll down and click on the "Go" button.

!phpmyadmin-go.png!

You will be notified that the user has been successfully created.

h4. Granting Permissions Using HeidiSQL

!08_php.png!

2. {color:black}Enter the session name in the list on the left, the Hostname or IP address of the MySQL server, the username, and the password, and press "Save."{color}

!heidi-creden.png!

Then press "Open."

!09_2_php.png!

You will see the main window of the application.

!10_php.png!

3. Select Tools > User manager from the application menu.

!11_php.png!

You will see the User manager window.

!12_php.png!

4. Click on the "Add" button.

!heidi-add.png!

!heidi-global.png!

6. Click on the "Save" button.

!13_2_php.png!\\
{excerpt:hidden=true}Instructions on how to allow remote connections to your MySQL Instance and on how to grant the privileges necessary for connection.{excerpt}