以前主要使用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/ 找到最新的
找到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的游标是以临时表实现的,性能不怎么样,如果游标中处理上十万条数据,就比较慢。