本文主要是介绍【服务器运维】MySQL数据存储至数据盘，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
查看磁盘及分区
[root@MySQL tmp]# fdisk -lDisk /dev/sda: 21.5 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000dbf4fDevice Boot Start End Blocks Id System
/dev/sda1 * 1 64 512000 83 Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2 64 2611 20458496 8e Linux LVMDisk /dev/sdb: 21.5 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0000145aDevice Boot Start End Blocks Id SystemDisk /dev/mapper/vg_mysql-lv_root: 17.8 GB, 17792237568 bytes
255 heads, 63 sectors/track, 2163 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000Disk /dev/mapper/vg_mysql-lv_swap: 3154 MB, 3154116608 bytes
255 heads, 63 sectors/track, 383 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
格式化磁盘
[root@MySQL tmp]# mkfs.ext4 /dev/sdb
mke2fs 1.41.12 (17-May-2010)
/dev/sdb is entire device, not just one partition!
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
1310720 inodes, 5242880 blocks
262144 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
160 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: doneThis filesystem will be automatically checked every 22 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
挂载磁盘到/data_disk
[root@MySQL /]# mkdir data_disk
[root@MySQL /]# mount /dev/sdb data_disk/
[root@MySQL /]# df -hl
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_mysql-lv_root17G 2.9G 13G 19% /
tmpfs 1.5G 0 1.5G 0% /dev/shm
/dev/sda1 485M 32M 429M 7% /boot
/dev/sdb 20G 172M 19G 1% /data_disk
[root@MySQL /]#
停止MySQL服务
[root@MySQL /]# service mysqld stop
Stopping mysqld: [ OK ]
[root@MySQL /]#
复制mysql (-a附带权限复制)
[root@MySQL /]# cp -a /var/lib/mysql /data_disk/
修改my.cnf
[root@MySQL /]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sockdatadir=/data_disk/mysql
socket=/data_disk/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
修改mysqld
[root@MySQL /]# vi /etc/init.d/mysqld
#get_mysql_option datadir "/var/lib/mysql" mysqld
get_mysql_option datadir "/data_disk/mysql" mysqld
修改selinux
vi /etc/sysconfig/selinux
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
#SELINUX=enforcing
SELINUX=disable
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
设置自动挂载磁盘fstab
vi /etc/fstab
#
# /etc/fstab
# Created by anaconda on Wed Jan 9 00:13:10 2019
#
# 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
#
/dev/mapper/vg_mysql-lv_root / ext4 defaults 1 1
UUID=58000176-24d9-4406-b3fe-30695aa82ca7 /boot ext4 defaults 1 2
/dev/mapper/vg_mysql-lv_swap swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
#新磁盘
/dev/sdb /data_disk ext4 defaults 0 0
重启CentOS
[root@MySQL ~]# reboot
启动MySQL服务
[root@MySQL ~]# service mysqld start
Starting mysqld: [ OK ]
[root@MySQL ~]#
创建一个数据表test_path验证一下
[root@MySQL ~]# cd /data_disk/mysql/
[root@MySQL mysql]# ls
auto.cnf ibdata1 ib_logfile1 mysql mysql.sock.lock sys
ib_buffer_pool ib_logfile0 ibtmp1 mysql.sock performance_schema test_path
[root@MySQL mysql]#
顺便提一下ONLY_FULL_GROUP_BY问题解决
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
my.cnf最后添加sql_mode，并重启MySQL服务
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
这篇关于【服务器运维】MySQL数据存储至数据盘的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
