• MYSQL二进制安装


    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


    作者:wang618
    出处:https://www.cnblogs.com/wang618/
    本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接,否则保留追究法律责任的权利。

  • 相关阅读:
    win平台搭建Lnmp环境
    YII2 model where 条件拼接
    yii2框架-yii2局部关闭(开启)csrf的验证
    Yii2 控制器单独向view(layout)传值
    Yii2 数据库基本操作
    PHP 多线程采集
    php 阿拉伯数字转中文
    javascript里的sleep()方法
    PHP数组内容不重复组合排列算法
    git使用经验(一)
  • 原文地址:https://www.cnblogs.com/wang618/p/10519947.html
Copyright © 2020-2023  润新知