MYSQL数据库的二进制安装和使用
一准备二进制安装包
要把安装的mariadb都卸载了。
输入rpm -qa "MariaDB*"或者rpm -qa "mariadb*"查看安装包。5.5之后的版本使用后面的命令。
注意我使用的是47虚拟机
[root@centos74 ~]# rpm -qa "MariaDB*" [root@centos74 ~]# rpm -qa "mariadb*" mariadb-libs-5.5.56-2.el7.x86_64
传输源码包，进行二进制安装
源码包要到官网下载
[root@centos74 ~]# rpm -qa "MariaDB*"
[root@centos74 ~]# rpm -qa "mariadb*"
mariadb-libs-5.5.56-2.el7.x86_64
[root@centos74 ~]# rz
对安装包进行解压,并且解压到/usr/local/
系统管理员在本机自行安装自己下载的软件（非distribution默认提供者），建议安装到此目录， 这样会比较便于管理。
注意工作中使用的软件的版本至少要滞后一个版本以上。
[root@centos74 ~]# tar fxv mariadb-10.2.12-linux-x86_64.tar.gz -C /usr/local/
#这个二进制程序放在哪是有讲究的。因为这个已经编译完了，编译的人已经指定路径。
[root@centos74 ~]# cd /usr/local/ [root@centos74 local]# ls bin etc games include lib lib64 libexec mariadb-10.2.12-linux-x86_64 sbin share src
注意解压之后生成了一个目录，但是不是正常编译安装后解压缩的目录。
可以改名，修改解压后的目录名为 mysql，但是加个软链接会比较好，因为软链接换版本会比较方便。
如果是自己源码编译的话想放哪就放哪。
[root@centos74 local]# ls bin etc games include lib lib64 libexec mariadb-10.2.12-linux-x86_64 sbin share src [root@centos74 local]# ll total 4 drwxr-xr-x. 2 root root 6 Apr 11 2018 bin drwxr-xr-x. 2 root root 6 Apr 11 2018 etc drwxr-xr-x. 2 root root 6 Apr 11 2018 games drwxr-xr-x. 2 root root 6 Apr 11 2018 include drwxr-xr-x. 2 root root 6 Apr 11 2018 lib drwxr-xr-x. 2 root root 6 Apr 11 2018 lib64 drwxr-xr-x. 2 root root 6 Apr 11 2018 libexec drwxrwxr-x. 12 1021 1004 4096 Jan 4 2018 mariadb-10.2.12-linux-x86_64 drwxr-xr-x. 2 root root 6 Apr 11 2018 sbin drwxr-xr-x. 5 root root 49 Dec 7 17:11 share drwxr-xr-x. 2 root root 6 Apr 11 2018 src
创建软链接
[root@centos74 local]# ln -s mariadb-10.2.12-linux-x86_64/ mysql
[root@centos74 local]# ll
total 4
drwxr-xr-x. 2 root root 6 Apr 11 2018 bin
drwxr-xr-x. 2 root root 6 Apr 11 2018 etc
drwxr-xr-x. 2 root root 6 Apr 11 2018 games
drwxr-xr-x. 2 root root 6 Apr 11 2018 include
drwxr-xr-x. 2 root root 6 Apr 11 2018 lib
drwxr-xr-x. 2 root root 6 Apr 11 2018 lib64
drwxr-xr-x. 2 root root 6 Apr 11 2018 libexec
drwxrwxr-x. 12 1021 1004 4096 Jan 4 2018 mariadb-10.2.12-linux-x86_64
lrwxrwxrwx. 1 root root 29 Dec 7 18:26 mysql -> mariadb-10.2.12-linux-x86_64/
drwxr-xr-x. 2 root root 6 Apr 11 2018 sbin
drwxr-xr-x. 5 root root 49 Dec 7 17:11 share
drwxr-xr-x. 2 root root 6 Apr 11 2018 src
二安装前的设置
（二）创建登录数据库的用户账号
先看一下本机是否有这个账号。如果有就不要建了。
-r表示的是系统账号root。-s指定shell类型。创建了账号就会自动创建一个和用户同名的组
注意DNS也是这样做的。官方先创建组在创建用户没有必要，会更麻烦。
[root@centos74 ~]# id mysql
id: mysql: no such user
[root@centos74 ~]# getent passwd mysql
[root@centos74 ~]# useradd -r mysql -s /sbin/nologin
[root@centos74 ~]# getent passwd mysql
mysql:x:995:993::/home/mysql:/sbin/nologin
[root@centos74 ~]# id mysql
uid=995(mysql) gid=993(mysql) groups=993(mysql)
（二）指定登录数据库账号的所有者和所属组
没有指定所有者，所属组是谁。把所有者，所属组都改成mysql
[root@centos local]# ll m mariadb-10.2.12-linux-x86_64/ mysql/ [root@centos local]# ll mysql/ total 180 drwxrwxr-x. 2 1021 1004 4096 Nov 14 2017 bin -rw-r--r--. 1 1021 1004 17987 Jan 3 2018 COPYING -rw-r--r--. 1 1021 1004 86263 Jan 3 2018 COPYING.thirdparty -rw-r--r--. 1 1021 1004 2275 Jan 3 2018 CREDITS drwxrwxr-x. 3 1021 1004 18 Jan 3 2018 data -rw-r--r--. 1 1021 1004 8245 Jan 3 2018 EXCEPTIONS-CLIENT drwxrwxr-x. 3 1021 1004 19 Jan 3 2018 include -rw-r--r--. 1 1021 1004 8694 Jan 3 2018 INSTALL-BINARY drwxrwxr-x. 4 1021 1004 4096 Nov 14 2017 lib drwxrwxr-x. 4 1021 1004 30 Jan 3 2018 man drwxrwxr-x. 11 1021 1004 4096 Jan 3 2018 mysql-test -rw-r--r--. 1 1021 1004 2374 Jan 3 2018 README.md -rw-r--r--. 1 1021 1004 19510 Jan 3 2018 README-wsrep drwxrwxr-x. 2 1021 1004 30 Jan 3 2018 scripts drwxrwxr-x. 32 1021 1004 4096 Jan 3 2018 share drwxrwxr-x. 4 1021 1004 4096 Jan 3 2018 sql-bench drwxrwxr-x. 3 1021 1004 275 Jan 3 2018 support-files [root@centos local]# ll mariadb-10.2.12-linux-x86_64/ total 180 drwxrwxr-x. 2 1021 1004 4096 Nov 14 2017 bin -rw-r--r--. 1 1021 1004 17987 Jan 3 2018 COPYING -rw-r--r--. 1 1021 1004 86263 Jan 3 2018 COPYING.thirdparty -rw-r--r--. 1 1021 1004 2275 Jan 3 2018 CREDITS drwxrwxr-x. 3 1021 1004 18 Jan 3 2018 data -rw-r--r--. 1 1021 1004 8245 Jan 3 2018 EXCEPTIONS-CLIENT drwxrwxr-x. 3 1021 1004 19 Jan 3 2018 include -rw-r--r--. 1 1021 1004 8694 Jan 3 2018 INSTALL-BINARY drwxrwxr-x. 4 1021 1004 4096 Nov 14 2017 lib drwxrwxr-x. 4 1021 1004 30 Jan 3 2018 man drwxrwxr-x. 11 1021 1004 4096 Jan 3 2018 mysql-test -rw-r--r--. 1 1021 1004 2374 Jan 3 2018 README.md -rw-r--r--. 1 1021 1004 19510 Jan 3 2018 README-wsrep drwxrwxr-x. 2 1021 1004 30 Jan 3 2018 scripts drwxrwxr-x. 32 1021 1004 4096 Jan 3 2018 share drwxrwxr-x. 4 1021 1004 4096 Jan 3 2018 sql-bench drwxrwxr-x. 3 1021 1004 275 Jan 3 2018 support-files
使用 chown -R 是为了连同家目录底下的用户/群组属性都一起变更的意思
使用 chmod 没有 -R ，是因为我们仅要修改目录的权限而非内部文件的权限
[root@centos local]# chown -R mysql.mysql mysql/
[root@centos74 local]# ll mariadb-10.2.12-linux-x86_64/
total 180
drwxrwxr-x. 2 mysql mysql 4096 Nov 14 2017 bin
-rw-r--r--. 1 mysql mysql 17987 Jan 3 2018 COPYING
-rw-r--r--. 1 mysql mysql 86263 Jan 3 2018 COPYING.thirdparty
-rw-r--r--. 1 mysql mysql 2275 Jan 3 2018 CREDITS
drwxrwxr-x. 3 mysql mysql 18 Jan 4 2018 data
-rw-r--r--. 1 mysql mysql 8245 Jan 3 2018 EXCEPTIONS-CLIENT
drwxrwxr-x. 3 mysql mysql 19 Jan 4 2018 include
-rw-r--r--. 1 mysql mysql 8694 Jan 3 2018 INSTALL-BINARY
drwxrwxr-x. 4 mysql mysql 4096 Nov 14 2017 lib
drwxrwxr-x. 4 mysql mysql 30 Jan 4 2018 man
drwxrwxr-x. 11 mysql mysql 4096 Jan 4 2018 mysql-test
-rw-r--r--. 1 mysql mysql 2374 Jan 3 2018 README.md
-rw-r--r--. 1 mysql mysql 19510 Jan 3 2018 README-wsrep
drwxrwxr-x. 2 mysql mysql 30 Jan 4 2018 scripts
drwxrwxr-x. 32 mysql mysql 4096 Jan 4 2018 share
drwxrwxr-x. 4 mysql mysql 4096 Jan 4 2018 sql-bench
drwxrwxr-x. 3 mysql mysql 275 Jan 4 2018 support-files
[root@centos74 local]# ll mysql/
total 180
drwxrwxr-x. 2 mysql mysql 4096 Nov 14 2017 bin
-rw-r--r--. 1 mysql mysql 17987 Jan 3 2018 COPYING
-rw-r--r--. 1 mysql mysql 86263 Jan 3 2018 COPYING.thirdparty
-rw-r--r--. 1 mysql mysql 2275 Jan 3 2018 CREDITS
drwxrwxr-x. 3 mysql mysql 18 Jan 4 2018 data
-rw-r--r--. 1 mysql mysql 8245 Jan 3 2018 EXCEPTIONS-CLIENT
drwxrwxr-x. 3 mysql mysql 19 Jan 4 2018 include
-rw-r--r--. 1 mysql mysql 8694 Jan 3 2018 INSTALL-BINARY
drwxrwxr-x. 4 mysql mysql 4096 Nov 14 2017 lib
drwxrwxr-x. 4 mysql mysql 30 Jan 4 2018 man
drwxrwxr-x. 11 mysql mysql 4096 Jan 4 2018 mysql-test
-rw-r--r--. 1 mysql mysql 2374 Jan 3 2018 README.md
-rw-r--r--. 1 mysql mysql 19510 Jan 3 2018 README-wsrep
drwxrwxr-x. 2 mysql mysql 30 Jan 4 2018 scripts
drwxrwxr-x. 32 mysql mysql 4096 Jan 4 2018 share
drwxrwxr-x. 4 mysql mysql 4096 Jan 4 2018 sql-bench
drwxrwxr-x. 3 mysql mysql 275 Jan 4 2018 support-files
（三）指定二进制程序的路径
下面这些都是二进制程序
[root@centos74 local]# cd mysql/bin/ [root@centos74 bin]# ls aria_chk mariadb_config mysql mysql_convert_table_format mysql_find_rows mysqlslap replace
wsrep_sst_xtrabackup-v2 aria_dump_log mbstream mysqlaccess mysqld mysql_fix_extensions mysqltest resolveip aria_ftdump msql2mysql mysqladmin mysqld_multi mysqlhotcopy mysqltest_embedded resolve_stack_dump aria_pack myisamchk mysqlbinlog mysqld_safe mysqlimport mysql_tzinfo_to_sql wsrep_sst_common aria_read_log myisam_ftdump mysqlcheck mysqld_safe_helper mysql_plugin mysql_upgrade wsrep_sst_mariabackup garbd myisamlog mysql_client_test mysqldump mysql_secure_installation mysql_waitpid wsrep_sst_mysqldump innochecksum myisampack mysql_client_test_embedded mysqldumpslow mysql_setpermission mytop wsrep_sst_rsync mariabackup my_print_defaults mysql_config mysql_embedded mysqlshow perror wsrep_sst_xtrabackup
指定二进制程序路径，执行数据库相关命令时就会更方便
[root@centos74 bin]# pwd
/usr/local/mysql/bin
[root@centos74 bin]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos74 bin]# . /etc/profile.d/mysql.sh 使其生效
[root@centos74 bin]# cd /etc/profile.d/
[root@centos74 profile.d]# ls
256term.csh abrt-console-notification.sh colorgrep.csh colorls.csh csh.local lang.sh less.sh sh.local vim.sh which2.sh
256term.sh bash_completion.sh colorgrep.sh colorls.sh lang.csh less.csh mysql.sh vim.csh which2.csh
[root@centos74 profile.d]# vim mysql.sh
[root@centos74 profile.d]# cat mysql.sh
PATH=/usr/local/mysql/bin:$PATH
（四）指定存放数据库的目录
准备存放数据库的文件夹，就是用来存放用户的数据
最好不要和操作系统的空间存放在一块，放在逻辑卷里面是最好的，因为可以扩展。
创建逻辑卷，空间是50G
[root@centos74 bin]# ls
[root@centos74 bin]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 50G 3.8G 47G 8% /
devtmpfs 477M 0 477M 0% /dev
tmpfs 488M 0 488M 0% /dev/shm
tmpfs 488M 7.8M 480M 2% /run
tmpfs 488M 0 488M 0% /sys/fs/cgroup
/dev/sda3 20G 33M 20G 1% /app
/dev/sda1 1014M 124M 891M 13% /boot
tmpfs 98M 0 98M 0% /run/user/0
/dev/sr0 4.2G 4.2G 0 100% /mnt
[root@centos74 bin]# fdisk -l
Disk /dev/sda: 214.7 GB, 214748364800 bytes, 419430400 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: 0x00014617
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 2099199 1048576 83 Linux
/dev/sda2 2099200 106956799 52428800 83 Linux
/dev/sda3 106956800 148899839 20971520 83 Linux
/dev/sda4 148899840 419430399 135265280 5 Extended
/dev/sda5 148901888 153096191 2097152 82 Linux swap / Solaris
[root@centos74 bin]# fdisk /dev/sda
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Command (m for help): p
Disk /dev/sda: 214.7 GB, 214748364800 bytes, 419430400 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: 0x00014617
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 2099199 1048576 83 Linux
/dev/sda2 2099200 106956799 52428800 83 Linux
/dev/sda3 106956800 148899839 20971520 83 Linux
/dev/sda4 148899840 419430399 135265280 5 Extended
/dev/sda5 148901888 153096191 2097152 82 Linux swap / Solaris
Command (m for help): n
All primary partitions are in use
Adding logical partition 6
First sector (153098240-419430399, default 153098240):
Using default value 153098240
Last sector, +sectors or +size{K,M,G} (153098240-419430399, default 419430399): +50G
Partition 6 of type Linux and of size 50 GiB is set
Command (m for help): p
Disk /dev/sda: 214.7 GB, 214748364800 bytes, 419430400 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: 0x00014617
Device Boot Start End Blocks Id System
/dev/sda1 * 2048 2099199 1048576 83 Linux
/dev/sda2 2099200 106956799 52428800 83 Linux
/dev/sda3 106956800 148899839 20971520 83 Linux
/dev/sda4 148899840 419430399 135265280 5 Extended
/dev/sda5 148901888 153096191 2097152 82 Linux swap / Solaris
/dev/sda6 153098240 257955839 52428800 83 Linux
Command (m for help): t
Partition number (1-6, default 6):
Hex code (type L to list all codes): L
0 Empty 24 NEC DOS 81 Minix / old Lin bf Solaris
1 FAT12 27 Hidden NTFS Win 82 Linux swap / So c1 DRDOS/sec (FAT-
2 XENIX root 39 Plan 9 83 Linux c4 DRDOS/sec (FAT-
3 XENIX usr 3c PartitionMagic 84 OS/2 hidden C: c6 DRDOS/sec (FAT-
4 FAT16 <32M 40 Venix 80286 85 Linux extended c7 Syrinx
5 Extended 41 PPC PReP Boot 86 NTFS volume set da Non-FS data
6 FAT16 42 SFS 87 NTFS volume set db CP/M / CTOS / .
7 HPFS/NTFS/exFAT 4d QNX4.x 88 Linux plaintext de Dell Utility
8 AIX 4e QNX4.x 2nd part 8e Linux LVM df BootIt
9 AIX bootable 4f QNX4.x 3rd part 93 Amoeba e1 DOS access
a OS/2 Boot Manag 50 OnTrack DM 94 Amoeba BBT e3 DOS R/O
b W95 FAT32 51 OnTrack DM6 Aux 9f BSD/OS e4 SpeedStor
c W95 FAT32 (LBA) 52 CP/M a0 IBM Thinkpad hi eb BeOS fs
e W95 FAT16 (LBA) 53 OnTrack DM6 Aux a5 FreeBSD ee GPT
f W95 Ext'd (LBA) 54 OnTrackDM6 a6 OpenBSD ef EFI (FAT-12/16/
10 OPUS 55 EZ-Drive a7 NeXTSTEP f0 Linux/PA-RISC b
11 Hidden FAT12 56 Golden Bow a8 Darwin UFS f1 SpeedStor
12 Compaq diagnost 5c Priam Edisk a9 NetBSD f4 SpeedStor
14 Hidden FAT16 <3 61 SpeedStor ab Darwin boot f2 DOS secondary
16 Hidden FAT16 63 GNU HURD or Sys af HFS / HFS+ fb VMware VMFS
17 Hidden HPFS/NTF 64 Novell Netware b7 BSDI fs fc VMware VMKCORE
18 AST SmartSleep 65 Novell Netware b8 BSDI swap fd Linux raid auto
1b Hidden W95 FAT3 70 DiskSecure Mult bb Boot Wizard hid fe LANstep
1c Hidden W95 FAT3 75 PC/IX be Solaris boot ff BBT
1e Hidden W95 FAT1 80 Old Minix
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
the next reboot or after you run partprobe(8) or kpartx(8)
Syncing disks.
[root@centos74 bin]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 200G 0 disk
├─sda1 8:1 0 1G 0 part /boot
├─sda2 8:2 0 50G 0 part /
├─sda3 8:3 0 20G 0 part /app
├─sda4 8:4 0 1K 0 part
└─sda5 8:5 0 2G 0 part [SWAP]
sr0 11:0 1 4.2G 0 rom /mnt
[root@centos74 bin]# pvs
[root@centos74 bin]# pvcreate /dev/sda6
Device /dev/sda6 not found.
[root@centos74 bin]# partprobe
Warning: Unable to open /dev/sr0 read-write (Read-only file system). /dev/sr0 has been opened read-only.
[root@centos74 bin]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 200G 0 disk
├─sda1 8:1 0 1G 0 part /boot
├─sda2 8:2 0 50G 0 part /
├─sda3 8:3 0 20G 0 part /app
├─sda4 8:4 0 512B 0 part
├─sda5 8:5 0 2G 0 part [SWAP]
└─sda6 8:6 0 50G 0 part
sr0 11:0 1 4.2G 0 rom /mnt
[root@centos74 bin]# pvcreate /dev/sda6
Physical volume "/dev/sda6" successfully created.
[root@centos74 bin]# pvs
PV VG Fmt Attr PSize PFree
/dev/sda6 lvm2 --- 50.00g 50.00g
[root@centos74 bin]# vgc
vgcfgbackup vgcfgrestore vgchange vgck vgconvert vgcreate
[root@centos74 bin]# vgcreate vg0 /dev/sda6 -s 16M
Volume group "vg0" successfully created
[root@centos74 bin]# vgs
VG #PV #LV #SN Attr VSize VFree
vg0 1 0 0 wz--n- 49.98g 49.98g
[root@centos74 bin]# vgdisplay
--- Volume group ---
VG Name vg0
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 1
Act PV 1
VG Size 49.98 GiB
PE Size 16.00 MiB
Total PE 3199
Alloc PE / Size 0 / 0
Free PE / Size 3199 / 49.98 GiB
VG UUID QA8fLr-liSW-5rOX-ZPS2-hjwh-D2rT-gkuRwT
[root@centos74 bin]# lvs
lvs lvscan
[root@centos74 bin]# lvcreate -n lv_mysqldata -l 100%FREE vg0
Logical volume "lv_mysqldata" created.
[root@centos74 bin]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
lv_mysqldata vg0 -wi-a----- 49.98g
[root@centos74 bin]# lvdisplay
--- Logical volume ---
LV Path /dev/vg0/lv_mysqldata
LV Name lv_mysqldata
VG Name vg0
LV UUID AnalRm-6Vo0-sMht-yoFT-TWIc-PxUW-JTM9Lx
LV Write Access read/write
LV Creation host, time centos74, 2018-12-07 19:01:18 +0800
LV Status available
# open 0
LV Size 49.98 GiB
Current LE 3199
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 8192
Block device 253:0
创建文件系统
[root@centos74 bin]# mkfs.xfs /dev/vg0/lv_mysqldata
meta-data=/dev/vg0/lv_mysqldata isize=512 agcount=4, agsize=3275776 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=13103104, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=6398, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[root@centos74 bin]# blkid
/dev/sda2: UUID="5502fbe5-9253-4749-ac48-4586fa80c2af" TYPE="xfs"
/dev/sda1: UUID="81e8da25-d9a0-49c3-878e-de5c4be39f61" TYPE="xfs"
/dev/sda5: UUID="3c9d8b20-4f55-4a90-a0d7-1a9dbce0206d" TYPE="swap"
/dev/sda3: UUID="0c452676-a9eb-4da3-8a47-67ad9b0d8005" TYPE="xfs"
/dev/sda6: UUID="iDbgii-Snb4-lIfx-jOoX-HkwP-xOgu-s7zvVe" TYPE="LVM2_member"
/dev/sr0: UUID="2018-05-03-20-55-23-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos"
/dev/mapper/vg0-lv_mysqldata: UUID="8359a60a-544e-49b5-b7d9-3493ae28f0a0" TYPE="xfs"
[root@centos74 bin]# df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 52403200 3923176 48480024 8% /
devtmpfs 487952 0 487952 0% /dev
tmpfs 498976 0 498976 0% /dev/shm
tmpfs 498976 7968 491008 2% /run
tmpfs 498976 0 498976 0% /sys/fs/cgroup
/dev/sda3 20961280 32944 20928336 1% /app
/dev/sda1 1038336 126596 911740 13% /boot
tmpfs 99796 0 99796 0% /run/user/0
/dev/sr0 4364408 4364408 0 100% /mnt
[root@centos74 bin]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 200G 0 disk
├─sda1 8:1 0 1G 0 part /boot
├─sda2 8:2 0 50G 0 part /
├─sda3 8:3 0 20G 0 part /app
├─sda4 8:4 0 512B 0 part
├─sda5 8:5 0 2G 0 part [SWAP]
└─sda6 8:6 0 50G 0 part
└─vg0-lv_mysqldata 253:0 0 50G 0 lvm
sr0 11:0 1 4.2G 0 rom /mnt
添加到磁盘的配置文件里面
[root@centos74 bin]# vim /etc/fstab
[root@centos74 bin]# lvdisplay
--- Logical volume ---
LV Path /dev/vg0/lv_mysqldata
LV Name lv_mysqldata
VG Name vg0
LV UUID AnalRm-6Vo0-sMht-yoFT-TWIc-PxUW-JTM9Lx
LV Write Access read/write
LV Creation host, time centos74, 2018-12-07 19:01:18 +0800
LV Status available
# open 0
LV Size 49.98 GiB
Current LE 3199
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 8192
Block device 253:0
创建空目录，作为挂载目录，并且把逻辑卷挂载到此目录下
[root@centos74 bin]# mkdir /data/mysqldb
[root@centos74 bin]# ls
[root@centos74 bin]# cd /data/mysqldb/
[root@centos74 mysqldb]# ls
[root@centos74 mysqldb]# cd ..
[root@centos74 data]# ls
mysqldb
[root@centos74 data]# vim /etc/fstab
[root@centos74 data]# mount -a
[root@centos74 data]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 50G 3.8G 47G 8% /
devtmpfs 477M 0 477M 0% /dev
tmpfs 488M 0 488M 0% /dev/shm
tmpfs 488M 7.8M 480M 2% /run
tmpfs 488M 0 488M 0% /sys/fs/cgroup
/dev/sda3 20G 33M 20G 1% /app
/dev/sda1 1014M 124M 891M 13% /boot
tmpfs 98M 0 98M 0% /run/user/0
/dev/sr0 4.2G 4.2G 0 100% /mnt
/dev/mapper/vg0-lv_mysqldata 50G 33M 50G 1% /data/mysqldb
[root@centos74 data]# ll -d /data/mysqldb/
drwxr-xr-x. 2 root root 6 Dec 7 19:02 /data/mysqldb/
修改目录的所有者和所属组为登录数据库的账号
[root@centos74 data]# chown mysql.mysql /data/mysqldb/
[root@centos74 data]# ll
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 7 19:02 mysqldb
[root@centos74 data]# ll -d
drwxr-xr-x. 3 root root 21 Dec 7 19:15 .
[root@centos74 data]# ll -d /data/mysqldb/
drwxr-xr-x. 2 mysql mysql 6 Dec 7 19:02 /data/mysqldb/
（五）对数据库进行初始化
把数据库的相关文件建起来
mysql存放的是系统设置。就是安装MySQL自动生成的数据库之一了
[root@centos74 ~]# cd /usr/local/mysql/ [root@centos74 mysql]# ls bin COPYING COPYING.thirdparty CREDITS data EXCEPTIONS-CLIENT include INSTALL-BINARY lib
man mysql-test README.md README-wsrep scripts share sql-bench support-files [root@centos74 mysql]# cd support-files/ [root@centos74 support-files]# ls binary-configure my-huge.cnf my-large.cnf my-small.cnf mysql-log-rotate policy wsrep_notify magic my-innodb-heavy-4G.cnf my-medium.cnf mysqld_multi.server mysql.server wsrep.cnf
[root@centos74 mysql]# cd scripts/ [root@centos74 scripts]# ls mysql_install_db
[root@centos74 scripts]# ll -h
total 20K
-rwxr-xr-x. 1 mysql mysql 18K Jan 3 2018 mysql_install_db
注意mysql_install_db是mysql系统数据库自带的shell脚本
[root@centos74 scripts]# cat mysql_install_db | wc
556 2197 17997
[root@centos74 scripts]# cat mysql_install_db | less
#!/bin/sh
# Copyright (c) 2000, 2013, Oracle and/or its affiliates.
# Copyright (c) 2009, 2013, Monty Program Ab
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
# This scripts creates the MariaDB Server system tables
#
# All unrecognized arguments to this script are passed to mysqld.
basedir=""
builddir=""
ldata="./data"
langdir=""
srcdir=""
查看脚本的相关帮助
[root@centos74 scripts]# ./mysql_install_db --help
Usage: ./mysql_install_db [OPTIONS]
--auth-root-authentication-method=normal|socket
Chooses the authentication method for the created initial
root user. The default is 'normal' to creates a root user
that can login without password, which can be insecure.
The alternative 'socket' allows only the system root user
to login as MariaDB root; this requires the unix socket
authentication plugin.
--auth-root-socket-user=user
Used with --auth-root-authentication-method=socket. It
specifies the name of the MariaDB root account, as well
as of the system account allowed to access it. Defaults
to 'root'.
--basedir=path The path to the MariaDB installation directory.
--builddir=path If using --srcdir with out-of-directory builds, you
will need to set this to the location of the build
directory where built files reside.
--cross-bootstrap For internal use. Used when building the MariaDB system
tables on a different host than the target.
--datadir=path The path to the MariaDB data directory.
--defaults-extra-file=name
Read this file after the global files are read.
--defaults-file=name Only read default options from the given file name.
--force Causes mysql_install_db to run even if DNS does not
work. In that case, grant table entries that
normally use hostnames will use IP addresses.
--help Display this help and exit.
--ldata=path The path to the MariaDB data directory. Same as
--datadir.
--no-defaults Don't read default options from any option file.
--defaults-file=path Read only this configuration file.
--rpm For internal use. This option is used by RPM files
during the MariaDB installation process.
--skip-auth-anonymous-user
Do not install an unprivileged anonymous user.
--skip-name-resolve Use IP addresses rather than hostnames when creating
grant table entries. This option can be useful if
your DNS does not work.
--srcdir=path The path to the MariaDB source directory. This option
uses the compiled binaries and support files within the
source tree, useful for if you don't want to install
MariaDB yet and just want to create the system tables.
--user=user_name The login username to use for running mysqld. Files
and directories created by mysqld will be owned by this
user. You must be root to use this option. By default
mysqld runs using your current login name and files and
directories that it creates will be owned by you.
All other options are passed to the mysqld program
注意要在/usr/local/mysql/目录下执行操作，也就是要在相对路径下执行脚本，否则会报错的。
指定数据库存放的路径和数据库账号
[root@centos74 mysql]# pwd /usr/local/mysql
[root@centos74 mysql]# scripts/mysql_install_db --datadir=/data/mysqldb --user=mysql Installing MariaDB/MySQL system tables in '/data/mysqldb' ... OK表示执行脚本成功了 To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !加上口令。这个是迟早就要做的 To do so, start the server, then issue the following commands: './bin/mysqladmin' -u root password 'new-password' './bin/mysqladmin' -u root -h centos74 password 'new-password' Alternatively you can run: './bin/mysql_secure_installation' which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the MariaDB Knowledgebase at http://mariadb.com/kb or the MySQL manual for more instructions. You can start the MariaDB daemon with: cd '.' ; ./bin/mysqld_safe --datadir='/data/mysqldb' You can test the MariaDB daemon with mysql-test-run.pl cd './mysql-test' ; perl mysql-test-run.pl Please report any problems at http://mariadb.org/jira The latest information about MariaDB is available at http://mariadb.org/. You can find additional information about the MySQL part at: http://dev.mysql.com Consider joining MariaDB's strong and vibrant community: https://mariadb.org/get-involved/
（六）对MySQL数据库的主配置文件设置
启动服务，要使用一个服务脚本
配置文件的设置是有问题的。比如默认数据库存放的地方是在/var/lib/mysql
[mysqld] datadir=/var/lib/mysql
#这个是yum默认安装的路径 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
修改配置文件，可参考MySQL系统数据库目录里面的自带文件。
[root@centos74 mysql]# cd support-files/
[root@centos74 support-files]# ls
binary-configure my-huge.cnf my-large.cnf my-small.cnf mysql-log-rotate policy wsrep_notify
magic my-innodb-heavy-4G.cnf my-medium.cnf mysqld_multi.server mysql.server wsrep.cnf
选择存储空间比较合适的那个，可以使用grep命令过滤出来
[root@centos74 support-files]# grep memory *
my-huge.cnf:# This is for a large system with memory of 1G-2G where the system runs mainly
my-huge.cnf:# of RAM but beware of setting memory usage too high
my-innodb-heavy-4G.cnf:# This is a MariaDB example config file for systems with 4GB of memory
my-innodb-heavy-4G.cnf:# Maximum allowed size for a single HEAP (in memory) table. This option
my-innodb-heavy-4G.cnf:# table which could otherwise use up all memory resources.
my-innodb-heavy-4G.cnf:# enable this option while running MariaDB to keep it locked in memory and
my-innodb-heavy-4G.cnf:# to avoid potential swapping out in case of high memory pressure. Good
my-innodb-heavy-4G.cnf:# Thread stack size to use. This amount of memory is always reserved at
my-innodb-heavy-4G.cnf:# memory, while if you use your own stack hungry UDF functions or your
my-innodb-heavy-4G.cnf:# Maximum size for internal (in-memory) temporary tables. If a table
my-innodb-heavy-4G.cnf:# Do not set it larger than 30% of your available memory, as some memory
my-innodb-heavy-4G.cnf:# have multiple CPUs and plenty of memory.
my-innodb-heavy-4G.cnf:# but you do not plan to use it. This will save memory and disk space
my-innodb-heavy-4G.cnf:# Additional memory pool that is used by InnoDB to store metadata
my-innodb-heavy-4G.cnf:# information. If InnoDB requires more memory for this purpose it will
my-innodb-heavy-4G.cnf:# parameter up to 80% of the machine physical memory size. Do not set it
my-innodb-heavy-4G.cnf:# too large, though, because competition of the physical memory may
my-innodb-heavy-4G.cnf:# might be limited to 2-3.5G of user level memory per process, so do not
my-innodb-heavy-4G.cnf:# Do not buffer the whole result set in memory before writing it to
my-large.cnf:# This is for a large system with memory = 512M where the system runs mainly
my-large.cnf:# of RAM but beware of setting memory usage too high
my-medium.cnf:# This is for a system with little memory (32M - 64M) where MariaDB plays
my-medium.cnf:# of RAM but beware of setting memory usage too high
my-small.cnf:# This is for a system with little memory (<= 64M) where MySQL is only used
my-small.cnf:# of RAM but beware of setting memory usage too high
grep: policy: Is a directory
拷贝参考的配置文件到主配置文件
[root@centos74 support-files]# cp my-huge.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
[root@centos74 support-files]# ll /etc/my.cnf
-rw-r--r--. 1 root root 4914 Dec 7 21:03 /etc/my.cnf
在主配置文件 /etc/my.cnf中设置路径
[mysqld]
port = 3306
datadir=/data/mysqldb
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
（七）启动服务脚本
查看数据库服务的脚本
是放在/usr/local/mysql/support-files目录下的mysql.server
[root@centos74 support-files]# ls
binary-configure my-huge.cnf my-large.cnf my-small.cnf mysql-log-rotate policy wsrep_notify
magic my-innodb-heavy-4G.cnf my-medium.cnf mysqld_multi.server mysql.server wsrep.cnf
[root@centos74 support-files]# ll
total 84
-rwxr-xr-x. 1 mysql mysql 1153 Jan 3 2018 binary-configure
-rw-r--r--. 1 mysql mysql 1328 Jan 3 2018 magic
-rw-r--r--. 1 mysql mysql 4914 Jan 3 2018 my-huge.cnf
-rw-r--r--. 1 mysql mysql 20421 Jan 3 2018 my-innodb-heavy-4G.cnf
-rw-r--r--. 1 mysql mysql 4901 Jan 3 2018 my-large.cnf
-rw-r--r--. 1 mysql mysql 4914 Jan 3 2018 my-medium.cnf
-rw-r--r--. 1 mysql mysql 2840 Jan 3 2018 my-small.cnf
-rwxr-xr-x. 1 mysql mysql 1061 Jan 3 2018 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql 918 Jan 3 2018 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 12227 Jan 3 2018 mysql.server
drwxrwxr-x. 4 mysql mysql 37 Jan 4 2018 policy
-rw-r--r--. 1 mysql mysql 3452 Jan 3 2018 wsrep.cnf
-rwxr-xr-x. 1 mysql mysql 2230 Jan 3 2018 wsrep_notify
[root@centos74 support-files]# pwd
/usr/local/mysql/support-files
数据库服务脚本是由shell写的
[root@centos74 support-files]# ll mysql.server -h
-rwxr-xr-x. 1 mysql mysql 12K Jan 3 2018 mysql.server
[root@centos74 support-files]# cat mysql.server | less
#!/bin/sh
#数据库服务脚本是由shell写的
# Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB
# This file is public domain and comes with NO WARRANTY of any kind
# MySQL daemon start/stop script.
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Should-Start: ypbind nscd ldap ntpd xntpd
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
查询所有的系统服务及其状态
[root@centos74 support-files]# chkconfig
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off
network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@centos74 support-files]# chkconfig --list
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off
network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
因为所有的服务启动脚本都放置于 /etc/init.d/ 目录下。
为了对数据库服务进行操作，比如启动停止就要把数据库的相关脚本拷贝到此目录下
[root@centos74 support-files]# cp mysql.server /etc/init.d/mysqld
[root@centos74 support-files]# ll /etc/init.d/mysqld
-rwxr-xr-x. 1 root root 12227 Dec 7 21:37 /etc/init.d/mysqld
把MySQL服务加入到系统服务里面
[root@centos74 support-files]# chkconfig --add mysqld
[root@centos74 support-files]# chkconfig
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off
network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
（八）启动MySQL数据库服务
启动mysql服务，虽然是在7版本上使用数据库，但是使用centos6的启动方式
[root@centos74 support-files]# service mysqld start Starting mysqld (via systemctl): [ OK ] [root@centos74 support-files]# ss -tnl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 80 :::3306 :::* LISTEN 0 128 :::22 :::* LISTEN 0 100 ::1:25 :::*
/tmp/mysql.sock 这个文件会随着服务的启动而生成，也会随着服务的停止而消失。
[root@centos74 support-files]# ll /tmp/mysql.sock
#这个文件会随着服务的启动而生成，也会随着服务的停止而消失。
srwxrwxrwx. 1 mysql mysql 0 Dec 7 21:39 /tmp/mysql.sock
[root@centos74 support-files]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
[root@centos74 support-files]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
[root@centos74 support-files]# ll /tmp/mysql.sock
ls: cannot access /tmp/mysql.sock: No such file or directory
三二进制安装后的数据库使用
（一）连接数据库
连接数据库，这时是没有口令的
[root@centos74 support-files]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 11 Server version: 10.2.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
创建普通用户，使用普通用户连接
即使是普通用户也是可以连接的
[root@centos74 support-files]# useradd wang
[root@centos74 support-files]# id wang
uid=1000(wang) gid=1000(wang) groups=1000(wang)
[root@centos74 support-files]# su - wang
[wang@centos74 ~]$ mysql
#即使是普通用户也是可以连接的
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 14
Server version: 10.2.12-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]>
（二）查看数据库存放路径
数据库数据存放路径/data/mysqldb
[root@centos74 ~]# cd /data/ [root@centos74 data]# ls mysqldb [root@centos74 data]# ll total 0 drwxrwx---. 5 mysql mysql 331 Dec 7 21:40 mysqldb [root@centos74 data]# ls mysqldb [root@centos74 data]# cd mysqldb/ [root@centos74 mysqldb]# ls aria_log.00000001 centos74.err ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql-bin.000002 performance_schema aria_log_control centos74.pid ibdata1 ib_logfile1 multi-master.info mysql-bin.000001 mysql-bin.index test
[root@centos74 mysqldb]# ls
aria_log.00000001 centos74.err ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql-bin.000002 performance_schema
aria_log_control centos74.pid ibdata1 ib_logfile1 multi-master.info mysql-bin.000001 mysql-bin.index test空文件，可以删除
[root@centos74 mysqldb]# ll
total 122932
-rw-rw----. 1 mysql mysql 16384 Dec 7 21:40 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Dec 7 21:40 aria_log_control
-rw-rw----. 1 mysql mysql 5372 Dec 7 21:40 centos74.err
-rw-rw----. 1 mysql mysql 6 Dec 7 21:40 centos74.pid
-rw-rw----. 1 mysql mysql 2793 Dec 7 21:40 ib_buffer_pool
-rw-rw----. 1 mysql mysql 12582912 Dec 7 21:40 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Dec 7 21:40 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Dec 7 20:52 ib_logfile1
-rw-rw----. 1 mysql mysql 12582912 Dec 7 21:40 ibtmp1
-rw-rw----. 1 mysql mysql 0 Dec 7 21:39 multi-master.info
drwx------. 2 mysql root 4096 Dec 7 20:52 mysql
-rw-rw----. 1 mysql mysql 351 Dec 7 21:40 mysql-bin.000001
-rw-rw----. 1 mysql mysql 328 Dec 7 21:40 mysql-bin.000002
-rw-rw----. 1 mysql mysql 38 Dec 7 21:40 mysql-bin.index
drwx------. 2 mysql mysql 20 Dec 7 20:52 performance_schema
drwx------. 2 mysql root 6 Dec 7 20:52 test
[root@centos74 mysqldb]# ls test/
（三）查看数据库
查看数据库
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)
MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> select user();
#查看当前用户的身份。这个root不是Linux主机的root，而是MySQL的。只不过名字相同而已。
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
（四）指定登录数据库的用户
我们还可以指定用户。如果是root的话就多此一举了，因为默认就是。
[root@centos74 ~]# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 17
Server version: 10.2.12-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
出现匿名账号用户可以连接是因为安装MySQL的时候存在匿名账号
[root@centos74 ~]# mysql -uxxxx Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 15 Server version: 10.2.12-MariaDB-log MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> select user(); +----------------+ | user() | +----------------+ | xxxx@localhost | +----------------+ 1 row in set (0.00 sec)
下面显示很多数据库已经隐藏起来了。
因为指定用户非root用户连接的话，他的身份不是MySQL数据库的管理员。所以很多东西都做不了
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec
（五）查看用户账号列表
这个表里面放的是我们当前数据库的用户账号列表。
[root@centos74 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 16
Server version: 10.2.12-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> select host,user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1 | root |
| centos74 | |
| centos74 | root |
| localhost | |
| localhost | root |
+-----------+------+
6 rows in set (0.00 sec)
MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | centos74 |
| root | centos74 |
| | localhost |这个就是刚才的匿名账号
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)
现在用户账号是没有口令的
MariaDB [(none)]> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |+------+-----------+----------+
| root | localhost | |
| root | centos74 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | centos74 | |
+------+-----------+----------+
6 rows in set (0.00 sec)
（六）设置数据库用户的口令
执行mysql_secure_installation来做一些常规化安全设置。
为了数据库安全设置口令，进行安全初始化
此时要输入设置的密码才可以连接
[root@centos74 ~]# my
myisamchk mysqlaccess mysql_config mysqldump mysqlimport mysqltest
myisam_ftdump mysqladmin mysql_convert_table_format mysqldumpslow mysql_plugin mysqltest_embedded
myisamlog mysqlbinlog mysqld mysql_embedded mysql_secure_installation mysql_tzinfo_to_sql
myisampack mysqlcheck mysqld_multi mysql_find_rows mysql_setpermission mysql_upgrade
my_print_defaults mysql_client_test mysqld_safe mysql_fix_extensions mysqlshow mysql_waitpid
mysql mysql_client_test_embedded mysqld_safe_helper mysqlhotcopy mysqlslap mytop
[root@centos74 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
没有指定用户默认是以root身份连接的，此时要输入正确的密码才可以连接
[root@centos74 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@centos74 ~]# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 28
Server version: 10.2.12-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]>
四总结二进制安装的过程
总结二进制安装的过程。
实验：二进制安装mariadb
1 tar -C /usr/local
ln -s /usr/local/mariadb-xxx /usr/local/mysql
2 useradd -r mysql -s /sbin/nologin
chown -R mysql.mysql /usr/local/mysql
3 PATH
4 mkdir /data/mysqldb
chown -R /data/mysqldb
5初始化数据库
scripts/mysql_install_db --datadir=/data/mysqldb --user=mysql
6 配置文件
cp /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf
vim /etc/my.cnf
[mysqld]
datadir=/data/mysqldb
7 启动服务脚本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
8 安全初始化
mysql_secure_installation