查看: 1801|回复: 0

[Mysql数据库] mysql 5.7.21 二进制安装

发表于 2018-4-4 08:00:02
1. 说明
  1. 适用于CentOS 6.*和CentOS 7.*
  2. 系统版本:CentOS 6.8_x86-64
  3. mysql版本:mysql-5.7.21-linux-glibc2.5-x86_64.tar.gz
  4. mysql程序安装路径:/data/mysql
  5. mysql数据存放路径:/data/mysql/data
  6. socket存放路径: /data/mysql/mysql.sock
  7. 安装方式:二进制文件安装
  8. MySQL 官方下载地址:https://dev.mysql.com/downloads/mysql/
  9. MySQL 5.7官方安装文档:https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
复制代码
2. 新建用户
  1. [root@MYSQL ~]# yum install libaio numactl -y
  2. [root@MYSQL ~]# groupadd mysql
  3. [root@MYSQL ~]# useradd -r -g mysql -M -s /bin/false mysql
复制代码
  1. 1、MySQL依赖于libaio 库。如果这个库没有在本地安装,数据目录初始化和后续的服务器启动步骤将会失败。
  2. 2、此用户仅用于运行mysql服务,而不是登录,因此使用useradd -r和-s /bin/false命令选项来创建对服务器主机没有登录权限的用户。
复制代码
3. 下载解压
  1. [root@MYSQL ~]# cd /data/
  2. [root@MYSQL data]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.21-linux-glibc2.5-x86_64.tar.gz
  3. [root@MYSQL data]# tar -xvf mysql-5.7.21-linux-glibc2.5-x86_64.tar.gz
  4. [root@MYSQL data]# mv mysql-5.7.21-linux-glibc2.5-x86_64 /data/mysql
  5. [root@MYSQL data]# mkdir -p/data/mysql/{logs,tmp}
  6. [root@MYSQL data]# chown -R mysql:mysql /data/mysql/
复制代码
4. 添加环境变量:
  1. [root@MYSQL data]# cd /data/mysql/
  2. [root@MYSQL mysql]# echo export PATH='${PATH}':/data/mysql/bin > /etc/profile.d/mysql.sh
  3. [root@MYSQL mysql]# source /etc/profile.d/mysql.sh
  4. [root@MYSQL mysql]# echo $PATH # 验证
复制代码
  1. # 环境变量添加到全局中时,两个软链接可以不用设置
复制代码
  1. [root@MYSQL mysql]# ln -s /data/mysql/bin/mysql /usr/bin
  2. [root@MYSQL mysql]# ln -s /data/mysql/bin/mysqld /usr/bin
复制代码
5. 添加MySQL类到系统
  1. [root@MYSQL mysql]# echo "/data/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
  2. [root@MYSQL mysql]# ldconfig # 重新加载动态链接库
复制代码
6. 修改启动脚本
  1. 如果安装在/usr/local/mysql/目录下,则两个sed不许执行,因为默认安装在/usr/local/目录下;
复制代码
  1. [root@MYSQL mysql]# sed -i 's#/usr/local/mysql#/data/mysql#g' /data/mysql/bin/mysqld_safe
  2. [root@MYSQL mysql]# sed -i 's#/usr/local/mysql#/data/mysql#g' /data/mysql/support-files/mysql.server
复制代码
7. 修改配置文件
  1. 参考知数堂叶金荣老师的my.cnf生成器,根据需要选择使用,附链接:
  2. http://imysql.com/my-cnf-wizard.html
复制代码
  1. [root@MYSQL mysql]# vim /etc/my.cnf
  2. [client]
  3. port = 3306
  4. socket = /data/mysql/mysql.sock
  5. default-character-set = utf8
  6. [mysqld]
  7. server-id = 1
  8. user = mysql
  9. port = 3306
  10. basedir = /data/mysql
  11. datadir = /data/mysql/data
  12. socket = /data/mysql/mysql.sock
  13. tmpdir = /data/mysql/tmp
  14. character-set-server = utf8mb4
  15. innodb_file_per_table = 1
  16. lower_case_table_names = 1
  17. #0:区分大小写,1:不区分大小写
  18. skip_name_resolve = 1
  19. open_files_limit = 65535
  20. back_log = 1024
  21. max_connections = 512
  22. max_connect_errors = 1000000
  23. table_open_cache = 60000
  24. table_definition_cache = 60000
  25. #两个table为预计建表个数的两倍
  26. table_open_cache_instances = 64
  27. thread_stack = 512K
  28. external-locking = FALSE
  29. max_allowed_packet = 32M
  30. sort_buffer_size = 16M
  31. join_buffer_size = 16M
  32. thread_cache_size = 768
  33. query_cache_size = 0
  34. query_cache_type = 0
  35. interactive_timeout = 600
  36. wait_timeout = 600
  37. tmp_table_size = 96M
  38. max_heap_table_size = 96M
  39. log_error = /data/mysql/logs/error.log
  40. slow_query_log = 1
  41. slow_query_log_file = /data/mysql/logs/slow.log
  42. long_query_time = 2
  43. binlog-ignore-db = mysql
  44. log-bin = /data/mysql/logs/mysql-bin
  45. sync_binlog = 0
  46. binlog_cache_size = 4M
  47. max_binlog_cache_size = 2G
  48. max_binlog_size = 1G
  49. expire_logs_days = 60
  50. #bin-log保留天数
  51. master_info_repository = TABLE
  52. relay_log_info_repository = TABLE
  53. gtid_mode = on
  54. enforce_gtid_consistency = 1
  55. log_slave_updates
  56. binlog_format = row
  57. relay_log_recovery = 1
  58. relay-log-purge = 1
  59. key_buffer_size = 32M
  60. read_buffer_size = 8M
  61. read_rnd_buffer_size = 16M
  62. bulk_insert_buffer_size = 64M
  63. myisam_sort_buffer_size = 128M
  64. myisam_max_sort_file_size = 10G
  65. myisam_repair_threads = 1
  66. lock_wait_timeout = 3600
  67. explicit_defaults_for_timestamp = 1
  68. #autocommit = 1
  69. #autocommit=1事务自动执行
  70. innodb_thread_concurrency = 0
  71. innodb_sync_spin_loops = 100
  72. innodb_spin_wait_delay = 30
  73. sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  74. #sql_mode自定义在项目sql语句报错时开启
  75. transaction_isolation = REPEATABLE-READ
  76. #innodb_additional_mem_pool_size = 16M
  77. innodb_buffer_pool_size = 10469M
  78. #innodb_buffer_pool_size物理内存的70%
  79. innodb_buffer_pool_instances = 8
  80. innodb_buffer_pool_load_at_startup = 1
  81. innodb_buffer_pool_dump_at_shutdown = 1
  82. #innodb_data_file_path = ibdata1:1G:autoextend
  83. #innodb_data_file_path待优化
  84. innodb_flush_log_at_trx_commit = 2
  85. #默认值1是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘
  86. #设成2是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘,处理效率会很高,设成2只会在整个操作系统挂了时才可能丢数据。
  87. innodb_log_buffer_size = 32M
  88. innodb_log_file_size = 2G
  89. innodb_log_files_in_group = 2
  90. innodb_max_undo_log_size = 4G
  91. # 根据您的服务器IOPS能力适当调整
  92. # 一般配普通SSD盘的话,可以调整到 10000 - 20000
  93. # 配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
  94. innodb_io_capacity = 4000
  95. innodb_io_capacity_max = 8000
  96. innodb_flush_neighbors = 0
  97. innodb_write_io_threads = 8
  98. innodb_read_io_threads = 8
  99. innodb_purge_threads = 4
  100. innodb_page_cleaners = 4
  101. innodb_open_files = 65535
  102. innodb_max_dirty_pages_pct = 50
  103. innodb_flush_method = O_DIRECT
  104. innodb_lru_scan_depth = 4000
  105. innodb_checksum_algorithm = crc32
  106. #innodb_file_format = Barracuda
  107. #innodb_file_format_max = Barracuda
  108. innodb_lock_wait_timeout = 10
  109. innodb_rollback_on_timeout = 1
  110. innodb_print_all_deadlocks = 1
  111. innodb_online_alter_log_max_size = 4G
  112. internal_tmp_disk_storage_engine = InnoDB
  113. innodb_stats_on_metadata = 0
  114. innodb_status_file = 1
  115. # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
  116. innodb_status_output = 0
  117. innodb_status_output_locks = 0
  118. #performance_schema
  119. performance_schema = 1
  120. performance_schema_instrument = '%=on'
  121. #innodb monitor
  122. innodb_monitor_enable="module_innodb"
  123. innodb_monitor_enable="module_server"
  124. innodb_monitor_enable="module_dml"
  125. innodb_monitor_enable="module_ddl"
  126. innodb_monitor_enable="module_trx"
  127. innodb_monitor_enable="module_os"
  128. innodb_monitor_enable="module_purge"
  129. innodb_monitor_enable="module_log"
  130. innodb_monitor_enable="module_lock"
  131. innodb_monitor_enable="module_buffer"
  132. innodb_monitor_enable="module_index"
  133. innodb_monitor_enable="module_ibuf_system"
  134. innodb_monitor_enable="module_buffer_page"
  135. innodb_monitor_enable="module_adaptive_hash"
  136. [mysqldump]
  137. quick
  138. max_allowed_packet = 32M
复制代码
8. 初始化数据库
  1. --initialize-insecure参数:暂时不设置密码。
复制代码
  1. [root@MYSQL mysql]# cd /data/mysql/
  2. [root@MYSQL mysql]# ./bin/mysqld --initialize-insecure --defaults-file=/etc/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql
复制代码
或者
  1. --initialize参数:生成随机密码。
复制代码
  1. [root@MYSQL mysql]# cd /data/mysql/
  2. [root@MYSQL mysql]# ./bin/mysqld --initialize --defaults-file=/etc/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql
  3. 2017-07-11T07:34:36.210764Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  4. 2017-07-11T07:34:37.826785Z 0 [Warning] InnoDB: New log files created, LSN=45790
  5. 2017-07-11T07:34:38.275547Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
  6. 2017-07-11T07:34:38.487524Z 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: 65189e9f-660b-11e7-912f-b0518e005cf6.
  7. 2017-07-11T07:34:38.544417Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
  8. 2017-07-11T07:34:38.545337Z 1 [Note] A temporary password is generated for root@localhost: chpta=hXj4*#
复制代码
  1. 注意:此时会生成一个临时密码,root@localhost:后面的是临时密码;
  2. 如果没有输出上面的信息,可以在error.log文件找,或把/etc/my.cnf文件移走;
复制代码
  1. [root@MYSQL mysql]# grep 'temporary password' /data/mysql/logs/error.log
复制代码
9. 开启SSL连接(可选)
  1. [root@MYSQL mysql]# cd /data/mysql/
  2. [root@MYSQL mysql]# bin/mysql_ssl_rsa_setup --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql
复制代码
10. 添加到系统服务
  1. CentOS 6.*:
复制代码
  1. [root@MYSQL ~]# cd /data/mysql
  2. [root@MYSQL mysql]# chmod 755 support-files/mysql.server
  3. [root@MYSQL mysql]# cp support-files/mysql.server /etc/init.d/mysqld
  4. [root@MYSQL mysql]# chkconfig --add mysqld
  5. [root@MYSQL mysql]# chkconfig mysqld on
  6. [root@MYSQL mysql]# chkconfig --list mysqld
复制代码
  1. CentOS 7.*:
复制代码
  1. [root@MYSQL mysql]# vim /usr/lib/systemd/system/mysqld.service
  2. # Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
  3. #
  4. # This program is free software; you can redistribute it and/or modify
  5. # it under the terms of the GNU General Public License as published by
  6. # the Free Software Foundation; version 2 of the License.
  7. #
  8. # This program is distributed in the hope that it will be useful,
  9. # but WITHOUT ANY WARRANTY; without even the implied warranty of
  10. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  11. # GNU General Public License for more details.
  12. #
  13. # You should have received a copy of the GNU General Public License
  14. # along with this program; if not, write to the Free Software
  15. # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
  16. #
  17. # systemd service file for MySQL forking server
  18. #
  19. [Unit]
  20. Description=MySQL Server
  21. Documentation=man:mysqld(8)
  22. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  23. After=network.target
  24. After=syslog.target
  25. [Install]
  26. WantedBy=multi-user.target
  27. [Service]
  28. User=mysql
  29. Group=mysql
  30. ExecStart=/data/mysql/bin/mysqld --defaults-file=/etc/my.cnf
  31. LimitNOFILE = 65535
  32. 让systemctl加载配置服务
  33. [root@MYSQL mysql]# systemctl enable mysqld.service
  34. [root@MYSQL mysql]# systemctl is-enabled mysqld
复制代码
11. 启动mysql
  1. CentOS 6.*
复制代码

  1. [root@MYSQL mysql]# service mysqld start
  2. [root@MYSQL mysql]# service mysqld status # 查看是否运行
复制代码
  1. CentOS 7.*
复制代码
  1. [root@MYSQL mysql]# systemctl start mysqld.service
  2. [root@MYSQL mysql]# systemctl status mysqld.service
复制代码
或者
  1. [root@MYSQL mysql]# /data/mysql/bin/mysqld_safe --user=mysql &
复制代码
12. 配置安全策略:
  1. [root@MYSQL ~]# cd /data/mysql
  2. [root@MYSQL mysql]# ./bin/mysql_secure_installation
  3. Securing the MySQL server deployment.
  4. Enter password for user root: #输入初始化时的临时密码
  5. The existing password for the user account root has expired. Please set a new password.
  6. New password: #设置新密码
  7. Re-enter new password: #重复新密码
  8. VALIDATE PASSWORD PLUGIN can be used to test passwords
  9. and improve security. It checks the strength of password
  10. and allows the users to set only those passwords which are
  11. secure enough. Would you like to setup VALIDATE PASSWORD plugin?
  12. Press y|Y for Yes, any other key for No: y #是否设置密码安全插件(不是DBA,不推荐设置)
  13. There are three levels of password validation policy:
  14. LOW Length >= 8
  15. MEDIUM Length >= 8, numeric, mixed case, and special characters
  16. STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
  17. Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0 #选择0,长度大于8位;根据自己设置
  18. Using existing password for root.
  19. Estimated strength of the password: 100
  20. Change the password for root ? ((Press y|Y for Yes, any other key for No) : n #是否更改root的现有密码
  21. ... skipping.
  22. By default, a MySQL installation has an anonymous user,
  23. allowing anyone to log into MySQL without having to have
  24. a user account created for them. This is intended only for
  25. testing, and to make the installation go a bit smoother.
  26. You should remove them before moving into a production
  27. environment.
  28. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #删除匿名用户
  29. Success.
  30. Normally, root should only be allowed to connect from
  31. 'localhost'. This ensures that someone cannot guess at
  32. the root password from the network.
  33. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y #禁止root登录远程
  34. Success.
  35. By default, MySQL comes with a database named 'test' that
  36. anyone can access. This is also intended only for testing,
  37. and should be removed before moving into a production
  38. environment.
  39. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #删除测试数据库
  40. - Dropping test database...
  41. Success.
  42. - Removing privileges on test database...
  43. Success.
  44. Reloading the privilege tables will ensure that all changes
  45. made so far will take effect immediately.
  46. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y #重新加载权限表
  47. Success.
  48. All done!
  49. [root@MYSQL mysql]#
复制代码
13. 登录并添加用户及授权
  1. [root@MYSQL mysql]# mysql -uroot -p
  2. mysql> grant all on *.* to 'java'@'%' identified by 'jado@1301'; #授权并同时新建用户
  3. mysql> flush privileges; #刷新权限
  4. 附:
  5. create user java identified by '123456'; #新建普通用户java(密码:123456)
  6. grant all on ras.* to "java"@"%"; #授权:把ras库内的所有权限授权给java用户
  7. revoke all on *.* from "java"@"%"; #取消授权
  8. drop user "java"@"%"; #直接删除用户
  9. grant all on *.* to 'java'@'%' identified by 'jado@1301'; #授权并同时新建用户
  10. grant select,insert,update,delete on ras.* to java@”%” Identified by “123456”; #权限:增删查改
复制代码
14. 拓展:导出用户及权限
  1. 在对mysql数据库进行迁移的时候,有时候也需要迁移源数据库内的用户与权限。
  2. 对于这个迁移我们可以从mysql.user表来获取用户的相关权限来生成相应的SQL语句,然后在目标服务器上来执行生成的SQL语句即可。
  3. 说明:mysql中直接通过授权即可使用对应用户,不必使用创建用户命令(如CREATE USER 'xxx'@'%' IDENTIFIED BY 'XXX';)先建用户再授权。
  4. 该脚本可以将所有授权数据到当前目录下的sql脚本(grants.sql)中,使用grants.sql脚本刷到数据库中即可完成授权数据迁移(注意:这里导出的数据包含root用户的授权关系,而且导入之后会把目前已有的数据覆盖掉,请确认需要覆盖再进行导入!!):
复制代码
  1. vim mysql_user_ migrate.sh
  2. #!/bin/bash
  3. #Function export user privileges
  4. pwd=rootroot
  5. expgrants()
  6. {
  7. mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" | mysql -u'root' -p${pwd} $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
  8. }
  9. expgrants > ./grants.sql
复制代码

  1. 注意:上述代码中,需要根据实际情况(mysql的root用户的密码)替换pwd的值。
  2. 将上述代码拷贝后,新建并贴到shell脚本(如exp_grants.sh)中执行该脚本即可完成数据库用户授权导出。
  3. 以上文件导出后的脚本(grants.sql)中内容类似如下片段:
复制代码


  1. 1. -- Grants for root@%
  2. 2. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2' WITH GRANT OPTION;
  3. 3.
  4. 4. -- Grants for skyleo@%
  5. 5. GRANT ALL PRIVILEGES ON *.* TO 'aaa'@'%' IDENTIFIED BY PASSWORD '*E6A7BF712C9294EEF165FC1CD0AD04FABC5E1136' WITH GRANT OPTION;
  6. 6.
  7. 7. -- Grants for skyleo1@%
  8. 8. GRANT ALL PRIVILEGES ON *.* TO 'aaa1'@'%' IDENTIFIED BY PASSWORD '*1E9DC9809EBE1D5089616868F2DE14B375DACF64' WITH GRANT OPTION;
  9. 9.
  10. 10. -- Grants for skyleo2@%
  11. 11. GRANT ALL PRIVILEGES ON *.* TO 'aaa2'@'%' IDENTIFIED BY PASSWORD '*A601FAAA6AB2D539501BA7FE6E816D499207BA76' WITH GRANT OPTION;
  12. 12.
复制代码
  1. 导入新数据库:
复制代码
  1. mysql -uroot -p mysql < grants.sql
复制代码
15. END


回复

使用道具 举报