MySQL ibdata1 File Migration

Purpose: Host system/var directory is almost full, the query is the largest file is MySQL ibdata1 file, 17G size, it is necessary to migrate this file to other directories to release the/var directory space.

1. It's a good habit to back up the database first.

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

2. Turn off the MySQL service

#/etc/init.d/mysqld Stop Stop mysqld: [OK]#/etc/init.d/mysqld Statusmysqld stopped

3. Move ibdata1 and its files in the same directory, move from/var/lib/mysql to/usr2/mysql

# pwd/var/lib/mysql# LS-LH Total dosage 17g-rw-rw----1 mysql mysql  17G October 10:23 ibdata1-rw-rw----1 mysql mysql 5.0M October 1 3 10:23 ib_logfile0-rw-rw----1 mysql mysql 5.0M October 10:23 ib_logfile1drwx------2 mysql mysql 4.0K February  mysq  lsrwxrwxrwx 1 mysql mysql    0 October 10:23 mysql.sockdrwx------2 mysql mysql 4.0K February  testdrwx------2 MySQL MySQL 4.0K February  zabbix# mkdir/usr2/mysql# chown mysql:mysql/usr2/mysql# su-mysql$iduid=27 (mysql) gid=27 (mys QL) group =27 (MySQL) $ mv/var/lib/mysql/*/usr2/mysql/$

4. Modify the MySQL configuration file/etc/my.cnf, modify the DataDir and socket path to point to

Modified before: # 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 Modified: # 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. Start the MySQL service

#/etc/init.d/mysqld Start is starting mysqld: [OK]#/etc/init.d/mysqld statusmysqld (PID  11907) is running ...

6. Connect MySQL, create Mysql.sock soft connection According to the error prompt, verify the library table data, complete the migration work.

# 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) #, Oracle and/or its Affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names 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 Comple tion 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 |

MySQL ibdata1 File Migration