1、查询MySQL数据目录
使用show variables命令查询数据目录
mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
可以看出我们的数据目录在/var/lib/mysql/目录下
2、创建MySQL的数据目录
[root@test1 ~]# mkdir -p /data/mysql/data
3、关闭MySQL服务
缺认是否关闭MySQL服务
[root@test1 ~]# systemctl stop mysqld
[root@test1 ~]# systemctl status mysqld
4、复制数据文件
复制数据文件到我们新创建的数据位置，并将原有的目录改名，确保数据库目录修改成功后在删除
[root@test1 ~]# cp -R /var/lib/mysql/* /data/mysql/data/
[root@test1 ~]# mv /var/lib/mysql /var/lib/mysqlback
5、修改数据目录的属主和属组
[root@test1 ~]# chown -R mysql:mysql /data/mysql/
6、修改配置文件
修改配置文件datadir和socket的值
[root@test1 ~]# vi /etc/my.cnf
[root@test1 data]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.scok
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
7、重启MySQL登陆数据库查看
[root@test1 data]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
数据库服务启动的时候报错,查看日志，大致如下
[root@test1 data]# cat /var/log/mysqld.log
2023-03-12T05:47:21.956736Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2023-03-12T05:47:21.956767Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2023-03-12T05:47:21.956781Z 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions
2023-03-12T05:47:21.956789Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
这是因为selinux开启导致的，我们关闭selinux和防火墙
#关闭防火墙
[root@test1 data]# systemctl stop firewalld.service
#开机不启动防火墙
[root@test1 data]# systemctl disable firewalld.service
#查询防火墙状态
[root@test1 data]# systemctl status firewalld.service
#永久关闭SELINUX
[root@test1 data]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
#临时关闭SELINUX
[root@test1 data]# setenforce 0
#查看SELINUX状态
[root@test1 data]# getenforce
重新启动MySQL，一切OK
[root@test1 data]# systemctl start mysqld
[root@test1 data]# systemctl status mysqld
#查询MySQL端口
[root@test1 data]# ss -tnl | grep 3306
LISTEN 0 80 [::]:3306 [::]:*
登陆MySQL，发现报错
[root@test1 data]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
解决方法：
方法1:既然找不到/var/lib/mysql/mysql.sock,那我直接做个软连接将/data/mysql/data/mysql.sock直接连接过去就可以了，实测实可以用的。
方法2:修改my.cnf配置文件
[mysql]
socket=/home/mysql/data/mysql.sock
[client]
socket=/data/mysql/data/mysql.scok
测试，MySQL可以正常登陆
[root@test1 data]# 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.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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>
8、修改MySQL字符集
我们可以使用show variables like '%char%'命令查询MySQL的字符集
mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name | Value |
+--------------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1 |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)
生产过程中，大多数系统默认字符集是utf8，所以我们需要对MySQL字符集进行修改，我们修改my.cnf配置文件
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
修改完成后配置文件如下
[root@test1 data]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.scok
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
[client]
socket=/data/mysql/data/mysql.scok
default-character-set=utf8
[mysql]
socket=/data/mysql/data/mysql.scok
default-character-set=utf8
我们现在查询MySQL字符集
mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name | Value |
+--------------------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1 |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)
9、忽略大小写
生产过程中，有时候数据库的表名为大写，而我们使用小写导致报错，我们修改my.cnf配置文件
[mysqld]
lower_case_table_names = 1
修改完成后配置文件如下
[root@test1 data]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql/data
socket=/data/mysql/data/mysql.scok
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
[client]
socket=/data/mysql/data/mysql.scok
default-character-set=utf8
lower_case_table_names = 1
[mysql]
socket=/data/mysql/data/mysql.scok
default-character-set=utf8
