mysql而二进制安装及规范
2021/8/14 19:06:01
本文主要是介绍mysql而二进制安装及规范,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1.一些安装规范
-
MySQL安装方式:二进制安装(源码编译安装、yum 、rpm)
-
MySQL运行用户:
mysql:mysql 注意该用户是虚拟用户,只是用于mysql进程运行使用,不允许登录、不创建家目录 useradd -s /sbin/nologin -M mysql
-
MySQL目录规范:
数据以单独分区存放,如/data 解压目录 /opt/mysql/mysql-xx.xx 软连接 ln -s /opt/mysql/mysql-xx.xx /usr/local/mysql 数据目录 /data/mysql/mysql+port/{data,logs} 配置文件 /data/mysql/mysql+port/my+port.cnf
-
MySQL版本选择
企业版:Enterprise , 一般不做考虑 社区版:这才是我们需要的,毕竟不要钱 包的选择:使用通用的二进制包 版本号选择: ga,尽量选择版本号最后一位是偶数
2.二进制安装
MySQL版本为5.7.30,系统为centos7最小化安装
2.1环境配置
#安装mysql相关的依赖包 yum -y install libaio libaio-devel numactl ncurses-devel rpm -e --nodeps mariadb-libs #创建mysql运行使用用户 useradd -s /sbin/nologin -M mysql #创建目录 mkdir -p /opt/mysql mkdir -p /data/mysql/mysql3306/{data,logs} #上传二进制包 [root@localhost ~]# ls /opt/mysql/ mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz #解压(或者直接解压到目录,然后mv重命名) [root@localhost ~]# tar xf /opt/mysql/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [root@localhost ~]# ln -s /usr/local/mysql-5.7.30-linux-glibc2.12-x86_64/ /usr/local/mysql
2.2配置配置文件
#使用简易配置文件 vim /data/mysql/mysql3306/my3306.cnf [mysqld] user=mysql basedir = /usr/local/mysql datadir=/data/mysql/mysql3306/data socket = /tmp/mysql.sock port = 3306 pid-file = /data/mysql/mysql3306/mysql.pid user = mysql skip-name-resolve #参考 vim /data/mysql/mysql3306/my3306.cnf [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/mysql/mysql3306/data socket = /data/mysql/mysql3306/mysql.sock server_id = 1 port = 3306 log_error=/data/mysql/mysql3306/logs/error.log log_bin=/data/mysql/mysql3306/logs/mysql-bin binlog_format=row gtid_mode=on enforce_gtid_consistency=true log_slave_updates=1 max_connections=1024 wait_timeout=60 sort_buffer_size=2M max_allowed_packet=32M join_buffer_size=2M innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=32M innodb_log_file_size=128M innodb_log_files_in_group=2 binlog_cache_size=2M max_binlog_cache_size=8M max_binlog_size=512M expire_logs_days=7 slow_query_log=on slow_query_log_file=/data/mysql/mysql3306/logs/slow.log long_query_time=0.5 log_queries_not_using_indexes=1
2.3初始化
#更改mysql相关目录属主 chown -R mysql:mysql /data/* chown -R mysql:mysql /usr/local/mysql* #配置环境变量 echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh . /etc/profile.d/mysql.sh #初始化 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data
2.4启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf & [root@localhost ~]# ss -tanlp | grep 3306 LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=93803,fd=20)) [root@localhost ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.30 MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
2.5配置开机自启
service方式
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld #修改/etc/init.d/mysqld中的下面两行 basedir=/usr/local/mysql datadir=/data/mysql/mysql3306/data [root@localhost ~]# service mysqld start Starting MySQL. SUCCESS! [root@localhost ~]# service mysqld status SUCCESS! MySQL running (99795) [root@localhost ~]# service mysqld stop Shutting down MySQL.. SUCCESS! [root@localhost ~]# ss -tanl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 *:22 *:* LISTEN 0 100 [::1]:25 [::]:* LISTEN 0 128 [::]:22 [::]:* [root@localhost ~]# service mysqld start Starting MySQL. SUCCESS! [root@localhost ~]# ss -tanl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 100 127.0.0.1:25 *:* LISTEN 0 128 *:22 *:* LISTEN 0 100 [::1]:25 [::]:* LISTEN 0 80 [::]:3306 [::]:* LISTEN 0 128 [::]:22 [::]:* #添加开机自启 [root@localhost ~]# chkconfig --add mysqld [root@localhost ~]# chkconfig --list | grep mysqld 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 #删除开机自启 [root@localhost ~]# chkconfig --del mysqld [root@localhost ~]# chkconfig --list | grep mysqld 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]'. [root@localhost ~]#
centos7方式
vim /usr/lib/systemd/system/mysqld.service [Unit] Description=MySQL Server Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql #PIDFile=/data/mysqldata/mysql.pid # Disable service start and stop timeout logic of systemd for mysqld service. TimeoutSec=0 # Execute pre and post scripts as root PermissionsStartOnly=true # Needed to create system tables #ExecStartPre=/usr/bin/mysqld_pre_systemd # Start main service ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --pid-file=/data/mysql/mysql3306/data/mysql3306.pid # Use this to switch malloc implementation #EnvironmentFile=-/etc/sysconfig/mysql # Sets open_files_limit LimitNOFILE = 5000 #Restart=on-failure #RestartPreventExitStatus=1 #PrivateTmp=false systemctl daemon-reload systemctl enable mysqld
3.配置文件参数解释
关于配置文件,没有能完美适应所有场景的“最佳配置”
mysql可配置性太强在这种情况来说非常不好,所以在一些基础配置上针对使用场景略作优化即可。没有十足的把握,不要去随意配置自己不明白的参数。而且mysql很多参数默认的值已经是最佳配置了,一般常见的优化只会在innodb或是日常维护管理相关的参数上。
纸上得来终觉浅,多做测试,然后用于生产环境,才是正道,不要过多纠结于某些参数配置,没有太多意义。
示例参照:
[mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/mysql/mysql3306/data socket = /data/mysql/mysql3306/mysql.sock server_id = 1 port = 3306 log_error=/data/mysql/mysql3306/logs/error.log log_bin=/data/mysql/mysql3306/logs/mysql-bin binlog_format=row gtid_mode=on enforce_gtid_consistency=true log_slave_updates=1 #让从库从主库复制数据时可以写入到binlog日志,为了让从库作为其他从库的主库 max_connections=1024 wait_timeout=60 sort_buffer_size=2M #connection级参数,默认256k(范围在256k-2m),每个连接第一次需要这个buffer时,一次性分配的内存大小,建议不配保持默认,因为高并发可能导致性能下降 max_allowed_packet=32M #设置一结果集缓存的最大值 join_buffer_size=2M #默认256k,如果多次使用join查询导致cost过高,可调至8-16m(尽量在session级别上调整) innodb_buffer_pool_size=128M #innodb缓冲区大小 innodb_flush_log_at_trx_commit=1 #控制Innodb事务日志写入的过程,0,1(默认),2;每次提交都会把log buffer写入log file,并刷到磁盘 innodb_log_buffer_size=32M # redo log 的写缓存,设置大一点能减少写操作,也不能设置过大 innodb_log_file_size=128M #控制事务日志ib_logfile的大小,范围5MB~4G innodb_log_files_in_group=2 #指定有及格日志组,一般2-3个 binlog_cache_size=2M #binlog缓存在内存的大小 max_binlog_cache_size=8M #binlog 能够使用的最大cache 内存大小,不足会报错ERROR 1197 max_binlog_size=512M #如果在开启了bin-log的机器,如主从等,导入大sql文件时要关闭sql_log_bin,让其不写入bin_log,也就不会让从库执行 expire_logs_days=7 #日志保留天数 slow_query_log=on #启用慢查询日志 slow_query_log_file=/data/mysql/mysql3306/logs/slow.log #慢查询日志路径 long_query_time=0.5 #慢查询阈值,单位秒,执行时间超过这个值的将被记录为慢查询日志中 log_queries_not_using_indexes=1 #没有使用索引的sql也将被记录到慢查询日志中
这篇关于mysql而二进制安装及规范的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-07MySQL读写分离入门:轻松掌握数据库读写分离技术
- 2024-12-07MySQL读写分离入门教程
- 2024-12-07MySQL分库分表入门详解
- 2024-12-07MySQL分库分表入门指南
- 2024-12-07MySQL慢查询入门:快速掌握性能优化技巧
- 2024-12-07MySQL入门:新手必读的简单教程
- 2024-12-07MySQL入门:从零开始学习MySQL数据库
- 2024-12-07MySQL索引入门:新手快速掌握MySQL索引技巧
- 2024-12-06BinLog学习:MySQL数据库BinLog入门教程
- 2024-12-06Binlog学习:MySQL数据库的日志管理入门教程