mysql 学习笔记

以前主要使用oracle做数据库,现在换成mysql了,发现不一样的地方还是挺多的,记录一下:

一、centos上的yum install方式安装 

完全卸载(可选,如果之前安装了旧版本)

a) rpm -qa|grep mysql

先查看是否已经安装了mysql

b) yum remove mysql*

执行完成后,再执行下a)中的命令确认下

c) 

rm -f /etc/my.cnf

rm -f /etc/my.cnf.rmp*

rm -rf /var/lib/mysql

安装最新版mysql

a) http://dev.mysql.com/downloads/repo/ 找到最新的

image.png

找到rpm的下载(目前最新是5.7版,8.6k)

b) 把这个文件上传到服务器,比如:/data/download 下

c) 导入yum库

yum localinstall /data/download/mysql57-community-release-el6-7.noarch.rpm

d) 安装

yum install mysql-community-server

以下是安装后的几个关键目录默认位置:

数据库目录

/var/lib/mysql/

配置文件

/usr/share/mysql(mysql.server命令及配置文件)

相关命令

/usr/bin(mysqladmin mysqldump等命令)

启动脚本

/etc/rc.d/init.d/(启动脚本文件mysql的目录)

查看配置文件位置

mysqld --verbose --help |grep -A 1 'Default options’

会输出类似下面的信息:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

这表示mysql启动时会查找/etc/my.cnf,如果找不到,则到路径/etc/mysql/my.cnf,依此类推...

修改my.cnf

参考配置:

 1 [client]
 2 
 3 default-character-set=utf8 
 4 
 5 [mysqld]
 6 
 7 default-character-set=utf8
 8 
 9 character_set_server=utf8
10 
11 # innodb_buffer_pool_size = 128M
12 
13 # log_bin
14 
15 # join_buffer_size = 128M
16 
17 # sort_buffer_size = 2M
18 
19 # read_rnd_buffer_size = 2M
20 
21 datadir=/data/mysql/db
22 
23 socket=/var/lib/mysql/mysql.sock 
24 
25 symbolic-links=0 
26 
27 log-error=/data/mysql/log/mysqld.log
28 
29 pid-file=/data/mysql/mysqld.pid

这里有几个关键参数,需要修改

default-character-set

character_set_server

这个是设置utf8编码,可以解决大多数中文乱码问题

datadir

log-error

pid-file

分别对应数据库文件目录位置,日志文件位置,pid文件位置,建议调整到剩余空间较大的分区

innodb_buffer_pool_size 这个是会影响mysql的性能,后面还会讲到

启动mysqld

a) 调整目录权限

chown -R mysql:mysql /data/mysql

启动前,建议先确认下相关目录,mysql有没有读写权限,否则启动会失败

service mysqld start

修改root密码:

mysql 5.7对用户安全性做了加强,默认root账号是无法登录的,修改方法如下:

service mysqld stop //先停止

mysqld_safe --skip-grant-tables //以安全模式启动

另开一个ssh终端窗口

mysql //进入mysql控制台

update user set authentication_string=password(‘新密码') where user='root’;

注:新密码必须复杂安全性要求,建议弄成A1b2c3@def.com这种复杂的

重启mysql

service mysqld stop

service mysqld start

现在mysql -uroot -p 应该能登录进去了

如果进去执行其它操作时,比如创建数据库时,提供要重设密码之类的,mysql命令行模式下再执行一遍下面的操作

set password=password(‘新密码’);

二、创建数据库及用户授权

特别要注意编码

create database xxx default character set utf8;

(注:从5.0.2开始,创建数据库也可以用create schema命令,这二者在mysql中等效的,这跟其它主流关系型数据库,比如:oracle,ms sql中的schema概念完全不同)

如果建错了,想删除数据库

drop database xxx;

切换数据库

use xxx

查看所有数据库

show database;

用户授权:

GRANT ALL PRIVILEGES ON db1.* TO ‘user1’@‘localhost’ IDENTIFIED BY ‘pwd1’;

上面的语句将db1的所有权限授权给用户user1,如果只想授权部分权限,参考下面的示例:

grant select on table2 to 'user1'@'localhost';

三、一些常用的sql操作

a) 执行外部sql脚本文件

mysql命令行下

source /opt/app/sql/xxx.sql (注:xxx.sql必须存在,且mysql账号必须有权限读取)

b)查看当前正在运行的sql

show processlist

通常mysql运行很卡时,用这个命令查找当前正在跑的sql,然后找到其id,方便将其kill掉

c) kill掉指定id的sql操作

mysqladmin -h 服务器 -u用户名 -p kill id号

d) 查看当前数据库引擎状态

show engine innodb status\G;

e) 查看几个关键参数:

show global status like 'innodb_buffer_pool_pages_data';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| Innodb_buffer_pool_pages_data | 4053  |

+-------------------------------+-------+

1 row in set (0.01 sec)

show global status like 'innodb_buffer_pool_pages_total’;

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| Innodb_buffer_pool_pages_total | 4095  |

+--------------------------------+-------+

1 row in set (0.01 sec)

这二个的比值,即innodb_buffer_pool_pages_data/innodb_buffer_pool_pages_total ,按网上的经验之谈,如果>95%,说明mysql内存快满负载了,建议大innodb_buffer_pool_size的值 ,建议设置成系统内存的75%

注:select @@innodb_buffer_pool_size 显示出来的值

+---------------------------+

| @@innodb_buffer_pool_size |

+---------------------------+

|                   8388608 |

+---------------------------+

1 row in set (0.00 sec)

是以字节为单位的,要转换成M,需要除1024*1024,上面的值8388608,即相当于8388608/(1024*1024)=8M

f) 数据导出

导出整个数据库(包括数据)

mysqldump -h服务器ip -u 用户名 -p 数据库名 > 导出的文件名 

导出单个表(包括数据)

 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

仅导出表结构

./mysqldump -u用户名 -p -d --add-drop-table 数据库名 > 导出的文件名

仅导出数据 

./mysqldump -u用户名 -p -t 数据库名 > 导出的文件名

导出后的sql脚本,可以在目标数据库上,通过前面提到的source命令导入

g) 查看所有表/视图/存储过程

show tables;

SELECT * from information_schema.VIEWS\G;

show procedure status\G;

h)查看表结构、视图结构、存储过程sql

show create table 表名\G;

show create view 视图名\G;

show create function 函数名\G;

show create procedure 存储过程名\G;

show create database 数据库名\G;

i) update ...join... on 操作

1 UPDATE table1
2 INNER JOIN table2 ON (
3      table1.id = table2.id
4 )
5 SET table1.x = table2.y;

j) 快速复制一张表

create table table1_bak select * from table1;

k) 将一张表的某些记录快速插入相同结构的备份表中

insert into table1_bak select * from table1 limit 0,5;

l) 跨库查询

1 SELECT
2     count(*)
3 FROM
4     db1.table1 t1
5 INNER JOIN db2.table2 t2 ON t1.id = t2.id
6 WHERE
7     t1.id > 0

前提:当前用户有db2.table2的select权限,如果没权限,先按前面的用户授权方法给相对的表授权。

四、自定义函数,游标,存储过程

a) 先解决命令行模式下;号的问题

因为;是默认的命令结束符号,写自定义函数或存储过程的时候,本身就会包含;符号,导致命令行下,mysql误认为存储过程代码结果,解决办法

delimiter //

上面的命令告诉mysql,命令结束符号为//,而不是默认的; 

存储过程写完了以后,再执行

delimiter ;

还原回来

b) 自定义函数示例

 1 DELIMITER //
 2 DROP FUNCTION
 3 IF EXISTS `ifempty`//
 4 
 5 CREATE FUNCTION `ifempty`(
 6   s1 VARCHAR(4096),
 7   s2 VARCHAR(4096)
 8 )
 9   RETURNS VARCHAR(4096)
10   CHARSET utf8 NO SQL DETERMINISTIC SQL SECURITY INVOKER
11   BEGIN
12 
13     IF (ISNULL(s1))
14     THEN
15       RETURN s2;
16     ELSEIF CHAR_LENGTH(s1) = 0
17       THEN
18         RETURN s2;
19     ELSE
20       RETURN s1;
21     END
22     IF;
23   END//

c) 存储过程示例

 1 DELIMITER //
 2 
 3 CREATE PROCEDURE test(IN b INT)
 4 
 5   BEGIN
 6 
 7     DECLARE a INT;
 8 
 9     SET a = b + 1;
10 
11     SELECT a;
12 
13   END
14 
15 //

d)三种常用的循环写法

while..do 写法

 1 DELIMITER //
 2 
 3 CREATE PROCEDURE test(IN b INT)
 4 
 5   BEGIN
 6 
 7     DECLARE i INT;
 8 
 9     SET i = 0;
10 
11     WHILE i < b DO
12 
13       SELECT i;
14 
15       SET i = i + 1;
16 
17     END WHILE;
18 
19   END
20 
21 //

repeat 写法

 1 DELIMITER //
 2 
 3 CREATE PROCEDURE test(IN b INT)
 4 
 5   BEGIN
 6 
 7     DECLARE i INT DEFAULT 0;
 8 
 9     REPEAT
10 
11       SELECT i;
12 
13       SET i = i + 1;
14 
15     UNTIL i >= b
16 
17     END REPEAT;
18 
19   END
20 
21 // 

loop .. end loop写法

 1 DELIMITER //
 2 
 3 CREATE PROCEDURE test(IN b INT)
 4 
 5   BEGIN
 6 
 7     DECLARE i INT DEFAULT 0;
 8 
 9     mylabel: LOOP
10 
11       SELECT i;
12 
13       SET i = i + 1;
14 
15       IF i >= b
16       THEN
17 
18         LEAVE mylabel;
19 
20       END IF;
21 
22     END LOOP;
23 
24   END
25 
26 //

e) 游标示例

 1 DELIMITER //
 2 
 3 CREATE PROCEDURE test(IN min_id INT)
 4 
 5   BEGIN
 6 
 7     DECLARE _done INT DEFAULT 0;
 8     -- 判断游标是否结束的标志 
 9 
10     DECLARE p_id INT DEFAULT 0;
11 
12     DECLARE p_name VARCHAR(100) DEFAULT '';
13 
14     DECLARE _cur CURSOR FOR
15 
16       SELECT
17         t.`d_id`,
18         t.`d_name`
19       FROM t_test AS t
20       WHERE t.`d_id` >= min_id;
21 
22     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
23     -- 标记循环结束 
24 
25     OPEN _cur;
26 
27     REPEAT
28 
29       FETCH _cur
30       INTO p_id, p_name;
31 
32       IF NOT _done
33       THEN
34 
35         SELECT
36           p_id,
37           p_name;
38 
39       END IF;
40 
41     UNTIL _done
42 
43     END REPEAT;
44 
45     CLOSE _cur;
46 
47   END
48 
49 //

当然,也可以将游标的遍历换成while do ...end while的写法

 1 DELIMITER //
 2 
 3 DROP PROCEDURE IF EXISTS p_test_cursor //
 4 
 5 CREATE PROCEDURE p_test_cursor()
 6 
 7   BEGIN
 8     DECLARE _done INT DEFAULT 0; -- 判断游标是否结束的标志
 9 
10     DECLARE p_activity_id INT DEFAULT 0;
11     DECLARE p_community_id INT DEFAULT 0;
12 
13     DECLARE _cur CURSOR FOR
14       SELECT
15         t.activity_id,
16         t.community_id
17       FROM h_activity_community AS t;
18 
19     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1; -- 标记循环结束
20 
21     OPEN _cur;
22 
23     WHILE _done != 1
24     DO
25       FETCH _cur
26       INTO p_activity_id, p_community_id;
27 
28       IF (_done != 1) -- 如果游标没结束,就打印出这些变量值
29       THEN
30         SELECT
31           p_activity_id,
32           p_community_id,
33           _done;
34       END IF;
35 
36     END WHILE;
37     
38     CLOSE _cur;
39     COMMIT;
40   END
41 //

注:mysql的游标是以临时表实现的,性能不怎么样,如果游标中处理上十万条数据,就比较慢。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java架构沉思录

MySQL存储引擎知多少

MySQL是我们经常使用的数据库处理系统(DBMS),不知小伙伴们有没有注意过其中的“存储引擎”(storage_engine)呢?有时候面试题中也会问道MyS...

10020
来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

266100
来自专栏zcqshine's blog

MAC OSX brew 升级 mysql5.6到5.7无法启动的问题

42580
来自专栏Ryan Miao

mysql创建定时执行存储过程任务

sql语法很多,是一门完整语言。这里仅仅实现一个功能,不做深入研究。 目标:定时更新表或者清空表。 案例:曾经做过定时清空位置信息表的任务。(然而,当时并未考虑...

43170
来自专栏CaiRui

Mysql-4-数据库的基本操作

1.创建数据库 create database database_name; 例:create database aa; show create databas...

20570
来自专栏JavaWeb

MySQL-大批量数据如何快速的数据迁移

31220
来自专栏奇梦博客

CentOS下mysql数据库常用命令总结 MySQL 参数配置

15610
来自专栏Ken的杂谈

CentOS 7下InfluxDB部署与使用入门

InfluxDB里存储的数据被称为时间序列数据,InfluxDB存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库+表+字段组织数据,InfluxD...

87740
来自专栏Ryan Miao

Ubuntu18.04(linux)安装MySQL

Ubuntu18.04 安装mysql或者mariadb之后,发现普通用户和远程都没有权限连接。

15020
来自专栏杨建荣的学习笔记

MySQL query rewrite插件简单测试

在机场继续努力一把,学习了下MySQL query rewrite这个插件,感觉还不错,j简答测试了下,已经找到Oracle FGA的影子了。

21020

扫码关注云+社区

领取腾讯云代金券