mysql工作笔记

mysql服务的启动及停止：
mysqladmin -uroot -p -S /home/data/mysql/mysql.sock shutdown  #多实例启动或关闭指定sock
/etc/init.d/mysqld start|stop
mysqld_multi start
mysqld_safe &
本地及授权远程ip登录mysql：
grant all on *.* to 'test'@'10.0.0.%' identified by 'test';
或
grant all on *.* to 'test'@'10.0.0.0/255.255.255.0' identified by 'test';  #授权10.0.0.0/24网段的ip可以通过test访问mysql；
或 
mysql -uteat -p -h 10.0.0.2 -P 3306   #连接到远程主机的mysql；
mysql -uroot -p
设置数据库用户密码及修改密码：
mysqladmin -uroot -p'123456'    #为没有密码的用户设置密码；
mysqladmin -uroot -p'123456' password '654321'  #修改用户的密码；
mysqladmin -uroot -p'123456' password '654321' -S /tmp/mysql3306.sock   #多实例修改密码；
mysql>update mysql.user set password=password("654321") where user='root' and host='localhost';   #数据库里面修改用户密码,利用password函数加密；
mysql>flush privileges;
或
mysql>set  password=password("654321");   #当前登录的用户；
mysql>flush privileges;
mysql密码丢失找回：
1、/etc/init.d/mysqld stop  #先停止数据库；
2、mysqld_safe --skip-grant-tables --user=mysql &  #使用--skip-grant-tables忽略授权登录验证启动mysql；
mysql -uroot -p  #空密码登录；
mysql>update mysql.user set password=password("654321") where user='root' and host='localhost';
mysql>flush privileges;
mysqladmin -uroot -p654321 shutdown  #停掉忽略授权登录的mysql服务，在用正常方式(/etc/init.d/mysqld start)启动mysql；
mysql -uroot -p654321  
多实例：
killall mysqld
mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-table &  #多实例找回密码要指定my.cnf,忽略
授权登录验证要写在后面；
字符集：
1、跟开发确认字符集(建议UTF8)
2、编译时指定字符集
  -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii #数据库支持创建库的字符集；
3、编译时没有指定字符集或指定了和程序不同的字符集解决办法：
mysql>create database daname DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;  #创建gbk字符集的数据库；
mysql>create database dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;  #创建utf8字符集的数据库； 
数据乱码解决办法： 
1、执行DQL,DML语句含有中文数据时，务必要加入set names 系统及库表的字符集(latin1)，确保不乱码；
mysql>set names latin1;    #临时生效；
mysql>insert into test1 values(1,'呵呵');  
2、命令行指定参数导入sql文件；
mysql -uroot -p123456 --default-character-set=latin1 test mysql -uroot -p123456 -e "set names latin1;select * from test.test1;"
3、在配置文件里设置客户端及服务端相关参数
3.1、修改my.cnf客户端模块参数；
[client]
default-character-set=latin1
3.2、修改服务端参数；
[mysqld]
default-character-set=latin1   #适用5.1及以前版本；
character-set-server=latin1   #适用5.5版本；
查看所有字符集；
mysql> show character set;
查看mysql变量中不同字符集参数；
mysql> show variables like "character%";
查看mysql数据库状态；
mysql> show global status;
更改线上mysql数据库字符集(例：latin1改为utf8)方案：
已有数据的字符集不能使用alter database character set * 或alter table tablename character set *更改；
1、导出表结构
mysqldump -uroot -p123456 --default-character-set=latin1 -d dbname >alltable.sql  #-d 只导表结构，--default-character-set=latin1 表示以latin1字符集进行连接；
2、编辑alltable.sql将latin1改成utf8；
3、确保数据库不再更新，导出所有数据；
mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 dbname > alldata.sql  
#--quick:用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行并输出前CACHE到内存中；
#--no-create-info：不创建create table语句；
#--extended-insert：使用包括几个values列表的多行insert语法，这样文件更小，io也小，导入数据时会非常快；
#--default-character-set=latin1 按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码。
4、打开alldata.sql将set names latin1 修改成set names utf8；
5、建库
create database dbname default charset utf8；
6、创建表，执行alltable.sql
mysql -uroot -p123456 dbname < alltable.sql
7、导入数据
mysql -uroot -p123456 dbname < alldata.sql  
数据库状态查询：
show procedure status;
查询数据库用户、密码、主机名：
select user,password,host from mysql.user;
创建用户及授权与撤销权限：
all privileges包括的权限（select、insert、update、delete、create、drop、references参考、index、alter、create temporary tables创建临时表、lock tables、execute执行、create view创建视图、show view显示视图、create routine创建存储过程、alter routine修改存储过程、event事件、trigger触发器）
grant all privileges on *.* to song@localhost identified by '123456' with grant option;   #提升song为超级管理员；
flush privileges;
create user 'dbcenter'@'localhost' identified by '密码';
grant all privileges on db1.* to 'dbcenter'@'127.0.0.1' indentified by '123456';  #dbcenter对数据库db1拥有所有权；
grant usage on *.* to 'dbcenter'@'127.0.0.1' identified by '密码';  #decenter对所有数据库和表无权限(usage代表无权限)；
grant select,insert,update,delete,execute on 'ds'.* to 'dbcenter'@'127.0.0.1' identified by '密码';  #dbcenter对数据库ds所有表有select、insert、update、delete、execute权限
flush privileges;
show grants for dbcenter@127.0.0.1;  #查看dbcenter用户的权限；
revoke insert on db1.* from 'dbcenter'@'127.0.0.1' ;  #撤销用户dbcenter的insert权限；
或
mysql -udbcenter -p'123456' -e "revoke insert on db1.* from 'dbcenter'@'127.0.0.1' ;"
revoke execute on procedure 'database'.'table' from 'user'@'host'; 
删除账号及用户：
drop user 'user'@'host';   #删除数据库中的user账号；
delete from mysql.user where user='root' and host='localhost';  #drop删除不了(一般是特殊字符或大写)
flush privileges;
delete from mysel.user;  #删除mysql中所有用户；
delete from mysql.user where user in ('sxm');  #删除mysql中指定用户；
flush privileges;
添加存储过程权限：
grant execute on procedure mysql.user to 'root'@'localhost';
flush privileges;
备份及注入：
mysqldump -uroot -p123456 -S /home/data/mysql/mysql.sock --opt minmin > minmin20150421.sql 
# -S /path/to/socket, --socket=/path/to/socket  与localhost连接时（它是缺省主机)使用的套接字文件。 (用于linux系统) ;
# --opt
此选项将打开所有会提高文件导出速度和创造一个可以更快导入的文件的选项。（个人推荐。

如果没有使用--opt，MYSQLDUMP就会把整个结果集装载到内存中，然后导出。如果数据非常大就会导致导出失败。这个开关在默认情况下是启用的，如果不想启用它：--skip-opt来关闭它。）
# -q or -quick
这个选项使得MySQL不会把整个导出的内容读入内存再执行导出，而是在读到的时候就写入导文件中。这个和上面的开关一个意思。
# -t or --no-create- info
这个选项使的mysqldump命令不创建CREATE TABLE语句，这个选项在您只需要数据而不需要DDL（数据库定义语句）时很方便。
# -c or - complete_insert
这个选项使得mysqldump命令给每一个产生INSERT语句加上列（field）的名字。当把数据导出导另外一个数据库时这个选项很有用。
source *.sql; 
建表及表的操作方法：
create table student(id int(4) not null,name char(20) not null,age tinyint(2) NOT NULL default 0,dept varchar(16) default Null)ENGINE=InnoDB DEFAULT CHARSET=latin1;  #引擎为InnoDB，字符集默认继承库latin1；
desc student;  #查看表结构；
update t_item set valid=0 where playerguid=id and slot in (100,102) limit 2;
drop user user@'host';
去重：
select distinct;
改名：
insert into table(guidid,oldname,newname)values(8,'oldname','newname');
获取md5值：
select md5('test1');
修改登录提示符：
临时生效：mysql>prompt \u@song \r:\m:\s->   #\u当前用户名,\r小时，\m分钟，\s秒；
永久生效：修改my.cnf配置文件中[mysql]模块下添加如下内容：
[mysql]
prompt=\\u@song \\r:\\m:\\s->