mysql数据库的逻辑卷管理（配置与扩容）
停止监控
停止前端服务
停止数据库
[root@nfs local]# service mysqld stop
Shutting down MySQL.. SUCCESS!
备份数据库
查看是否存在新扩展的硬盘设备
查看存在设备sdb
== 生产中由于扩展的分区数量以及磁盘大小都是比较大，所以使用GPT分区，MBR分区只能分4个主分区，1个逻辑分区，大小也只限制在2TB，有一定的局限性 ==
更多的分区知识可以参考
链接: [link](http://www.eassos.cn/jiao-cheng/ying-pan/mbr-vs-gpt.php(https://www.csdn.net/)
[root@nfs ~]# gdisk -l /dev/sdb
GPT fdisk (gdisk) version 0.8.10
Partition table scan:
MBR: protective
BSD: not present
APM: not present
GPT: present
Found valid GPT with protective MBR; using GPT.
Disk /dev/sdb: 20971520 sectors, 10.0 GiB
Logical sector size: 512 bytes
Disk identifier (GUID): C105F6D4-40BA-4A63-8BA9-28C20AA0B7FB
Partition table holds up to 128 entries
First usable sector is 34, last usable sector is 20971486
Partitions will be aligned on 2048-sector boundaries
Total free space is 20971453 sectors (10.0 GiB)
Number Start (sector) End (sector) Size Code Name
磁盘设备sdb分区
这里使用的GPT分区方式分区2G的空间用于备份数据库的文件
// [root@nfs ~]# gdisk /dev/sdb #对设备sdb进行分区
GPT fdisk (gdisk) version 0.8.10
Partition table scan:
MBR: protective
BSD: not present
APM: not present
GPT: present
Found valid GPT with protective MBR; using GPT.
Command (? for help): ? #寻求帮助
b back up GPT data to a file
c change a partition's name
d delete a partition
i show detailed information on a partition
l list known partition types
n add a new partition
o create a new empty GUID partition table (GPT)
p print the partition table
q quit without saving changes
r recovery and transformation options (experts only)
s sort partitions
t change a partition's type code
v verify disk
w write table to disk and exit
x extra functionality (experts only)
? print this menu
Command (? for help): n #添加新分区
Partition number (1-128, default 1): 1 #分区序号
First sector (34-20971486, default = 2048) or {+-}size{KMGTP}: #其实位置默认，直接回车
Last sector (2048-20971486, default = 20971486) or {+-}size{KMGTP}: +2G #添加2G空间
Current type is 'Linux filesystem'
Hex code or GUID (L to show codes, Enter = 8300): 8300 #默认8300
Changed type of partition to 'Linux filesystem'
Command (? for help): w #保存分区与退出
Final checks complete. About to write GPT data. THIS WILL OVERWRITE EXISTING
PARTITIONS!!
Do you want to proceed? (Y/N): y #输入y确认写分区表
OK; writing new GUID partition table (GPT) to /dev/sdb.
The operation has completed successfully.
[root@nfs ~]# gdisk -l /dev/sdb #再次查看分区内容，已经有一个分区
GPT fdisk (gdisk) version 0.8.10
Partition table scan:
MBR: protective
BSD: not present
APM: not present
GPT: present
Found valid GPT with protective MBR; using GPT.
Disk /dev/sdb: 20971520 sectors, 10.0 GiB
Logical sector size: 512 bytes
Disk identifier (GUID): C105F6D4-40BA-4A63-8BA9-28C20AA0B7FB
Partition table holds up to 128 entries
First usable sector is 34, last usable sector is 20971486
Partitions will be aligned on 2048-sector boundaries
Total free space is 16777149 sectors (8.0 GiB)
Number Start (sector) End (sector) Size Code Name
1 2048 4196351 2.0 GiB 8300 Linux filesystem #分区信息
创建逻辑卷
创建物理卷 -》 创建卷组 -》 创建逻辑卷
[root@nfs /]# pvcreate /dev/sdb1
Physical volume "/dev/sdb1" successfully created.
[root@nfs /]# vgcreate vg01 /dev/sdb1
Volume group "vg01" successfully created
[root@nfs /]# lvcreate -n lv01 --extents 100%free vg01 #分配100%的空余空间给逻辑组
Logical volume "lv01" created.
[root@nfs /]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
root centos -wi-ao---- <17.00g
swap centos -wi-ao---- 2.00g
lv01 vg01 -wi-a----- <2.00g
格式化与挂载
[root@nfs /]# mkfs.ext4 /dev/vg01/lv01
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
130816 inodes, 523264 blocks
26163 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=536870912
16 block groups
32768 blocks per group, 32768 fragments per group
8176 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912
Allocating group tables: done
Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done
[root@nfs /]# mount /dev/vg01/lv01 /u01_mysqldata/
[root@nfs /]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 894M 0 894M 0% /dev
tmpfs 910M 0 910M 0% /dev/shm
tmpfs 910M 11M 900M 2% /run
tmpfs 910M 0 910M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 11G 6.1G 65% /
/dev/sda1 1014M 185M 830M 19% /boot
tmpfs 182M 12K 182M 1% /run/user/42
tmpfs 182M 0 182M 0% /run/user/0
/dev/mapper/vg01-lv01 2.0G 6.0M 1.9G 1% /u01_mysqldata
备份数据库数据文件
复制前先试用命令du -sh查看原文件的大小，复制过去目标目录在次使用该命令确认完整复制过去
[root@nfs /]# cd /usr/local/mysql/
[root@nfs mysql]# ls
bin COPYING data docs include lib man my.cnf mysql-test README scripts share sql-bench support-files
[root@nfs mysql]# du -sh data/
176M data/
[root@nfs mysql]# rsync -av data /u01_mysqldata #复制mysql数据文件给逻辑卷
sending incremental file list
data/
data/auto.cnf
data/ib_logfile0
data/ib_logfile1
data/ibdata1
data/nfs.test.cn.err
data/nfs.test.cn.pid
data/mysql/
data/mysql/columns_priv.MYD
data/mysql/columns_priv.MYI
data/mysql/columns_priv.frm
data/mysql/db.MYD
data/mysql/db.MYI
data/mysql/db.frm
data/mysql/event.MYD
data/mysql/event.MYI
data/mysql/event.frm
data/mysql/func.MYD
data/mysql/func.MYI
data/mysql/func.frm
data/mysql/general_log.CSM
data/mysql/general_log.CSV
data/mysql/general_log.frm
data/mysql/help_category.MYD
data/mysql/help_category.MYI
data/mysql/help_category.frm
data/mysql/help_keyword.MYD
data/mysql/help_keyword.MYI
data/mysql/help_keyword.frm
data/mysql/help_relation.MYD
data/mysql/help_relation.MYI
data/mysql/help_relation.frm
data/mysql/help_topic.MYD
data/mysql/help_topic.MYI
data/mysql/help_topic.frm
data/mysql/innodb_index_stats.frm
data/mysql/innodb_index_stats.ibd
data/mysql/innodb_table_stats.frm
data/mysql/innodb_table_stats.ibd
data/mysql/ndb_binlog_index.MYD
data/mysql/ndb_binlog_index.MYI
data/mysql/ndb_binlog_index.frm
data/mysql/plugin.MYD
data/mysql/plugin.MYI
data/mysql/plugin.frm
data/mysql/proc.MYD
data/mysql/proc.MYI
data/mysql/proc.frm
data/mysql/procs_priv.MYD
data/mysql/procs_priv.MYI
data/mysql/procs_priv.frm
data/mysql/proxies_priv.MYD
data/mysql/proxies_priv.MYI
data/mysql/proxies_priv.frm
data/mysql/servers.MYD
data/mysql/servers.MYI
data/mysql/servers.frm
data/mysql/slave_master_info.frm
data/mysql/slave_master_info.ibd
data/mysql/slave_relay_log_info.frm
data/mysql/slave_relay_log_info.ibd
data/mysql/slave_worker_info.frm
data/mysql/slave_worker_info.ibd
data/mysql/slow_log.CSM
data/mysql/slow_log.CSV
data/mysql/slow_log.frm
data/mysql/tables_priv.MYD
data/mysql/tables_priv.MYI
data/mysql/tables_priv.frm
data/mysql/time_zone.MYD
data/mysql/time_zone.MYI
data/mysql/time_zone.frm
data/mysql/time_zone_leap_second.MYD
data/mysql/time_zone_leap_second.MYI
data/mysql/time_zone_leap_second.frm
data/mysql/time_zone_name.MYD
data/mysql/time_zone_name.MYI
data/mysql/time_zone_name.frm
data/mysql/time_zone_transition.MYD
data/mysql/time_zone_transition.MYI
data/mysql/time_zone_transition.frm
data/mysql/time_zone_transition_type.MYD
data/mysql/time_zone_transition_type.MYI
data/mysql/time_zone_transition_type.frm
data/mysql/user.MYD
data/mysql/user.MYI
data/mysql/user.frm
data/performance_schema/
data/performance_schema/accounts.frm
data/performance_schema/cond_instances.frm
data/performance_schema/db.opt
data/performance_schema/events_stages_current.frm
data/performance_schema/events_stages_history.frm
data/performance_schema/events_stages_history_long.frm
data/performance_schema/events_stages_summary_by_account_by_event_name.frm
data/performance_schema/events_stages_summary_by_host_by_event_name.frm
data/performance_schema/events_stages_summary_by_thread_by_event_name.frm
data/performance_schema/events_stages_summary_by_user_by_event_name.frm
data/performance_schema/events_stages_summary_global_by_event_name.frm
data/performance_schema/events_statements_current.frm
data/performance_schema/events_statements_history.frm
data/performance_schema/events_statements_history_long.frm
data/performance_schema/events_statements_summary_by_account_by_event_name.frm
data/performance_schema/events_statements_summary_by_digest.frm
data/performance_schema/events_statements_summary_by_host_by_event_name.frm
data/performance_schema/events_statements_summary_by_thread_by_event_name.frm
data/performance_schema/events_statements_summary_by_user_by_event_name.frm
data/performance_schema/events_statements_summary_global_by_event_name.frm
data/performance_schema/events_waits_current.frm
data/performance_schema/events_waits_history.frm
data/performance_schema/events_waits_history_long.frm
data/performance_schema/events_waits_summary_by_account_by_event_name.frm
data/performance_schema/events_waits_summary_by_host_by_event_name.frm
data/performance_schema/events_waits_summary_by_instance.frm
data/performance_schema/events_waits_summary_by_thread_by_event_name.frm
data/performance_schema/events_waits_summary_by_user_by_event_name.frm
data/performance_schema/events_waits_summary_global_by_event_name.frm
data/performance_schema/file_instances.frm
data/performance_schema/file_summary_by_event_name.frm
data/performance_schema/file_summary_by_instance.frm
data/performance_schema/host_cache.frm
data/performance_schema/hosts.frm
data/performance_schema/mutex_instances.frm
data/performance_schema/objects_summary_global_by_type.frm
data/performance_schema/performance_timers.frm
data/performance_schema/rwlock_instances.frm
data/performance_schema/session_account_connect_attrs.frm
data/performance_schema/session_connect_attrs.frm
data/performance_schema/setup_actors.frm
data/performance_schema/setup_consumers.frm
data/performance_schema/setup_instruments.frm
data/performance_schema/setup_objects.frm
data/performance_schema/setup_timers.frm
data/performance_schema/socket_instances.frm
data/performance_schema/socket_summary_by_event_name.frm
data/performance_schema/socket_summary_by_instance.frm
data/performance_schema/table_io_waits_summary_by_index_usage.frm
data/performance_schema/table_io_waits_summary_by_table.frm
data/performance_schema/table_lock_waits_summary_by_table.frm
data/performance_schema/threads.frm
data/performance_schema/users.frm
data/tp5shop/
data/tp5shop/db.opt
data/tp5shop/tpshop_address.frm
data/tp5shop/tpshop_address.ibd
data/tp5shop/tpshop_attribute.frm
data/tp5shop/tpshop_attribute.ibd
data/tp5shop/tpshop_auth.frm
data/tp5shop/tpshop_auth.ibd
data/tp5shop/tpshop_cart.frm
data/tp5shop/tpshop_cart.ibd
data/tp5shop/tpshop_category.frm
data/tp5shop/tpshop_category.ibd
data/tp5shop/tpshop_goods.frm
data/tp5shop/tpshop_goods.ibd
data/tp5shop/tpshop_goods_attr.frm
data/tp5shop/tpshop_goods_attr.ibd
data/tp5shop/tpshop_goodspics.frm
data/tp5shop/tpshop_goodspics.ibd
data/tp5shop/tpshop_manager.frm
data/tp5shop/tpshop_manager.ibd
data/tp5shop/tpshop_order.frm
data/tp5shop/tpshop_order.ibd
data/tp5shop/tpshop_order_goods.frm
data/tp5shop/tpshop_order_goods.ibd
data/tp5shop/tpshop_role.frm
data/tp5shop/tpshop_role.ibd
data/tp5shop/tpshop_type.frm
data/tp5shop/tpshop_type.ibd
data/tp5shop/tpshop_user.frm
data/tp5shop/tpshop_user.ibd
sent 184,180,358 bytes received 3,221 bytes 52,623,879.71 bytes/sec
total size is 184,124,428 speedup is 1.00
[root@nfs mysql]# du -sh /u01_mysqldata/
176M /u01_mysqldata/
备份数据库数据文件成功！
同步数据库
这步也是如法炮制，先创建足够大小空间的逻辑卷，将原不合理的数据库文件转移到逻辑卷，然后释放root下面的数据库文件，将同步到逻辑卷的数据库文件挂到数据库文件目录
设备分区
[root@nfs ~]# gdisk /dev/sdb
GPT fdisk (gdisk) version 0.8.10
Partition table scan:
MBR: protective
BSD: not present
APM: not present
GPT: present
Found valid GPT with protective MBR; using GPT.
Command (? for help): n
Partition number (2-128, default 2): 2
First sector (34-20971486, default = 4196352) or {+-}size{KMGTP}:
Last sector (4196352-20971486, default = 20971486) or {+-}size{KMGTP}:
Current type is 'Linux filesystem'
Hex code or GUID (L to show codes, Enter = 8300): 8300
Changed type of partition to 'Linux filesystem'
Command (? for help): w
Final checks complete. About to write GPT data. THIS WILL OVERWRITE EXISTING
PARTITIONS!!
Do you want to proceed? (Y/N): y
OK; writing new GUID partition table (GPT) to /dev/sdb.
Warning: The kernel is still using the old partition table.
The new table will be used at the next reboot.
The operation has completed successfully.
[root@nfs ~]# gdisk -l /dev/sdb
GPT fdisk (gdisk) version 0.8.10
Partition table scan:
MBR: protective
BSD: not present
APM: not present
GPT: present
Found valid GPT with protective MBR; using GPT.
Disk /dev/sdb: 20971520 sectors, 10.0 GiB
Logical sector size: 512 bytes
Disk identifier (GUID): C105F6D4-40BA-4A63-8BA9-28C20AA0B7FB
Partition table holds up to 128 entries
First usable sector is 34, last usable sector is 20971486
Partitions will be aligned on 2048-sector boundaries
Total free space is 2014 sectors (1007.0 KiB)
Number Start (sector) End (sector) Size Code Name
1 2048 4196351 2.0 GiB 8300 Linux filesystem
2 4196352 20971486 8.0 GiB 8300 Linux filesystem
Tip：
再添加分区后，不能够在分区表列出，需要进行重启刷新
重启前
[root@nfs ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 19G 0 part
├─centos-root 253:0 0 17G 0 lvm /
└─centos-swap 253:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 10G 0 disk
├─sdb1 8:17 0 2G 0 part
└─vg01-lv01 253:2 0 2G 0 lvm
sr0 11:0 1 1024M 0 rom
重启后
[root@nfs ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 19G 0 part
├─centos-root 253:0 0 17G 0 lvm /
└─centos-swap 253:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 10G 0 disk
├─sdb1 8:17 0 2G 0 part
│ └─vg01-lv01 253:2 0 2G 0 lvm
└─sdb2 8:18 0 8G 0 part
sr0 11:0 1 1024M 0 rom
创建逻辑卷与格式挂载
创建逻辑卷
通常生产中策略存储空间超过85%会发生告警，所以分配的空间一定要大于告警的大小。如告警时数据库文件大小为17G，那分配的空间必定要大于20G。
[root@nfs ~]# du -sh /usr/local/mysql/
1.2G /usr/local/mysql/
[root@nfs ~]# pvcreate /dev/sdb2
Physical volume "/dev/sdb2" successfully created.
[root@nfs ~]# vgcreate vg02 /dev/sdb2
Volume group "vg02" successfully created
[root@nfs ~]# lvcreate -n lv_mysql -l 100%free vg02
Logical volume "lv_mysql" created.
[root@nfs ~]# vgs
VG #PV #LV #SN Attr VSize VFree
centos 1 2 0 wz--n- <19.00g 0
vg01 1 1 0 wz--n- <2.00g 0
vg02 1 1 0 wz--n- <8.00g 0
[root@nfs ~]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
root centos -wi-ao---- <17.00g
swap centos -wi-ao---- 2.00g
lv01 vg01 -wi-a----- <2.00g
lv_mysql vg02 -wi-a----- <8.00g
格式化与挂载
[root@nfs ~]# mkdir /u02_mysql
[root@nfs ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 894M 0 894M 0% /dev
tmpfs 910M 0 910M 0% /dev/shm
tmpfs 910M 11M 900M 2% /run
tmpfs 910M 0 910M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 11G 6.1G 65% /
/dev/sda1 1014M 185M 830M 19% /boot
tmpfs 182M 12K 182M 1% /run/user/42
tmpfs 182M 0 182M 0% /run/user/0
[root@nfs ~]# mkfs.ext4 /dev/vg0
vg01/ vg02/
[root@nfs ~]# mkfs.ext4 /dev/vg02/lv_mysql
mke2fs 1.42.9 (28-Dec-2013)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
524288 inodes, 2096128 blocks
104806 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=2147483648
64 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
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
[root@nfs ~]# mount /dev/vg02/lv_mysql /u02_mysql/
[root@nfs ~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 20G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 19G 0 part
├─centos-root 253:0 0 17G 0 lvm /
└─centos-swap 253:1 0 2G 0 lvm [SWAP]
sdb 8:16 0 10G 0 disk
├─sdb1 8:17 0 2G 0 part
│ └─vg01-lv01 253:2 0 2G 0 lvm /u01_mysqldata
└─sdb2 8:18 0 8G 0 part
└─vg02-lv_mysql 253:3 0 8G 0 lvm /u02_mysql
sr0 11:0 1 1024M 0 rom
同步数据库（重点）
思路：首先将数据库同步到逻辑卷和卸载逻辑卷，然后清除root下面的数据库文件，最后挂载逻辑卷到数据库目录
[root@nfs ~]# rsync -av /usr/local/mysql/* /u02_mysql/ #同步原数据库文件到逻辑眷属
sending incremental file list
mysql/
mysql/COPYING
mysql/README
mysql/my.cnf
mysql/bin/
mysql/bin/innochecksum
mysql/bin/msql2mysql
mysql/bin/my_print_defaults
mysql/bin/myisam_ftdump
mysql/bin/myisa
...
...
...
mysql/support-files/mysql.server
mysql/support-files/mysqld_multi.server
sent 1,196,938,635 bytes received 194,262 bytes 114,012,656.86 bytes/sec
total size is 1,196,004,103 speedup is 1.00
[root@nfs ~]# df -h #可以观察到逻辑卷增加1.2G，同步成功
Filesystem Size Used Avail Use% Mounted on
devtmpfs 894M 0 894M 0% /dev
tmpfs 910M 0 910M 0% /dev/shm
tmpfs 910M 11M 900M 2% /run
tmpfs 910M 0 910M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 11G 6.1G 65% /
/dev/sda1 1014M 185M 830M 19% /boot
tmpfs 182M 12K 182M 1% /run/user/42
tmpfs 182M 0 182M 0% /run/user/0
/dev/mapper/vg01-lv01 2.0G 182M 1.7G 10% /u01_mysqldata
/dev/mapper/vg02-lv_mysql 7.8G 1.2G 6.2G 17% /u02_mysql
[root@nfs ~]# umount /u02_mysql #卸载逻辑卷
[root@nfs ~]# rm -rf /usr/local/mysql/* #释放root上的原数据库文件
[root@nfs ~]# df -h #确认root上空间的释放以及逻辑卷的卸载
Filesystem Size Used Avail Use% Mounted on
devtmpfs 894M 0 894M 0% /dev
tmpfs 910M 0 910M 0% /dev/shm
tmpfs 910M 11M 900M 2% /run
tmpfs 910M 0 910M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 11G 7.0G 60% /
/dev/sda1 1014M 185M 830M 19% /boot
tmpfs 182M 12K 182M 1% /run/user/42
tmpfs 182M 0 182M 0% /run/user/0
/dev/mapper/vg01-lv01 2.0G 182M 1.7G 10% /u01_mysqldata
[root@nfs local]# mount /dev/vg02/lv_mysql /usr/local/mysql/ #挂载逻辑卷到数据库目录
[root@nfs local]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 894M 0 894M 0% /dev
tmpfs 910M 0 910M 0% /dev/shm
tmpfs 910M 11M 900M 2% /run
tmpfs 910M 0 910M 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 11G 7.0G 60% /
/dev/sda1 1014M 185M 830M 19% /boot
tmpfs 182M 12K 182M 1% /run/user/42
tmpfs 182M 0 182M 0% /run/user/0
/dev/mapper/vg01-lv01 2.0G 182M 1.7G 10% /u01_mysqldata
/dev/mapper/vg02-lv_mysql 7.8G 1.2G 6.2G 17% /usr/local/mysql #成功挂载
启动相关服务
启动数据库
[root@nfs local]# service mysqld start
Starting MySQL. SUCCESS!
设置开机自启
[root@nfs ~]# vim /etc/rc.local
添加 mount /dev/vg02/lv_mysql /usr/local/mysql
测试
重启服务器，查看设备有没有自动挂载，数据库mysql是否自启，登录数据库添加文件是否成功
1762
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
