mysql 操作记录

操作记录，用于留存

语句记录

– 新建表

CREATE TABLE pnodetail (
id varchar(100) NOT NULL,
flag varchar(2) DEFAULT NULL,
time int(11) DEFAULT NULL,
pho varchar(100) DEFAULT NULL,
updatetime timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

– 创建用户
CREATE USER dgm@’%’ IDENTIFIED BY ‘dgm’;
create user ‘root’@’%’ identified by ‘xiao3sun’

–修改密码
SET PASSWORD FOR ‘dgm’@’10.50.22.130’ = PASSWORD(‘yidianjiuxing’);
SET PASSWORD FOR ‘root’@’%’ = PASSWORD(‘shimadajie’);
或
UPDATE user SET Password = password ( ‘game123’ ) WHERE User = ‘root’ ;

–查看dgm权限
show grants for dgm

– 给超级权限
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ WITH GRANT OPTION;

对于刚安装的mysql, 可以用上面创建用户建 一个root@%,然后给超级权限，%代表可以所有ip访问,同时不要忘记了my.cnf中的 bind-address

–给权限
GRANT ALL PRIVILEGES ON ots.* TO ‘ess’@’%’

其实上面的user,都存在 mysql 中的mysql库的user表中,记得一个用户 ，如root,在各ip的密码/权限等可以是不一样的，参考上面的修改码
可以理解为 root@’127.0.0.1’ 这样形式的为一条数据.

–建数据库
CREATE DATABASE ytyt;
grant all privileges on ytyt.* to dgm

–修改表
alter table t_item add iaverage float null;
alter table t_ui add iamount float null;
alter table t_ui add icontribution float null;
alter table t_item add icountindex varchar(8) null;

– 增加索引
ALTER TABLE tx_member_user ADD INDEX i_area ( area )

自动更新 的时间字段
ALTER TABLE t_upload_cyf ADD COLUMN update_time timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’ ;

–注释
alter table t_ui modify column icontribution float comment ‘这条订单的贡献值 ‘;
alter table t_item modify column icountindex varchar(8) comment ‘这条订单的账期 ‘;

– insert
insert into T_USER (UID, UNAME, UDESC) values (‘dgm’,’dgm’,’dgm’);

–增加唯一约束
Alter table TB_USER add unique(email);

–当前时间
curdate()

– exists 更新t_uuuuser
update t_uuuuser set user_type = 2
where 1=1
and uname in (
–
SELECT o.uname from (
SELECT a.uname from t_uuuuser a
where 1=1
and a.user_type is null
and EXISTS (
SELECT * from t_uuuuser as b where a.uname = b.uname and user_type = 0
)
) o
)

mysql 运维

找出占用CPU时间过长的SQL
show processlist;
假定最后一条sql处于Query状态，且Time时间过长，就锁定它的ID，直接干掉即可。
kill QUERY 4487855;

• 忘记root 密码

• 1．首先确认服务器出于安全的状态，也就是没有人能够任意地连接MySQL数据库。
  因为在重新设置MySQL的root密码的期间，MySQL数据库完全出于没有密码保护的
  状态下，其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对
  外的端口封闭，并且停止Apache以及所有的用户进程的方法实现服务器的准安全
  状态。最安全的状态是到服务器的Console上面操作，并且拔掉网线。

• 2．修改MySQL的登录设置：
  vi /etc/my.cnf
  在[mysqld]的段中加上一句：skip-grant-tables
  例如：
  [mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  skip-grant-tables
  保存并且退出vi。

• 3．重新启动mysqld
  /etc/init.d/mysqld restart
  Stopping MySQL: [ OK ]
  Starting MySQL: [ OK ]
• 4．登录并修改MySQL的root密码
  /usr/bin/mysql
  Welcome to the MySQL monitor. Commands end with ; or \g.
  Your MySQL connection id is 3 to server version: 3.23.56
  Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
  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> UPDATE user SET Password = password ( ‘new-password’ ) WHERE User = ‘root’ ;
  Query OK, 0 rows affected (0.00 sec)
  Rows matched: 2 Changed: 0 Warnings: 0
  mysql> flush privileges ;
  Query OK, 0 rows affected (0.01 sec)
  mysql> quit
  Bye
• 5．将MySQL的登录设置修改回来
  vi /etc/my.cnf
  将刚才在[mysqld]的段中加上的skip-grant-tables删除
  保存并且退出vi。

• 6．重新启动mysqld
  /etc/init.d/mysqld restart
  Stopping MySQL: [ OK ]
  Starting MySQL: [ OK ]
  Work for fun,Live for love!

• mysql远端访问
  my.cnf中 bind-address =0.0.0.0

mysql乱码

mysql数据库的默认编码并不是utf-8。
安装mysql后，启动服务并登陆
，使用show variables命令可查看mysql数据库的默认编码:
show VARIABLES like ‘character%’;

vim /etc/my.cnf

character_set_server=utf8
init_connect=’SET NAMES utf8’

重启server

性能分析

• 慢查询
  参考 http://blog.csdn.net/mingover/article/details/55669172
  SHOW VARIABLES LIKE ‘long_query_time’;
  show VARIABLES LIKE ‘slow%’;
  select * from information_schema.PROCESSLIST where db = ‘xx’;

show table status;
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;
SELECT * FROM sys.statements_with_errors_or_warnings;
SHOW VARIABLES LIKE ‘long_query_time’;
show status like ‘Slave_running’;
show status like ‘Threads_connected’;
show status like ‘Threads_running’;
show status like ‘Aborted_clients’;
show status like ‘Questions’;
show status like ‘Handler_%’;
show status like ‘Opened_tables’;
show status like ‘Select_full_join’;
show status like ‘Select_scan’;
show status like ‘Slow_queries’;
show status like ‘Threads_created’;
show processlist;
show global status like ‘thread%’;
show innodb status;
– set global thread_cache_size=16;

show variables like ‘thread_cache_size’;

show variables like ‘%tmp%’;

– 查询数据库最大连接数
show variables like ‘max_connections’;
show variables like ‘max_used_connections’;

SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;

– create table t_tmp_statistics_daily_notest as SELECT * from tmp_statistics_daily_notest limit 1;

• 出现锁，请参考http://blog.csdn.net/mchdba/article/details/38313881
  select @@tx_isolation;
  SELECT * FROM information_schema.INNODB_TRX

– 执行的事务
SELECT * FROM information_schema.INNODB_TRX;

– 锁等待和持有锁的相互关系
SELECT * FROM information_schema.INNODB_LOCK_WAITS

出现一个事务依赖于另一个事务，如果上面的查询出现很多的事务，就可以查看下这条

– 事务的锁
SELECT * FROM information_schema.INNODB_LOCKS;