How to Change a MySQL Data Directory to a New Location on CentOS 7
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.
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@datadir;
+-------------------+
| @@datadir |
+-------------------+
| /var/lib/mysql/ |
+-------------------+
1 row in set (0.00 sec)
step.2.
To ensure the integrity of the data, we’ll shut down MySQL before we actually make changes to the data directory:
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Tue 2020-09-22 23:49:45 IST; 8s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2812 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 2790 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2816 (code=exited, status=0/SUCCESS)
Sep 22 23:42:47 localhost.localdomain systemd[1]: Starting MySQL Server...
Sep 22 23:42:50 localhost.localdomain systemd[1]: Started MySQL Server.
Sep 22 23:49:43 localhost.localdomain systemd[1]: Stopping MySQL Server...
Sep 22 23:49:45 localhost.localdomain systemd[1]: Stopped MySQL Server.
step.3.
To create a new mysql directory.
mkdir -p /home/data/mysql/
chmod 755 /home/data/mysql/
chown mysql:mysql /home/data/mysql/
step.4.
Then copy recursively the contents of /var/lib/mysql to /home/data/mysql/ preserving original permissions and timestamps:
[root@localhost ~]# cp -R -p /var/lib/mysql/* /home/data/mysql
step.5.
Configure a New MySQL Data Directory
[root@localhost ~]# vi /etc/my.cnf
BEFORE MYSQL DIRECTORY
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/lib/mysql/mysql.sock
AFTER EDIT MySQL DIRECTORY
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
datadir=/home/data/mysql
socket=/home/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
#socket=/var/lib/mysql/mysql.sock
socket=/home/data/mysql/mysql.sock
step.6.
Restarting MySQL
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-09-23 00:57:20 IST; 19s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 4368 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 4342 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 4371 (mysqld)
CGroup: /system.slice/mysqld.service
└─4371 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Sep 23 00:57:16 localhost.localdomain systemd[1]: Starting MySQL Server...
Sep 23 00:57:20 localhost.localdomain systemd[1]: Started MySQL Server.
step.7. finally login mysql databases.
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@datadir;
+-------------------+
| @@datadir |
+-------------------+
| /home/data/mysql/ |
+-------------------+
1 row in set (0.00 sec)
step.8.
finallly directroy looking in image
Thank You.
No comments:
Post a Comment