MYSQL

发表于 | 分类于

MYSQL

DATABASE：

1
2
3
4
SET GLOBAL innodb_flush_log_at_trx_commit=0;
DROP DATABASE `flight`;
CREATE DATABASE lianjiax DEFAULT CHARACTER SET utf8;
CREATE USER 'renet'@'%' IDENTIFIED BY  '1234';

grant

1
2
3
4
5
6
grant select delete update on renet.word renet.account to renet_w@'127.0.0.1'  identified by 'YhKcxncuBOSRHtEv' with grant option;
grant all PRIVILEGES on weiye.* to weiye@'%'  identified by 'weiye' with grant option;
GRANT USAGE ON * . * TO  'renet'@'%' IDENTIFIED BY  '1234' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON  `wordpress` . * TO  'renet'@'%' WITH GRANT OPTION ;
flush PRIVILEGES;
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

TABLE

1
2
3
4
5
6
7
8
CREATE TABLE NewTable (
id  int(11) NOT NULL AUTO_INCREMENT ,
a1  varchar(255) NOT NULL ,
a2  datetime NULL ,
a3  double(20,8) NULL COMMENT '数值' ,
a4  text NULL COMMENT '文档' ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8;

导出备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/home/mysql/mysql5500/bin/mysqldump --defaults-extra-file=/home/mysql/mysql5500/etc/user.renet_w.cnf
# 导出整个数据库 
mysqldump -u 用户名 -p 数据库名 > 导出的文件名 
mysqldump -u root -p  smgp_apps_wcnc --skip-add-drop-table > wcnc.sql 
# --skip-add-drop-table 忽略删表语句
# 导出一个数据库结构 -d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table 
mysqldump -uroot -p -d --add-drop-table renet >/opt/wcnc_db.sql 
# 导出多个表
/home/mysql/mysql5500/bin/mysqldump -uroot -p renet cities attractions hotels restaurants > /home/mysql/data_backup/tables2015-4-22.sql
/home/mysql/mysql5500/bin/mysqldump -uroot -p renet progress_tracer > /home/mysql/data_backup/progress_tracer2015-10-13.sql
# 导出加条件
/home/mysql/mysql5500/bin/mysqldump -uroot -p renet emotion -w'vpuid=0' > /home/mysql/data_backup/emotion2015-3-29vpuids.sql
# 导出n条
mysqldump --default-character-set=utf8 -uroot -p renet -w'true limit 10000' > test.sql

导入

1
2
3
source d:\wcnc_db.sql 
/home/mysql/mysql5500/bin/mysql -hwwmysql01.mysql.rds.aliyuncs.com -uuser5i5j -P 3306 -p data_5i5j < /home/zmzmysql/to5i5j/5i5j.sql
 mysql -u root -p -D flight < d:\ customers.sql

CSV文件

1
2
3
4
5
6
7
 select * from video into outfile "/tmp/video.csv" fields terminated by ',' lines terminated by '\r\n';
 select * from test_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';
 
 load data infile 'D:/db/video.csv' into table video fields terminated by ',' lines terminated by '\r\n';
 load data infile  '/tmp/test.csv' into table test_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

 select count(*) a,moviename,pubdate from moviewish group by moviename,pubdate having a>3 into outfile '/tmp/moviewishlist.csv' fields terminated by ',' lines terminated by '\n'
  • 参考：http://blog.csdn.net/sara_yhl/article/details/6850107 select count(*) a,moviename,pubdate from moviewish group by moviename,pubdate having a>3 into outfile ‘/tmp/moviewishlist.csv’ fields terminated by ‘,’ lines terminated by ‘\n’

VIEW

1
2
3
4
CREATE VIEW newsnothave AS
select olds.id as id,olds.name as name,olds.name_en as name_en,olds.douban_uri as douban_uri
from star olds left join analyzeuser.star news on olds.id=news.id
where news.id is null;

防锁死

  • 查的时候试试这样 
    1
    2
    3
     SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 查询语句 ;
 COMMIT ;

    ALTER 变更表结构

  • 变更表字符集
    alter table airports convert to character set utf8;
  • 删除列
    ALTER TABLE 【表名字】 DROP 【列名称】
  • 增加列
    ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT '注释说明' alter table video add predicte_view varchar(255) default null;
  • 修改列的类型信息
    ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL COMMENT '注释说明'
  • 重命名列
    ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 【原有类型】 COMMENT '注释说明'
  • 重命名表
    ALTER TABLE 【表名字】 RENAME 【表新名字】
  • 删除表中主键
    Alter TABLE 【表名字】 drop primary key
  • 添加主键
    ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid);
  • 添加索引
    ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
  • 添加唯一限制条件索引
    ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
  • 删除索引
    alter table tablename drop index emp_name; innodb_flush_log_at_trx_commit 这个参数 看看配置的是什么 如果是1 可以调整成2或者0，建议先调整成2

配置

  • 查看最大连接数:
    show variables like '%max_connections%';
  • 修改最大连接数
    方法：命令行修改。命令行登录MySQL后。设置新的MySQL最大连接数为2000：
    MySQL> set global max_connections=2000 这种方式有个问题，就是设置的最大连接数只在mysql当前服务进程有效，一旦mysql重启，又会恢复到初始状态。 所以需要同时修改配置文件。 进入MySQL安装目录 打开MySQL配置文件 my.ini 或 /etc/my.cnf 新加入或修改配置 max_connections=2000 这样 重启MySQL服务后也不会失效

连接未释放排查

  • 思路

    使用 information_schema 里的信息，按时长排查出未释放的连接，并确认这些长时间的连接最后执行SQL语句，通过SQL语句定位代码位置。

  • 步骤：

    • 查找processlist 中时间较长的记录，

      select * from information_schema.processlist order by TIME desc ;

      1
      2
      3
      4
      5
      ID	USER	HOST	DB	COMMAND	TIME	STATE	INFO
1317503	testUser	192.168.1.138:2371	coredb	Sleep	69385		
1323810	testUser	192.168.1.166:53268		Sleep	23464		
1323811	testUser	192.168.1.166:53269		Sleep	23464		
1323809	testUser	192.168.1.166:53262	coredb	Sleep	23457

    • 通过THREAD_ID查找较长时间的连接的事件信息，可以在 SQL_TEXT 字段看到锁执行的SQL

      select * from performance_schema.events_statements_current where THREAD_ID in (1317503,1323810,1323811,1323809)

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      THREAD_ID	EVENT_ID	END_EVENT_ID	EVENT_NAME	SOURCE	TIMER_START	TIMER_END	TIMER_WAIT	LOCK_TIME	SQL_TEXT
1324163	42	42	statement/sql/select	mysqld.cc:962	10900850449118862028	10900850449143324212	24462184	0	SELECT CURRENT_TIMESTAMP * 1000
1325587	14	14	statement/sql/select	mysqld.cc:962	10900902402969061788	10900902403113274896	144213108	0	SELECT CURRENT_TIMESTAMP * 1000
1325589	14	14	statement/sql/select	mysqld.cc:962	10900902411816123520	10900902411943838876	127715356	0	SELECT CURRENT_TIMESTAMP * 1000
1325590	14	14	statement/sql/select	mysqld.cc:962	10900902412423411460	10900902412549704596	126293136	0	SELECT CURRENT_TIMESTAMP * 1000
1325591	14	14	statement/sql/select	mysqld.cc:962	10900902413005383884	10900902413132814796	127430912	0	SELECT CURRENT_TIMESTAMP * 1000
1325624	718	718	statement/sql/insert	mysqld.cc:962	10901234592602263564	10901234592702672296	100408732	0	insert into testtable (column1, column2, column3) values (884, 20210408154400, 20210408154320), (8126, 20210408154400, 20210408154320)
    ON DUPLICATE KEY UPDATE
    column2 =values(column2),
    column3 =values(column3)

    • 确定该 insert SQL在代码中的位置，确定代码里的transaction是否正常关闭。