mysql 迁移 ibdata1_mysql ibdata1文件迁移

目的：主机系统/var目录快满了，经查询最大的文件是mysql的ibdata1文件，有17G大小，故需要迁移这个文件到其他目录下，以释放/var目录空间。

1.先备份下数据库是个好习惯

# mysqldump -q -uuser -ppassword --add-drop-table --all-databases > /usr2/backup/mysql_1013.sql

2.关闭mysql服务

# /etc/init.d/mysqld stop

停止 mysqld： [确定]

#/etc/init.d/mysqld status

mysqld 已停

3.移动ibdata1及其同目录下的文件，从/var/lib/mysql移动到/usr2/mysql

# pwd

/var/lib/mysql

#ls -lh

总用量 17G-rw-rw---- 1 mysql mysql 17G 10月 13 10:23ibdata1-rw-rw---- 1 mysql mysql 5.0M 10月 13 10:23ib_logfile0-rw-rw---- 1 mysql mysql 5.0M 10月 13 10:23ib_logfile1

drwx------ 2 mysql mysql 4.0K 2月 19 2014mysql

srwxrwxrwx1 mysql mysql 0 10月 13 10:23mysql.sock

drwx------ 2 mysql mysql 4.0K 2月 19 2014test

drwx------ 2 mysql mysql 4.0K 2月 26 2014zabbix

#mkdir /usr2/mysql

#chown mysql:mysql /usr2/mysql

#su -mysql

$iduid=27(mysql) gid=27(mysql) 组=27(mysql)

$mv /var/lib/mysql/*/usr2/mysql/

$

4.修改mysql配置文件/etc/my.cnf,修改datadir和socket路径指向

修改前：

#more /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0[mysqld_safe]

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

pid-file=/var/run/mysqld/mysqld.pid

修改后：

#more /etc/my.cnf

[mysqld]

datadir=/usr2/mysql

socket=/usr2/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0[mysqld_safe]

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

pid-file=/var/run/mysqld/mysqld.pid

5.启动mysql服务

# /etc/init.d/mysqld start

正在启动 mysqld： [确定]

#/etc/init.d/mysqld status

mysqld (pid11907) 正在运行...

6.连接mysql，根据报错提示创建mysql.sock的软连接，验证库表数据，完成迁移工作。

# mysql -uuser -ppassword

ERROR2002 (HY000): Can‘t connect to local MySQL server through socket‘/var/lib/mysql/mysql.sock‘(2)

#ln -s /usr2/mysql/mysql.sock /var/lib/mysql/mysql.sock

# mysql-uuser -ppassword

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

Your MySQL connectionid is 21Server version:5.1.66Source distribution

Copyright (c)2000, 2012, 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 clearthe current input statement.

mysql>show databases;+--------------------+

| Database |

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

| information_schema |

| test |

| zabbix |

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

3 rows in set (0.00sec)

mysql>use zabbix

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql>show tables;+-----------------------+

| Tables_in_zabbix |

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

| acknowledges |

| actions |

| alerts |……