|
一、定位原因 首先，先放MySQL的相关配置
basedir=/usr/local/mysql datadir=/data/mysql/data
#binlog log_slave_updates=1 binlog_format=row log-bin=/data/mysql/logs/mysql-bin #日志文件的存放时间是7天 expire_logs_days=7 sync_binlog=1 binlog_cache_size=4M max_binlog_size=1024M max_binlog_cache_size=256M lower_case_table_names=1 #错误日志 log-error=/data/mysql/logs/error.log
查看error.log出现如下错误日志
2019-10-24T08:40:00.186176Z 704591 [ERROR] Disk is full writing '/data/mysql/logs/mysql-bin.000014' (Errcode: 16044192 - No space left on device). Waiting for someone to free space... 2019-10-24T08:40:00.186193Z 704591 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2019-10-24T08:50:00.188449Z 704591 [ERROR] Disk is full writing '/data/mysql/logs/mysql-bin.000014' (Errcode: 16044192 - No space left on device). Waiting for someone to free space... 2019-10-24T08:50:00.188477Z 704591 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2019-10-24T09:00:00.190693Z 704591 [ERROR] Disk is full writing '/data/mysql/logs/mysql-bin.000014' (Errcode: 16044192 - No space left on device). Waiting for someone to free space... 2019-10-24T09:00:00.190720Z 704591 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2019-10-24T09:10:00.192057Z 704591 [ERROR] Disk is full writing '/data/mysql/logs/mysql-bin.000014' (Errcode: 16044192 - No space left on device). Waiting for someone to free space... 2019-10-24T09:10:00.192083Z 704591 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2019-10-24T09:20:00.193426Z 704591 [ERROR] Disk is full writing '/data/mysql/logs/mysql-bin.000014' (Errcode: 16044192 - No space left on device). Waiting for someone to free space... 2019-10-24T09:20:00.193453Z 704591 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2019-10-24T09:25:51.789734Z 704901 [Note] Got packets out of order 2019-10-24T09:30:00.195120Z 704591 [ERROR] Disk is full writing '/data/mysql/logs/mysql-bin.000014' (Errcode: 16044192 - No space left on device). Waiting for someone to free space... 2019-10-24T09:30:00.195146Z 704591 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2019-10-24T09:40:00.196549Z 704591 [ERROR] Disk is full writing '/data/mysql/logs/mysql-bin.000014' (Errcode: 16044192 - No space left on device). Waiting for someone to free space... 2019-10-24T09:40:00.196575Z 704591 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
查看mysql-bin
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# ll total 3469032 -rw-r----- 1 mysql mysql 39371438 Oct 30 17:27 error.log -rw-r----- 1 mysql mysql 431598428 Oct 18 16:47 mysql-bin.000013 -rw-r----- 1 mysql mysql 234 Oct 30 16:47 mysql-bin.000014 -rw-r----- 1 mysql mysql 431598428 Oct 30 16:47 mysql-bin.000015 -rw-r----- 1 mysql mysql 68 Oct 30 16:47 mysql-bin.index drwxr-xr-x 2 mysql mysql 4096 Oct 30 17:11 relay-log -rw-r----- 1 mysql mysql 3077816504 Oct 30 16:47 slow.log
查看磁盘空间使用情况 [root@iZ2zefyvsfkm30mv8ve3ccZ logs]# df -h
Filesystem Size Used Avail Use% Mounted on /dev/vda1 20G 19G 11M 100% / devtmpfs 3.9G 0 3.9G 0% /dev tmpfs 3.9G 0 3.9G 0% /dev/shm tmpfs 3.9G 600K 3.9G 1% /run tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup tmpfs 783M 0 783M 0% /run/user/0
/dev/vda1 空间占用100%，空间严重不足；
切记不可直接删除mysql-bin*文件，直接删除会导致mysql-bin.index文件异常，该MySQL使用主从复制，如果直接删除，可能无法主从复制
结果：
日志文件太大，导致磁盘空间不足，MySQL无法自动重启；
解决方法； 在安装未考虑当前服务器是否挂载数据盘，就将MySQL安装到服务器，而现在磁盘空间仅为40G，才会导致如此问题。 最直接的方法就是：挂载数据盘
二、挂载数据盘 1.查看当前服务器是否存在未挂载的数据盘，执行"fdisk -l"命令查看。
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# fdisk -l
Disk /dev/vda: 21.5 GB, 21474836480 bytes, 41943040 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0x000a7708
Device Boot Start End Blocks Id System /dev/vda1 * 2048 41943039 20970496 83 Linux
Disk /dev/vdb: 536.9 GB, 536870912000 bytes, 1048576000 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes
存在未挂载数据盘 /dev/vdb,如果不存在可能需要扩容，忘了收了，该服务器是阿里云的。
2、执行"fdisk /dev/vdb" 对数据盘进行分区
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# fdisk /dev/vdb Command (m for help):m Command (m for help):n Partition number （1-4）：1 First cylinder（1-175664， default 1）：1 Last cylinder or + size or sizeM or + sizeK(1-1775664,default 175664): 回车 Using default value 175664
Command (m for help):wq
3、执行"fdisk -l" 查看分区情况
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# fdisk -l
Disk /dev/vda: 21.5 GB, 21474836480 bytes, 41943040 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0x000a7708
Device Boot Start End Blocks Id System /dev/vda1 * 2048 41943039 20970496 83 Linux
Disk /dev/vdb: 536.9 GB, 536870912000 bytes, 1048576000 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0xd471030e
Device Boot Start End Blocks Id System /dev/vdb1 2048 1048575999 524286976 83 Linux
这时候需要注意，有两种情况， 第一种，你这个数据盘是新购买的，以前没有用过（没有存储过数据），那么就要对其进行格式化，使用“mkfs.ext3 /dev/vdb1”命令。 第二种，如果你之前就有数据盘，只是你把系统盘重置了，需要将数据盘再次挂载到重置后的系统上，那么就不要对其格式化（跳过此步骤），直接执行后面的步骤挂载就可以了。 由于当前数据盘是新的未曾使用过，也就不需要格式化；
4.编辑"/etc/fstab"
由于的MySQL的datadir和log是在/data/mysql/目录下，为了后面转移数据先将其挂载到/data/backup(必须提前创建)目录下，之后再正式挂载到/dat目录下。 在其中添加 /dev/xvdb1 /data/backup ext3 defaults 1 1
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# vim /etc/fstab
# # /etc/fstab # Created by anaconda on Wed Dec 12 07:53:08 2018 # # Accessible filesystems, by reference, are maintained under '/dev/disk' # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info # UUID=d67c3b17-255b-4687-be04-f29190d37396 / ext4 defaults 1 1 /dev/vdb1 /data/backup ext3 defaults 1 1
也可以使用 echo '/dev/vdb1 /data/backup ext3 defaults 1 1 ' >> /etc/fstab
5.使用"mount -a"挂载新数据盘，并用'df -h'命令查看
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# mount -a [root@iZ2zefyvsfkm30mv8ve3ccZ logs]# df -h Filesystem Size Used Avail Use% Mounted on /dev/vda1 20G 19G 11M 100% / devtmpfs 3.9G 0 3.9G 0% /dev tmpfs 3.9G 0 3.9G 0% /dev/shm tmpfs 3.9G 552K 3.9G 1% /run tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup tmpfs 783M 0 783M 0% /run/user/0 /dev/vdb1 493G 0G 468G 0% /data/backup
挂载成功，接下来就是转移数据
三、转移数据
1、备份/data/mysql的所有数据 cd /data/ tar cvf /data/backup/mysql.tar mysql rm -rf mysql
2.卸载数据盘 umount -v /data/backup 3.修改 /etc/fstab,重新挂载到/data/目录下
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# vim /etc/fstab
# # /etc/fstab # Created by anaconda on Wed Dec 12 07:53:08 2018 # # Accessible filesystems, by reference, are maintained under '/dev/disk' # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info # UUID=d67c3b17-255b-4687-be04-f29190d37396 / ext4 defaults 1 1 /dev/vdb1 /data ext3 defaults 1 1
4. mount -a 或者 mount /dev/vdb1 /data 这只是临时挂载
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# df -h Filesystem Size Used Avail Use% Mounted on /dev/vda1 20G 8.4G 11G 45% / devtmpfs 3.9G 0 3.9G 0% /dev tmpfs 3.9G 0 3.9G 0% /dev/shm tmpfs 3.9G 552K 3.9G 1% /run tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup tmpfs 783M 0 783M 0% /run/user/0 /dev/vdb1 493G 21G 447G 5% /data
5. 解压mysql.tar
cd /data/ tar xvf mysql.tar
6. 重启mysql
[root@iZ2zefyvsfkm30mv8ve3ccZ logs]# systemctl restart mysql [root@iZ2zefyvsfkm30mv8ve3ccZ logs]# systemctl status mysql ● mysql.server.service - LSB: start and stop MySQL Loaded: loaded (/etc/rc.d/init.d/mysql.server; bad; vendor preset: disabled) Active: active (running) since Wed 2019-10-30 16:47:56 CST; 1h 37min ago Docs: man:systemd-sysv-generator(8) Process: 4369 ExecStart=/etc/rc.d/init.d/mysql.server start (code=exited, status=0/SUCCESS) CGroup: /system.slice/mysql.server.service ├─4380 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/iZ2zefyvsfkm30mv8ve3ccZ.pid └─5866 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/m...
Oct 30 16:47:44 iZ2zefyvsfkm30mv8ve3ccZ systemd[1]: Starting LSB: start and stop MySQL... Oct 30 16:47:56 iZ2zefyvsfkm30mv8ve3ccZ mysql.server[4369]: Starting MySQL............[ OK ] Oct 30 16:47:56 iZ2zefyvsfkm30mv8ve3ccZ systemd[1]: Started LSB: start and stop MySQL.
成功。
|