mariadb 替换为 mysql8 修改mariadb数据库文件路径

转载

AI领域布道师 2023-12-26 15:26:15

文章标签 mariadb 替换为 mysql8 mysql 数据库重启 文章分类 MySQL 数据库

随着业务的变更，需要在新的一台CentOS下部署MariaDB数据库，并把之前的数据库文件迁移到新数据库中。之前想来应该不会太复杂，结果没想到也折腾了一天多。记录下希望今后能避免类似问题，再次遇到此类问题时也不用到处搜索。

在新的CentOS服务器上安装部署MariaDB，安装过很多次了，所以这块就不多累述。新版本的CentOS都有自带的MariaDB，所以得先把自带的卸载了再装自己想安装的版本。接下来就是修改数据目录的操作，这块就得详细记录下。

第一步：停止MariaDB服务，并修改数据目录

1、执行命令，停止MariaDB服务

systemctl stop mariadb

2、在 /home 下创建新的数据文件夹

mkdir mysql

3、修改my.cnf （新增红色部分）

[mysqld]
     datadir=/home/mysql
     socket=/home/mysql/mysql.sock
     skip-name-resolve

4、修改 server.cnf (红色部分)

[mysqld]

datadir=/home/mysql

mysql_install_db --defaults-file=/etc/my.cnf --datadir=/home/mysql/ --user=mysql

[root@localhost home]# mysql_install_db --defaults-file=/etc/my.cnf --datadir=/home/mysql/ --user=mysql
Installing MariaDB/MySQL system tables in '/home/mysql/' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/home/mysql/'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

6、重启数据库服务

[root@localhost mysql]# systemctl start mariadb
Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

经核查日志文件，提示没有权限问题导致的：

mariadb 替换为 mysql8 修改mariadb数据库文件路径_数据库

第二步：解决权限不足的问题

1、根据提示信息，显示数据库服务不能操作新建目录

1.1、核查之前目录所用的权限

mariadb 替换为 mysql8 修改mariadb数据库文件路径_数据库_02

1.2、经核实是用的mysql用户组及用户，所以新建的文件夹也应该要加此用户组

[root@localhost home]# chown -R mysql:mysql /home/mysql
[root@localhost home]# chomd 775 /home/mysql
bash: chomd: 未找到命令...
相似命令是： 'chmod'
[root@localhost home]# chmod 775 /home/mysql
[root@localhost home]# ll
总用量 4
drwxrwxr-x. 16 mysql mysql 4096 7月  10 17:53 admin
drwxrwxr-x.  9 mysql mysql  277 7月  14 10:31 data
drwxrwxr-x   2 mysql mysql  130 7月  14 10:41 mysql

2、再次重启数据库服务，结果还是失败，还是提示没有权限

Jul 14 10:32:00 localhost systemd: mariadb.service failed.
Jul 14 10:35:13 localhost systemd: Starting MariaDB 10.2.32 database server...
Jul 14 10:35:13 localhost mysqld: 2020-07-14 10:35:13 140669217863872 [Note] /usr/sbin/mysqld (mysqld 10.2.32-MariaDB) starting as process 16051 ...
Jul 14 10:35:13 localhost mysqld: 2020-07-14 10:35:13 140669217863872 [Warning] Could not increase number of max_open_files to more than 16364 (request: 32222)
Jul 14 10:35:13 localhost mysqld: 2020-07-14 10:35:13 140669217863872 [Warning] Can't create test file /home/data/localhost.lower-test
Jul 14 10:35:13 localhost mysqld: #007/usr/sbin/mysqld: Can't change dir to '/home/data/' (Errcode: 13 "Permission denied")
Jul 14 10:35:13 localhost mysqld: 2020-07-14 10:35:13 140669217863872 [ERROR] Aborting
Jul 14 10:35:14 localhost systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE
Jul 14 10:35:14 localhost systemd: Failed to start MariaDB 10.2.32 database server.
Jul 14 10:35:14 localhost systemd: Unit mariadb.service entered failed state.
Jul 14 10:35:14 localhost systemd: mariadb.service failed.
Jul 14 10:40:01 localhost systemd: Started Session 124 of user root.
Jul 14 10:45:21 localhost systemd: Starting MariaDB 10.2.32 database server...
Jul 14 10:45:21 localhost mysqld: 2020-07-14 10:45:21 140469303916736 [Note] /usr/sbin/mysqld (mysqld 10.2.32-MariaDB) starting as process 16815 ...
Jul 14 10:45:21 localhost mysqld: 2020-07-14 10:45:21 140469303916736 [Warning] Could not increase number of max_open_files to more than 16364 (request: 32222)
Jul 14 10:45:21 localhost mysqld: 2020-07-14 10:45:21 140469303916736 [Warning] Can't create test file /home/mysql/localhost.lower-test
Jul 14 10:45:21 localhost mysqld: #007/usr/sbin/mysqld: Can't change dir to '/home/mysql/' (Errcode: 13 "Permission denied")
Jul 14 10:45:21 localhost mysqld: 2020-07-14 10:45:21 140469303916736 [ERROR] Aborting
Jul 14 10:45:21 localhost systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE
Jul 14 10:45:21 localhost systemd: Failed to start MariaDB 10.2.32 database server.
Jul 14 10:45:21 localhost systemd: Unit mariadb.service entered failed state.
Jul 14 10:45:21 localhost systemd: mariadb.service failed.

3、各种度娘后发现要修改此文件配置（migrated-from-my.cnf-settings.conf）

[root@localhost mysql]# find / -name migrated-from-my.cnf-settings.conf
/etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
[root@localhost mysql]# vi /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf

[Service]
ProtectHome=false

[root@localhost mysql]# systemctl daemon-reload

4、再次重启服务，直接成功了。

第三步：拷贝当前正式服务器数据库文件到新服务器上

1、拷贝到/home/mysql下后，用数据库查看确实也能看到数据库了，但是发现却不能正常操作表，提示：

ERROR 1932 (42S02): Table 'users' doesn't exist in engine

度娘后发现要同步 ibdata1 此文件才行。

2、停止服务后，同步时需要完全覆盖当前的文件，重启数据库服务报错。

Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] /usr/sbin/mysqld (mysqld 10.2.32-MariaDB) starting as process 18711 ...
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Warning] Could not increase number of max_open_files to more than 16364 (request: 32222)
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Uses event mutexes
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Compressed tables use zlib 1.2.7
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Using Linux native AIO
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Number of pools: 1
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Using SSE2 crc32 instructions
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [Note] InnoDB: Completed initialization of buffer pool
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069184190208 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
Jul 14 11:11:13 localhost mysqld: 2020-07-14 11:11:13 140069742344384 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable

根据此提示，重新加下权限即可