|
Key
This line was removed.
This word was removed. This word was added.
This line was added.
|
Changes (59)
View Page History
...
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 [CDP3:Adding MySQL Instance to the Policy]. 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}
IDENTIFIED by 'some-pass';{code}
{code}
GRANT ALL PRIVILEGES ON *.* to 'user1'@'localhost'
IDENTIFIED by 'some-pass';
{code}
IDENTIFIED by 'some-pass';
{code}
{note:title=Notice }The command will allow you to backup databases, but not restore.
{note}
{note}
...1. First, log in over {color:blue}ssh{color} to the remote MySQL database server. To do this, on Linux or Mac, open the terminal and execute the command:
{code}
ssh user@mysql.server.com
{code}ssh user@mysql.server.com{code}
{code}
Then you will be asked about an untrusted key. Enter "yes" and press <Enter>. Then enter the password.
...* If you are using Red Hat Linux/Fedora/Centos Linux and installed MySQL from {color:blue}rpms{color} downloaded from {color:blue}dev.mysql.com{color}, the full path to the {color:blue}my.cnf{color} file is {color:blue}/etc/my.cnf{color}.
{info}
{info}
{code}
# vim /etc/my.cnf
{code}# vim /etc/my.cnf{code}
{code}
3. Open the file in a text editor and find the section starting with the line:
{code}
{code}[mysqld]{code}
[mysqld]
{code}
Make sure the line skip-networking is commented (or remove the line) and add the following line:
{code}
bind-address = *
{code}bind-address = *{code}
{code}
The entire section should look like this:
{code}
{code}[mysqld]
character-set-server=utf8
collation_server=utf8_general_ci
collation_server=utf8_general_ci
...skip-name-resolve....
..
..
....{code}
{code}
Where,
* *bind-address* \- Contains an asterisk, meaning that the service should bind to all available IP addresses.
* *bind-address* \- Contains an asterisk, meaning that the service should bind to all available IP addresses.
...4. Restart the {color:blue}mysql{color} service.
{code}
# /etc/init.d/mysql restart
{code}# /etc/init.d/mysql restart{code}
{code}
!mysql-restart.png!
...Connect to the MySQL server by executing the following command:
{code}
$ mysql -u root -p mysql
{code}$ mysql -u root -p mysql{code}
{code}
!7.png!
...{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.
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}
{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}
{code}
!8 copy.png!
6. Log out of MySQL by typing the following command:
6. Log out of MySQL by typing the following command:
{code}
exit {code}exit{code}
{code}
!allow09.png!
7. Make sure that there is no firewall that blocks connections to TCP port 3306 of the MySQL server from the CDP Server. It is not always possible to check all the firewalls between one server and the other. But you can always check local {color:blue}iptables{color} firewall on the MySQL server. To add the rule that permits connections to TCP port 3306 from CDP Server with IP address 202.202.200.20, execute the following command:
7. Make sure that there is no firewall that blocks connections to TCP port 3306 of the MySQL server from the CDP Server. It is not always possible to check all the firewalls between one server and the other. But you can always check local {color:blue}iptables{color} firewall on the MySQL server. To add the rule that permits connections to TCP port 3306 from CDP Server with IP address 202.202.200.20, execute the following command:
{code}
{code}iptables -A INPUT -s 202.202.200.20 -m tcp -p tcp --dport 3306 -j ACCEPT{code}
{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}
{code}iptables -A INPUT -s 202.202.200.0/24 -m tcp -p tcp --dport 3306 -j ACCEPT{code}
{code}
Finally, save firewall rules so they will be automatically reloaded when the server reboots:
{code}
# /etc/init.d/iptables save
{code}# /etc/init.d/iptables save{code}
{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:
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}$ mysql -h mysql.server.com -u r1soft -p{code}
{code}
Where,
* *\-h IP or hostname* \- {color:blue}mysql.server.com{color} is the hostname (FQDN) of MySQL server.
* *\-h IP or hostname* \- {color:blue}mysql.server.com{color} is the hostname (FQDN) of MySQL server.
...You can also test if port 3306 is reachable through the firewall using telnet:
{code}
$ telnet mysql.server.com 3306
{code}$ telnet mysql.server.com 3306{code}
{code}
!12_1.png!
If the port is reachable, you will see the following message:
If the port is reachable, you will see the following message:
{code}
{code}Connected to mysql.server.com Escape character is ^]{code}
{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.
...