通过rpm安装的mysql默认程序文件放在/usr/bin，数据文件放在/var/lib/mysql，需要将数据文件放在不同分区，保证数据量时将根目录撑爆
一、关闭数据库
[root@node1 bin]# systemctl stop mysqld
[root@node1 bin]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Wed 2024-01-10 22:23:45 CST; 16s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 103618 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 103590 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 103621 (code=exited, status=0/SUCCESS)
Jan 06 23:37:04 node1 systemd[1]: Starting MySQL Server...
Jan 06 23:37:05 node1 systemd[1]: Started MySQL Server.
Jan 10 22:23:41 node1 systemd[1]: Stopping MySQL Server...
Jan 10 22:23:45 node1 systemd[1]: Stopped MySQL Server.二、将数据文件放到其它分区
[root@node1 bin]# cd /home/
[root@node1 home]# mkdir mysqldata
[root@node1 home]# cp -a /var/lib/mysql /home/mysqldata/
[root@node1 home]# cd mysqldata/
[root@node1 mysqldata]# ls
mysql
[root@node1 mysqldata]# ll
total 4
drwxr-x--x 7 mysql mysql 4096 Jan 10 22:23 mysql
[root@node1 mysqldata]# cd mysql/
[root@node1 mysql]# ll
total 176252
drwxr-x--- 2 mysql mysql 20 Jan 6 23:39 12233
-rw-r----- 1 mysql mysql 56 Jan 6 15:33 auto.cnf
-rw------- 1 mysql mysql 1680 Jan 6 15:33 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 6 15:33 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 6 15:33 client-cert.pem
-rw------- 1 mysql mysql 1680 Jan 6 15:33 client-key.pem
-rw-r----- 1 mysql mysql 790 Jan 10 22:23 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Jan 10 22:23 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 10 22:23 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 6 15:33 ib_logfile1
drwxr-x--- 2 mysql mysql 40960 Jan 6 16:36 ivsom
drwxr-x--- 2 mysql mysql 4096 Jan 6 15:33 mysql
drwxr-x--- 2 mysql mysql 8192 Jan 6 15:33 performance_schema
-rw------- 1 mysql mysql 1680 Jan 6 15:33 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jan 6 15:33 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jan 6 15:33 server-cert.pem
-rw------- 1 mysql mysql 1680 Jan 6 15:33 server-key.pem
drwxr-x--- 2 mysql mysql 8192 Jan 6 15:33 sys三、修改配置文件
原来配置文件
[root@node1 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[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
user=root
datadir=/var/lib/mysql //数据文件目录
socket=/var/lib/mysql/mysql.sock //sock目录
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 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修改后的配置文件
[root@node1 mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[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
user=root
datadir=/home/mysqldata/mysql
socket=/home/mysqldata/mysql/mysql.sock
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 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]
default-character-set=utf8
socket=/home/mysqldata/mysql/mysql.sock
[mysql]
default-character-set=utf8
socket=/home/mysqldata/mysql/mysql.sock四、启动数据库
[root@node1 mysql]# systemctl start mysqld
[root@node1 mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2024-01-10 22:42:29 CST; 12s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 126422 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 126385 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 126425 (mysqld)
Tasks: 27
Memory: 191.0M
CGroup: /system.slice/mysqld.service
└─126425 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Jan 10 22:42:27 node1 systemd[1]: Starting MySQL Server...
Jan 10 22:42:29 node1 systemd[1]: Started MySQL Server.五、验证数据文件更新分区是否成功
[root@node1 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> SHOW VARIABLES LIKE 'datadir';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /home/mysqldata/mysql/ | //确实已经更新
+---------------+------------------------+
1 row in set (0.05 sec)
mysql>