MySQL ibdata1文件迁移
2014-10-13 11:37 AlfredZhao 阅读(1180) 评论(1) 编辑 收藏 举报目的:主机系统/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:23 ibdata1 -rw-rw---- 1 mysql mysql 5.0M 10月 13 10:23 ib_logfile0 -rw-rw---- 1 mysql mysql 5.0M 10月 13 10:23 ib_logfile1 drwx------ 2 mysql mysql 4.0K 2月 19 2014 mysql srwxrwxrwx 1 mysql mysql 0 10月 13 10:23 mysql.sock drwx------ 2 mysql mysql 4.0K 2月 19 2014 test drwx------ 2 mysql mysql 4.0K 2月 26 2014 zabbix # mkdir /usr2/mysql # chown mysql:mysql /usr2/mysql # su - mysql $id uid=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 (pid 11907) 正在运行...
6.连接mysql,根据报错提示创建mysql.sock的软连接,验证库表数据,完成迁移工作。
# mysql -uuser -ppassword ERROR 2002 (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 connection id is 21 Server version: 5.1.66 Source 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 clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | | zabbix | +--------------------+ 3 rows in set (0.00 sec) mysql> use zabbix Reading table information for completion 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 | ……
AlfredZhao©版权所有「从Oracle起航,领略精彩的IT技术。」