[TOC] ## 安裝 [[官方文檔]]([https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/](https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/)) [[MySQL v5.7安裝]]([https://dev.mysql.com/doc/refman/5.7/en/installing.html](https://dev.mysql.com/doc/refman/5.7/en/installing.html)) 操作系統CentOS 7.x ### yum全新安裝 0. 查看操作系統內核版本 ~~~ shell> uname -a Linux tatahy-lamp 3.10.0-123.9.3.el7.x86_64 shell>1 SMP Thu Nov 6 15:06:03 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux ~~~ 內核版本/platform：3.10.0-123.9.3.el7.x86\_64 1. Download the release package for your platform. ~~~ shell> wget https://dev.mysql.com/downloads/repo/yum/mysql80-community-release-el7-2.noarch.rpm ~~~ 2. Install the downloaded release package ~~~ shell> rpm -Uvh mysql80-community-release-el7-2.noarch.rpm ~~~ 3.Selecting a Release Series * By command ~~~ shell> yum-config-manager --disable mysql80-community shell> yum-config-manager --enable mysql57-community ~~~ * By editing manually the `/etc/yum.repos.d/mysql-community.repo` file. Specify enabled=0 to disable a subrepository, or enabled=1 to enable a subrepository. You should only enable subrepository for one release series at any time. When subrepositories for more than one release series are enabled, the latest series will be used by Yum. ~~~ * Enable to use MySQL 5.7 [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/ enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql [mysql80-community] name=MySQL 8.0 Community Server baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/ enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql ~~~ 4. Verify that the correct subrepositories have been enabled and disabled by running the following command and checking its output. ~~~ shell> yum repolist enabled | grep mysql ~~~ 5. Installing MySQL ~~~ shell> yum install mysql-community-server *This installs the package for the MySQL server, as well as other required packages. ~~~ 6. Starting the MySQL Server ~~~ shell> service mysqld start ~~~ For EL7-based platforms, this is the preferred command: ~~~ shell> systemctl start mysqld.service ~~~ check the status of the MySQL server with the following command: ~~~ shell> sudo service mysqld status ~~~ For EL7-based platforms, this is the preferred command: ~~~ shell> sudo systemctl status mysqld.service ~~~ MySQL Server Initialization (as of MySQL 5.7): At the initial start up of the server, the following happens, given that the data directory of the server is empty: * The server is initialized. * An SSL certificate and key files are generated in the data directory. * The validate_password plugin is installed and enabled. * A superuser account 'root'@'localhost' is created. * A password for the superuser is set and stored in the error log file. To reveal it, use the following command: ~~~ shell> sudo grep 'temporary password' /var/log/mysqld.log ~~~ Change the root password as soon as possible by logging in with the generated, temporary password and set a custom password for the superuser account: ~~~ shell> mysql -uroot -p shell> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; ~~~ >[warning] Note: MySQL's `validate_password` plugin is installed by default. This will require that passwords contain at least one upper case letter, one lower case letter, one digit, and one special character, and that the total password length is at least 8 characters. ### 通過yum升級 Use the MySQL Yum repository to perform an in-place update (that is, replacing the old version and then running the new version using the old data files) for your MySQL installation by following these steps (they assume you have installed MySQL with the MySQL Yum repository or with an RPM package directly downloaded from [MySQL Developer Zone's MySQL Download page](https://dev.mysql.com/downloads/); if that is not the case, following the instructions in Replacing a Native Third-Party Distribution of MySQL instead): 1. Selecting a Target Series By default, the MySQL Yum repository updates MySQL to the latest version in the release series you have chosen during installation，which means, for example, a 5.7.x installation will not be updated to a 8.0.x release automatically. To update to another release series, you need to first disable the subrepository for the series that has been selected (by default, or by yourself) and enable the subrepository for your target series. As a general rule, to upgrade from one release series to another, go to the next series rather than skipping a series. For example, if you are currently running MySQL 5.6 and wish to upgrade to 8.0, upgrade to MySQL 5.7 first before upgrading to 8.0. 2. Upgrading MySQL ~~~ shell> sudo yum update mysql-server ~~~ Alternatively, you can update MySQL by telling Yum to update everything on your system, which might take considerably more time. ~~~ shell> sudo yum update ~~~ 3. Restarting MySQL The MySQL server always restarts after an update by Yum. Prior to MySQL 8.0.16, run `mysql_upgrade` after the server restarts to check and possibly resolve any incompatibilities between the old data and the upgraded software. As of MySQL 8.0.16, this step is not required, as the server performs all tasks previously handled by `mysql_upgrade`. You can also update only a specific component. Use the following command to list all the installed packages for the MySQL components. ~~~ shell> sudo yum list installed | grep "^mysql" ~~~ After identifying the package name of the component of your choice, update the package with the following command, replacing package-name with the name of the package. ~~~ shell> sudo yum update package-name ~~~ ### 替換第三方的MySQL 1. Backing Up Your Database 2. Adding the MySQL Yum Repository 3. Replacing the Native Third-Party Distribution by a Yum Update or a DNF Upgrade By design, the MySQL Yum repository will replace your native, third-party MySQL with the latest GA release (from the MySQL 8.0 series currently) from the MySQL Yum repository when you perform a yum update command (or dnf upgrade for dnf-enabled systems) on the system, or a yum update mysql-server (or dnf upgrade mysql-server for dnf-enabled systems). After updating MySQL using the Yum repository, applications compiled with older versions of the shared client libraries should continue to work. However, if you want to recompile applications and dynamically link them with the updated libraries, see `Upgrading the Shared Client Libraries` for some special considerations. >[warning]Notes: > * For EL7-based platforms: See `Compatibility Information for EL7-based platforms`. > * If you have a third-party distribution of MySQL that you have downloaded and installed from a nonnative repository (for example, from MariaDB or Percona), it is important to follow the `instructions for replacing them` given in the MySQL server's reference manual. ## 創建用戶與授權 1. 創建用戶 命令: ~~~ mysql> CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’; ~~~ 說明： username：你將創建的用戶名 host：指定該用戶在哪個主機上可以登陸，如果是本地用戶可用localhost，如果想讓該用戶可以從任意遠程主機登陸，可以使用通配符% password：該用戶的登陸密碼，密碼可以為空，如果為空則該用戶可以不需要密碼登陸服務器 例子： ~~~ mysql> CREATE USER ‘dog’@’localhost’ IDENTIFIED BY ‘123456’; mysql> CREATE USER ‘pig’@’192.168.1.101\_’ IDENDIFIED BY ‘123456’; mysql> CREATE USER ‘pig’@’%’ IDENTIFIED BY ‘123456’; mysql> CREATE USER ‘pig’@’%’ IDENTIFIED BY ”; mysql> CREATE USER ‘pig’@’%’; ~~~ 2. 授權: 命令: ~~~ mysql> GRANT privileges ON databasename.tablename TO ‘username’@’host’ ~~~ 說明: privileges：用戶的操作權限，如`SELECT，INSERT，UPDATE`等，如果要授予所的權限則使用`ALL ` databasename：數據庫名 tablename：表名，如果要授予該用戶對所有數據庫和表的相應操作權限則可用`*`表示，如`*.*` 例子: ~~~ mysql> GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; mysql> GRANT ALL ON *.* TO 'pig'@'%'; ~~~ 注意: 用以上命令授權的用戶不能給其它用戶授權，如果想讓該用戶可以授權，用以下命令: ~~~ mysql> GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; ~~~ 3. 設置與更改用戶密碼 命令: ~~~ mysql> SET PASSWORD FOR 'username'@'host' = PASSWORD(‘newpassword’); ~~~ 如果是當前登陸用戶用: ~~~ mysql> SET PASSWORD = PASSWORD(“newpassword”); ~~~ 例子: ~~~ mysql> SET PASSWORD FOR ‘pig’@’%’ = PASSWORD(“123456”); ~~~ **最新版MySQL請采用如下SQL：** ~~~ mysql> UPDATE user SET authentication_string=PASSWORD('newpassword') where USER='root'; ~~~ 刷新權限 ~~~ mysql> FLUSH PRIVILEGES; ~~~ 4. 撤銷用戶權限 命令: ~~~ mysql> REVOKE privilege ON databasename.tablename FROM ‘username’@’host’; ~~~ 說明: `privilege, databasename, tablename`：同授權部分 例子: ~~~ mysql> REVOKE SELECT ON * .* FROM 'pig'@'%'; ~~~ 注意: 假如你在給用戶’pig’@’%’授權的時候是這樣的（或類似的）：GRANT SELECT ON test.user TO ‘pig’@’%’，則在使用REVOKE SELECT ON*.*FROM ‘pig’@’%’;命令并不能撤銷該用戶對test數據庫中user表的SELECT 操作。相反，如果授權使用的是GRANT SELECT ON*.*TO ‘pig’@’%’;則REVOKE SELECT ON test.user FROM ‘pig’@’%’;命令也不能撤銷該用戶對test數據庫中user表的Select權限。 具體信息可以用命令SHOW GRANTS FOR ‘pig’@’%’; 查看。 5. 刪除用戶 命令: ~~~ mysql> DROP USER ‘username’@’host’; ~~~ 6.創建數據庫 ~~~ mysql> CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8\_general\_ci; ~~~ ## 登錄 ~~~ # mysql -u root -p ~~~ `-u`：以用戶名“root”登錄MySQL `-p`：登錄用戶要輸入登錄密碼 ## 數據遷移 ### MySQL數據表導入*.xlsx文件 1. Office Excel另存：`*.xlsx`文件另存為`*.csv`文件 2. NP++處理：編輯`*.csv`文件，‘編碼’->'轉為UTF-8'編碼。因為Office用的是ANSI編碼，而網絡編程應用中MySQL一般采用UTF-8編碼。 3. phpMyAdmin導入：選擇要導入數據的數據表，清空數據表，點擊‘導入’進入數據導入頁面，注意各個分隔符內容的選擇，要與選擇導入的`*.csv`文件中使用的一致。 ### MySQL數據表導出*.csv文件給office Excel使用 phpMyAdmin導出：選擇要導處數據的數據表，點擊‘導出’進入數據導出頁面，各個導出項選擇 * `格式`：“CSV for MS Excel” * `首行保存字段名`：選中 * `Excel版本`：Excel 2008 * `文件的字符集`：gb2312 ## 事務（transaction） ## 索引（index） 唯一索引 普通索引 ## 鎖（lock） 庫級鎖 表級鎖 行級鎖 ## 存儲引擎 ### InnoDB ## Tips ### MySQL主機127.0.0.1與localhost區別總結 [[來源]](https://cloud.tencent.com/developer/article/1160455) 1. `mysql] -h 127.0.0.1 `的時候，使用**TCP/IP**連接， `mysql server`認為該連接來自于127.0.0.1或者是"localhost.localdomain" 2. `mysql -h localhost `的時候，是**不使用TCP/IP**連接的，而使用**Unix socket**；此時，`mysql server`則認為該client是來自"localhost" 3. `mysql`權限管理中的"localhost"有特定含義： **當主機填寫為localhost時mysql會采用 unix domain socket連接** **當主機填寫為127.0.0.1時mysql會采用tcp方式連接** **這是linux套接字網絡的特性，win平臺不會有這個問題** 解決方法 在my.cnf的\[mysql\]區段里添加 ~~~bash protocol=tcp ~~~ 保存重啟MySQL，問題解決！