一、安裝
參考：https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html
# 檢測系統中是否安裝了mysql yum list installed | grep mysql ### 顯示內容 ### ### 我已經裝過了……沒裝之前可能什麼都沒有，也可能有低版本的mysql mysql-community-client.x86_64 5.7.23-1.el6 @mysql57-community-dmr mysql-community-common.x86_64 5.7.23-1.el6 @mysql57-community-dmr mysql-community-libs.x86_64 5.7.23-1.el6 @mysql57-community-dmr mysql-community-release.noarch el6-5 @/mysql-community-release-el6-5.noarch mysql-community-server.x86_64 5.7.23-1.el6 @mysql57-community-dmr # 解除安裝低版本mysql，才能安裝上mysql5.7 # remove後面是上面顯示的檔案（-y表示全過程選是）,上面出現的全部都要remove掉 yum -y remove mysql-community-client.x86_64 mysql-community-common.x86_64 ... ### 顯示內容 ### ... Removed: mysql-community-client.x86_64 0:5.7.23-1.el6 mysql-community-common.x86_64 0:5.7.23-1.el6 mysql-community-libs.x86_64 0:5.7.23-1.el6 mysql-community-release.noarch 0:el6-5 Dependency Removed: mysql-community-server.x86_64 0:5.7.23-1.el6 Complete! # 再檢測一次，直到為空 # 下載mysql的yum源（el6-5表示惹的redhat6和sentos6-mysql5） wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm # 載入yum源 yum install mysql-community-release-el6-5.noarch.rpm # 檢視mysql可用的安裝源（安裝源裡面有mysql5的好幾個版本） yum repolist enabled | grep mysql ### 顯示內容 ### mysql-connectors-community MySQL Connectors Community 59 mysql-tools-community MySQL Tools Community 65 mysql56-community MySQL 5.6 Community Server 453 # 發現沒有5.7版本的，修改repo檔案，把5.7版本改為可用，5.6版本改為不可用 vi /etc/yum.repos.d/mysql-community.repo ### 顯示內容 ### ############################################################################################################ [mysql-connectors-community] name=MySQL Connectors Community baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/6/$basearch/ enabled=1 gpgcheck=1 gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql [mysql-tools-community] name=MySQL Tools Community baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/6/$basearch/ enabled=1 gpgcheck=1 gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql # Enable to use MySQL 5.5 [mysql55-community] name=MySQL 5.5 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/6/$basearch/ enabled=0 gpgcheck=1 gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql # Enable to use MySQL 5.6 [mysql56-community] name=MySQL 5.6 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/ enabled=1 #把這個1改成0 gpgcheck=1 gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql # Note: MySQL 5.7 is currently in development. For use at your own risk. # Please read with sub pages: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/ [mysql57-community-dmr] name=MySQL 5.7 Community Server Development Milestone Release baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/ enabled=0 #把這個0改成1 gpgcheck=1 gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql ############################################################################################################ # 按i進入編輯模式！ # 輸入數字不要使用小鍵盤！ # 按ESC鍵退出編輯模式！ # 輸入:wq 回車--儲存並退出！ # 再次檢查mysql可用的安裝源 yum repolist enabled | grep mysql ### 顯示內容 ### mysql-connectors-community MySQL Connectors Community 59 mysql-tools-community MySQL Tools Community 65 mysql57-community-dmr MySQL 5.7 Community Server Development Milesto 273 # 發現5.7版本的mysql安裝源可用 # 使用yum安裝mysql yum install mysql-community-server -y ### 顯示內容 ### ### 安裝成功！ ... Installed: mysql-community-server.x86_64 0:5.7.23-1.el6 Dependency Installed: mysql-community-client.x86_64 0:5.7.23-1.el6 mysql-community-common.x86_64 0:5.7.23-1.el6 mysql-community-libs.x86_64 0:5.7.23-1.el6 Complete! # 啟動服務 service mysqld start # 5.7版本跟5.6版本的登入方式不一樣，5.6預設root密碼為空，5.7的root密碼是隨機生成的 # 首次登入可檢視root密碼，登入後要立即修改密碼，設定複雜一些，不然報錯說密碼不安全 grep "password" /var/log/mysqld.log ### 顯示內容 ### 2018-09-05T03:32:48.523907Z 1 [Note] A temporary password is generated for root@localhost: rr_pVi=rj3<u ... # 可以看到密碼為rr_pVi=rj3<u # 登入mysql mysql -uroot -p Enter password: rr_pVi=rj3<u
# 修改root密碼 mysql> ALTER USER `root`@`localhost` IDENTIFIED BY `MyNewPassword!`;
# 退出 mysql> exit; # 重新登入
二、配置
# linux系統安裝mysql預設區分大小寫 # 設定不區分大小寫 [root@i-epo5ap9i ~]# vi /etc/my.cnf ######################################################################################### # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock lower_case_table_names=1 #在這裡加上這句，linux下1不區分，0區分 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~ ~ ######################################################################################### # 儲存並退出 # 重啟mysql服務 [root@i-epo5ap9i ~]# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] # 登入mysql [root@i-epo5ap9i ~]# mysql -uroot -p Enter password:... 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) # 使用資料庫：mysql 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> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec) # 檢視使用者表（只看主機和使用者名稱欄位，*的話太多了） mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | host | username | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 4 rows in set (0.00 sec) # 建立資料庫 mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) # 建立使用者並授予test資料庫的所有許可權 mysql> grant all privileges on test.* to `test_user`@`%` identified by `test123.PassWord`; Query OK, 0 rows affected, 1 warning (0.00 sec) # 重新整理許可權 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) # 再次檢視使用者表 mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | test_user | | host | username | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 5 rows in set (0.00 sec) # 檢視test_user使用者許可權 mysql> show grants for `test_user`@`%`; +-----------------------------------------------------+ | Grants for test_user@% | +-----------------------------------------------------+ | GRANT USAGE ON *.* TO `test_user`@`%` | | GRANT ALL PRIVILEGES ON `test`.* TO `test_user`@`%` | +-----------------------------------------------------+ 2 rows in set (0.00 sec) # 退出 mysql> exit; Bye
三、導庫
# 把資料庫的sql檔案上傳到linux伺服器 [root@i-epo5ap9i ~]# rz -bash: rz: command not found # 安裝lrzsz [root@i-epo5ap9i ~]# yum install lrzsz -y ... Installed: lrzsz.x86_64 0:0.12.20-27.1.el6 Complete! # 從windows本機上傳到linux伺服器（回車後會彈出一個選擇檔案的彈窗，選中檔案點選add後再點OK） [root@i-epo5ap9i ~]# rz rz waiting to receive. Starting zmodem transfer. Press Ctrl+C to cancel. Transferring test.sql... 100% 953 bytes 953 bytes/sec 00:00:01 0 Errors # 換使用者登入mysql [root@i-epo5ap9i ~]# mysql -u test_user -p Enter password: ... Type `help;` or `h` for help. Type `c` to clear the current input statement. # 使用test資料庫 mysql> use test; Database changed # 給test資料庫設定編碼方式 mysql> set names utf8; Query OK, 0 rows affected (0.00 sec) # 匯入資料庫（執行部署專案對應的sql檔案） mysql> source /root/test.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) ... # 檢視該資料庫的所有表 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | role | | user | | ... | +----------------+ 2 rows in set (0.00 sec) # 匯入成功，退出mysql mysql> exit; Bye