MySQL 初步安装后更改datadir目录几种方式

2021-08-24 04:20:40

链接：http://blog.itpub.net/28602568/viewspace-1663979/

标题：MySQL 初步安装后更改datadir目录几种方式

注释：

若mysql 安装初步使用rpm默认将db安装到/var/lib/mysql/路径后，想更改db路径，怎么处理呢？

测试步骤：

1、简单叙述安装步骤

2、更改db目录的2中方式..

①. 通过db初始化命令更改db存放位置 /usr/bin/mysql_install_db --user=mysql --basedir=/usr/ --datadir=/data/public3306/db/

②. 通过将原来db目录内内容拷贝到新地址，再更改my.cnf 文件的datadir （类似oracle 冷备，更改控制文件 datafile指针..）

3、卸载搭建的测试环境

一、简单叙述安装步骤

--> 准备安装包

[root@lottery ~]# ll
-rw-rw-rw- 1 root root 18360416 5月 19 10:54 MySQL-client-5.6.19-1.el6.x86_64.rpm
-rw-rw-rw- 1 root root 54592892 5月 19 10:54 MySQL-server-5.6.19-1.el6.x86_64.rpm
-->rpm -vhi 安装包

[root@lottery ~]# rpm -hvi MySQL-client-5.6.19-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client ########################################### [100%]
[root@lottery ~]# rpm -hvi MySQL-server-5.6.19-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server ########################################### [100%]
-->启动mysql服务

[root@lottery ~]# /etc/init.d/mysql start

Starting MySQL... SUCCESS!
-->初始化密码

# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -uroot
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# /etc/init.d/mysqld restart
# mysql -uroot -p
Enter password:
mysql> show variables like '%datadir%';

二、更改db目录的2中方式..

①. 通过db初始化命令更改db存放位置 /usr/bin/mysql_install_db --user=mysql --basedir=/usr/ --datadir=/data/public3306/db/

-- 将之前数据库停掉--用mysql_install_db 脚本初始化（新建库）到/data/public3306/db/ 目录的数据库..

-- 若原数据库有新建的database ，需要将database对应的目录cp -rp 到更改后的路径/data/public3306/db/ ；

-- 否则原新的database不会在新路径体现....
[root@lottery public3306]# /usr/bin/mysql_install_db --user=mysql --basedir=/usr/ --datadir=/data/public3306/db/
Installing MySQL system tables...OK
Filling help tables...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 MySQL 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 lottery 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 manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr//bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
WARNING: Found existing config file /usr//my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr//my-new.cnf,
please compare it with your file and take the changes you need.
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server
[root@lottery public3306]# /etc/init.d/mysql start
Starting MySQL.... SUCCESS!
[root@lottery public3306]# cd db
[root@lottery db]# ll
总用量 2097172
-rw-rw---- 1 mysql mysql         56 5月 19 15:47 auto.cnf
drwx------ 2 mysql mysql       4096 5月 19 15:46 mysql
drwx------ 2 mysql mysql       4096 5月 19 15:46 performance_schema
drwx------ 2 mysql mysql       4096 5月 19 15:46 test
[root@lottery db]#

②. 通过将原来db目录内内容拷贝到新地址，再更改my.cnf 文件的datadir

-- 测试之前将原database，执行create database lottery ，利于迁移后是否正确.

--> 原路径

[root@lottery mysql]# pwd

/var/lib/mysql/
--> 目录开头的为database

[root@lottery mysql]# ll
-rw-rw---- 1 mysql mysql         56 5月 19 15:47 auto.cnf
drwx------ 2 mysql mysql       4096 5月 19 15:52 lottery
drwx------ 2 mysql mysql       4096 5月 19 15:46 mysql
drwx------ 2 mysql mysql       4096 5月 19 15:46 performance_schema
drwx------ 2 mysql mysql       4096 5月 19 15:46 test
--> 查看lottery 数据库的表

[root@lottery lottery]# ll lottery/*
-rw-rw---- 1 mysql mysql 54 5月 19 15:47 db.opt
-rw-rw---- 1 mysql mysql 8560 5月 19 15:52 t1.frm ---t1表一张
-rw-rw---- 1 mysql mysql 98304 5月 19 15:52 t1.ibd

--> 创建新datadir目录

[root@lottery lottery ]# mkdir -p /data/public3306/db

-- 参数-p：级联创建
--> 整个mysql datadir进行拷贝

[root@lottery lottery ]# cp -pr /var/lib/mysql/ /data/public3306/db

-- 参数-p：连带属性拷贝，-r是目录拷贝
--> 检查cp 的文件内容

[root@lottery lottery  ]# cd /data/public3306/db
[root@lottery db]# ll
-rw-rw---- 1 mysql mysql         56 5月 19 15:47 auto.cnf
drwx------ 2 mysql mysql       4096 5月 19 15:52 lottery
drwx------ 2 mysql mysql       4096 5月 19 15:46 mysql
drwx------ 2 mysql mysql       4096 5月 19 15:46 performance_schema
drwx------ 2 mysql mysql       4096 5月 19 15:46 test
--> 更改my.cnf 的datadir参数，将原来的/var/lib/mysql 改成 /data/public3306/db

[root@lottery db]# vi /etc/my.cnf
--> 启动mysql服务

[root@lottery db]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

--> 查看更改后datadir是否有效的2种方式

--> OS层查看
[root@lottery db]# ps -ef | grep mysql
root     11346     1 0 16:07 pts/6    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data/public3306/db --pid-file=/data/public3306/mysqld.pid
mysql    12007 11346 17 16:07 pts/6    00:00:03 /usr/sbin/mysqld --basedir=/usr --datadir=/data/public3306/db --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/data/public3306/err.log --pid-file=/data/public3306/mysqld.pid --socket=/data/public3306/mysql.sock --port=3306
--> DB层查看

三、卸载搭建的测试环境

--> 查看系统中是否以rpm包安装的mysql

[root@lottery db]# rpm -qa | grep -i mysql
MySQL-client-5.6.19-1.el6.x86_64
MySQL-server-5.6.19-1.el6.x86_64
--> 通过rpm -e 卸载软件包

[root@lottery db]# rpm -e MySQL-client-5.6.19-1.el6.x86_64
[root@lottery db]# rpm -e MySQL-server-5.6.19-1.el6.x86_64
--> 查看是否有mysql服务，删除 mysql服务命令：chkconfig --del mysql

[root@lottery db]# chkconfig --list | grep -i mysql
--> mysql程序命令进行删除

[root@lottery db]# whereis mysql
mysql: /usr/lib64/mysql
[root@lottery db]# rm -rf /usr/lib64/mysql
.... 卸载完成..

【源于本人笔记】若有书写错误，表达错误，请指正...

码农公寓

相关文章