無外網安裝mysql · 學習筆記

服務器環境： ***** centos7 x64 需要安裝mysql5.7+ ***** ## 1、卸載centos7自帶的mariadb # 查看系統自帶的Mariadb [root@CDH-141 ~]# rpm -qa|grep mariadb mariadb-libs-5.5.44-2.el7.centos.x86_64 # 卸載系統自帶的Mariadb [root@CDH-141 ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64 # 刪除etc目錄下的my.cnf [root@CDH-141 ~]# rm /etc/my.cnf ## 2、檢查mysql是否存在 # 檢查mysql是否存在 [root@CDH-141 ~]# rpm -qa | grep mysql [root@CDH-141 ~]# ## 3、查看用戶和是否存在 #### 1、）檢查mysql組和用戶是否存在 ``` # 檢查mysql組和用戶是否存在，如無則創建 [root@CDH-141 ~]# cat /etc/group | grep mysql [root@CDH-141 ~]# cat /etc/passwd | grep mysql ``` #### 2、）若不存在，則創建mysql組和用戶 # 創建mysql用戶組 [root@CDH-141 ~]# groupadd mysql # 創建一個用戶名為mysql的用戶，并加入mysql用戶組 [root@CDH-141 ~]# useradd -g mysql mysql # 制定password 為111111 [root@CDH-141 ~]# passwd mysql Changing password for user mysql. New password: BAD PASSWORD: The password is a palindrome Retype new password: passwd: all authentication tokens updated successfully. ## 4、下載mysql離線安裝包tar文件官網下載地址：[https://dev.mysql.com/downloads/mysql/5.7.html#downloads]() 版本選擇，可以選擇一下兩種方式：使用Red Hat Enterprise Linux Select Version:5.7.25 Select Operating System:Red Hat Enterprise Linux / Oracle Linux Select OS Version:Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit) 列表中下載： Compressed TAR Archive：(mysql-5.7.25-el7-x86_64.tar.gz) ## 5、上傳下載的mysql安裝包到服務器 /usr/local/ # 進入/usr/local/文件夾 [root@CDH-141 ~]# cd /usr/local/ # 解壓mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz [root@CDH-141 local]# tar -zxvf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz # 進入/usr/local下，將解壓后的文件夾修改為mysql [root@CDH-141 local]# mv mysql-5.7.25-linux-glibc2.12-x86_64 mysql ## 6、更改所屬用戶組 # 更改所屬的組和用戶 [root@CDH-141 ~]# cd /usr/local/ [root@CDH-141 local]# chown -R mysql mysql/ [root@CDH-141 local]# chgrp -R mysql mysql/ [root@CDH-141 local]# cd mysql/ [root@CDH-141 mysql]# mkdir data [root@CDH-141 mysql]# chown -R mysql:mysql data ## 7、在 /etc/下創建my.cnf文件 mysql配置文件可放在 /etc/ 或者 /usr/local/etc 或者mysql目錄下 # 進入/usr/local/mysql文件夾下 [root@CDH-141 ~]# cd /usr/local/mysql # 創建my.cnf文件 [root@CDH-141 mysql]# touch my.cnf #或者cd ''>my.conf # 編輯my.cnf [root@CDH-141 mysql]# vi my.conf ``` [mysql] socket=/var/lib/mysql/mysql.sock # set mysql client default chararter default-character-set=utf8 [mysqld] socket=/var/lib/mysql/mysql.sock # set mysql server port port = 3306 #默認是3306，這里發現3306已經被占用，因此防止這種情況發生，可以避免使用3306mysql默認端口 # set mysql install base dir basedir=/usr/local/mysql # set the data store dir datadir=/usr/local/mysql/data # set the number of allow max connnection max_connections=200 # set server charactre default encoding character-set-server=utf8 # the storage engine default-storage-engine=INNODB lower_case_table_names=1 max_allowed_packet=16M explicit_defaults_for_timestamp=true [mysql.server] user=mysql basedir=/usr/local/mysql ``` [root@CDH-141 mysql]# ## 8、進入mysql文件夾，安裝mysql ``` # 進入mysql [root@CDH-141 local]# cd /usr/local/mysql # 安裝mysql [root@CDH-141 mysql]# bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ 2019-03-08 18:11:07 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize 2019-03-08 18:11:24 [WARNING] The bootstrap log isn't empty: 2019-03-08 18:11:24 [WARNING] 2019-03-08T10:11:07.208602Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead ``` 此處如果出現錯誤： 2021-06-30T18:14:43.919417Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2021-06-30T18:14:43.919426Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 問題原因：用戶需要打開的文件數超過了上限，通過命令“ulimit -a”可查看如下信息：可以看到該用戶的最大打開文件數為1024個，而我啟動的mysql服務需要打開15000個，所以出現了上述的警告。出現了這個警告后，mysql服務是正常可用的，只是性能沒有達到最優。下面我們著手解決這個問題。首先在文件sudo vi?/etc/security/limits.conf中追加下面信息： root hard?nofile?65535 ``` #* soft core 0 #* hard rss 10000 #@student hard nproc 20 #@faculty soft nproc 20 #@faculty hard nproc 50 #ftp hard nproc 0 #@student - maxlogins 4 root hard nofile 65535 ``` 其中root 為我當前使用的linux用戶。然后退出該終端，再重啟一個終端，執行命令：ulimit -n 65535 重新安裝，問題解決設置文件及目錄權限： [root@CDH-141 mysql]# cp ./support-files/mysql.server /etc/init.d/mysqld [root@CDH-141 mysql]# chown 777 my.cnf [root@CDH-141 mysql]# ls bin COPYING data docs include lib man my.cnf README share support-files [root@CDH-141 mysql]# ls -l total 60 drwxr-xr-x 2 root root 4096 Mar 8 15:56 bin -rw-r--r-- 1 7161 31415 17987 Dec 21 18:39 COPYING drwxr-x--- 5 mysql mysql 4096 Mar 8 16:21 data drwxr-xr-x 2 root root 4096 Mar 8 15:56 docs drwxr-xr-x 3 root root 4096 Mar 8 15:56 include drwxr-xr-x 5 root root 4096 Mar 8 15:56 lib drwxr-xr-x 4 root root 4096 Mar 8 15:56 man -rw-r--r-- 1 777 root 516 Mar 8 16:19 my.cnf -rw-r--r-- 1 7161 31415 2478 Dec 21 18:39 README drwxr-xr-x 28 root root 4096 Mar 8 15:56 share drwxr-xr-x 2 root root 4096 Mar 8 15:56 support-files `[root@CDH-141 mysql]# chmod +x /etc/init.d/mysqld [root@CDH-141 mysql]# [root@CDH-141 mysql]# mkdir data [root@CDH-141 mysql]# [root@CDH-141 mysql]# chown -R mysql:mysql data [root@CDH-141 mysql]#` ``` ## 9、啟動mysql # 啟動mysql [root@CDH-141 mysql]# /etc/init.d/mysqld restart MySQL server PID file could not be found![FAILED] Starting MySQL.Logging to '/usr/local/mysql/data/CDH-141.err'. ..The server quit without updating PID file (/usr/local/mysql/data/CDH-141.pid).[FAILED] [root@CDH-141 mysql]# 、出現錯誤解決方法 #找到是否已經有進程占用 [root@CDH-141 mysql]# ps aux|grep mysql root 32483 0.0 0.0 113252 1620 pts/0 S 18:04 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/CDH-141.pid mysql 32684 0.1 0.1 1119892 178224 pts/0 Sl 18:04 0:00 /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=CDH-141.err --pid-file=/usr/local/mysql/data/CDH-141.pid --port=3323 root 35137 0.0 0.0 112648 944 pts/0 S+ 18:12 0:00 grep --color=auto mysql #關閉進程 [root@CDH-141 mysql]# kill -9 32684 [root@CDH-141 mysql]# /usr/local/mysql/bin/mysqld_safe: line 198: 32684 Killed nohup /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=CDH-141.err --pid-file=/usr/local/mysql/data/CDH-141.pid --port=3323 < /dev/null > /dev/null 2>&1 #確認是否還占用 [root@CDH-141 mysql]# ps aux|grep mysql root 35501 0.0 0.0 112644 948 pts/0 S+ 18:13 0:00 grep --color=auto mysql [root@CDH-141 mysql]# /etc/init.d/mysqld restart MySQL server PID file could not be found![FAILED] Starting MySQL..[ OK ] [root@CDH-141 mysql]# # 重啟mysql [root@CDH-141 mysql]# /etc/init.d/mysqld restart Shutting down MySQL..[ OK ] Starting MySQL..[ OK ] [root@CDH-141 mysql]# 此時報錯： ``` [root@localhost etc]# /etc/init.d/mysqld restart ERROR! MySQL server PID file could not be found! Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'. 2021-07-01T06:27:54.013397Z mysqld_safe Directory '/var/lib/mysql' for UNIX socket file don't exists. ERROR! The server quit without updating PID file (/usr/local/mysql/data/localhost.localdomain.pid). ``` #### 解決方法： mkdir /var/lib/mysql chmod 777 /var/lib/mysql ## 10、設置開機啟動 [root@CDH-141 mysql]# chkconfig --level 35 mysqld on [root@CDH-141 mysql]# chkconfig --list 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@CDH-141 mysql]# chmod +x /etc/rc.d/init.d/mysqld [root@CDH-141 mysql]# chkconfig --add mysqld [root@CDH-141 mysql]# chkconfig --list 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@CDH-141 mysql]# service mysqld status MySQL running (26122)[ OK ] [root@CDH-141 mysql]# ## 11、修改配置文件 # 進入/etc/profile文件夾 [root@CDH-141 mysql]# vim /etc/profile 修改/etc/profile，在最后添加如下內容 # 修改/etc/profile文件 #set mysql environment export PATH=$PATH:/usr/local/mysql/bin # 使文件生效 [root@CDH-141 mysql]# source /etc/profile ## 12、獲取mysql root初始密碼 [root@CDH-141 mysql]# cat /root/.mysql_secret # Password set for user 'root@localhost' at 2019-03-08 17:40:42 poc3u0mO_luv [root@CDH-141 mysql]# 修改密碼后嘗試登陸mysql，出現錯誤： ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) 解決步驟： 1）檢查服務有沒有啟動 2）在my.cnf文件中查看socket參數指定的路徑，查看這個路徑有沒有訪問權限 3）到那個路徑下去看一下到底有沒有這個mysql.sock文件，如果該路徑下沒有sock文件，我們先用find命令找出這個文件的位置，如果find也找不到，重啟一下mysql服務即可，會自動生成一個。然后把他復制到soctek參數指定的路徑下去。或者建立一個軟連接，這也是比較推薦的方法，比如這個文件在/var/lib/mysql.sock，socket參數指定的路徑是/tmp/mysql.sock。我們就可以這樣創建： ln -s /var/lib/mysql.sock /tmp/mysql.sock 創建完之后，再嘗試連接另外需要注意的是，mysql.sock文件默認是在/tmp下，數據庫啟動的時候，系統也默認去這個文件下找mysql.sock文件，但是/tmp目錄有時會被某個定時任務給清除，那么我們可以給/tmp目錄加一個sticky權限，保護其不被刪除，chmod +t /tmp即可，使得/tmp下的文件只能由文件所有者和root用戶才能刪除 ## 13、修改密碼 [root@CDH-141 mysql]# mysql -uroot -p Enter password: #此處填寫上邊獲取到的初始密碼‘poc3u0mO_luv’ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.25 Copyright (c) 2000, 2015, 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> set PASSWORD = PASSWORD('123456'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye ## 14、驗證修改密碼是否成功 [root@CDH-141 mysql]# mysql -uroot -p Enter password: #此處輸入新密碼‘123456’ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.25 MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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 tables; ERROR 1046 (3D000): No database selected mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> ## 15、添加遠程訪問權限 # 添加遠程訪問權限 mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set host='%' where user='root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | root | | localhost | mysql.session | | localhost | mysql.sys | +-----------+---------------+ 3 rows in set (0.00 sec) mysql> # 16、重啟mysql時配置生效 # 重啟mysql [root@CDH-141 mysql]# /etc/init.d/mysqld restart Shutting down MySQL..[ OK ] Starting MySQL..[ OK ] [root@CDH-141 mysql]# *********************************************************** **測試遠程連接時，發現無法連接mysql，可嘗試防火墻開放3306端口** *********************************************************** ``[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent success` **命令含義：** \--zone #作用域 \--add-port=3306/tcp #添加端口，格式為：端口/通訊協議 \--permanent #永久生效，沒有此參數重啟后失效 #### 重啟防火墻 `[root@localhost ~]# systemctl restart firewalld.service` linux下mysql開啟遠程訪問權限及防火墻開放3306端口 1、登陸mysql mysql -u root -p 2、設置訪問地址如果你想允許用戶root從ip為192.168.1.123的主機連接到mysql服務器，并使用root作為密碼 ``GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.123'IDENTIFIED BY 'password' WITH GRANT OPTION; `` 3、刷新 `flush privileges;` 防火墻開啟 1、開啟端口3306 `firewall-cmd --zone=public --add-port=3306/tcp --permanent` 2、重啟防火墻 `firewall-cmd --reload` 查看已經開放的端口： `firewall-cmd --list-ports`