2、Linux 坏境MySQL数据目录和字符集修改

DG0913L

已于 2023-03-12 23:03:15 修改

阅读量177

点赞数 2

分类专栏： Linux Centos7 文章标签： mysql linux 数据库 Powered by 金山文档

于 2023-03-12 14:36:45 首次发布

本文链接：https://blog.csdn.net/liang921119/article/details/129474788

版权

Linux 同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

Centos7

13 篇文章 0 订阅

订阅专栏

文章描述了如何将MySQL的数据目录从/var/lib/mysql/迁移到/data/mysql/data，涉及关闭服务、复制文件、修改配置、重启服务以及处理因SELINUX和防火墙引起的启动问题。此外，还涵盖了修改字符集为utf8和设置忽略表名大小写的步骤。

摘要由CSDN通过智能技术生成

由于我服务器上的磁盘分配/data为数据盘，空间比较大,所以要修改一下mysql的数据目录

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

数据库目前迁移、字符集修改和忽略大小写就此结束，有什么疑问可以评论留言！！！