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 insteadconnects to MySQL through the agent connection.
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:
GRANT ALL PRIVILEGES ON *.* to 'user1'@'localhost'
IDENTIFIED by 'some-pass';
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.
Using Command Line Interface
1. First, log in over ssh to the remote MySQL database server. To do this, on Linux or Mac, open the terminal and execute the command:
ssh user@mysql.server.com
Then you will be asked about an untrusted key. Enter "yes" and press <Enter>. Then enter the password.
On Windows, you can use the PuTTy utility. Download it from this page - http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html - launch it, enter "mysql.server.com" in the "Host name (or IP address)" input field, and click on the "Open" button.
When you are asked about an untrusted key, click on the "Yes" button.
Then enter the login and password.
2. When the connection is established, you have to open the MySQL server configuration file my.cnf in a text editor such as vim.
# vim /etc/my.cnf
3. Open the file in a text editor and find the section starting with the line:
[mysqld]
Make sure the line skip-networking is commented (or remove the line) and add the following line:
bind-address = *
The entire section should look like this:
[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....
..
....
Where,
- bind-address - Contains an asterisk, meaning that the service should bind to all available IP addresses.
- skip-networking - Do not listen for the TCP port at all. All interactions with mysqld must be made via the Unix domain socket. This option is highly recommended for systems where only local requests are allowed because it provides more security. Since you need to allow a remote connection, this line should be removed from my.cnfor commented out.
4. Restart the mysql service.
# /etc/init.d/mysql restart
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:
$ mysql -u root -p mysql
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.
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.
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';
6. Log out of MySQL by typing the following command:
exit
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 iptables 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:
iptables -A INPUT -s 202.202.200.20 -m tcp -p tcp --dport 3306 -j ACCEPT
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:
iptables -A INPUT -s 202.202.200.0/24 -m tcp -p tcp --dport 3306 -j ACCEPT
Finally, save firewall rules so they will be automatically reloaded when the server reboots:
# /etc/init.d/iptables save
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:
$ mysql -h mysql.server.com -u r1soft -p
Where,
- -h IP or hostname - mysql.server.com is the hostname (FQDN) of MySQL server.
- -u r1soft - Log in as MySQL user r1soft.
- -p - Prompt for password.
You can also test if port 3306 is reachable through the firewall using telnet:
$ telnet mysql.server.com 3306
If the port is reachable, you will see the following message:
Connected to mysql.server.com Escape character is ^]
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.
Using GUI Tools
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.
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.
1. First, open phpMyAdmin in your browser and log in.
2. Click the "Privileges" link. Depending on the version of phpMyAdmin, this link can be found in different parts of the window.
3. Click the "Add a new User" link.
4. On the "Add a New User" window, do the following:
- Enter "r1soft" in the "User name" field.
- Select the "Use text field" option from the drop-down menu next to "Host" and enter 202.202.200.20 in the field next to the option list.
- Enter "r1soft" in the "Password" and "Re-type" fields.
- Click "Check all" next to "Global privileges."
Scroll down and click on the "Go" button.
You will be notified that the user has been successfully created.
5. Repeat steps 2-4. This time, type "127.0.0.1" instead of "202.202.200.20" in the "Host" field.
6. Repeat steps 2-4 again. This time select "Local" in the option list next to the "Host" field. You will not have to type "localhost" in the input field as it will appear there automatically.
Granting Permissions Using HeidiSQL
HeidiSQL(http://www.heidisql.com/) is a freeware Windows application written in Delphi. It is popular among web developers because of its intuitive interface and ease of use. It can be installed on a laptop and can be used for managing MySQL servers that do not have phpMyAdmin installed.
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 the username "r1soft" and password"r1soft" without quotes.
1. First, launch HeidiSQL and press the "New" button to enter MySQL server credentials.
2. 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."
Then press "Open."
You will see the main window of the application.
3. Select Tools > User manager from the application menu.
You will see the User manager window.
4. Click on the "Add" button.
5. Then do the following:
- Enter "r1soft" in the "User name" field.
- Leave the default value "localhost" in the "From host" field.
- Enter "r1soft" in the "Password" and "Repeat password" fields.
- Check the box next to "Global privileges."
6. Click on the "Save" button.
7. Repeat step 4. This time enter 127.0.0.1 in the "From host" field.
8. Repeat step 4. This time enter 202.202.200.20 in the "From host" field.
9. Click on the "Close" button to close the "User manager" window.
