正文

Mysql基本操作整理

IP云
IP云V管理员/5 阅读
11/29
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1、登录数据库

格式： mysql -h主机地址 -u用户名 －p用户密码 –P端口 –D数据库 –e “SQL 内容”>mysql -uroot -p 数据库名称
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2、修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码
Mysqladmin  -uroot  -password ab12
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
例2：再将root的密码改为djg345。
mysqladmin -u root -p ab12 password djg345
shell>mysql -u root -p
mysql> update user setpassword=password(”xueok654123″) where user=’root’;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
上面该密码不行
5.5用的一直都很爽，到5.6居然说命令行方式写密码不安全？那密码写哪呢？
在官网文档找到了缘由，大家可以点击这里看看：http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html
MySQL users should use the following guidelines to keep passwords secure.
When you run a client program to connect to the MySQL server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. The methods you can use to specify your password when you run client programs are listed here, along with an assessment of the risks of each method. In short, the safest methods are to have the client program prompt for the password or to specify the password in a properly protected option file.
英文有点烂，但大概读懂意思，翻译过来大意是在命令行下如果要使用密码可以在执行命令后的提示输入里输入密码，或者在指定的安全文件内指定密码。那安全文件时哪个呢？文档对此给出了答案：
Store your password in an option file. For example, on Unix, you can list your password in the [client] section of the .my.cnf file in your home directory:
可以在my.cnf内指定，于是打开我的my.cnf，在[mysqldump]下增加：user=root password=roo
文中说的在[client]下面加也可以，但那样就所有块的操作都能共享了，所以生产环境上为了安全还是尽量分开。保存退出再dump就ok了。

[root@qttc ~]# /usr/local/mysql/bin/mysqldump db > bak.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3 、添加用户

格式：grant select on 数据库.* to 用户名@登录主机 identified by \"密码\"
grant select on 数据库.* to 用户名@登录主机 identified by "密码"
例1、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：
grant select,insert,update,deleteon *.* to test2@localhost identified by \"abc\";
如果你不想test2有密码，可以再打一个命令将密码消掉。
grantselect,insert,update,delete on mydb.* to test2@localhostidentified by \"\";
还有两种，
(1)>INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES('%','newuser1',PASSWORD('123456'),'','','')
(2)>CREATE USER 'admin'@'%' IDENTIFIED BY 'admin'　　执行之后user表会增加一行记录，但权限暂时全部为‘N’。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

4、创建数据库

create databasename; 创建数据库
usedatabasename; 选择数据库
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

5       备份/恢复数据库

shell>mysqldump -h host -u root -p dbname >dbname_backup.sql
shell>mysqladmin -h myhost -u root -p create dbname
shell>mysqldump -h host -u root -p dbname < dbname_backup.sql
如果只想卸出建表指令，则命令如下：
shell>mysqladmin -u root -p -d databasename > a.sql
mysqldump -uroot -p test mytable>./test.txt
将备份数据导入到数据库：(导回test数据库)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

备份数据库staffer
mysqldump -uroot-proot staffer > staffer.sql
得到的staffer.sql是一个sql脚本，不包括建库的语句，所以你需要手工
创建数据库才可以导入
恢复数据库staffer,需要创建一个空库staffer
mysql -uroot-proot staffer<staffer.sql
如果不希望后来手工创建staffer,可以
mysqldump -uroot-proot --databases staffer> staffer.sql
mysql -uroot-proot > staffer.sql
但这样的话系统种就不能存在staffer库，且无法导入其他名字的数据库，
当然你可以手工修改staffer.sql文件

如果只想卸出插入数据的sql命令，而不需要建表命令，则命令如下：
shell>mysqladmin -u root -p -t databasename > a.sql
那么如果我只想要数据，而不想要什么sql命令时，应该如何操作呢？
mysqldump -T./phptest driver
其中，只有指定了-T参数才可以卸出纯文本文件，表示卸出数据的目录，./表示当前目录，即与mysqldump同一目录。如果不指定driver 表，则将卸出整个数据库的数据。每个表会生成两个文件，一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，且没有sql指令。
可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。例如，如果在文件my_file.sql 中存放有查
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

6、导入.sql文件命令（例如D:/mysql.sql）

mysql>use database;
mysql>source /home/mysql.sql;
UPDATE以新的值更新现存表中行的列。SET 子句指出要修改哪个列和他们应该给定的值。WHERE子句如果被给出，指定哪个记录行应该被更新。否则，所有的记录行被更新。如果 ORDER BY 子句被指定，记录行将被以指定的次序更新。
如果你指定关键词LOW_PRIORITY，UPDATE 的执行将被延迟，直到没有其它的客户端正在读取表。
如果你指定关键词IGNORE，该更新语句将不会异常中止，即使在更新过程中出现重复键错误。导致冲突的记录行将不会被更新。
如果在一个表达式中从tbl_name 中访问一个列，UPDATE 使用列的当前值。举例来说，下面的语句设置 age 列值为它的当前值加 1 ：
mysql> UPDATE persondata SETage=age+1;
UPDATE赋值是从左到右计算的。举例来说，下列语句将 age 列设置为它的两倍，然后再加 1 ：
mysql> UPDATE persondata SETage=age*2, age=age+1;
如果你设置列为其当前的值，MySQL 注意到这点，并不更新它。
UPDATE返回实际被改变的记录行数目。在 MySQL 3.22 或更新的版本中，C API 函数 mysql_info()
返回被匹配并更新的记录行数目，以及在 UPDATE 期间发生的警告的数目。
在 MySQL3.23 中，你可以使用 LIMIT # 来确保只有给定的记录行数目被更改。
如果一个 ORDER BY 子句被使用(从 MySQL4.0.0 开始支持)，记录行将以指定的次序被更新。这实际上只有连同 LIMIT
一起才有用。
从 MySQL 4.0.4 开始，你也可以执行一个包含多个表的UPDATE 的操作：
UPDATE items,month SETitems.price=month.price
WHERE items.id=month.id;
注意：多表 UPDATE 不可以使用 ORDER BY 或LIMIT。
关键字: mysql
启动：net start mySql;
进入：mysql -u root -p/mysql -hlocalhost -u root -p databaseName;
列出数据库：show databases;
选择数据库：use databaseName;
列出表格：show tables；
显示表格列的属性：show columns fromtableName；
建立数据库：source fileName.txt;
匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

7 、用文本方式将数据装入数据库表中（例如D:/mysql.txt）

mysql> LOAD DATA LOCAL INFILE/mysql.txt" INTO TABLE MYTABLE;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
如果出现错误
ERROR 1148 (42000): The used command is not allowed with this MySQL version
解决方式有两种，
一是重新编译安装加上下面的参数:
1. ./configure --prefix=/usr/local/mysql --enable-local-infile
3. make
5. make install
二是直接用命令行执行，例如：
mysql -uroot -p test2 --local-infile=1 -e 'load data local infile "/usr/local/mysql/bin/ip_list.txt" into table ceshi fields terminated by "\t" lines terminated by "\n";'

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

8、删除数据库

drop database name 直接删除数据库
mysqladmin drop database name 删除数据库前，有提示
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

9、数据库授权

GRANT ALL PRIVILEGES ON bdc.* TObdc@'%' IDENTIFIEDBY 'bdc123' WITH GRANT OPTION;
WITH GRANT OPTION解释：权限传递，使用这个子句时将允许用户将其权限分配给他人对象授权加的是选项不能被授予角色
flush privileges
授权访问bdc 的所有表 用户为bdc 任何主机 ，密码为bdc@123
创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb
mysql >grant select, insert,update, delete, create,drop on fangchandb.* to custom@it363.com identified by ‘passwd’
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

10、删除授权

mysql> revoke all privilegeson *.* from root@”%”;
mysql> delete from user whereuser=”root” and host=”%”;
mysql> flush privileges;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

11、显示当前mysql版本和当前日期

select version(),current_date;
查询时间：select now();
查询当前用户：select user();
查询数据库版本：select version();
查询当前使用的数据库：select database();
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

12、查询所有数据表

>show tables;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

13、查询表的字段信息

>desc 表名称;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

14、创建表

create table s_position
(
id int not null auto_increment,
name varchar(20) not nulldefault '经理', #设定默认值
description varchar(100),
primary key PK_positon (id) #设定主键
);
create table department
(
id int not null auto_increment,
name varchar(20) not nulldefault '系统部', #设定默认值
description varchar(100),
primary key PK_department (id) #设定主键
);
create table depart_pos
(
department_id int not null,
position_id int not null,
primary key PK_depart_pos(department_id,position_id) #设定复和主键
);
create table staffer
(
id int not null auto_incrementprimary key, #设定主键
name varchar(20) not nulldefault '无名氏', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id)#设定唯一值
);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

15    修改结构

语法：修改默认自增，从100开始
alter table F auto_increment=100;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
mysql>
#表position增加列test
alter table position add (test char(10));
#表position修改列test
alter table position modify test char(20) not null;
#表position修改列test默认值
alter table position alter test set default 'system';
#表position去掉test默认值
alter table position alter test drop default;
#表position去掉列test
alter table position drop column test;
#表depart_pos删除主键
alter table depart_pos drop primary key;
#表depart_pos增加主键
alter table depart_pos add primary key PK_depart_pos (department_id,position_id);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

16    操作数据

#插入表department
insert into department(name,description)values('系统部','系统部');
insert into department(name,description)values('公关部','公关部');
insert into department(name,description)values('客服部','客服部');
insert into department(name,description)values('财务部','财务部');
insert into department(name,description)values('测试部','测试部');
#插入表s_position
insert into s_position(name,description)values('总监','总监');
insert into s_position(name,description)values('经理','经理');
insert into s_position(name,description)values('普通员工','普通员工');
#插入表depart_pos
insert intodepart_pos(department_id,position_id)
select a.id department_id,b.id postion_id
from department a,s_position b;
#插入表staffer
insert intostaffer(name,department_id,position_id) values('陈达治',1,1);
insert into staffer(name,department_id,position_id)values('李文宾',1,2);
insert intostaffer(name,department_id,position_id) values('马佳',1,3);
insert intostaffer(name,department_id,position_id) values('亢志强',5,1);
insert intostaffer(name,department_id,position_id) values('杨玉茹',4,1);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

17    查询及删除操作

#显示系统部的人员和职位
select a.name,b.name department_name,c.name position_name from staffera,department b,s_position c where a.department_id=b.id and a.position_id=c.id and b.name='系统部';
#显示系统部的人数
select count(*)from staffer a,department b where a.department_id=b.id
and b.name='系统部'
#显示各部门的人数
select count(*)cou,b.name from staffer a,department b
wherea.department_id=b.idgroup by b.name;
#删除客服部
delete fromdepartment where name='客服部';
#将财务部修改为财务一部
update department set name='财务一部' wherename='财务部';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

18    删除表

mysql>drop TABLE MYTABLE;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

19    清空表

mysql>delete from MYTABLE;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

20    添加表字段

alter tabletable1 add transactor varchar(10) not Null;
alter tabletable1 add id int unsigned not Null auto_increment primary key
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

21    修改某个表的字段类型及指定为空或非空

>alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
>alter table 表名称 modify 字段名称 字段类型 [是否允许非空];
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

22    如果要删除某一字段，可用命令：

ALTER TABLEmytable DROP 字段名;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

23    添加唯一键

ALTER TABLE`test2` ADD UNIQUE ( `userid`)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

24    修改主键

先删除以前的主键，然后增加新的主键
ALTER TABLE`test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )
alter tableversion_template drop primary key ,add primary key version,role,type);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

25    增加索引

ALTER TABLE`test2` ADD INDEX ( `id` )
alter tabletable1 add index ind_id (id);
create indexind_id on table1 (id);
create uniqueindex ind_id on table1 (id);//建立唯一性索引
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

26    删除索引

drop indexidx_id on table1;
alter tabletable1 drop index ind_id;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

27    重命名表

alter table t1 rename t2;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

28    增加一个字段

alter table tabelName add column fieldName dateType;
例如：alter table losnau add phone varchar(20);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

29    增加多个字段

alter tabletabelName add column fieldName1 dateType,add columns fieldName2 dateType;
多行命令输入:注意不能将单词断开;当插入或更改数据时，不能将字段的字符串展开到多行里，否则硬回车将被储存到数据中;

增加一个管理员帐户：grant all on *.* to username@localhost identified by "password";
也可以这样 gtant all privileges on *.* to username@'%' indentified by '';
每条语句输入完毕后要在末尾填加分号';'，或者填加'\g'也可以；
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

30    创建临时表

create temporarytable zengchao(name varchar(10));

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

31    创建表是先判断表是否存在

create table if not exists students(……);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

32    从已经有的表中复制表的结构

create table tabletable2 select * from table1 where 1<>1;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

33    复制表

create tabletable2 select * from table1;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

34    对表重新命名

alter table table1 rename astable2;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

35    修改列的类型

alter table table1 modify id intunsigned;//修改列id的类型为int unsigned
alter table table1 change id sid intunsigned;//修改列id的名字为sid，而且把属性修改为inunsigned
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

36    联合字符或者多个列(将列id与":"和列name和"="连接)

select concat(id,':',name,'=') from students;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

37    limit(选出10到20条)<第一个记录集的编号是0>

select * from students order by id limit 9,10;
(root@localhost) [daorutxt] select * from ip order by IP limit 9,10;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

38    MySQL会使用索引的操作符号

<,<=,>=,>,=,between,in,不带%或者_开头的like
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

39    存储过程

1）查看存储过程
select name from mysql.proc where db=’数据库名’;
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

40    MySQL存储过程的修改

ALTER PROCEDURE
更改用CREATEPROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

41     MySQL存储过程的删除

删除一个存储过程比较简单，和删除表一样：
DROP PROCEDURE
从MySQL的表格中删除一个或多个存储过程。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

42    存储过程基本函数

(1).字符串类
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string[,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

mysql> select substring('abcd',0,2);
+-----------------------+
| substring('abcd',0,2) |
+-----------------------+
|                       |
+-----------------------+
1 row in set (0.00 sec)

mysql> select substring('abcd',1,2);
+-----------------------+
| substring('abcd',1,2) |
+-----------------------+
|     ab                |
+-----------------------+
1 row in set (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
(2).数学类
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如：
(1)默认变为整形值
mysql> select round(1.23);
+-------------+
| round(1.23) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select round(1.56);
+-------------+
| round(1.56) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据
mysql> select round(1.567,2);
+----------------+
| round(1.567,2) |
+----------------+
|           1.57 |
+----------------+
1 row in set (0.00 sec)
SIGN (number2 ) //
(3).日期时间类
ADDTIME (date2 ,time_interval )//将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
SQRT(number2) //开平方
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

43    使用索引的缺点

1)  增删改数据的速度；
2）占用磁盘空间；
3）增加查询优化器的负担；
当查询优化器生成执行计划时，e会考虑索引，太多的索引会给查询优化器增加
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

44    分析索引效率

方法：在一般的SQL语句前加上explain；
分析结果的含义：
1）table：表名；
2）type：连接的类型，(ALL/Range/Ref)。其中ref是最理想的；
3）possible_keys：查询可以利用的索引名；
4）key：实际使用的索引；
5）key_len：索引中被使用部分的长度（字节）；
6）ref：显示列名字或者"const"（不明白什么意思）；
7）rows：显示MySQL认为在找到正确结果之前必须扫描的行数；
8）extra：MySQL的建议；
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

45    使用较短的定长列

1）尽可能使用较短的数据类型；
2）尽可能使用定长数据类型；
a）用char代替varchar，固定长度的数据处理比变长的快些；
b）对于频繁修改的表，磁盘容易形成碎片，从而影响数据库的整体性能；
c）万一出现数据表崩溃，使用固定长度数据行的表更容易重新构造。使用固定长度的数据行，每个记录的开始位置都是固定记录长度的倍数，可以很容易被检测到，但是使用可变长度的数据行就不一定了；
d）对于MyISAM类型的数据表，虽然转换成固定长度的数据列可以提高性能，但是占据的空间也大；
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

46    使用not null和enum

尽量将列定义为notnull，这样可使数据的出来更快，所需的空间更少，而且在查询时，MySQL不需要检查是否存在特例，即null值，从而优化查询；
如果一列只含有有限数目的特定值，如性别，是否有效或者入学年份等，在这种情况下应该考虑将其转换为enum列的值，MySQL处理的更快，因为所有的enum值在系统内都是以标识数值来表示的；
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

47    使用optimize table

对于经常修改的表，容易产生碎片，使在查询数据库时必须读取更多的磁盘块，降低查询性能。具有可变长的表都存在磁盘碎片问题，这个问题对blob数 据类型更为突出，因为其尺寸变化非常大。可以通过使用optimize table来整理碎片，保证数据库性能不下降，优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表，然后使用转存后的文件并重新建数据 表；
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

48    使用procedure analyse()

可以使用procedure analyse()显示最佳类型的建议，使用很简单，在select语句后面加上procedureanalyse()就可以了；例如：
select * from students procedureanalyse();
select * from students procedureanalyse(16,256);
第二条语句要求procedure analyse()不要建议含有多于16个值，或者含有多于256字节的enum类型，如果没有限制，输出可能会很长；
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

49    使用查询缓存

1）查询缓存的工作方式：
第一次执行某条select语句时，服务器记住该查询的文本内容和查询结果，存储在缓存中，下次碰到这个语句时，直接从缓存中返回结果；当更新数据表后，该数据表的任何缓存查询都变成无效的，并且会被丢弃。
2）配置缓存参数：
变量：query_cache_type，查询缓存的操作模式。有3中模式，0：不缓存；1：缓存查询，除非与 select sql_no_cache开头；2：根据需要只缓存那些以select sql_cache开头的查询； query_cache_size：设置查询缓存的最大结果集的大小，比这个值大的不会被缓存。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

50    调整硬件

1）在机器上装更多的内存；
2）增加更快的硬盘以减少I/O等待时间；
寻道时间是决定性能的主要因素，逐字地移动磁头是最慢的，一旦磁头定位，从磁道读则很快；
3）在不同的物理硬盘设备上重新分配磁盘活动；
如果可能，应将最繁忙的数据库存放在不同的物理设备上，这跟使用同一物理设备的不同分区是不同的，因为它们将争用相同的物理资源（磁头）。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

52、MySQL版本查看

[root@wulaoer ~]# mysql -V
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 或者

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

53、二进制日志查看

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

开 启二进制日志，在windows里my.ini或者linux里my.cnf里，在[mysqld]下加上log-bin=日志名，注{等号后面可以定义 二进制日志文件名和位置}。如果只对某个库进行日志文件记录，那么再添加binlog-do-db=test，这样就只记录test数据库的日志，而放弃 其他的，而binlog-ignore-db=sales 除sales库不记录，其他都记录。配置文件设置好需要重启一下mysql
mysql> show master status;
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| log.000001 |      106 |              |                  |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

54、mysql日志管理

设置my.cnf中的参数[mysqld]下的expire_logs_day = 天数  。此参数是设置日志的过期天数，过期的日志将会被自动删除，如果日志长度超过了max_binlog_size = (大小可以带单位，默认k)的上限（默认是1G）也会创建一个新的日志文件①show variables like 'log_%';查看所有的log命令
②show variables like 'log_bin';查看具体的log命令删除日志：mysql> purge master logs to 'mysql-bin.000009' ;
Query OK, 0 rows affected (0.01 sec)
mysql> purge master logs before '2015-0-22 00:00:00′;
Query OK, 0 rows affected (0.05 sec)
RESET MASTER#删除之前所有的mysqld-bin，并重新生成新的mysqld-bin后缀从000001开始。注：如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。44、远程使用客户端连接数据库
（1）删除连接的空账户
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec) （2）建立客户端连接：
先在防火墙加如一条，防火墙允许3306端口通过，添加后重启防火墙。
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT （3）先看以下mysql的配置文件my.cnf或者my.conf,里面（[mysqld]）有没有bind_address=127.0.0.1，如果有就注释掉，没有的话就不用操作了，
（4）添加允许连接的IP和帐号密码.
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root"; 更多可以参考3、添加用户，这里的用户和密码是客户端连接的用户和密码。
（5）查看连接的客户端
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,host from user;
+------+--------------+
| user | host         |
+------+--------------+
| root | 127.0.0.1    |
| root | 192.168.1.36 |
| root | localhost    |
| root | wulaoer      |
+------+--------------+
4 rows in set (0.00 sec)

（6）删除连接的客户端
关闭远程访问：
mysql> use mysql;
mysql> update user set host = "localhost" where user = "root" and host= "%"; （7）删除连接的IP：
mysql> select user,host from user;
+------+--------------+
| user | host         |
+------+--------------+
| root | 127.0.0.1    |
| root | 192.168.1.36 |
| root | localhost    |
| root | wulaoer      |
+------+--------------+
4 rows in set (0.00 sec)

mysql> DELETE FROM user WHERE  Host="localhost";
Query OK, 1 row affected (0.00 sec)

mysql> select user,host from user;
+------+--------------+
| user | host         |
+------+--------------+
| root | 127.0.0.1    |
| root | 192.168.1.36 |
| root | wulaoer      |
+------+--------------+
3 rows in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

56、查看连接MYSQL数据库的IP信息

我们通常情况下要统计数据库的连接数指的是统计总数，没有细分到每个IP上。现在要监控每个IP的连接数，实现方式如下：
方法一： 

select SUBSTRING_INDEX(host,':',) as ip , count(*) from information_schema.processlist group by ip;

方法二： 

mysql -u root -h127.0.0. -e "show processlist\G;"| egrep "Host\:" | awk -F: '{ print $2 }'| sort | uniq -c

方法三： 

mysql -u root -h127.0.0. --skip-column-names -e "show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq –c

以上就是三种监控IP连接数的实现方式，希望对大家的学习有所帮助。

相关阅读

目录[+]