Tagged: MySql

MySQL 常用命令

-- 查看数据库引擎的运行时信息
show engine innodb status\G;

-- 显示正在运行的线程
show processlist;

-- 杀死进程
kill id

-- 锁表处理
show status like ‘%lock%’
show OPEN TABLES where In_use > 0; 

-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

-- 查看服务器状态
show status like ‘%lock%’;

-- 显示一个用户的权限
show grants for user_name; 

-- 显示一些系统特定资源的信息
show status; 

-- 显示系统变量的名称和值
show variables; 

-- 显示BDB存储引擎的日志
show logs; 

-- 显示最后一个执行的语句所产生的错误、警告和通知
show warnings; 

-- 只显示最后一个执行语句所产生的错误
show errors; 

-- 查看MySQL服务器运行的各种状态值
show global status;

-- 显示连接数
show variables like 'max_connections'; 
show global status like 'max_used_connections'; 

Mybatis 插入或更新数据

在日常的开发中,我们经常会遇到这样的需求,我们有一个存储主数据的表,当有新的数据写入的时候,若库中存在数据则更新,否则当作新的数据写入。

一般情况下,我们会先对数据库进行一次Select,若数据存在我们则执行更新操作,当然这种方法在大部分情况下是可行的,但数据量大或者数据写入频繁的时候,这种方法就表现出来它的不稳定。

其实在数据库中都提供了UNIQUE约束规则,我们可以在数据库中建立UNIQUE约束规则,这样我们在插入具有约束限制数据的时候,我们就会得到一个异常,我们可以捕获这个异常,在异常块中进行更新操作。

在Mysql中我们可以使用更加简单的方式,Mysql提供了这样一个方法on duplicate key update可以让数据库直接搞定数据的插入或更新。

首先在数据库中创建约束

create unique index mytest_uindex on table (fieldA, FieldB);

编写SQL语句

insert into table (fieldA, filedB)) values ('a', 'b') on duplicate key update filedA = a1, fieldB = a2;

这样一波操作下来,你会神奇的发现,原来可以如此简单。。

该方法会删除掉已有数据,再进行插入,具体可以观察数据的自增ID。

具体用法可以查看这里:https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

WordPress 备份迁移记录

备份

使用之前写的脚本 VPS数据库及文件备份脚本

使用scp将文件拷贝到其他主机(密钥登陆的方式)

chmod 600 id_rsa
scp -i id_rsa -r backup/ root@10.0.0.100:/root/backup

文件恢复

文件恢复就不写了,放回原来的地方就可以了。

数据恢复

# 创建数据库
mysqladmin -uroot -p create DBName

#恢复数据
mysql -uroot -p DBName < DBName.sql

Shell Command

# 连接数据库
mysql -h localhost -uroot -p

# 创建数据库
create database [databasename];

# 更改用户密码(命令行)
mysqladmin -u username -h hostname -p password 'new-password'

# 备份单个库
mysqldump -u username -ppassword --databases databasename > /tmp/databasename.sql

# 备份所有
mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

# 备份表
mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

# 恢复备份数据
mysql -u username -ppassword databasename < /tmp/databasename.sql

Mysql Command

-- 显示库
show databases;

-- 切换库
use [db name];

-- 显示表
show tables;

-- 显示表结构
describe [table name];
show columns from [table name];

-- 删除表
drop table [table name];

-- 显示表数据
SELECT * FROM [table name];

-- 创建一个新用户
use mysql;
INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
flush privileges;

-- 更改用户密码
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
flush privileges;

Centos Install Mysql (解压缩版)

下载并解压Mysql

下载地址http://dev.mysql.com/downloads/mysql/ (选择Linux - Generic版本的Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed)

# 检查库文件是否存在,如有删除。
rpm -qa | grep mysql
rpm -e mysql-libs-5.1.52.x86_64 --nodeps

# 下载mysql
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

# 解压
tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

添加用户及用户组

# 检查mysql用户及用户组是否存在
cat /etc/group | grep mysql
cat /etc/passwd | grep mysql

# 创建mysql用户及用户组
groupadd mysql
useradd -r -g mysql mysql

授权

# 授权
cd /usr/local
chown -R mysql:mysql mysql/

安装

cd mysql

# 初始化(这里要记录下密码,后续会用到)
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

# 创建执行程序软连接
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

#启动mysql
./support-files/mysql.server start

# 拷贝文件到开机启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --list mysqld # 如果不存在则执行下面命令,存在则不执行下面命令
chkconfig --add mysqld

配置my.cnf

vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data 
user=mysql 
character-set-server=utf8 
collation-server=utf8_general_ci 
# Disabling symbolic-links is recommended to prevent assorted security
risks symbolic-links=0 

初始化密码

mysql -u root -h 127.0.0.1 -p
# 这里的密码是刚才初始化时生成的随机密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
flush privileges;

常用命令

service mysqld start
service mysqld stop
service mysqld restart

Windows安装Mysql Zip绿色版

安装服务

REM 自动启动
mysqld --install
mysqld --defaults-file="D:\Program Files\mysql\mysql.ini" --initialize --explicit_defaults_for_timestamp
net start mysql

REM 手动启动
mysqld --install-manual
mysqld --defaults-file="D:\Program Files\mysql\mysql.ini" --initialize --explicit_defaults_for_timestamp
net start mysql

REM 卸载服务
mysqld --remove

初始密码

# mysql.ini 内容
[mysql]
default-character-set=utf8

[mysqld]
basedir = D:\Program Files\mysql
datadir = D:\Program Files\mysql\data 
port = 3306 
max_connections=200 
character-set-server=utf8 
default-storage-engine=INNODB   sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

查看Data目录下**.err文件,搜索"password is generated",结束就是初始密码

mysql初始密码是无法作为登陆使用的,所以登陆mysql必须修改自动生成的密码。

mysql -u root -h 127.0.0.1 -p
Enter password: (enter the random password here)
 
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> flush privileges;

MySQL基本语句

MySQL脚本的基本组成

与常规的脚本语言类似, MySQL 也具有一套对字符、单词以及特殊符号的使用规定, MySQL 通过执行 SQL 脚本来完成对数据库的操作, 该脚本由一条或多条MySQL语句(SQL语句 + 扩展语句)组成, 保存时脚本文件后缀名一般为 .sql。在控制台下, MySQL 客户端也可以对语句进行单句的执行而不用保存为.sql文件。

标识符

标识符用来命名一些对象, 如数据库、表、列、变量等, 以便在脚本中的其他地方引用。MySQL标识符命名规则稍微有点繁琐, 这里我们使用万能命名规则: 标识符由字母、数字或下划线(_)组成, 且第一个字符必须是字母或下划线。

对于标识符是否区分大小写取决于当前的操作系统, Windows下是不敏感的, 但对于大多数 linux\unix 系统来说, 这些标识符大小写是敏感的。

关键字:

MySQL的关键字众多, 这里不一一列出, 在学习中学习。 这些关键字有自己特定的含义, 尽量避免作为标识符。

语句:

MySQL语句是组成MySQL脚本的基本单位, 每条语句能完成特定的操作, 他是由 SQL 标准语句 + MySQL 扩展语句组成。

函数:

MySQL函数用来实现数据库操作的一些高级功能, 这些函数大致分为以下几类: 字符串函数、数学函数、日期时间函数、搜索函数、加密函数、信息函数。

MySQL中的数据类型

MySQL有三大类数据类型, 分别为数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型:

  • 数字类型
    1. 整数: tinyint、smallint、mediumint、int、bigint
    2. 浮点数: float、double、real、decimal
  • 日期和时间: date、time、datetime、timestamp、year
  • 字符串类型
    1. 字符串: char、varchar
    2. 文本: tinytext、text、mediumtext、longtext
    3. 二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

创建一个数据库

使用 create database 语句可完成对数据库的创建, 创建命令的格式如下:

create database 数据库名 [其他选项];

例如我们需要创建一个名为 samp_db 的数据库, 在命令行下执行以下命令:

create database samp_db character set gbk;

为了便于在命令提示符下显示中文, 在创建时通过 character set gbk 将数据库字符编码指定为 gbk。创建成功时会得到 Query OK, 1 row affected(0.02 sec) 的响应。

注意: MySQL语句以分号(;)作为语句的结束, 若在语句结尾不添加分号时, 命令提示符会以 -> 提示你继续输入(有个别特例, 但加分号是一定不会错的);

提示: 可以使用 show databases; 命令查看已经创建了哪些数据库。

insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);
select 列名称 from 表名称 [查询条件];
select 列名称 from 表名称 where 条件;
update 表名称 set 列名称=新值 where 更新条件;
delete from 表名称 where 删除条件;

Archives