首页 > 代码库 > 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 statusmysqld 已停
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_logfile1drwx------ 2 mysql mysql 4.0K 2月 19 2014 mysqlsrwxrwxrwx 1 mysql mysql 0 10月 13 10:23 mysql.sockdrwx------ 2 mysql mysql 4.0K 2月 19 2014 testdrwx------ 2 mysql mysql 4.0K 2月 26 2014 zabbix# 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/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid修改后：# more /etc/my.cnf[mysqld]datadir=/usr2/mysqlsocket=/usr2/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid
5.启动mysql服务
# /etc/init.d/mysqld start正在启动 mysqld： [确定]# /etc/init.d/mysqld statusmysqld (pid 11907) 正在运行...
6.连接mysql，根据报错提示创建mysql.sock的软连接，验证库表数据，完成迁移工作。
# mysql -uuser -ppasswordERROR 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 -ppasswordWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 21Server version: 5.1.66 Source distributionCopyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 zabbixReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-----------------------+| Tables_in_zabbix |+-----------------------+| acknowledges || actions || alerts |……
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。