MySQL · Web软件技术栈随笔

[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，问题解决！