随着业务的变更，需要在新的一台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
5、重新执行安装命令：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.
经核查日志文件，提示没有权限问题导致的：
第二步：解决权限不足的问题
1、根据提示信息，显示数据库服务不能操作新建目录
1.1、核查之前目录所用的权限
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
根据此提示，重新加下权限即可
chown -R mysql:mysql /home/mysql
再次重启就没有问题了。数据表也能正常操作了。