MySql操作大全

1、Grant赋权

#大数据库迁移

SET GLOBAL log_bin_trust_function_creators = 1;

#远程访问开放

grant all privileges on *.* to root@'%' identified by '123456';

grant all on uclass3.* to muser@'192.168.0.60' identified by "123456"; 

grant all on *.* to muser@'192.168.0.60' identified by "123456"; 

grant select,insert,update,delete on uclass3.* to muser@'192.168.0.60' identified by "123456"; 

2、引擎切换

select concat('ALTER TABLE ',TABLE_name,' ENGINE = innodb;') from information_schema.TABLES where TABLE_SCHEMA='scpt1435';

3、索引创建、导出

select CONCAT('create index ',INDEX_NAME,' ON ',TABLE_NAME,'(',GROUP_CONCAT(COLUMN_NAME),');') 

from information_schema.statistics where TABLE_SCHEMA='scpt_online_140319' and INDEX_NAME!='PRIMARY' and TABLE_NAME!='sys_user_library'

GROUP BY INDEX_NAME;

4、数据库备份

--hex-blob

使用十六进制格式导出二进制字符串字段。

mysqldump  -uroot -p --all-databases --hex-blob

--routines, -R

导出存储过程以及自定义函数。

mysqldump  -uroot -p --host=localhost --all-databases --routines

mysql mysqldump 只导出表结构 不导出数据

复制代码 代码如下:

mysqldump --opt -d 数据库名 -u root -p > xxx.sql 

备份数据库 

复制代码 代码如下:

#mysqldump　数据库名　>数据库备份名 

#mysqldump　-A　-u用户名　-p密码　数据库名>数据库备份名 

#mysqldump　-d　-A　--add-drop-table　-uroot　-p　>xxx.sql 

1.导出结构不导出数据 

复制代码 代码如下:

mysqldump　--opt　-d　数据库名　-u　root　-p　>　xxx.sql　　 

2.导出数据不导出结构 

复制代码 代码如下:

mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql　 

3.导出数据和表结构 

复制代码 代码如下:

mysqldump　数据库名　-uroot　-p　>　xxx.sql　 

4.导出特定表的结构 

复制代码 代码如下:

mysqldump　-uroot　-p　-B　数据库名　--table　表名　>　xxx.sql　　 

导入数据： 

　　由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了： 

复制代码 代码如下:

#mysql　数据库名　<　文件名 

#source　/tmp/xxx.sql　　 

 

5、慢查询设置

6、分区表

7、数据库主从

CHANGE MASTER TO

MASTER_HOST='192.168.0.61',

MASTER_USER='muser',

MASTER_PASSWORD='123456',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=98,

MASTER_CONNECT_RETRY=10;

 

MYSQL主从同步的作用

(1) 数据分布

(2) 负载平衡(load balancing)

(3) 备份

(4) 高可用性(high availability)和容错

8、存储过程

9、数据库初始化及清库脚本编写

select concat('DELETE FROM  ',TABLE_name,';') from information_schema.TABLES where TABLE_SCHEMA='scpt_res_sp';

10、垂直分库优缺点赏析

11、数据库引擎修改

select concat('ALTER TABLE ',TABLE_name,' ENGINE = innodb;') from information_schema.TABLES where TABLE_SCHEMA='scpt1435';

select concat('DELETE FROM  ',TABLE_name,';') from information_schema.TABLES where TABLE_SCHEMA='scpt_res_sp';

12、查询库各表空间大小

查看MySQL数据库大小

1、进去指定schema 数据库（存放了其他的数据库的信息）

use information_schema

2、查询所有数据的大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES

3、查看指定数据库的大小

比如说 数据库apoyl

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='apoyl';

4、查看指定数据库的表的大小

比如说 数据库apoyl 中apoyl_test表

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='apoyl' and table_name='apoyl_test';

例子：

select table_name, concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from 

TABLES where table_schema='tc' GROUP BY TABLE_NAME;

13、MySQL---使用GROUP_CONCAT后limit无效

SELECT GROUP_CONCAT(t.id) from (SELECT id from md.entity_field where domain_entity_id=1370 limit 60,20)t;

14、MySQL实现笛卡尔积

 

SELECT count(*) from (
SELECT * from price_store pss 
where pss.tenant_id=11068  and pss.factor_dm_bo_name is not  null
and pss.price_subject_id not in(SELECT DISTINCT pss1.price_subject_id from price_store pss1 where pss1.tenant_id=11068 and pss1.factor_dm_bo_name is not null and pss1.price_subject_id is not null ) 
)pss_temp
cross join store ps  where ps.tenant_id=11068;

15、mysql列转行