参考 https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html https://dev.mysql.com/doc/refman/8.0/en/starting-server.html https://www.cnblogs.com/kaishirenshi/p/12667004.html


安装MySQL

  1.     ###下载安装包
  2.     wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.25-linux-glibc2.12-x86_64.tar.xz
  5.     ### 对MySQL进行重命名
  6.     [root@localhost local]# mv mysql-8.0.25-linux-glibc2.12-x86_64/ mysql
  8.     [root@localhost local]# ll
  9.     total 0
  10.     drwxr-xr-x. 2 root root   6 Nov  3  2020 bin
  11.     drwxr-xr-x. 2 root root   6 Nov  3  2020 etc
  12.     drwxr-xr-x. 2 root root   6 Nov  3  2020 games
  13.     drwxr-xr-x. 2 root root   6 Nov  3  2020 include
  14.     drwxr-xr-x. 2 root root   6 Nov  3  2020 lib
  15.     drwxr-xr-x. 3 root root  17 Jun 16 02:59 lib64
  16.     drwxr-xr-x. 2 root root   6 Nov  3  2020 libexec
  17.     drwxr-xr-x. 9 root root 129 Jun 16 03:16 mysql
  18.     drwxr-xr-x. 2 root root   6 Nov  3  2020 sbin
  19.     drwxr-xr-x. 5 root root  49 Jun 16 02:59 share
  20.     drwxr-xr-x. 2 root root   6 Nov  3  2020 src
  21.     [root@localhost local]#
  22.     [root@localhost local]#
  23.     [root@localhost local]#
  24.     [root@localhost local]#
  25.     [root@localhost local]#
  26.     [root@localhost local]#
  27.     ###创建用户组
  28.     [root@localhost local]# groupadd mysql
  29.     [root@localhost local]#
  30.     [root@localhost local]#
  31.     [root@localhost local]#
  32.     ####添加MySQL用户
  33.     [root@localhost local]# useradd -r -g mysql -s /bin/false mysql
  36.     [root@localhost local]#
  37.     [root@localhost local]#
  38.     [root@localhost local]#
  40.     [root@localhost local]# cd mysql/
  41.     [root@localhost mysql]#
  42.     [root@localhost mysql]#
  43.     [root@localhost mysql]#
  45.     [root@localhost mysql]# mkdir mysql-files
  46.     [root@localhost mysql]#
  47.     [root@localhost mysql]#
  48.     [root@localhost mysql]#
  49.     [root@localhost mysql]# ll
  50.     total 284
  51.     drwxr-xr-x.  2 7161 31415   4096 Apr 23 14:56 bin
  52.     drwxr-xr-x.  2 7161 31415     55 Apr 23 14:56 docs
  53.     drwxr-xr-x.  3 7161 31415    282 Apr 23 14:56 include
  54.     drwxr-xr-x.  6 7161 31415    201 Apr 23 14:56 lib
  55.     -rw-r--r--.  1 7161 31415 274942 Apr 23 11:06 LICENSE
  56.     drwxr-xr-x.  4 7161 31415     30 Apr 23 14:56 man
  57.     drwxr-xr-x.  2 root root       6 Jun 16 03:17 mysql-files
  58.     -rw-r--r--.  1 7161 31415    666 Apr 23 11:06 README
  59.     drwxr-xr-x. 28 7161 31415   4096 Apr 23 14:56 share
  60.     drwxr-xr-x.  2 7161 31415     77 Apr 23 14:56 support-files
  61.     [root@localhost mysql]#
  62.     [root@localhost mysql]#
  63.     ####赋予MySQL用户MySQL用户组 对mysql-files的权限
  64.     [root@localhost mysql]# chown mysql:mysql mysql-files
  65.     [root@localhost mysql]#
  66.     [root@localhost mysql]#
  67.     [root@localhost mysql]#
  68.     [root@localhost mysql]# ll
  69.     total 284
  70.     drwxr-xr-x.  2  7161 31415   4096 Apr 23 14:56 bin
  71.     drwxr-xr-x.  2  7161 31415     55 Apr 23 14:56 docs
  72.     drwxr-xr-x.  3  7161 31415    282 Apr 23 14:56 include
  73.     drwxr-xr-x.  6  7161 31415    201 Apr 23 14:56 lib
  74.     -rw-r--r--.  1  7161 31415 274942 Apr 23 11:06 LICENSE
  75.     drwxr-xr-x.  4  7161 31415     30 Apr 23 14:56 man
  76.     drwxr-xr-x.  2 mysql mysql      6 Jun 16 03:17 mysql-files
  77.     -rw-r--r--.  1  7161 31415    666 Apr 23 11:06 README
  78.     drwxr-xr-x. 28  7161 31415   4096 Apr 23 14:56 share
  79.     drwxr-xr-x.  2  7161 31415     77 Apr 23 14:56 support-files
  80.     [root@localhost mysql]#
  81.     [root@localhost mysql]#
  82.     ###赋予MySQL用户的执行权限
  83.     [root@localhost mysql]# chmod 750 mysql-files
  84.     [root@localhost mysql]#
  85.     [root@localhost mysql]#
  86.     [root@localhost mysql]# ll
  87.     total 284
  88.     drwxr-xr-x.  2  7161 31415   4096 Apr 23 14:56 bin
  89.     drwxr-xr-x.  2  7161 31415     55 Apr 23 14:56 docs
  90.     drwxr-xr-x.  3  7161 31415    282 Apr 23 14:56 include
  91.     drwxr-xr-x.  6  7161 31415    201 Apr 23 14:56 lib
  92.     -rw-r--r--.  1  7161 31415 274942 Apr 23 11:06 LICENSE
  93.     drwxr-xr-x.  4  7161 31415     30 Apr 23 14:56 man
  94.     drwxr-x---.  2 mysql mysql      6 Jun 16 03:17 mysql-files
  95.     -rw-r--r--.  1  7161 31415    666 Apr 23 11:06 README
  96.     drwxr-xr-x. 28  7161 31415   4096 Apr 23 14:56 share
  97.     drwxr-xr-x.  2  7161 31415     77 Apr 23 14:56 support-files
  98.     [root@localhost mysql]#
  99.     [root@localhost mysql]#
  100.     ###执行MySQL的初始化  将会生成一个data文件夹 ，最后一行生成一个temporary密码
  101.     [root@localhost mysql]# bin/mysqld --initialize --user=mysql
  102.     2021-06-16T07:18:33.509368Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25) initializing of server in progress as process 1682
  103.     2021-06-16T07:18:33.521073Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  104.     2021-06-16T07:18:34.461153Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  105.     2021-06-16T07:18:35.564759Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 2M78VcFDrf+G
  106.     [root@localhost mysql]#
  107.     [root@localhost mysql]#
  108.     [root@localhost mysql]# cd bin
  109.     [root@localhost bin]#
  110.     [root@localhost bin]#
  111.     [root@localhost bin]# ps -ef | grep mysql
  112.     root        1734    1523  0 03:19 pts/0    00:00:00 grep --color=auto mysql
  114.     [root@localhost mysql]# ll
  115.     total 288
  116.     drwxr-xr-x.  2  7161 31415   4096 Apr 24 02:56 bin
  117.     drwxr-x---.  6 mysql mysql   4096 Jun 16 15:18 data
  118.     drwxr-xr-x.  2  7161 31415     55 Apr 24 02:56 docs
  119.     drwxr-xr-x.  3  7161 31415    282 Apr 24 02:56 include
  120.     drwxr-xr-x.  6  7161 31415    201 Apr 24 02:56 lib
  121.     -rw-r--r--.  1  7161 31415 274942 Apr 23 23:06 LICENSE
  122.     drwxr-xr-x.  4  7161 31415     30 Apr 24 02:56 man
  123.     drwxr-x---.  2 mysql mysql      6 Jun 16 15:17 mysql-files
  124.     -rw-r--r--.  1  7161 31415    666 Apr 23 23:06 README
  125.     drwxr-xr-x. 28  7161 31415   4096 Apr 24 02:56 share
  126.     drwxr-xr-x.  2  7161 31415     77 Apr 24 02:56 support-files
  127.     [root@localhost mysql]#
  128.     [root@localhost mysql]#
  129.     [root@localhost mysql]# cd data
  130.     [root@localhost data]# ll
  131.     total 176568
  132.     -rw-r-----. 1 mysql mysql       56 Jun 16 15:18  auto.cnf
  133.     -rw-------. 1 mysql mysql     1676 Jun 16 15:18  ca-key.pem
  134.     -rw-r--r--. 1 mysql mysql     1112 Jun 16 15:18  ca.pem
  135.     -rw-r--r--. 1 mysql mysql     1112 Jun 16 15:18  client-cert.pem
  136.     -rw-------. 1 mysql mysql     1676 Jun 16 15:18  client-key.pem
  137.     -rw-r-----. 1 mysql mysql   196608 Jun 16 15:18 '#ib_16384_0.dblwr'
  138.     -rw-r-----. 1 mysql mysql  8585216 Jun 16 15:18 '#ib_16384_1.dblwr'
  139.     -rw-r-----. 1 mysql mysql     5931 Jun 16 15:18  ib_buffer_pool
  140.     -rw-r-----. 1 mysql mysql 12582912 Jun 16 15:18  ibdata1
  141.     -rw-r-----. 1 mysql mysql 50331648 Jun 16 15:18  ib_logfile0
  142.     -rw-r-----. 1 mysql mysql 50331648 Jun 16 15:18  ib_logfile1
  143.     drwxr-x---. 2 mysql mysql        6 Jun 16 15:18 '#innodb_temp'
  144.     drwxr-x---. 2 mysql mysql      143 Jun 16 15:18  mysql
  145.     -rw-r-----. 1 mysql mysql 25165824 Jun 16 15:18  mysql.ibd
  146.     drwxr-x---. 2 mysql mysql     8192 Jun 16 15:18  performance_schema
  147.     -rw-------. 1 mysql mysql     1680 Jun 16 15:18  private_key.pem
  148.     -rw-r--r--. 1 mysql mysql      452 Jun 16 15:18  public_key.pem
  149.     -rw-r--r--. 1 mysql mysql     1112 Jun 16 15:18  server-cert.pem
  150.     -rw-------. 1 mysql mysql     1676 Jun 16 15:18  server-key.pem
  151.     drwxr-x---. 2 mysql mysql       28 Jun 16 15:18  sys
  152.     -rw-r-----. 1 mysql mysql 16777216 Jun 16 15:18  undo_001
  153.     -rw-r-----. 1 mysql mysql 16777216 Jun 16 15:18  undo_002
  154.     [root@localhost data]# cd ..
  155.     [root@localhost mysql]#
  156.     [root@localhost mysql]#
  157.     [root@localhost mysql]#
  158.     ####无法采用这种方式启动
  159.     [root@localhost mysql]# systemctl start mysqld
  160.     Failed to start mysqld.service: Unit mysqld.service not found.
  163.     [root@localhost mysql]#
  164.     [root@localhost mysql]#
  166.     ###启动MySQL
  167.     [root@localhost mysql]# bin/mysqld_safe --user=mysql &
  168.     [1] 1816
  169.     [root@localhost mysql]# Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
  170.     2021-06-16T07:22:46.224871Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
  172.     [root@localhost mysql]#
  173.     [root@localhost mysql]#
  175.     ###查看MySQL是否启动
  176.     [root@localhost mysql]# ps -ef | grep mysql
  177.     root        1816    1523  0 15:22 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
  178.     mysql       1898    1816  9 15:22 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=localhost.localdomain.pid
  179.     root        1951    1523  0 15:22 pts/0    00:00:00 grep --color=auto mysql
  180.     [root@localhost mysql]#
  181.     [root@localhost mysql]#
  182.     [root@localhost mysql]#
  183.     [root@localhost mysql]#
  184.     [root@localhost mysql]# cd bin
  185.     ###进入MySQL发现错误
  186.     [root@localhost bin]# ./mysql -uroot -p
  187.     ./mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
  188.     [root@localhost bin]#
  189.     [root@localhost bin]#
  190.     ####创建符号链接
  191.     [root@localhost bin]# sudo ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
  192.     [root@localhost bin]#
  193.     [root@localhost bin]#
  194.     [root@localhost bin]# ./mysql -uroot -p
  195.     Enter password:
  196.     ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  198.     ###将产生的临时密码已下面方式进行登录
  199.     [root@localhost bin]# ./mysql -uroot -p2M78VcFDrf+G
  200.     mysql: [Warning] Using a password on the command line interface can be insecure.
  201.     Welcome to the MySQL monitor.  Commands end with ; or \g.
  202.     Your MySQL connection id is 9
  203.     Server version: 8.0.25
  205.     Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  207.     Oracle is a registered trademark of Oracle Corporation and/or its
  208.     affiliates. Other names may be trademarks of their respective
  209.     owners.
  211.     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  213.     mysql> show databases;
  214.     ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
  215.     mysql>
  216.     mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
  217.     Query OK, 0 rows affected (0.00 sec)
  219.     mysql> show databases;
  220.     +--------------------+
  221.     | Database           |
  222.     +--------------------+
  223.     | information_schema |
  224.     | mysql              |
  225.     | performance_schema |
  226.     | sys                |
  227.     +--------------------+
  228.     4 rows in set (0.00 sec)
  230.     mysql> use mysql
  231.     Reading table information for completion of table and column names
  232.     You can turn off this feature to get a quicker startup with -A
  234.     Database changed
  235.     mysql>
  236.     mysql>
  237.     mysql> select user,password,host from user;
  238.     ERROR 1054 (42S22): Unknown column 'password' in 'field list'
  239.     mysql> select user,host from user;
  240.     +------------------+-----------+
  241.     | user             | host      |
  242.     +------------------+-----------+
  243.     | mysql.infoschema | localhost |
  244.     | mysql.session    | localhost |
  245.     | mysql.sys        | localhost |
  246.     | root             | localhost |
  247.     +------------------+-----------+
  248.     4 rows in set (0.00 sec)
  250.     mysql> update user set host = '%' where user = 'root';
  251.     Query OK, 1 row affected (0.00 sec)
  252.     Rows matched: 1  Changed: 1  Warnings: 0
  254.     mysql> select user,host from user;
  255.     +------------------+-----------+
  256.     | user             | host      |
  257.     +------------------+-----------+
  258.     | root             | %         |
  259.     | mysql.infoschema | localhost |
  260.     | mysql.session    | localhost |
  261.     | mysql.sys        | localhost |
  262.     +------------------+-----------+
  263.     4 rows in set (0.00 sec)
  265.     mysql>
  266.     mysql>
  267.     mysql> select version();
  268.     +-----------+
  269.     | version() |
  270.     +-----------+
  271.     | 8.0.25    |
  272.     +-----------+
  273.     1 row in set (0.00 sec)
  274.     ###刷新权限 就可以远程登陆了
  275.     mysql> flush privileges;
  276.     Query OK, 0 rows affected (0.00 sec)
  279.     因为MySQL8.0加密方式改变，所以远程连接需要修改加密方式或者更新驱动
  281.     修改mysql加密方式：ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的root账号密码';
  282.     刷新权限：FLUSH PRIVILEGES；
  285.     mysql>
  286.     mysql>

配置防火墙

  2.     //查看防火墙的状态
  3.     [root@localhost ~]#  firewall-cmd --state
  4.     running
  5.     //关闭防火墙
  6.     [root@localhost ~]# systemctl stop firewalld.service
  7.     [root@localhost ~]#  firewall-cmd --state
  8.     not running
  11.     //查看3306端口是否开启
  12.     firewall-cmd --query-port=3306/tcp
  14.     ###开启防火墙设置端口
  15.     systemctl start firewalld.service
  17.     #### zone --作用域
  18.     #### add-port=80/tcp--添加端口，格式为：端口/通讯协议
  19.     #### permanent--永久生效，没有此参数重启后失效
  20.     firewall-cmd --zone=public --add-port=3306/tcp --permanent
  21.     ####开启3306端口后，workbench或naivcat 就能连接到MySQL数据库了
  23.     ####重启防火墙
  24.     firewall-cmd --reload
  26.   添加端口号：
  27.     添加：iptables -I INPUT -p tcp --dport 3306 -j ACCET
  28.     查看：firewall-cmd --zone=public --list-ports

设置开机自启动

  2.         //配置环境变量和系统自启动
  3.     配置mysql环境变量：将mysql的bin目录加入PATH环境变量，编辑 /etc/profile文件：
  5.     vim /etc/profile
  7.     #最后添加下面一行
  8.     ###PATH与=不能有空格
  9.     export PATH=$PATH:/usr/local/mysql/bin
  11.     #执行命令使其生效：
  12.     source /etc/profile
  14.     ###用 export 命令查看PATH值：
  16.     echo $PATH
  18.     ####设置开机自启动：
  20.     ####复制启动脚本到资源目录：
  22.     [root@localhost mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
  24.     ####增加 mysqld 服务控制脚本执行权限：
  26.     [root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysqld
  28.     ####将 mysqld 服务加入到系统服务：
  30.     [root@localhost mysql]# chkconfig --add mysqld
  32.     ####检查mysqld服务是否已经生效：
  34.     [root@localhost mysql]# chkconfig --list mysqld
  36.     ####命令输出类似下面的结果：
  38.     mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
  40.     表明mysqld服务已经生效，在2、3、4、5运行级别随系统启动而自动启动，以后可以使用 service 命令控制 mysql 的启动和停止。
  42.     ####启动指令
  43.     service mysqld stop

查看MySQL配置文件

  1. #### MySQL配置文件加载顺序
  2. [root@localhost ~]# mysql --help|grep 'my.cnf'
  3.                       order of preference, my.cnf, $MYSQL_TCP_PORT,
  4. /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
  8. ####查看是否使用了MySQL配置文件 好像没用
  9. [root@localhost ~]# ps aux|grep mysql|grep 'my.cnf'
  10. [root@localhost ~]#

配置My.cnf

https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html 由于MySQL8 已不在安装包中包含配置文件，建议初始化MySQL之前去建立my.cnf Note: service mysqld stop

  1. [client]
  2. port = 3306
  3. #根据实际情况调整mysql.sock配置
  4. socket = /tmp/mysql.sock
  6. [mysqld]
  7. #Mysql服务的唯一编号 每个mysql服务Id需唯一
  8. server-id = 1
  10. #服务端口号 默认3306
  11. port = 3306
  13. #mysql安装根目录
  14. basedir = /usr/local/mysql
  16. #mysql数据文件所在位置
  17. datadir = /usr/local/mysql/data
  19. #pid
  20. pid-file = /usr/local/mysql/data/mysql.pid
  22. #设置socke文件所在目录
  23. socket = /tmp/mysql.sock
  25. #设置临时目录
  26. tmpdir = /tmp
  28. # 用户
  29. user = mysql
  31. # 允许访问的IP网段
  32. bind-address = 0.0.0.0
  34. # 跳过密码登录
  35. #skip-grant-tables
  37. #主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
  38. skip-external-locking
  40. #只能用IP地址检查客户端的登录，不用主机名
  41. skip_name_resolve = 1
  43. #事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
  44. transaction_isolation = REPEATABLE-READ
  46. #数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
  47. character-set-server = utf8mb4
  49. #数据库字符集对应一些排序等规则，注意要和character-set-server对应
  50. collation-server = utf8mb4_general_ci
  52. #设置client连接mysql时的字符集,防止乱码
  53. init_connect='SET NAMES utf8mb4'
  55. #是否对sql语句大小写敏感，1表示不敏感
  56. lower_case_table_names = 1
  58. #最大连接数
  59. max_connections = 400
  61. #最大错误连接数
  62. max_connect_errors = 1000
  64. #TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
  65. explicit_defaults_for_timestamp = true
  67. #SQL数据包发送的大小，如果有BLOB对象建议修改成1G
  68. max_allowed_packet = 128M
  71. #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
  72. #MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
  73. interactive_timeout = 1800
  74. wait_timeout = 1800
  76. #内部内存临时表的最大值 ，设置成128M。
  77. #比如大数据量的group by ,order by时可能用到临时表，
  78. #超过了这个值将写入磁盘，系统IO压力增大
  79. tmp_table_size = 134217728
  80. max_heap_table_size = 134217728
  84. #数据库错误日志文件
  85. log_error = error.log
  87. #慢查询sql日志设置
  88. slow_query_log = 1
  89. slow_query_log_file = slow.log
  91. #检查未使用到索引的sql
  92. log_queries_not_using_indexes = 1
  94. #针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
  95. log_throttle_queries_not_using_indexes = 5
  97. #作为从库时生效,从库复制中如何有慢sql也将被记录
  98. log_slow_slave_statements = 1
  100. #慢查询执行的秒数，必须达到此值可被记录
  101. long_query_time = 8
  103. #检索的行数必须达到此值才可被记为慢查询
  104. min_examined_row_limit = 100
  106. #mysql binlog日志文件保存的过期时间，过期后自动删除
  107. expire_logs_days = 5

1,删除data目录

  1. cd /usr/local/mysql
  2. rm -rf data

2,在/etc/ 创建my.cnf

3,初始化MySQL

  1. bin/mysqld --initialize --user=mysql

临时密码会在 usr/local/mysql/data/error.log 中

4,启动MySQL

  1. bin/mysqld_safe --user=mysql &

5,进入MySQL

  1. ./mysql -uroot -p
  2. 输入密码
  4. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
  5. mysql> FLUSH PRIVILEGES;
  6. update user set host = '%' where user = 'root';
  7. mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的root账号密码';
  8. mysql> FLUSH PRIVILEGES;

6,验证配置文件

比如 大小写的问题