MySQL、MariaDB数据库
     分类:数据库     有: 0 条评论

MySQL、MariaDB数据库

     分类:数据库     有: 0 条评论

安装、配置

mysql在5.7以后的版本初始密码出现了一些变化,yum安装的默认是生成随机密码,源码编译安装在未使用参数指定的情况下是默认为空密码。但这点还不敢保证,只是我个人得出的结论。在安装之前先了解一下初始化密码的情况。

Mysql初始化成空密码或随机密码
初始化为随机密码:mysqld --initialize --user=mysql
初始化为空密码:mysqld --initialize-insecure --user=mysql
默认帐号root,后面的-user=mysql不更改
官方文档:http://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html

随机密码或重置密码后修改密码时有2种情况,
1,用mysqladmin(不登录mysql命令模式),这样是可以直接修改的。
2,用随机密码或空密码登录数据库,用语句修改密码,会提示用ALTER USER语句来修改密码。

# 密码要足够复杂,简单密码无法通过检测。
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

Mysql yum安装

参考:http://blog.csdn.net/jameshadoop/article/details/72821970

# mysql 5.7
rpm -ivh http://repo.mysql.com/mysql57-community-release-el7-9.noarch.rpm
或(mysql5.6):rpm -ivh http://repo.mysql.com/mysql-community-release-el7-7.noarch.rpm
yum install mysql-server
systemctl start mysqld

MariaDB 安装方法也是一样的。
官方说明:https://mariadb.com/kb/en/mariadb/yum

cat > /etc/yum.repos.d/MariaDB.repo <<EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

yum update               #会提示确认安装MariaDB10.2的源及源指纹
yum install MariaDB-server MariaDB-client   #安装的是MariaDB10.2.7

# 启动服务并修改密码(默认空密码)
systemctl start mariadb

安装完成后是随机密码,grep "password" /var/log/mysqld.log
mark
然后修改密码mysqladmin -u root -p password回车后输入随机密码(空密码直接回车),然后输入两次新密码,修改完成。

注意:
在Ubuntu、Deepin、Debian中会有一个问题,mysql5.7即使设置了密码依然可以无密码登录;用第三方客户端无法连接。

# 解决方法
登录mysql    mysql -u root -p 或 mysql

use mysql;
update user set authentication_string=PASSWORD("密码") where user='root';
update user set plugin="mysql_native_password";
flush privileges;
quit;
 
systemctl restart mysql

源码安装

编译安装

下载源码包:https://dev.mysql.com/downloads/mysql

安装编译工具:

yum install ncurses-devel bison openssl-devel bison-devel libaio libaio-devel gcc gcc-c++ cmake -y

安装脚本
这里以mysql-5.5.47.tar.gz为例,指定安装目录是/usr/local/mysql,指定data目录是/usr/local/mysql/data。

groupadd mysql
useradd -r -g mysql mysql
cd /root
wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.47.tar.gz
tar xvf mysql-5.5.47.tar.gz
cd mysql-5.5.47/
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_USER=mysql

make -j2      #-j2是指定用2个CPU核进行make
make install

配置

chmod +w /usr/local/mysql
chown -R mysql:mysql /usr/local/mysql
ln -s /usr/local/mysql/lib/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16
chown -R mysql:mysql /usr/local/mysql/

cd /usr/local/mysql/support-files/
cp my-large.cnf /etc/my.cnf             #一般只要运行这台命令
cp mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/rc.d/init.d/mysqld

vim /etc/rc.d/init.d/mysqld
#确保有以下两行
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

<font color="#EE3B3B" >开机启动</font>

# Centos 6
chkconfig --add mysqld
chkconfig --level 345 mysqld on

# Centos 7
systemctl enable mysqld

<font color="#EE3B3B" >第一次运行设置密码</font>
启动数据库后用mysql -u root -p然后直接回车,密码不用输直接回车。然后用以下语句修改密码(<font color="#EE3B3B" >只有root用户登录才能看到mysql用户表</font>),如果无法连接数据库,先停止数据库,再按下面的方法初始化数据库后重新启动数据库。
或者尝试:mysql -uroot -S /usr/local/mysql/mysql.sock -p进行登录。
如果用mysql.sock登录报错:ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/usr/local/mysql/mysql.sock‘ (2)。先看看有没有mysql.sock文件,有的话可能是权限问题,更改一下权限为当前用户(root用户一般不会出现这种情况):chown -R leo:leo /usr/local/mysql(mysql安装目录)然后重启一下数据库。

show databases;
use mysql;
grant  all on *.* to root@'%' identified by 'password';
grant  all on *.* to mysql@'%' identified by 'password';
flush privileges;

修改好后,在第三方客户端登陆,本机命令行登录依然不用密码,要断开连接后重连才会生效。


<font color="#EE3B3B" >初始化数据库(用于sock文件丢失无法登陆时)(貌似数据不会丢)</font>

/usr/local/mysql/scripts/mysql_install_db \
--defaults-file=/etc/my.cnf \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--user=mysql

<font color="#EE3B3B" >初始化的MySQL5.7.6+与MySQL5.6.xx不同之处</font>
初始化工具不同
MySQL5.6.xx使用的是mysql_install_db,MySQL5.7.6+官方推荐使用mysqld –initialize,见本文开头内容。

初始化数据库不同
MySQL5.6.xx初始化之后存在mysql,information_schema,performance_schema,test四个数据库,MySQL5.7.6+初始化之后存在mysql,information_schema,performance_schema,sys四个数据库。

初始化用户不同
MySQL5.6.xx初始化之后存在root@localhost,root@'::1',root@'hostname',''@'localhost',''@'hostname'五个用户,MySQL5.7.6+初始化之后存在mysql.sys,root@localhost用户

初始化root密码
MySQL5.6.xx初始化之后root用户密码为空,MySQL5.7.6+初始化之后会为root@localhost用户生成随机密码。

参考:http://www.jb51.net/article/99965.htm
http://www.cnblogs.com/qq78292959/archive/2012/11/21/2780917.html
http://blog.csdn.net/daydreamingboy/article/details/8125700


<font color="#EE3B3B" >关于my.cnf文件位置</font>
默认情况下,MySQL会依次按顺序查找如下几个路径来获取MySQL配置问文件:
/etc/my.cnf
/etc/mysql/my.cnf
/etc/my.cnf/my.cnf
/usr/local/mysql/my.cnf
~/.my.cnf


<font color="#EE3B3B" >关于安全模式</font>
/usr/local/mysql/bin/mysqld_safe --user=mysql &
慎用安全模式,会清除sock文件,必须重新初始化数据库才能使用;先看密码管理一节内容。


密码管理

新安装默认密码为空mysql -u root 即可进入,不要mysql -u root -p 进不去的
大于5.7的版本默认有随机密码,执行grep "password" /var/log/mysqld.log查看随机生成的密码。或者:cat /var/log/mysqld.log | grep password | grep 'root@localhost' | awk -F 'root@localhost: ' '{print $2}'也行。


设置密码(空密码情况下)

mysql>flush privileges;

mysql>flush privileges;

mysql>update user set password='你的密码' where user='root';
mysql>flush privileges;


设置某个数据库的独立密码

mysql -u root -p
> (输入密码)
> create user 'user1'@'localhost' identified by '1234';
> grant all privileges on *.* to 'root'@'localhost';
> grant all privileges on 库名.* to 'user1'@'192.168.10.%' identified by '1234';
> flush privileges;
> exit;

该命令也可以用来修改数据库权限等等灵活应用,库名.表名改为对应的,如库名.*;localhost可以为一个网段192.168.10.%;
删除用户的权限:drop user root@'%';


修改Mysql密码(知道旧密码)

Linux

方法1: 用SET PASSWORD命令
首先登录MySQL。
格式:mysql> set password for 用户名@localhost = password('新密码');
例子:mysql> set password for root@localhost = password('123');

方法2:用mysqladmin
格式:mysqladmin -u用户名 -p旧密码 password 新密码
例子:mysqladmin -uroot -p123456 password 123

方法3:用UPDATE直接编辑user表
首先登录MySQL。

mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='localhost'; 
mysql> flush privileges; 

Windows

使用mysqladmin
cd到mysql的bin文件夹,然后执行如下
D:Mysqlbin>mysqladmin -u root -p password 新密码
Enter password:(在此输入原密码)
格式:mysqladmin -u用户名 -p旧密码 password 新密码。


忘记密码恢复

法1:带参数的安全模式
使用--skip-grant-tables参数启动MySQL Server可以直接在命令后面接参数,也可以在MySQL配置文件[mysqld]栏中加入一句skip-grant-tables(这种方式改好密码后记得删除)

Linux:

另开一个窗口

之后关闭第一个安全模式的窗口,重启MySQL Server(一般在关闭安全模式窗口时就会自动启动MySQL的)。

Windows:

  1. 关闭正在运行的MySQL服务。 net stop MySQL
  2. 打开DOS窗口,转到mysqlbin目录。
  3. 输入mysqld --skip-grant-tablesmysqld-nt --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
  4. 再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了),转到mysqlbin目录。
  5. 输入mysql回车,如果成功,将出现MySQL提示符 >。
  6. 连接权限数据库: use mysql;改密码:update user set password=password("123") where user="root";
  7. 刷新权限(必须步骤):flush privileges;
  8. 退出 quit
  9. 注销系统,再进入,使用用户名root和刚才设置的新密码123登录。

法2:其他方法
使用phpmyadmin,直接修改Mysql库的user 表。或者使用Navicat for Mysql 直接修改连接属性。
【据说可以用直接修改user表文件的方法:
法一:关闭MySQL,Windows下打开Mysqldatamysql,有三个文件user.frm,user.MYD,user.MYI找个知道密码的MySQL,替换相应的这三个文件,如果user表结构没改过,一般也没人去改,替换user.MYD就可以了。
法二:也可以直接编辑user.MYD,找个十六进制编辑器,UltraEdit就有这个功能。关闭MySQL,打开user.MYD。将用户名root后面的八个字符改为565491d704013245,新密码就是123456。或者将它们对应的十六进制数字,(左边那里,一个字符对应两个数字),改为 00 02 02 02 02 02 02 02,这就是空密码,在编辑器右边看到的都是星号*,看起来很象小数点。重开MySQL,输入root和你的新密码。】


常用操作:http://139.199.212.60/admin11/4d329f37-1934-4047-9d81-4c49dd213797.html
http://139.199.212.60/admin11/5e0089a9-f946-42b0-b1f7-6abc181cef47.html


创建数据库

mysql> CREATE DATABASE 库名;
mysql> USE 库名;
mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

创建只读用户

use mysql;
GRANT Select ON *.* TO 'zabbix_read'@'192.168.10.%'  IDENTIFIED BY "123456";
flush privileges;

创建表

use demo;
create table pet(
        name varchar(20),        #名字
        owner varchar(20),       #主人
        species varchar(20),     #种类
        sex char(1),             #性别
        birth date,              #出生日期
        death date               #死亡日期
)

为了验证你的表是按你期望的方式创建,使用一个DESCRIBE语句:


创建表(复杂形式):

创建customer表:

create table customers(
    id int not null auto_increment,
    name char(20) not null,
    address char(50) null,
    city char(50) null,
    age int not null,
    love char(50) not null default 'No habbit',
    primary key(id)
)engine=InnoDB;

SELECT last_insert_id();这个函数可以获得返回最后一个auto_increment值。默认值为:default 'No habbit',
引擎类型,多为engine = InnoDB,如果省略了engine=语句,则使用默认的引擎(MyISAM)


在表中增加字段

基本的语法为:

alter table <表名> add <字段名> <字段类型int,text...>;
# 例
alter tables rediscache add dataKey varchar(500);
# rediscache是表名,添加字段名dataKey,类型为:varchar,长度:500

另一种稍微复杂一点:(在ims_users表的remark字段之后增加ucuserid字段,设置为int类型,该字段不为空)
alter table ims_users add column ucuserid int not null default 0 after remark;



删除数据库

mysql> DROP DATABASE 库名;

删除表

mysql> DROP TABLE 表名;

将表中记录清空

mysql> DELETE FROM 表名;

删除字段

基本的语法为:

alter table <表名> drop <字段名>;
# 例
alter tables rediscache drop dataKey;
# rediscache是表名,字段名dataKey

删除用户、用户权限

use mysql;
删除用户:Delete FROM user Where User='user1' and Host='localhost';
删除用户权限:drop user root@'%';
flush privileges;

清空表

Mysql清空表是很重要的操作,也是最常见的操作之一,下面就为您详细介绍Mysql清空表的实现方法,希望能够对您有所帮助。
方法1:重建库和表
mysqldump --no-data把建表SQL导出来,然后drop databasecreate database,执行一下导出的SQL文件,把表建上;
mysqldump -u root -p --no-data dbname > schema.sql

方法2:生成清空所有表的SQL
mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,

如果只想清空某个表,执行以下语句(-f是强制执行):

mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='eab12'" | mysql -f eab12

即清空eab12中所有的表。如果有外键的话,很可能会报错。多执行几次,直到不报错。


删除数据库中的数据

DELETE FROM 语句用于从数据库表中删除记录。
语法

DELETE FROM table_name
WHERE column_name = some_value

注释:SQL 对大小写不敏感。DELETE FROM 与 delete from 等效。
为了让 PHP 执行上面的语句,我们必须使用 mysql_query( 函数。该函数用于向 SQL 连接发送查询和命令。
例子
稍早时,我们在本教程中创建了一个名为 "Persons" 的表。它看起来类似这样:

FirstNameLastNameAge
PeterGriffin35
GlennQuagmire33

下面的例子删除 "Persons" 表中所有 LastName='Griffin' 的记录:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("DELETE FROM Persons WHERE LastName='Griffin'");

mysql_close($con);
?>

在这次删除之后,表是这样的:

FirstNameLastNameAge
GlennQuagmire33

删除字段

执行删除命令,使用drop关键字。
基本的语法为:alter table <表名> drop column <字段名>;
先看test_table表中有那些字段:select * from test_table;
删除birth1字段:alter table test_table drop column birth1;
再次检查:select * from test_table;



开启数据库远程登录

use mysql;
Grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

%表示是所有的外部机器,如果指定某一台机,就将%改为相应的机器名(或者是ip地址);‘root’则是指要使用的用户名。
此命令也可以用来修改登录端,或者修改用户密码。


修改表类型(修改存储引擎)

创建时指定存储引擎

CREATE TABLE `rediscache` (
`id` int(11) NOT NULL auto_increment,
`sender` varchar(255) default NULL,
`receiver` varchar(255) default NULL,
`content` longtext,
`is_read` char(1) default 'N',
`send_time` datetime default NULL,
`del_flag` tinyint(4) default '0',
PRIMARY KEY (`id`)
) ENGINE=MYISAM;

创建后修改存储引擎

ALTER TABLE `rediscache` ENGINE = INNODB;    #rediscache是表名,修改存储引擎为INNODB

修改字段长度(字段容量)

例如在typecho中,typecho_contents表中text字段下存放文章数据,字段长度是text(~64kb),当文章字数太多了,64kb不够存储,就会报错:Database Query Error;只要修改字段长度为:longtext就行了。

describe `rediscache`;            #rediscache是表名,就能看到Type的类型
SHOW CREATE TABLE `rediscache`;   #能看到存储引擎

格式:alter table 表名 modify column 字段名 类型;
alter table rediscache modify column data varchar(500);
alter table rediscache modify column data longtext;

批量修改表前缀

# 用phpmyadmin执行SQL语句就可以得到修改语句。再在命令行中执行。
Select CONCAT( 'ALTER TABLE ', table_name, ' RENAME TO ', replace(table_name,'旧前缀_','新前缀_'),';')
from information_schema.tables
where TABLE_SCHEMA = '数据库名' and table_name LIKE '旧前缀_%';

# 得到如下格式的修改语句;粘贴回phpmyadmin中检查语法;无误后在命令行中执行。
ALTER TABLE tc_wx_img RENAME TO gw_wx_img;

mysql max_allowed_packet 设置过小导致记录写入失败

mysql根据配置文件会限制server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet 参数限制,导致写入或者更新失败。

查看目前配置:show VARIABLES like '%max_allowed_packet%';
看到的是以B为单位,除以1024转换为KB,再除以1024装换为MB。
修改配置:my.cnf(windows下是my.ini );把max_allowed_packet = 20M 改为你需要的值。
找不到配置文件可以用:mysql --help | grep my.cnf

法二:在mysql命令行中修改

在mysql 命令行中运行:set global max_allowed_packet = 20*1024*1024(这里改为20M)
然后退出命令行,重启mysql服务,再进入。

show VARIABLES like '%max_allowed_packet%';
查看下max_allowed_packet是否编辑成功


max_connection 关于最大连接数

MySQL> show status like '%connect%';

connections:试图连接到MySQL服务器的连接数(不管成功与否)。
max_used_connections:每个数据库用户的最大连接数。
threads_connected:当前的连接数。

mysql> show variables like '%connect%';

max_connections:数据库服务器最大连接数。

mysql> show processlist;

显示当前正在执行的MySQL连接。

修改连接数直接修改配置文件,加上对应的语句就行,如:
在配置文件(my.cnf或my.ini)在最下面,填加一句:max_connections=32000
然后,用命令重启:/etc/init.d/mysqld restart
虽然这里写的32000,实际MySQL服务器允许的最大连接数16384;添加了最大允许连接数,对系统消耗增加不大。

# 临时修改,不用重启数据库立刻生效。
set global max_connections = 1000;

关于设置及优化,参考:https://yq.aliyun.com/articles/50273

连接数设置太小报错:Too many connections

修改链接数就可以了。最好先用set修改,看看能否解决问题,然后再改配置文件。


关于连接错误次数过多时,MySQL禁止客户机连接

使用mysqladmin flush-hosts命令清除缓存,命令执行方法如下:
1.命令行或终端:mysqladmin -u user_name -p flush-hosts
接着输入账号密码即可

2.修改mysql配置文件,在[mysqld]下面添加 max_connect_errors=1000,然后重启mysql


修改列名、修改字段名

alter table table_name change <旧字段名> <新字段名> <int(表类型)>;

字段顺序调整

参考:http://database.51cto.com/art/201011/234259.htm


修改操作

UPDATE 语句用于修改数据库表中的数据。
语法

mysql
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value

注释:SQL 对大小写不敏感。UPDATE 与 update 等效。
为了让 PHP 执行上面的语句,我们必须使用 mysql_query( 函数。该函数用于向 SQL 连接发送查询和命令。
例子
稍早时,我们在本教程中创建了一个名为 "Persons" 的表。它看起来类似这样:

FirstNameLastNameAge
PeterGriffin35
GlennQuagmire33

下面的例子更新 "Persons" 表的一些数据:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("UPDATE Persons SET Age = '36'
WHERE FirstName = 'Peter' AND LastName = 'Griffin'");

mysql_close($con);
?>

在这次更新后,"Persons" 表格是这样的:

FirstNameLastNameAge
PeterGriffin36
GlennQuagmire33

插入

INSERT INTO table_name
VALUES (value1, value2,....)

您还可以规定希望在其中插入数据的列:

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)

注释:SQL 语句对大小写不敏感。INSERT INTO 与 insert into 相同。

为了让 PHP 执行该语句,我们必须使用 mysql_query() 函数。该函数用于向 MySQL 连接发送查询或命令。
例子
在前面的章节,我们创建了一个名为 "Persons" 的表,有三个列:"Firstname", "Lastname" 以及 "Age"。我们将在本例中使用同样的表。下面的例子向 "Persons" 表添加了两个新记录:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

mysql_query("INSERT INTO Persons (FirstName, LastName, Age) 
VALUES ('Peter', 'Griffin', '35')");

mysql_query("INSERT INTO Persons (FirstName, LastName, Age) 
VALUES ('Glenn', 'Quagmire', '33')");

mysql_close($con);
?>

更改表结构

alter table pet add id int not null
primary key auto_increment first;

详见:http://www.cnblogs.com/beginman/p/3249472.html


关于数据库排序规则

数据库中的排序规则一般很少去改,不过如JIRA之类的软件可能不支持某些排序规则,就会不断的弹出警告,修改排序规则参考:
https://www.22vd.com/7013.html
http://blog.csdn.net/li_xiao_ming/article/details/52152414
https://confluence.atlassian.com/jirakb/health-check-database-collation-790955315.html


把来自表单的数据插入数据库

现在,我们创建一个 HTML 表单,这个表单可把新记录插入 "Persons" 表。
这是这个 HTML 表单:

<html>
<body>

<form action="insert.php" method="post">
Firstname: <input type="text" name="firstname" />
Lastname: <input type="text" name="lastname" />
Age: <input type="text" name="age" />
<input type="submit" />
</form>

</body>
</html>

当用户点击上例中 HTML 表单中的提交按钮时,表单数据被发送到 "insert.php"。"insert.php" 文件连接数据库,并通过 $_POST 变量从表单取回值。然后,mysql_query() 函数执行 INSERT INTO 语句,一条新的记录会添加到数据库表中。
下面是 "insert.php" 页面的代码:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$sql="INSERT INTO Persons (FirstName, LastName, Age)
VALUES
('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";

mysql_close($con)
?>


常用查询

status                                          #查看数据库运行状态
show processlist;                               #查看正在执行的Sql语句
show full processlist;                          #查看正在执行的Sql语句
show variables like '%max_connections%';        #查看最大连接数量
show status like '%Max_used_connections%';      #服务器响应的最大连接数
show global status like 'Max_used_connections'; #服务器响应的最大连接数
show status like '%Threads_connected%';         #查询当前连接数
show status like '%table_lock%';                #查询表锁定
show status like 'innodb_row_lock%';            #查询行锁定
show status like '%qcache%';                    #查询缓存情况
show variables like "%query_cache%";            #查询缓存配置信息
show variables like "%binlog%";                 #查询核心日志
show status like 'Aborted_clients';             #由于客户端没有正确关闭连接已经死掉,已经放弃的连接数量
show variables like '%timeout%';                #查看超时时间
show variables like 'log_%';                    #查看日志是否启动
show warnings;                                  #查询警告信息
select database();                              #查看当前选择的数据库
show variables like '%datadir%';                #查看数据库保存位置

慢查询

# 查看mysql系统参数

show variables like "%slow%";
show variables like '%quer%';
show variables like 'slow_query%';
show variables like 'long_query_time';           #查看要记录的时间阀值
show variables like '%log_output%';              #查看日志输出格式,

注意以下几个值

开启慢日志

# 法一(临时:不用重启mysql):
# root登陆数据库
# 注意:5.5以下版本可能无法开启,这时先设置set global log_slow_queries=ON;
set global slow_query_log=ON;
set global log_queries_not_using_indexes = ON;   #记录所有没有利用索引的查询;slow_query_log开启了此项开启才会有效,否则是不生效的
set global long_query_time = 3;                  #设置查询超过3秒的没有使用索引的将会被记录到慢查询日志中
set global slow_query_log_file='/var/run/mysqld/mysqld-slow.log';    #设置慢查询日志存放位置,也可以保持默认



# 可选参数
set global log_output='TABLE';        #设置日志输出到表
set global log_output='FILE';         #设置日志输出到文件
set global log_output='FILE,TABLE';   #设置日志输出到文件和表


# 法二(永久:需要重启mysql):
vim /etc/my.cnf
# 在[mysqld]下的下方加入

slow_query_log = ON   #5.5以下版本改为:log_slow_queries = ON
long_query_time = 3
slow_query_log_file = /var/run/mysqld/mysqld-slow.log

测试

# 登陆数据库,查看是否生效了
show variables like 'slow_query%';
show variables like 'long_query_time';

# 执行一条慢查询语句
select sleep(4);
cat /var/run/mysqld/mysqld-slow.log

参考:
https://www.cnblogs.com/luyucheng/p/6265594.html
http://blog.csdn.net/timchen525/article/details/75268151

关于processlist:https://www.cnblogs.com/jasondan/p/3491258.html
关于Max connections:https://www.cnblogs.com/qmfsun/p/4844285.html

查询数据库的大小,占用空间

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;      #查询所有数据的总大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='databases_name';    #查询指定数据库的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='库名' and table_name='表名';    #查询指定数据库中的某个表的大小

查看库,表类型(存储引擎),表结构

sataus                                  #查看数据库运行状态
show databases;
use weixin888;
show tables;                            #只能显示表名,不能得到表类型的信息
SHOW TABLE STATUS FROM weixin888;       #weixin888是库名,显示的(所有表的信息)第二项type即是表的类型
describe rediscache;                    #rediscache是表名,就能看到Type的类型
desc table_name;                        #查看表结构
SHOW CREATE TABLE rediscache;           #显示的是表创建时的信息,其中有TYPE或ENGINE一项,指定了表的类型
use mysql;select host,user from user;   #查看用户的权限情况
use mysql;select host,user,password from user;    #查看用户账户、密码、可登录host

查看表中的列、类型

SHOW COLUMNS FROM auth_user; 
或
DESCRIBE auth_user;

describe 表名show columns from 表名 的一种快捷方式。


查询

SELECT 语句用于从数据库中选取数据。
语法
SELECT column_name(s) FROM table_name
注释:SQL 语句对大小写不敏感。SELECT 与 select 等效。
为了让 PHP 执行上面的语句,我们必须使用 mysql_query() 函数。该函数用于向 MySQL 发送查询或命令。
例子
下面的例子选取存储在 "Persons" 表中的所有数据(* 字符选取表中所有数据):

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "<br />";
  }

mysql_close($con);
?>

上面这个例子在 $result 变量中存放由 mysql_query() 函数返回的数据。接下来,我们使用 mysql_fetch_array() 函数以数组的形式从记录集返回第一行。每个随后对 mysql_fetch_array() 函数的调用都会返回记录集中的下一行。 while loop 语句会循环记录集中的所有记录。为了输出每行的值,我们使用了 PHP 的 $row 变量( $row['FirstName']$row['LastName'])。
以上代码的输出:
Peter Griffin
Glenn Quagmire



备份(导出)、恢复数据库

备份一个数据库

通用方法
mysqldump -u user_name -p db_name > file_name.sql

mysqldump db_name > file_name.sql -u root -p

windows:进到MySQL的安装目录(bin下),运行:

mysqldump --opt -h192.168.0.156 -uusername -ppassword --skip-lock-tables database_name>database.sql

IP改为localhost也行。

导出数据库中的一个表

mysqldump -u user_name -p db_name table_name> file_name.sql


导入恢复

# 先登录数据库创建该库。
source /root/mydb123.sql
mysql -u用户名 -p 数据库名 < 数据库名.sql
mysql -uuser_name -p mydb123 < /root/mydb123.sql

MySQL特性

MySQL 数据类型

下面的可使用的各种 MySQL 数据类型:

数值类型描述
int(size)仅支持整数。在 size 参数中规定数字的最大值。
smallint(size)仅支持整数。在 size 参数中规定数字的最大值。
tinyint(size)仅支持整数。在 size 参数中规定数字的最大值。
mediumint(size)仅支持整数。在 size 参数中规定数字的最大值。
bigint(size)仅支持整数。在 size 参数中规定数字的最大值。
decimal(size,d)支持带有小数的数字。在 size 参数中规定数字的最大值。在 d 参数中规定小数点右侧的数字的最大值。
double(size,d)支持带有小数的数字。在 size 参数中规定数字的最大值。在 d 参数中规定小数点右侧的数字的最大值。
float(size,d)支持带有小数的数字。在 size 参数中规定数字的最大值。在 d 参数中规定小数点右侧的数字的最大值。
文本数据类型容量描述
char(size)~支持固定长度的字符串。(可包含字母、数字以及特殊符号)。在 size 参数中规定固定长度。
varchar(size)~支持可变长度的字符串。(可包含字母、数字以及特殊符号)。在 size 参数中规定最大长度。
tinytext~1kb支持可变长度的字符串,最大长度是 255 个字符。
text~64kb支持可变长度的字符串,最大长度是 65535 个字符。
mediumtext~16MB支持可变长度的字符串,最大长度是 16777215 个字符。
longtext~4GB支持可变长度的字符串,最大长度是 4294967295 个字符。
日期数据类型描述
date(yyyy-mm-dd)支持日期或时间
datetime(yyyy-mm-dd hh:mm:ss)支持日期或时间
timestamp(yyyymmddhhmmss)支持日期或时间
time(hh:mm:ss)支持日期或时间
杂项数据类型描述
enum(value1,value2,ect)ENUM 是 ENUMERATED 列表的缩写。可以在括号中存放最多 65535 个值。
setSET 与 ENUM 相似。但是,SET 可拥有最多 64 个列表项目,并可存放不止一个 choice

主键和自动递增字段

每个表都应有一个主键字段。
主键用于对表中的行进行唯一标识。每个主键值在表中必须是唯一的。此外,主键字段不能为空,这是由于数据库引擎需要一个值来对记录进行定位。
主键字段永远要被编入索引。这条规则没有例外。你必须对主键字段进行索引,这样数据库引擎才能快速定位给予该键值的行。
下面的例子把 personID 字段设置为主键字段。主键字段通常是 ID 号,且通常使用 AUTO_INCREMENT 设置。AUTO_INCREMENT 会在新记录被添加时逐一增加该字段的值。要确保主键字段不为空,我们必须向该字段添加 NOT NULL 设置。
例:

$sql = "CREATE TABLE Persons 
(
personID int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)";

mysql_query($sql,$con);


其他情况

在 HTML 表格中显示结果

下面的例子选取的数据与上面的例子相同,但是将把数据显示在一个 HTML 表格中:

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons");

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['FirstName'] . "</td>";
  echo "<td>" . $row['LastName'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

以上代码的输出:

FirstNameLastName
PeterGriffin
GlennQuagmire

连接数据库失败

看提示,PHP执行时看错误,是用mysql还是mysqli
语法
mysql_connect(servername,username,password);

参数描述servername可选。规定要连接的服务器。默认是 "localhost:3306"。username可选。规定登录所使用的用户名。默认值是拥有服务器进程的用户的名称。password可选。规定登录所用的密码。默认是 ""。

语法
CREATE DATABASE database_name

参数描述
servername可选。规定要连接的服务器。默认是 "localhost:3306"。
username可选。规定登录所使用的用户名。默认值是拥有服务器进程的用户的名称。
passwd可选。规定登录所用的密码。默认是 ""。

为了让 PHP 执行上面的语句,我们必须使用 mysql_query() 函数。此函数用于向 MySQL 连接发送查询或命令。
连接:
例子:

php
<?php
//在一个变量中 ($con) 存放了在脚本中供稍后使用的连接。如果连接失败,将执行 "die" 部分:
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

创建:

mysql
// Create database
//创建了一个名为 "my_db" 的数据库:
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table in my_db database
//创建一个名为 "Persons" 的表,此表有三列。列名是 "FirstName", "LastName" 以及 "Age":
//重要事项:在创建表之前,必须首先选择数据库。通过 mysql_select_db() 函数选取数据库。
//注释:当您创建 varchar 类型的数据库字段时,必须规定该字段的最大长度,例如:varchar(15)。
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE Persons 
(
FirstName varchar(15),
LastName varchar(15),
Age int
)";
mysql_query($sql,$con);

mysql_close($con);   //提前关闭连接,请使用 mysql_close() 函数,不加此句则脚本结束后关闭。
?>

Mariadb Cluster 集群

参考:http://www.cnblogs.com/elvi/p/7736637.html

(●゚ω゚●)