linux更改数据目录,linux 更改mysql 数据存储目录

一般存储在/var/lib/mysql下

1.查看mysql当前的数据存储位置

[root@dlzx ~]# mysqladmin -u root -p variables |grep datadir

Enter password:

| datadir | /usr/local/mysql/data/ |

[root@dlzx ~]#

2.关闭mysql服务

[root@dlzx ~]# service mysqld stop

Shutting down MySQL. SUCCESS!

[root@dlzx ~]#

3.创建新的存储目录  如果已经有了 直接跳过

这里的 /mnt/sdc为之前博文中的新加挂载硬盘

[root@dlzx ~]# cd /mnt

[root@dlzx mnt]# cd sdc/

[root@dlzx sdc]# mkdir mysqldata

[root@dlzx sdc]#

4.把以前的mysql数据存储文件移动到新的目录

[root@dlzx sdc]# mv /usr/local/mysql/data /mnt/sdc/mysqldata/

[root@dlzx sdc]#

5.修改my.cnf

注:并不是所有版本都包含有my.cnf这个配置文件，如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/下找到*.cnf文件，拷贝其中一个到/etc/并改名为my.cnf中。可以删除内容自定义里面你需要的参数配置

修改白色字体的两个对应的地址  对应你要转移mysqldata文件目录

# Uncomment the following if you are using InnoDB tables

#innodb_data_home_dir = /usr/local/mysql/data

innodb_data_home_dir = /mnt/sdc/mysqldata

#innodb_data_file_path = ibdata1:10M:autoextend

#innodb_log_group_home_dir = /usr/local/mysql/data

innodb_log_group_home_dir = /mnt/sdc/mysqldata

还有一个

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

character-set-server=utf8

max_connections=1000

datadir = /mnt/sdc/mysqldata

需要指定 datadir = /mnt/sdc/mysqldata

重启后完成mysql数据存储目录移动变更

问题:

1. 没有找到网上说的.socket文件  以前的my.cnf 中是默认的路径tmp 下 这个路径应该是mysql的启动的时候自动生成的

2. 重新启动mysq  会报一个这样的错误

Starting MySQL.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/dlzx.pid).

具体问题可以看日志

在my.cnf中添加日志记录

[mysqld_safe]

log-error=/var/log/mysqld.log

日志内容

170904 10:21:49 [Note] Plugin 'FEDERATED' is disabled.

/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist

170904 10:21:49 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

170904 10:21:49 InnoDB: The InnoDB memory heap is disabled

170904 10:21:49 InnoDB: Mutexes and rw_locks use GCC atomic builtins

170904 10:21:49 InnoDB: Compressed tables use zlib 1.2.3

170904 10:21:49 InnoDB: Using Linux native AIO

170904 10:21:49 InnoDB: Initializing buffer pool, size = 128.0M

170904 10:21:49 InnoDB: Completed initialization of buffer pool

170904 10:21:49 InnoDB: highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 49439

170904 10:21:49 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

InnoDB: Doing recovery: scanned up to log sequence number 1595675

170904 10:21:49 InnoDB: Starting an apply batch of log records to the database...

第一个error 提示没找到表

我之前更改了我linux的 hostname 也许和这个有关系

我尝试恢复表数据及权限

进入mysql安装目录

执行初始化数据库

scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

重启成功

[root@dlzx mysql]# seriver mysqld start

-bash: seriver: command not found

[root@dlzx mysql]# service mysqld start

Starting MySQL.. SUCCESS!

[root@dlzx mysql]#