前言：之前见过各种各样的安装方式，甚至所有参数都默认的情况也有，有些参数可以在线更改，但是有些参数不得不重启MySQL服务来更改，这不仅对线上环境造成影响，更对咱们运维人员的绳命造成影响（半夜起床重启服务）。因此，对于MySQL标准化安装是非常有必要的，一来对于运维人员来说，各种文件路径放在哪个位置都很清楚，二来标准化的安装可以在一开始就设定好各种参数在一个比较合理的值，尽量避免或者减少重启MySQL服务的可能。
  下面介绍一下MySQL的标准化安装

一、硬件部分

  • 关闭numa

  • 限制设置 /etc/security/limits.conf & 网络优化

  • swap

  • io调度

  • 文件系统

  • selinux&iptables

1.关闭numa
numa的关闭方法可以通过bios关闭，也可以在操作系统层面关闭，这里推荐在bios关闭。
查看是否开启numa

  1. grep -i numa /var/log/dmesg

讲一下在操作系统怎么关闭

  1. cat /boot/grub/grub.conf

在内核对应的位置添加numa=off或者numactl —interleave=all来启动MySQL

2.限制设置&网络优化
查看系统设置

  1. ulimit -a

主要注意
open files可以改成65535
max user processes可以改成65535
修改/etc/security/limits.d/xxx.conf

网络优化方面MySQL可以做的就比较少了，可以看博客：http://wubx.net/php-connect-mysql-hy000-2003/
3.Swap 对于swap把握两个原则：
（1）对于大内存的服务器（64G），可以完全关闭swap
（2）对于小内存的服务器，swap配置4-8G
4.IO调度 sas:deadline
ssd:noop

echo dealine >/sys/block/{DEV-NAME}/queue/scheduler

5.文件系统
数据目录：ext4或者XFS（推荐）
挂载XFS参数：
(rw, noatime,nodiratime,nobarrier)

mount -o rw,noatime,nodiratime,nobarrier /dev/sdb1 /storage

挂载ext4参数：
ext4 (rw,noatime,nodiratime,nobarrier,data=ordered)

6.selinux & iptables
selinux万年坑，直接关闭吧，骚年
iptables看实际情况，如果跑在内网环境下可以不开iptables，否则还是建议开启iptables为佳。

二、软件部分

yum -y install make gcc-c++ cmake bison-devel ncurses-devel numactl libaio
1.创建用户和用户组 groupadd mysql
useradd -s /sbin/nologin -g mysql -M mysql

2.创建mysql基目录
mkdir -p /usr/local/mysql
更改文件夹所属
chown -R mysql.mysql /usr/local/mysql

3.创建目录
mkdir -p /data/mysql/{data,logs,tmp}
更改文件夹所属
chown -R mysql.mysql /data/

4.上传安装包并解压到/usr/local/mysql

5.创建mysql配置文件my.cnf
vim my.cnf

  1. #my.cnf
  2. [client]
  3. port            = 3306
  4. socket          = /tmp/mysql3306.sock
  6. [mysql]
  7. prompt="\\u@\\h [\\d]>" 
  8. #pager="less -i -n -S"
  9. #tee=/opt/mysql/query.log
  10. no-auto-rehash #非自动补全
  12. [mysqld]
  13. #misc
  14. user = mysql
  15. basedir = /usr/local/mysql                 
  16. datadir = /data/mysql/data     
  17. port = 3306                             
  19. socket = /tmp/mysql3306.sock
  20. event_scheduler = 0                     
  22. tmpdir = /data/mysql/tmp         
  23. #timeout
  24. interactive_timeout = 300 
  25. wait_timeout = 300
  27. #character set
  28. character-set-server = utf8             
  30. open_files_limit = 65535                 
  31. max_connections = 100                    
  32. max_connect_errors = 100000                
  33. lower_case_table_names =1                
  34. #file
  35. #@secure-file-priv=/tmp
  36. #symi replication                        
  38. #rpl_semi_sync_master_enabled=1
  39. #rpl_semi_sync_master_timeout=1000 # 1 second
  40. #rpl_semi_sync_slave_enabled=1
  42. #logs
  43. log-output=file
  44. slow_query_log = 1
  45. slow_query_log_file = slow.log
  46. log-error = error.log
  47. log_warnings = 2
  48. pid-file = mysql.pid
  49. long_query_time = 1
  50. #log-slow-admin-statements = 1
  51. #log-queries-not-using-indexes = 1
  52. log-slow-slave-statements = 1
  54. #binlog
  55. #binlog_format = STATEMENT
  56. binlog_format = row
  57. server-id = 1003306
  58. log-bin = /data/mysql/logs/mysql-bin
  59. max_binlog_size = 256M
  60. sync_binlog = 0
  61. expire_logs_days = 10
  62. #procedure 
  63. log_bin_trust_function_creators=1
  65. #file
  66. secure_file_priv="/tmp"
  67. #
  68. gtid-mode = on
  69. enforce-gtid-consistency=1
  72. #relay log
  73. skip_slave_start = 1
  74. max_relay_log_size = 1024M
  75. relay_log_purge = 1
  76. relay_log_recovery = 1
  77. relay-log=relay-bin
  78. relay-log-index=relay-bin.index
  79. log_slave_updates
  81. #slave-skip-errors=1032,1053,1062
  82. #skip-grant-tables
  84. #buffers & cache
  85. table_open_cache = 2048
  86. table_definition_cache = 2048
  87. table_open_cache = 2048
  88. max_heap_table_size = 96M
  89. sort_buffer_size = 128K
  90. join_buffer_size = 128K
  91. thread_cache_size = 200
  92. query_cache_size = 0
  93. query_cache_type = 0
  94. query_cache_limit = 256K
  95. query_cache_min_res_unit = 512
  96. thread_stack = 192K
  97. tmp_table_size = 96M
  98. key_buffer_size = 8M
  99. read_buffer_size = 2M
  100. read_rnd_buffer_size = 16M
  101. bulk_insert_buffer_size = 32M
  103. #myisam
  104. myisam_sort_buffer_size = 128M
  105. myisam_max_sort_file_size = 10G
  106. myisam_repair_threads = 1
  108. #innodb
  109. innodb_buffer_pool_size = 4G
  110. innodb_buffer_pool_instances = 1
  111. innodb_data_file_path = ibdata1:1024M:autoextend
  112. innodb_flush_log_at_trx_commit = 2
  113. innodb_log_buffer_size = 8M
  114. innodb_log_file_size = 1024M
  115. innodb_log_files_in_group = 3
  116. innodb_max_dirty_pages_pct = 50
  117. innodb_file_per_table = 1
  118. innodb_rollback_on_timeout
  119. innodb_io_capacity = 2000
  120. transaction_isolation = READ-COMMITTED
  121. innodb_flush_method = O_DIRECT

6.初始化数据库
mysql5.6:/usr/local/mysql/scripts/mysql_install_db —basedir=/usr/local/mysql/ —datadir=/data/mysql/data —defaults-file=/etc/my.cnf —user=mysql
mysql5.7:/usr/local/mysql/bin/mysqld —defaults-file=/etc/my.cnf —initialize

若出现

  1. (Can't locate Data/Dumper.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at scripts/mysql_install_db line 42.
  2. BEGIN failed--compilation aborted at scripts/mysql_install_db line 42.)

执行下面命令
yum install ‘perl(Data::Dumper)’

7.添加环境变量
vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin

8.环境变量生效
. /etc/profile

9.启动数据库服务
mysqld_safe —defaults-file=/etc/my.cnf 2>&1 > /dev/null &

10.更改系统变量
echo “export PATH=$PATH:/usr/local/mysql/bin” >>/etc/profile
source /etc/profile