mysql 数据目录迁移 丢失_mysql数据文件目录迁移

有个需求是需要把mysql的数据文件目录放置在外接存储上面，简单来说就是迁移mysql的datadir目录吧，如果我们已经用rpm包安装了mysql，mysql的数据文件目录默认存储在/var/lib/mysql上面，此时我们需要迁移这个datadir目录到新的目录中。

[root@localhost ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.5.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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> show variables like 'datadir';

+---------------+-----------------+

| Variable_name | Value           |

+---------------+-----------------+

| datadir       | /var/lib/mysql/ |

+---------------+-----------------+

1 row in set (0.00 sec)

[root@localhost ~]# cd /var/lib/mysql/

[root@localhost mysql]# ls -l

total 29968

-rw-rw---- 1 mysql mysql 18874368 Nov  8 01:19 ibdata1

-rw-rw---- 1 mysql mysql  5242880 Nov  8 01:20 ib_logfile0

-rw-rw---- 1 mysql mysql  5242880 Nov  8 01:19 ib_logfile1

-rw-r----- 1 mysql mysql    60253 Nov  8 01:20 localhost.localdomain.err

-rw-rw---- 1 mysql mysql        5 Nov  8 01:20 localhost.localdomain.pid

drwx------ 2 mysql mysql     4096 Nov  8 01:16 mysql

-rw-rw---- 1 mysql mysql      771 Nov  8 01:19 mysql-bin.000001

-rw-rw---- 1 mysql mysql      301 Nov  8 01:19 mysql-bin.000002

-rw-rw---- 1 mysql mysql      301 Nov  8 01:19 mysql-bin.000003

-rw-rw---- 1 mysql mysql      107 Nov  8 01:19 mysql-bin.000004

-rw-rw---- 1 mysql mysql      391 Nov  8 01:19 mysql-bin.000005

……

这个mysql是采用rpm包来安装的，现在有需求需要将其数据文件全部转移到外接存储上面，而这块外接存储是挂载在/data目录下。

现在开始迁移mysql的数据文件目录到/data目录下

[root@localhost ~]# service mysql stop

Shutting down MySQL.[  OK  ]

[root@localhost ~]# mkdir /data

[root@localhost ~]# chown -R mysql:mysql /data

[root@localhost ~]# mv /var/lib/mysql/ /data/

[root@localhost ~]# ls -l /data/mysql/

修改/etc/my.cnf文件

[client]

#password       = your_password

port            = 3306

socket          = /data/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port            = 3306

socket          = /data/mysql/mysql.sock

datadir=/data/mysql

这里主要修改了：

client的socket变量/data/mysql/mysql.sock

mysqld的变量socket = /data/mysql/mysql.sock

datadir=/data/mysql

然后重新启动mysql

[root@localhost ~]# service mysql restart

Shutting down MySQL.[  OK  ]

Starting MySQL..[  OK  ]

mysql> show variables like 'datadir';

+---------------+--------------+

| Variable_name | Value        |

+---------------+--------------+

| datadir       | /data/mysql/ |

+---------------+--------------+

1 row in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.03 sec)

此时mysql的数据文件目录已经迁移完成。