MySQL数据库环境之一：基于二进制方式安装mysql5.7.30

环境：

操作系统： CentOS7.6

配置： 4核/8G, 40G+100G硬盘

一）操作系统环境配置

1）设置主机名

 echo "192.168.31.51  itpuxdb" >> /etc/hosts
 hostnamectl set-hostname itpuxdb
 exec bash

2）配置英文环境

 echo "export LANG=en_US" >> ~/.bash_profile

3) 修改资源限制参数

 vim /etc/security/limit.conf
 *  soft    nproc   65535
 *  hard    nproc   65535
 *  soft    nofile  65535
 *  hard    nofile  65535
 *  soft    stack   65535
 *  hard    stack   65535
 
 echo "* - proc 65535" >> /etc/security/limits.d/20-nproc.conf

4)设置系统安全策略

 sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
 setenforce 0
 
 systemctl disable firewalld
 systemctl stop firewalld

5)控制资源分配限制

 echo "session    required  pom_limits.so" >> /etc/pam.d/login

6) IO调度算法

[root@itpuxdb ~]# cat /sys/block/sda/queue/scheduler 
 noop [deadline] cfq 
 [root@itpuxdb ~]# cat /sys/block/sdb/queue/scheduler 
 noop [deadline] cfq 
 
 [root@itpuxdb ~]# cat /sys/block/sdb/queue/read_ahead_kb 
 128
 [root@itpuxdb ~]# cat /sys/block/sdb/queue/nr_requests 
 128
 
 调整：
 
 echo "16" > /sys/block/sdb/queue/read_ahead_kb
 echo "512" > /sys/block/sdb/queue/nr_requests
 echo "deadline" > /sys/block/sdb/queue/scheduler
 echo "16" > /sys/block/sdc/queue/read_ahead_kb
 echo "512" > /sys/block/sdc/queue/nr_requests
 echo "deadline" > /sys/block/sdc/queue/scheduler 
                                                
 
 添加以上echo到/etc/rc.local
 
 chmod +x /etc/rc.local

7）虚拟内存

 vim /etc/sysctl.conf
 vm.swappiness=10
 vm.min_free_kbytes=512000
 
 
 sysctl -p

8)配置yum源

 yum -y install wget vim
 mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
 wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
 
 mv /etc/yum.repos.d/epel.repo /etc/yum.repos.d/epel.repo.backup
 mv /etc/yum.repos.d/epel-testing.repo /etc/yum.repos.d/epel-testing.repo.backup
 
 wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

9) 准备磁盘与分区----最规范的

安装目录：   /mysql/app/mysql
 数据目录：  /mysql/data
 日志目录：  /mysql/log
 备份目录：  /mysql/backup
 
 LVM
 /dev/sdb   100G  分3个，分别为安装目录，数据目录，日志目录
 /dev/sdc   110G  分50G为备份目录
 
 Disk /dev/sdb: 107.4 GB, 107374182400 bytes, 209715200 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 /dev/sdc: 118.1 GB, 118111600640 bytes, 230686720 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
 
 
 
 
 命令如下：
 pvcreate /dev/sdb /dev/sdc
 vgcreate mysqlvg /dev/sdb
 vgcreate bakvg /dev/sdc
 lvcreate -n mysqllv -L 30G mysqlvg
 lvcreate -n datalv -L 30G mysqlvg
 lvcreate -n loglv -L 30G mysqlvg
 lvcreate -n baklv -L 50G bakvg
 
 mkfs.xfs /dev/mysqlvg/mysqllv 
 mkfs.xfs /dev/mysqlvg/datalv 
 mkfs.xfs /dev/mysqlvg/loglv 
 mkfs.xfs /dev/bakvg/baklv 
 
 
 mkdir -p /mysql/app
 mkdir -p /mysql/log
 mkdir -p /mysql/data
 mkdir -p /mysql/backup
 
 
 /dev/mapper/mysqlvg-mysqllv: UUID="9e3a5e67-68a7-4156-9a63-bb70d532aa1c" TYPE="xfs" 
 /dev/mapper/mysqlvg-datalv: UUID="f66d552a-a033-46f9-b7ae-f733bb8c13c3" TYPE="xfs" 
 /dev/mapper/mysqlvg-loglv: UUID="96d65dfc-cb61-4b56-9946-10b881be5a23" TYPE="xfs" 
 /dev/mapper/bakvg-baklv: UUID="44e090d4-5c55-49de-83f3-53632ecdacd5" TYPE="xfs" 
 
 挂载
 UUID="9e3a5e67-68a7-4156-9a63-bb70d532aa1c"  /mysql/app   xfs   defaults   0 0 
 UUID="f66d552a-a033-46f9-b7ae-f733bb8c13c3"  /mysql/data   xfs   defaults  0 0 
 UUID="96d65dfc-cb61-4b56-9946-10b881be5a23"  /mysql/log    xfs   defaults  0 0 
 UUID="44e090d4-5c55-49de-83f3-53632ecdacd5"  /mysql/backup    xfs    defaults  0 0

二）mysql5.7二进制安装部署

1）软件下载

 https://downloads.mysql.com/archives/community/
 
 cd /mysql/app
 wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

2) 安装依赖包

 yum -y install libaio libaio-devel

3) 创建用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

4)创建目录

 mkdir -p /mysql/data/3306/data
 mkdir -p /mysql/log/3306

5)安装mysql

cd /mysql/app
 tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz 
 ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql
 
 [root@itpuxdb app]# ll
 total 644552
 lrwxrwxrwx. 1 root root        35 Sep  1 15:53 mysql -> mysql-5.7.30-linux-glibc2.12-x86_64
 
 [root@itpuxdb mysql]# vim ~/.bash_profile 
 PATH=$PATH:$HOME/bin:/mysql/app/mysql/bin
 
 [root@itpuxdb mysql]#source ~/.bash_profile
 
 [root@itpuxdb mysql]# mysql --version
 mysql  Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using  EditLine wrapper
 
 更改权限
 chown -R mysql.mysql /mysql

6）初始化参数（参数准备）

cat /mysql/data/3306/my.cnf
 [client]
 port=3306
 socket  = /mysql/data/3306/mysql.sock
 
 [mysql]
 no-beep
 prompt="u@itpux R:m:s [d]> "
 #no-auto-rehash
 auto-rehash
 default-character-set=utf8
 
 
 [mysqld]
 ########basic settings########
 server-id=3306
 port=3306
 user = mysql
 bind_address= 192.168.31.51
 basedir=/mysql/app/mysql
 datadir=/mysql/data/3306/data
 socket  = /mysql/data/3306/mysql.sock
 pid-file=/mysql/data/3306/mysql.pid
 character-set-server=utf8
 skip-character-set-client-handshake=1
 autocommit = 0
 skip_name_resolve = 1
 max_connections = 800
 max_connect_errors = 1000
 default-storage-engine=INNODB
 transaction_isolation = READ-COMMITTED
 explicit_defaults_for_timestamp = 1
 sort_buffer_size = 32M
 join_buffer_size = 128M
 tmp_table_size = 72M
 max_allowed_packet = 16M
 sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
 interactive_timeout = 1800
 wait_timeout = 1800
 read_buffer_size = 16M
 read_rnd_buffer_size = 32M
 
 query_cache_type = 1
 query_cache_size=1M
 table_open_cache=2000
 thread_cache_size=768
 myisam_max_sort_file_size=10G
 myisam_sort_buffer_size=135M
 key_buffer_size=32M
 read_buffer_size=8M
 read_rnd_buffer_size=4M
 
 back_log=1024
 #flush_time=0
 open_files_limit=65536
 table_definition_cache=1400
 #binlog_row_event_max_size=8K
 #sync_master_info=10000
 #sync_relay_log=10000
 #sync_relay_log_info=10000
 
 ########log settings########
 log-output=FILE
 general_log = 0
 general_log_file=/mysql/log/3306/itpuxdb-general.err
 slow_query_log = ON
 slow_query_log_file=/mysql/log/3306/itpuxdb-query.err
 long_query_time=10
 log-error=/mysql/log/3306/itpuxdb-error.err
 
 log_queries_not_using_indexes = 1
 log_slow_admin_statements = 1
 log_slow_slave_statements = 1
 log_throttle_queries_not_using_indexes = 10
 expire_logs_days = 90
 min_examined_row_limit = 100
 #log_bin = "/log/bin_log/binlog"
 
 ########replication settings########
 #master_info_repository = TABLE
 #relay_log_info_repository = TABLE
 #log_bin = bin.log
 #sync_binlog = 1
 #gtid_mode = on
 #enforce_gtid_consistency = 1
 #log_slave_updates
 #binlog_format = row 
 #relay_log = relay.log
 #relay_log_recovery = 1
 #binlog_gtid_simple_recovery = 1
 #slave_skip_errors = ddl_exist_errors
 
 ########innodb settings########
 innodb_io_capacity = 4000
 innodb_io_capacity_max = 8000
 innodb_buffer_pool_size = 500M     #物理内存的70%
 innodb_buffer_pool_instances = 8
 innodb_buffer_pool_load_at_startup = 1
 innodb_buffer_pool_dump_at_shutdown = 1
 innodb_lru_scan_depth = 2000
 innodb_lock_wait_timeout = 5
 #innodb_flush_method = O_DIRECT
 
 innodb_log_file_size = 200M
 innodb_log_files_in_group = 2 
 innodb_log_buffer_size = 16M
 
 innodb_undo_logs = 128
 innodb_undo_tablespaces = 3
 innodb_undo_log_truncate = 1
 innodb_max_undo_log_size = 2G
 
 innodb_flush_neighbors = 1
 innodb_purge_threads = 4
 innodb_large_prefix = 1
 innodb_thread_concurrency = 64
 innodb_print_all_deadlocks = 1
 innodb_strict_mode = 1
 innodb_sort_buffer_size = 64M
 innodb_flush_log_at_trx_commit=1
 innodb_autoextend_increment=64
 innodb_concurrency_tickets=5000
 innodb_old_blocks_time=1000
 innodb_open_files=65536
 innodb_stats_on_metadata=0
 innodb_file_per_table=1
 innodb_checksum_algorithm=0
 
 innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:10G  #生产环境1-5G一个
 innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G  #生产环境1-5G一个
 
 innodb_buffer_pool_dump_pct = 40
 innodb_page_cleaners = 4
 innodb_purge_rseg_truncate_frequency = 128
 binlog_gtid_simple_recovery=1
 log_timestamps=system
 #transaction_write_set_extraction=MURMUR32
 show_compatibility_56=on
 #role角色
 check_proxy_users=ON
 mysql_native_password_proxy_users=ON

 创建一个文件
 touch /mysql/log/3306/itpuxdb-error.err
 
 chown -R mysql.mysql /mysql

7)初始化mysql数据库

[root@itpuxdb 3306]# mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql  --datadir=/mysql/data/3306/data 
 
 
 [root@itpuxdb 3306]# cat /mysql/log/3306/itpuxdb-error.err 
  100 200
  100 200
  100 200
  100 200
  100 200
 2020-09-01T16:10:16.102668+08:00 0 [Warning] InnoDB: New log files created, LSN=48434
  100 200
 2020-09-01T16:10:16.720394+08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
 2020-09-01T16:10:16.782022+08:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 91d7772b-ec2a-11ea-9d3a-a65b22bf9f0c.
 2020-09-01T16:10:16.783319+08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
 2020-09-01T16:10:17.812578+08:00 0 [Warning] CA certificate ca.pem is self signed.
 2020-09-01T16:10:17.898641+08:00 1 [Note] A temporary password is generated for root@localhost: 9KsnC04Xdw#v
 
 
 检查数据目录
 [root@itpuxdb 3306]# ll /mysql/data/3306/data/
 total 1054788
 -rw-r-----. 1 mysql mysql        56 Sep  1 16:10 auto.cnf
 -rw-------. 1 mysql mysql      1680 Sep  1 16:10 ca-key.pem
 -rw-r--r--. 1 mysql mysql      1112 Sep  1 16:10 ca.pem
 -rw-r--r--. 1 mysql mysql      1112 Sep  1 16:10 client-cert.pem
 -rw-------. 1 mysql mysql      1676 Sep  1 16:10 client-key.pem
 -rw-r-----. 1 mysql mysql       701 Sep  1 16:10 ib_buffer_pool
 -rw-r-----. 1 mysql mysql 209715200 Sep  1 16:10 ibdata1
 -rw-r-----. 1 mysql mysql 209715200 Sep  1 16:10 ibdata2
 -rw-r-----. 1 mysql mysql 209715200 Sep  1 16:10 ibdata3
 -rw-r-----. 1 mysql mysql 209715200 Sep  1 16:10 ib_logfile0
 -rw-r-----. 1 mysql mysql 209715200 Sep  1 16:10 ib_logfile1
 drwxr-x---. 2 mysql mysql      4096 Sep  1 16:10 mysql
 drwxr-x---. 2 mysql mysql      8192 Sep  1 16:10 performance_schema
 -rw-------. 1 mysql mysql      1676 Sep  1 16:10 private_key.pem
 -rw-r--r--. 1 mysql mysql       452 Sep  1 16:10 public_key.pem
 -rw-r--r--. 1 mysql mysql      1112 Sep  1 16:10 server-cert.pem
 -rw-------. 1 mysql mysql      1676 Sep  1 16:10 server-key.pem
 drwxr-x---. 2 mysql mysql      8192 Sep  1 16:10 sys
 -rw-r-----. 1 mysql mysql  10485760 Sep  1 16:10 undo001
 -rw-r-----. 1 mysql mysql  10485760 Sep  1 16:10 undo002
 -rw-r-----. 1 mysql mysql  10485760 Sep  1 16:10 undo003

8)准备mysql启动脚本

cd /mysql/app/mysql/support-files
 cp mysql.server /etc/init.d/mysql
 更改3个地方
 1）指定目录
 basedir=/mysql/app/mysql
 datadir=/mysql/data/3306/data
 
 # Default value, in seconds, afterwhich the script should timeout waiting
 # for server start. 
 # Value here is overriden by value in my.cnf. 
 # 0 means don't wait at all
 # Negative numbers mean to wait indefinitely
 service_startup_timeout=900
 
 # Lock directory for RedHat / SuSE.
 lockdir='/var/lock/subsys'
 lock_file_path="$lockdir/mysql"
 
 # The following variables are only set for letting mysql.server find things.
 
 # Set some defaults
 mysqld_pid_file_path=/mysql/data/3306/mysql.pid
 
 2）指定目录
 if test -z "$basedir"
 then
   basedir=/mysql/app/mysql
   bindir=/mysql/app/mysql/bin
   if test -z "$datadir"
   then
     datadir=/mysql/data/3306//data
   fi
   sbindir=/mysql/app/mysql/bin
   libexecdir=/mysql/app/mysql/bin
 else
 
 3）--defaults-file=/mysql/data/3306/my.cnf
 
 case "$mode" in
   'start')
     # Start daemon
 
     # Safeguard (relative paths, core dumps..)
     cd $basedir
 
     echo $echo_n "Starting MySQL"
     if test -x $bindir/mysqld_safe
     then
       # Give extra arguments to mysqld with the my.cnf file. This script
       # may be overwritten at next upgrade.
       $bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf  $other_args >/dev/null &
       wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?

9）测试mysql的启动停止

[root@itpuxdb ~]# service mysql start
 Starting MySQL.. SUCCESS! 
 
 [root@itpuxdb ~]# ps aux|grep mysql
 root     14760  1.0  0.0  11816  1596 pts/0    S    16:43   0:00 /bin/sh /mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf
 mysql    15901  5.2  2.7 1571276 220720 pts/0  Sl   16:43   0:00 /mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data --plugin-dir=/mysql/app/mysql/lib/plugin --user=mysql --log-error=/mysql/log/3306/itpuxdb-error.err --open-files-limit=65536 --pid-file=/mysql/data/3306/mysql.pid --socket=/mysql/data/3306/mysql.sock --port=3306
 root     15932  0.0  0.0 114768   952 pts/0    S+   16:43   0:00 grep --color=auto mysql
 
 停止数据库
 [root@itpuxdb ~]# service mysql stop
 Shutting down MySQL.. SUCCESS! 
 [root@itpuxdb ~]# ll /mysql/data/3306/
 total 8
 drwxr-xr-x. 5 mysql mysql 4096 Sep  1 16:45 data
 -rw-r--r--. 1 mysql mysql 3489 Sep  1 16:26 my.cnf
 [root@itpuxdb ~]# ps aux|grep mysql
 root     15963  0.0  0.0 114768   952 pts/0    S+   16:45   0:00 grep --color=auto mysql
 
 
 添加到开启自动启动
 [root@itpuxdb ~]# chkconfig --level 2345 mysql on
 [root@itpuxdb ~]# chkconfig --list|grep mysql
 
 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]'.
 
 mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off
 
 重启服务器，看数据库是否自动启动
 reboot
 
 发现mysql自动启动
 [root@itpuxdb ~]# ps aux|grep mysql
 root      3830  0.1  0.0 115436  1692 ?        S    14:30   0:00 /bin/sh /mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf
 mysql     6225  1.4  2.7 1571260 221268 ?      Sl   14:30   0:01 /mysql/app/mysql/bin/mysqld --defaults-file=/mysql/data/3306/my.cnf --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data --plugin-dir=/mysql/app/mysql/lib/plugin --user=mysql --log-error=/mysql/log/3306/itpuxdb-error.err --open-files-limit=65536 --pid-file=/mysql/data/3306/mysql.pid --socket=/mysql/data/3306/mysql.sock --port=3306

10）更改root用户的密码

 [root@itpuxdb ~]# ln -s /mysql/data/3306/mysql.sock /tmp/mysql.sock
 [root@itpuxdb ~]# mysql -u root -p
 Enter password: 
 
 mysql> alter user root@localhost identified by 'Aa123456';
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> flush privileges;
 Query OK, 0 rows affected (0.00 sec)

11) 创建一个管理员用户，允许从本地和192.168.31网段登录

mysql> grant all privileges on *.* to 'dbaadmin'@'localhost' identified by 'dba123456' with grant options;
 mysql> grant all privileges on *.* to 'dbaadmin'@'192.168.31.%' identified by 'dab123456';
 mysql> flush privileges;
 
 mysql> show grants for dbaadmin@'localhost';
 +-------------------------------------------------------------------------+
 | Grants for dbaadmin@localhost                                           |
 +-------------------------------------------------------------------------+
 | GRANT ALL PRIVILEGES ON *.* TO 'dbaadmin'@'localhost' WITH GRANT OPTION |
 +-------------------------------------------------------------------------+
 1 row in set (0.00 sec)
 
 mysql> show grants for dbaadmin@'192.168.31.%';
 +----------------------------------------------------------+
 | Grants for dbaadmin@192.168.31.%                         |
 +----------------------------------------------------------+
 | GRANT ALL PRIVILEGES ON *.* TO 'dbaadmin'@'192.168.31.%' |
 +----------------------------------------------------------+
 1 row in set (0.00 sec)
 
 (从本地localhost登录，可以为其他用户授权)

MySQL数据库环境之一： 基于二进制方式安装mysql5.7.30

MySQL数据库环境之一：基于二进制方式安装mysql5.7.30