mysql基础管理

1.用户管理

1.1作用

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n4" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">登录
管理对象</pre>

1.2定义

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n6" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">1.2.1
用户@'白名单'
用户名： 不要太长，和业务有关
1.2.2
白名单：允许登录
黑名单：
白名单支持的方式
wordpress@'10.0.0.%'    
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%' --50-59
wordpress@'10.0.0.0/255.255.254.0</pre>

1.3管理操作：8.0+

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n8" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">增：
mysql> create user oldboy@'10.0.0.%' identified by '123';
查：
mysql> desc mysql.user;    ---->  authentication_string
mysql> select user,host from mysql.user;
mysql> select user,host,authentication_string from mysql.user；
改:
mysql> alter user oldboy@'10.0.0.%' identified by '456';
删：
mysql> drop user oldboy@'10.0.0.%';
​
说明：
 8.0+ 版本：必须先创建用户，再授权
 8.0以前: 可以grant 授权时自动创建用户.</pre>

1.4权限管理

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n10" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">show privileges; 查看权限列表
grant 权限 on 权限作用范围 to 用户 identified  by '123';
grant all on wordpress.* to wordpress@'10.0.0.%' identified  by '123';        </pre>

1.4.2 权限作用范围 :

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n12" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">* .*       ====>  一般是管理员会设置的方法
oldguo.*  ====>  一般是业务用户会设置的方法
oldguo.t1 ====>  一般是业务用户户设置的方法    </pre>

1.5企业授权案例

（1）授权一个管理员用户oldyang,可以从10网段任意地址登录管理数据库

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n15" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">create user oldyang@'10.0.0.%' identified by '12'
grant all on *.* to oldyang@'10.0.0.%' identified by '12' with grant option;</pre>

（2）授权一个业务

grant select,update,insert,delete ON app.* TO app@'10.0.0.%' IDENTIFIED BY '123' ;

（3）授权一个开发用户dev,可以对dev库进行业务开发

**1.6 root管理员密码忘记或被篡改如何处理？**

--skip-grant-tables

--skip-networking --关闭tcp

(1) 关闭数据库,启动到"单用户"模式

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n23" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 data_3306]# systemctl stop mysqld
[root@db01 data_3306]# mysqld_safe  --skip-grant-tables  --skip-networking  & --回车两下</pre>

(2) 无密码登录

<pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="" cid="n25" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 data_3306]# mysql
错误：
mysql> alter user root@'localhost' identified by '123456';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
正确：
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '123456';</pre>

(3) 重启数据库到正常模式

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n27" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 data_3306]# systemctl restart mysqld
</pre>

1.7查询权限

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n29" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">show grants for oldyang@'10.0.0.%';
</pre>

1.8 回收权限

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n31" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysql> revoke delete,drop on app.* from 'app'@'10.0.0.%';
</pre>

2.mysql的连接管理

3.1自带的客户端工具连接

mysql :

| -u | 用户名 |
| --- | --- |
| -p | 密码 |
| -h | ip |
| -P | 端口 |
| -S | socket位置 |
| -e | 免交互执行命令 |
| < | 导入SQL脚本 |

mysqladmin

mysqldump

例子: (1) TCP连接串远程登录

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n60" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">注:需要提前创建好远程用户
mysql> grant all on *.* to oldguo@'10.0.0.%' identified by '123';
[root@db01 data_3306]# mysql -uroot -p -h 10.0.0.51 -P 3306
</pre>

(2) Socket连接方式

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n62" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">注:需要提前创建好localhost用户
mysql> grant all on *.* to oldguo@'localhost' identified by '123';
[root@db01 data_3306]# mysql -uoldguo -p -S /tmp/mysql.sock 
Enter password: 

如何验证一个用户是通过本地还是远程和登录的.
show processlist;
</pre>

(3) 免交互执行命令

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n64" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 ~]# mysql -uroot -p -e "show processlist"
</pre>

(4) 导入SQL脚本

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n66" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 ~]# mysql -uroot -p < t100w.sql
Enter password: 
mysql> source /root/world.sql
</pre>

3.1.2 mysqladmin  (1) 修改密码

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n68" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 ~]# mysqladmin -uroot -p123456 password 123
[root@db01 ~]# mysql -uroot -p123
</pre>

(2) 关闭数据库

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n70" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 ~]# mysqladmin -uroot -p123  shutdown 
</pre>

mysqldump(备份恢复)  略.

3.2第三方开发设计工具

sqlyog

navicat

workbench

3.3应用程序连接

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n77" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">php-mysql
pip3 install mysql
jar
go
......
</pre>

4.mysql的关闭启动

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n79" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">systemctl  --->mysql.server start
----> mysqld_safe  ---->mysqld &
</pre>

5.MySQL的初始化配置

5.1 初始化配置方法  源码安装定制 < 初始化配置文件 < 命令行启动时定制

5.2 初始化配置文件

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n83" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 data_3306]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
</pre>

建议一个mysql实例一个配置文件

5.3 配置文件书写格式

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n86" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">[root@db01 data_3306]# cat /etc/my.cnf 
[mysqld]
user=mysql
port=3306
basedir=/usr/local/mysql57
datadir=/data/mysql/data_3306
server_id=6
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

标签项  ====> [mysqld]
服务器端 [server]: [mysqld],[mysqld_safe]  ====> 影响到MySQL启动
客户端 [clinet] : [mysql] ,[mysqldump]    ====> 影响本地客户端程序

配置项  ====> key=value
</pre>

5.4 自定制初识化配置文件位置

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n88" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">mysqld --defaults-file=/opt/a.cnf &
</pre>

6.初始化配置文件

6.1 编译

6.2 配置文件

6.3 命令行

6.3.1 --defaults-file

--skip-grant-tables

--skip-networking

7.多实例的规划和实施

分布式架构中应用广泛

6.1 端口

1.  mkdir -p /data/mysql/data_{3307,3308,3309}

2.  配置文件准备 cat > /data/mysql/my3307.cnf <<EOF  [mysqld]  user=mysql  port=3307  basedir=/usr/local/mysql57  datadir=/data/mysql/data_3307  server_id=7  socket=/tmp/mysql3307.sock  EOF  cat > /data/mysql/my3308.cnf <<EOF  [mysqld]  user=mysql  port=3308  basedir=/usr/local/mysql57  datadir=/data/mysql/data_3308  server_id=8  socket=/tmp/mysql3308.sock  EOF  cat > /data/mysql/my3309.cnf <<EOF  [mysqld]  user=mysql  port=3309  basedir=/usr/local/mysql57  datadir=/data/mysql/data_3309  server_id=9  socket=/tmp/mysql3309.sock  EOF

3.  授权 [root@db01 ~]# chown -R mysql.mysql /data/

4.  初始化数据 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3307  mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3308  mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3309

5.  启动多实例 [root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3307.cnf &  [root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3308.cnf &  [root@db01 mysql]# mysqld --defaults-file=/data/mysql/my3309.cnf &  [root@db01 mysql]# netstat -tulnp

6.  使用 systemd 管理多实例  cat >/etc/systemd/system/mysqld3307.service <<EOF  [Unit]  Description=MySQL Server  Documentation=man:mysqld(8)  Documentation=[图片上传失败...(image-996d1e-1577973894573)][http://dev.mysql.com/doc/refman/en/using-systemd.html](http://dev.mysql.com/doc/refman/en/using-systemd.html)  After=network.target  After=syslog.target  [Install]  WantedBy=multi-user.target  [Service]  User=mysql  Group=mysql  ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3307.cnf  LimitNOFILE = 5000  EOF

cat >/etc/systemd/system/mysqld3308.service <<EOF  [Unit]  Description=MySQL Server  Documentation=man:mysqld(8)  Documentation=[图片上传失败...(image-876697-1577973894573)][http://dev.mysql.com/doc/refman/en/using-systemd.html](http://dev.mysql.com/doc/refman/en/using-systemd.html)  After=network.target  After=syslog.target  [Install]  WantedBy=multi-user.target  [Service]  User=mysql  Group=mysql  ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3308.cnf  LimitNOFILE = 5000  EOF

cat >/etc/systemd/system/mysqld3309.service <<EOF  [Unit]  Description=MySQL Server  Documentation=man:mysqld(8)  Documentation=[图片上传失败...(image-d27b37-1577973894573)][http://dev.mysql.com/doc/refman/en/using-systemd.html](http://dev.mysql.com/doc/refman/en/using-systemd.html)  After=network.target  After=syslog.target  [Install]  WantedBy=multi-user.target  [Service]  User=mysql  Group=mysql  ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/mysql/my3309.cnf  LimitNOFILE = 5000  EOF

7.重启

pkill mysqld  systemctl start mysqld3307  systemctl start mysqld3308  systemctl start mysqld3309

第三章 SQL基础

1.SQL介绍

1.1简介

结构化查询语言

1.2SQL标准

1.3SQL_MODE

除数为零  日期  mysql> select @@sql_mode;

1.4 SQL类型

DDL : 数据定义语言 : 库名,库属性,表名,表属性,列(列名,列属性)  DCL : 数据控制语言 : 权限  DML : 数据操作语言 : 数据行  DQL : 数据查询语言 : 数据行

1.5 SQL功能

管理,操作数据库对象: 库: 库名,库属性  表: 表名,表属性,列(列名,列属性),数据行

2.MySQL规范性存储限制

2.1 字符集Charset

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n129" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">show charset;

utf8     :  最大字节长度3个.
utf8mb4  :  最大字节长度4个. 可以存储emoji表情字符.
mysql> show charset;
</pre>

2.2 排序规则 (校对规则)

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n131" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">show  collation;
默认是大小写不敏感.
utf8mb4_general_ci 
utf8mb4_bin
</pre>

2.3 数据类型

2.3.1

**数值类型**

![image](https://upload-images.jianshu.io/upload_images/16956686-6cb8cf2c0c681252.png?imageMogr2/auto-orient/strip%7CimageView2/2/format/webp) 

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n136" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">tinyint   1字节长度数字  ===> 11111111  ===> 0-2^8-1 ===> -2^7-2^7-1 (3位)
int       4字节长度      ====> 0-2^32-1 ====> -2^31 - 2^31-1  (10位数)
bigint    8字节长度      ====> 0-2^64-1 ====> -2^63 - 2^63-1  (20位数)
</pre>

**字符类型**

![image](https://upload-images.jianshu.io/upload_images/16956686-599be7ba0c7040a9.png?imageMogr2/auto-orient/strip%7CimageView2/2/format/webp) 

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n139" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">char(11) ：
定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。
varchar(11):
变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。
enum('bj','tj','sh')：
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。
</pre>

**时间类型**

![image](https://upload-images.jianshu.io/upload_images/16956686-7abec7820bfb10cd.png?imageMogr2/auto-orient/strip%7CimageView2/2/format/webp) 

<pre spellcheck="false" class="md-fences mock-cm md-end-block" lang="" cid="n142" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Consolas, Menlo, Monaco, monospace, serif; font-size: 0.9rem; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(254, 254, 254); display: block; break-inside: avoid; text-align: left; white-space: pre-wrap; position: relative !important; margin-left: 1em; padding-left: 1em; border: 1px solid rgb(221, 221, 221); padding-bottom: 8px; padding-top: 6px; margin-bottom: 1.5em; width: inherit; color: rgb(51, 51, 51); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">DATETIME 
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP 
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响
</pre>

**二进制类型**