mysql ibdata 数据迁移

mysql ibdata1数据迁移

这两天zabbix上提示一台数据库的空间要满了。mariadb是直接yum安装的，查了一下，/var/lib/mysql/下的文件ibdata1太大了，34G+，根目录下已经快撑满 了。看了一下硬盘的其它分区，还有一个目录挂载的还有500多个G，是肯定够用的。迁移操作记录备忘。

操作步骤

1 备份数据库

cd /home
mkdri mysql 
chown mysql:mysql ./mysql -R
mysqldump -u root -p --add-drop-table --all-databases > ./mysql_20200307.sql

2 关闭mysql服务

systemctl stop mariadb

3 迁移数据

cd /var/lib/mysql/
mv -rv ./* /home/mysql/

4 修改mysql配置文件

vi /etc/my.cnf
# 修改datadir和socket路径

# 修改前
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

# 修改后
[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

5 重新启动mariadb

systemctl restart mariadb
# 创建mysql.sock软链接

ln -s /home/mysql/mysql.sock /var/lib/mysql/mysql.sock

mysql -u root -p

登录成功，mariadb服务和登录正常。

结束。