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