How to Configure MySQL Data Directory on Block Storage (datadir Setup)
When you want to move MySQL data to block storage for better capacity or performance. This article explains how to relocate the MySQL data directory, update the datadir configuration in my.cnf, and restart the service to apply the changes.
How to Enable Remote Access To MySQL Database Server?
By default, remote access to the MySQL database server is disabled for security reasons. However, sometimes you need to provide remote access to a database server from home or a web server. This article will explain how to set up a user account and access a MySQL server remotely on Linux or Unix-like systems.
Before we begin this guide, you have to know how to establish an SSH connection using PuTTY (Windows) or built-in terminal shell (Linux, macOS). First, login over ssh to the remote MySQL database server. You may need to login to your MySQL server as the root user:
Step 1. Changing MySQL Configuration
By default, MySQL is not listening for external connections. You need to change that by adding an extra option in the configuration file. Here are the steps:
1. Log in to your server and run this command to determine the location of the MySQL configuration file:
# mysql --help | grep "Default options" -A 1
2. Now that you know MySQL looks at /etc/my.cnf file for options. You need to open it using the nano editor:
# nano /etc/my.cnf
3. Locate the line that contains [mysqld] label and add the following code below:
bind-address=YOUR.SERVER.IP
Make sure line skip-networking is commented (or remove line).
Where,
bind-address: IP address to bind to.
skip-networking : Do not listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in a comment state.
4. Save the file by pressing CTRL+X (or COMMAND+X if you are on MAC). For changes to take effect, restart your MySQL daemon by running this command:
If you are using Debian / Ubuntu Linux, type the following command to restart the mysql server:
# /etc/init.d/mysql restart
OR
# systemctl restart mysql
If you are using RHEL / CentOS, type the following command to restart the mysql server:
# /etc/init.d/mysqld restart
OR
# systemctl restart mysqld
Step 2: Granting Access to a User from a Remote Machine
The next step is to allow access to the database to the remote user.
Log in to the MySQL server as the root user by typing:
# mysql -u root -p
From inside the MySQL shell, use the GRANT statement to grant access for the remote user.
mysql> GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';
Where,
database_name is the name of the database that the user will connect to.
user_name is the name of the MySQL user.
ip_address is the IP address from which the user will connect. Use % to allow the user to connect from any IP address.
user_password is the user password.
For example, to grant access to a database dbname to a user named foo with password my_passwd from a client machine with IP 10.6.0.1, you would run:
GRANT ALL ON dbname.* TO foo@'10.6.0.1' IDENTIFIED BY 'my_passwd';
Step 3. Opening The Required Port
By default, MySQL is set to use TCP 3306 port for external connections. Thus, you need to open this port in the firewall by executing the command below:
# iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
Alternatively, you can grant access to just one IP:
# iptables -A INPUT -i eth0 -s IP_address -p tcp --destination-port 3306 -j ACCEPT
2. Save the iptables configuration by entering:
# service iptables save
From now on, your server will allow remote connections to your MySQL database.
4. Verifying the Changes
To verify that the remote user can connect to the MySQL server run the following command:
# mysql -u user_name -h mysql_server_ip -p
Where,
username: name of the user you granted access to
mysql_server_ip: IP address of the host where the MySQL server runs.
If everything is set up correctly, you will be able to login to the remote MySQL server.
How to configure MySQL in additional block storage.
Databases grow over time, sometimes outgrowing the space on the file system. You can also run into I/O contention when they’re located on the same partition as the rest of the operating system. RAID, network block storage, and other devices can offer redundancy and other desirable features. Whether you’re adding more space, evaluating ways to optimize performance, or looking to take advantage of other storage features, you may need to relocate MySQL’s data directory.
In this example, we’re moving the data to a block storage device mounted at /mnt/volume-01
Step 1 — Moving the MySQL Data Directory
To prepare for moving MySQL’s data directory, let’s verify the current location by starting an interactive MySQL session using the administrative credentials.
# mysql -u root -p
When prompted, supply the MySQL root password. Then from the MySQL prompt, select the data directory:
mysql> select @@datadir; output: +-----------------+ | @@datadir | +-----------------+ | /var/lib/mysql/ | +-----------------+ 1 row in set (0.00 sec)
This output confirms that MySQL is configured to use the default data directory, /var/lib/mysql/, so that’s the directory we need to move. Once you’ve confirmed this, type exit and press “ENTER” to leave the monitor:
mysql> exit
To ensure the integrity of the data, we’ll shut down MySQL before we actually make changes to the data directory:
# systemctl stop mysqld
Or
# service mysql status
Now that the server is shut down, we’ll copy the existing database directory to the new location with rsync. Using the -a flag preserves the permissions and other directory properties, while-v provides verbose output so you can follow the progress.
Note: Be sure there is no trailing slash on the directory, which may be added if you use tab completion. When there’s a trailing slash, rsync will dump the contents of the directory into the mount point instead of transferring it into a containing mysql directory:
# rsync -av /var/lib/mysql /mnt/volume-01
Once the rsync is complete, rename the current folder with a .bak extension and keep it until we’ve confirmed the move was successful. By renaming it, we’ll avoid confusion that could arise from files in both the new and the old location:
# mv /var/lib/mysql /var/lib/mysql.bak
Step 2 — Pointing to the New Data Location
MySQL has several ways to override configuration values. By default, the datadir is set to /var/lib/mysql in the /etc/my.cnf file. Edit this file to reflect the new data directory:
# vi /etc/my.cnf
Find the line in the [mysqld] block that begins with datadir=, which is separated from the block heading with several comments. Change the path which follows to reflect the new location. In addition, since the socket was previously located in the data directory, we’ll need to update it to the new location:
[mysqld]
. . .
datadir=/mnt/volume-01/mysql
socket=/mnt/volume-01/mysql/mysql.sock
. . .
After updating the existing lines, we’ll need to add configuration for the mysql client. Insert the following settings at the bottom of the file so it won’t split up directives in the [mysqld] block:
/etc/my.cnf [client] port=3306 socket=/mnt/volume-01/mysql/mysql.sock
When you’re done, hit ESCAPE, then type :wq! to save and exit the file.
Step 3 — Restarting MySQL
Now that we’ve updated the configuration to use the new location, we’re ready to start MySQL and verify our work.
# systemctl start mysqld
# systemctl status mysqld
Or
# service mysql start
# service mysql status
To make sure that the new data directory is indeed in use, start the MySQL monitor.
# mysql -u root -p
Look at the value for the data directory again:
mysql> select @@datadir;
Output +----------------------------+ | @@datadir | +----------------------------+ | /mnt/volume-01/mysql/ | +----------------------------+ 1 row in set (0.01 sec)
Now that you’ve restarted MySQL and confirmed that it’s using the new location, take the opportunity to ensure that your database is fully functional. Once you’ve verified the integrity of any existing data, you can remove the backup data directory with sudo rm -rf /var/lib/mysql.bak.