一、简介
SQL语言
DDL:表、视图、索引、触发器操作等。CREATE/ALTER/DROP语句
DML:数据操作。SELECT/INSERT/UPDATE/DELETE
DCL:权限设置。GRANT/REVOKE
数据库访问
不同的语言使用不同的数据库访问技术
C#使用ADO.NET,JAVA使用JDBC等
版本
Community Edition:免费、自由下载,无技术支持
Enterprise:收费、不能下载,有技术支持
Alpha:开发阶段
Beta:开发完成,未测试
Gamma:已发行一段时间的测试版
Generally Available(GA):稳定版
工具
MySQL Community Server:客户端和服务器整合起来的核心包
MySQL Cluster:提供Mysql集群功能的程序包
MySQL Fabric:为高可用性和分片管理提供了一个框架
MySQL Utilities:提供维护和管理的实用工具
MySQL Workbench:可视化编辑工具
MySQL Proxy:MySQL中间件,代理接收发往MySQL数据库的请求,将需要求路由至不同的后端主机上去
MySQL Connectors:MySQL的连接器,程序连接MySQL的驱动
MySQL Yum Repository:下载MySQL的YUM源
MySQL APT Repository:APT源
RPM包
MySQL-client:客户端连接工具,GUI工具有navicat、phpmyadmin等
MySQL-server:服务器包
MySQL-devel:库和包含文件
MySQL-shared:某些语言和应用程序需要动态装载的共享库
MySQL-test:测试组件
MySQL-embedded:嵌入式
MySQL-bundle:整合包
相关文件
/etc/my.cnf:配置文件
/usr/share/doc/MySQL-server-5.6.26/my-default.cnf:参考配置文件
/usr/share/mysql/my-default.cnf:同上
/usr/bin:客户端程序和脚本
/usr/sbin mysqld:服务器
/var/lib/mysql:日志文件,数据库
/usr/lib/mysql:数据库
/usr/share/doc/packages:文档
/usr/include/mysql:包含(头)文件
/usr/share/mysql:错误消息和字符集文件
/usr/share/sql-bench:基准程序
二、安装
环境:CentOS 6.7 x86、MySQL 5.6
报错,删除mysql-libs包
[root@CentOS MySQL]# rpm -qa | grep -i mysql
mysql-libs-5.1.-.el6_6.i686
[root@CentOS MySQL]# yum remove mysql-libs-5.1.-.el6_6.i686
RPM
[root@CentOS MySQL]# rpm -ivh MySQL-server-5.6.-.el6.i686.rpm
[root@CentOS MySQL]# rpm -ivh MySQL-client-5.6.-.el6.i686.rpm
YUM
[root@CentOS ~]# yum install mysql-server mysql-client
防火墙
[root@CentOS ~]# iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport -j ACCEPT
[root@CentOS ~]# service iptables save
[root@CentOS ~]# service iptables restart
配置
5.1版本root无密码;5.6安装完成后,root生成随机密码在/root/.mysql_secret
5.1有配置文件;5.6无配置文件,需从/usr/share/mysql/my-default.cnf复制到/etc/my.cnf
5.1服务名mysqld,5.6为mysql
开启服务
[root@CentOS ~]# service mysql start
查看登录数据库账号root的密码
[root@CentOS ~]# cat .mysql_secret
# The random password set for the root user at Tue Sep :: (local time): 4VZTzey0LML2N7e1
初始化设置
[root@CentOS ~]# /usr/bin/mysql_secure_installation --user=mysql #输入root的密码
Enter current password for root (enter for none): #是否修改root的密码
Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success! #删除匿名用户
Remove anonymous users? [Y/n] y
... Success! #禁止root远程登录
Disallow root login remotely? [Y/n] y
... Success! #删除测试数据库
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success! #重新加载权限表
Reload privilege tables now? [Y/n] y
... Success!
三、连接数据库
参数:
-h:主机名或IP
-P:端口号,默认3306
-u:用户名
-p:密码
-e:指定SQL语句
[root@CentOS ~]# mysql -u root -p
Enter password:
[root@CentOS ~]# mysql -u root -p db01#连接db01库
[root@CentOS ~]# mysql -h 192.168.41.135 -u root -p#远程连接
四、创建远程用户
user1用户拥有所有权限,%表示任意主机可登录
mysql> create user 'user1'@'%' identified by '';
mysql> grant all privileges on *.* to 'user1'@'%';
五、迁移data目录
data目录应独立分区
关闭服务
[root@CentOS ~]# service mysql stop
移动目录
[root@CentOS ~]# mv /var/lib/mysql/ /data/
配置文件
[root@CentOS ~]# cp -a /usr/share/mysql/my-default.cnf /etc/my.cnf
[root@CentOS ~]# vim /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock [mysql]
socket=/data/mysql/mysql.sock #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
启动服务
[root@CentOS ~]# service mysql start
SELinux报错解决方法(mv命令保留权限,所以没报错)
[root@CentOS ~]# chcon -R -t mysqld_db_t /data//mysql
<!--
@font-face{
font-family:"Times New Roman";
}
@font-face{
font-family:"宋体";
}
@font-face{
font-family:"Wingdings";
}
@list l0:level1{
mso-level-number-format:decimal;
mso-level-suffix:tab;
mso-level-text:"%1.";
mso-level-tab-stop:39.0000pt;
mso-level-number-position:left;
margin-left:39.0000pt; text-indent:-18.0000pt; font-family:'Times New Roman'; }
@list l1:level1{
mso-level-number-format:bullet;
mso-level-suffix:tab;
mso-level-text:\F06C;
mso-level-tab-stop:81.0000pt;
mso-level-number-position:left;
margin-left:81.0000pt; text-indent:-18.0000pt; font-family:Wingdings; }
@list l2:level1{
mso-level-number-format:decimal;
mso-level-suffix:tab;
mso-level-text:"%1.";
mso-level-tab-stop:18.0000pt;
mso-level-number-position:left;
margin-left:18.0000pt; text-indent:-18.0000pt; font-family:'Times New Roman'; }
@list l3:level1{
mso-level-number-format:bullet;
mso-level-suffix:tab;
mso-level-text:\F06C;
mso-level-tab-stop:18.0000pt;
mso-level-number-position:left;
margin-left:18.0000pt; text-indent:-18.0000pt; font-family:Wingdings; }
@list l4:level1{
mso-level-number-format:bullet;
mso-level-suffix:tab;
mso-level-text:\F06C;
mso-level-tab-stop:60.0000pt;
mso-level-number-position:left;
margin-left:60.0000pt; text-indent:-18.0000pt; font-family:Wingdings; }
@list l5:level1{
mso-level-number-format:decimal;
mso-level-suffix:tab;
mso-level-text:"%1.";
mso-level-tab-stop:60.0000pt;
mso-level-number-position:left;
margin-left:60.0000pt; text-indent:-18.0000pt; font-family:'Times New Roman'; }
@list l6:level1{
mso-level-number-format:bullet;
mso-level-suffix:tab;
mso-level-text:\F06C;
mso-level-tab-stop:39.0000pt;
mso-level-number-position:left;
margin-left:39.0000pt; text-indent:-18.0000pt; font-family:Wingdings; }
@list l7:level1{
mso-level-number-format:bullet;
mso-level-suffix:tab;
mso-level-text:\F06C;
mso-level-tab-stop:102.0000pt;
mso-level-number-position:left;
margin-left:102.0000pt; text-indent:-18.0000pt; font-family:Wingdings; }
@list l8:level1{
mso-level-number-format:decimal;
mso-level-suffix:tab;
mso-level-text:"%1.";
mso-level-tab-stop:81.0000pt;
mso-level-number-position:left;
margin-left:81.0000pt; text-indent:-18.0000pt; font-family:'Times New Roman'; }
@list l9:level1{
mso-level-number-format:decimal;
mso-level-suffix:tab;
mso-level-text:"%1.";
mso-level-tab-stop:102.0000pt;
mso-level-number-position:left;
margin-left:102.0000pt; text-indent:-18.0000pt; font-family:'Times New Roman'; }
p.MsoNormal{
mso-style-name:正文;
mso-style-parent:"";
margin:0pt;
margin-bottom:.0001pt;
mso-pagination:none;
text-align:justify;
text-justify:inter-ideograph;
font-family:'Times New Roman';
mso-fareast-font-family:宋体;
font-size:10.5000pt;
mso-font-kerning:1.0000pt;
}
span.10{
font-family:'Times New Roman';
}
span.15{
font-family:'Times New Roman';
color:rgb(0,0,255);
text-decoration:underline;
text-underline:single;
}
span.16{
font-family:'Times New Roman';
font-weight:bold;
}
p.p{
mso-style-name:"普通\(网站\)";
mso-style-noshow:yes;
margin-top:5.0000pt;
margin-right:0.0000pt;
margin-bottom:5.0000pt;
margin-left:0.0000pt;
mso-margin-top-alt:auto;
mso-margin-bottom-alt:auto;
mso-pagination:none;
text-align:left;
font-family:'Times New Roman';
mso-fareast-font-family:宋体;
font-size:12.0000pt;
}
p.pre{
mso-style-name:"HTML 预设格式";
mso-style-noshow:yes;
margin:0pt;
margin-bottom:.0001pt;
mso-pagination:none;
text-align:left;
font-family:宋体;
font-size:12.0000pt;
}
span.msoIns{
mso-style-type:export-only;
mso-style-name:"";
text-decoration:underline;
text-underline:single;
color:blue;
}
span.msoDel{
mso-style-type:export-only;
mso-style-name:"";
text-decoration:line-through;
color:red;
}
table.MsoNormalTable{
mso-style-name:普通表格;
mso-style-parent:"";
mso-style-noshow:yes;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-padding-alt:0.0000pt 5.4000pt 0.0000pt 5.4000pt;
mso-para-margin:0pt;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-family:'Times New Roman';
font-size:10.0000pt;
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;
}
@page{mso-page-border-surround-header:no;
mso-page-border-surround-footer:no;}@page Section0{
margin-top:72.0000pt;
margin-bottom:72.0000pt;
margin-left:90.0000pt;
margin-right:90.0000pt;
size:623.6000pt 1573.2500pt;
layout-grid:15.6000pt;
}
div.Section0{page:Section0;}
-->
本文出自 “运维菜鸟.log” 博客,谢绝转载!