MySQL磁盘满移动数据到其他磁盘

MySQL磁盘满移动数据到其他磁盘

环境信息

[root@centos7 ~]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core)
[root@centos7 ~]# mysql -uroot -prootroot -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| version()  |
+------------+
| 5.7.43-log |
+------------+
[root@centos7 ~]#

#磁盘信息
[root@centos7 ~]# lsblk 
NAME              MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda                 8:0    0  100G  0 disk 
|-sda1              8:1    0  7.5G  0 part [SWAP]
`-sda2              8:2    0 92.6G  0 part /
sdb                 8:16   0  200G  0 disk 
|-mysqlvg-mysqllv 253:1    0   50G  0 lvm  /mysql/app
|-mysqlvg-datalv  253:2    0  100G  0 lvm  /mysql/data
`-mysqlvg-loglv   253:3    0   40G  0 lvm  /mysql/log
sdc                 8:32   0  200G  0 disk 
`-bakvg-baklv     253:0    0  190G  0 lvm  /mysql/backup
sr0                11:0    1  4.3G  0 rom  
[root@centos7 ~]#

#文件系统
[root@centos7 ~]# df -h
Filesystem                   Size  Used Avail Use% Mounted on
/dev/sda2                     93G  2.0G   91G   3% /
devtmpfs                     1.9G     0  1.9G   0% /dev
tmpfs                        1.9G     0  1.9G   0% /dev/shm
tmpfs                        1.9G   12M  1.9G   1% /run
tmpfs                        1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/mapper/bakvg-baklv      187G   61M  178G   1% /mysql/backup
/dev/mapper/mysqlvg-mysqllv   50G  2.7G   44G   6% /mysql/app
/dev/mapper/mysqlvg-datalv    99G  1.3G   93G   2% /mysql/data
/dev/mapper/mysqlvg-loglv     40G   49M   38G   1% /mysql/log
tmpfs                        378M     0  378M   0% /run/user/0
[root@centos7 ~]# 

操作说明

创建测试数据

create database testdb;
use testdb
-- 创建表
CREATE TABLE `tbl_test` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `item_code` bigint NOT NULL COMMENT '子项编号',
  `order_code` varchar(100) NOT NULL COMMENT '订单编号',
  `id_card` varchar(30) NOT NULL COMMENT '身份证',
  `goods_number` bigint NOT NULL COMMENT '商品数量',
  `amount` decimal(6,2) NOT NULL COMMENT '金额',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `random` bigint NOT NULL COMMENT '数据数',
  PRIMARY KEY (`id`),
  KEY `index_item_code` (`item_code`),
  KEY `index_id_card` (`id_card`),
  KEY `index_random` (`random`)
);

-- 在tbl_test表中，goods_number列上创建索引
CREATE INDEX index_goods_number ON tbl_test (goods_number);

insert into tbl_test (
    name,
	item_code,
	order_code,
	id_card,
	goods_number,
	amount,
	create_time,
	random)
select
   CONCAT("test", substring(md5(rand()),1,5)),
   floor(rand()*10000),
   CONCAT("order", substring(md5(rand()),1,5)),
   FLOOR(RAND() * 10000000000000),
   floor(rand()*10000),
   ROUND(RAND() * 100, 2),
   NOW(),
   FLOOR(RAND() * 1000000)
FROM
    information_schema.tables a,
    information_schema.tables b
LIMIT 6000000;


mysql> select count(*) from tbl_test;
+----------+
| count(*) |
+----------+
|    79524 |
+----------+
1 row in set (0.02 sec)

模拟数据盘磁盘满，需要把数据迁移到其他磁盘

mysql> show variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /mysql/data/3306/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)

假设/mysql/data文件系统满了，现在想要把数据移动到/mysql/backup文件系统

#1、停数据库
service mysql stop
#2、移动数据文件到新路径
cd /mysql/data/3306/data
mv * /mysql/backup/
#3、修改配置文件，指定新路径
sed -i 's#/mysql/data/3306/data#/mysql/backup#' /mysql/data/3306/my.cnf
grep 'datadir' /mysql/data/3306/my.cnf
#4、启动数据库
service mysql start

问题说明：

使用mysql脚本管理启停的时候，一定要注意mysql脚本里涉及的路径也得修改

[root@centos7 data]# systemctl status mysql 
* mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:systemd-sysv-generator(8)
[root@centos7 data]# grep '^datadir=' /etc/rc.d/init.d/mysql
datadir=/mysql/data/3306/data
[root@centos7 data]#

使用以下命令启动：

nohup /bin/sh /mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir=/mysql/backup --pid-file=/mysql/data/3306/mysql.pid &