mysql输出代码_mysql常用代码

1、mysql导出和导入

导入：mysql -uroot -p --default-character-set=utf8  数据库名  < 数据SQL文件

导出：mysqldump --opt -R -E -u root --password=1234 数据库名 > 数据SQL文件.sql

2、检查某表字段是否有NULL

set @dbname='库名',@tbname='表名';

select concat('select * from ',@tbname,' where ')

union all

select concat(COLUMN_NAME,' is null or ') from information_schema.COLUMNS

where table_name = @tbname and table_schema = @dbname

3、修改数据库用户密码

mysqladmin -uroot -p旧密码 password 新密码

4、忘记root密码时重置root密码

重设root密码

sudo systemctl stop mysql

sudo systemctl start mysql

/etc/mysql/mysql.conf.d/mysqld.cnf最后加 skip-grant-tables

mysql>update mysql.user set authentication_string=password('密码') where user='root';

mysql>flush privileges;

5、添加数据库用户myuser对mydb库完全权限

create database mydb default character set utf8 collate utf8_general_ci;

CREATE USER 'myuser'@'%' IDENTIFIED BY '888888';

GRANT  ALL ON mydb.* TO 'myuser'@'%';

GRANT GRANT OPTION ON mydb.* TO 'myuser'@'%';

6、检查定义者

#检查触发器定义者

SELECT trigger_name,EVENT_OBJECT_SCHEMA,EVENT_OBJECT_TABLE,DEFINER

FROM information_schema.`TRIGGERS` where EVENT_OBJECT_SCHEMA='mydb';

#检查事件定义者

SELECT EVENT_SCHEMA,EVENT_NAME,DEFINER

FROM information_schema.`EVENTS` where EVENT_SCHEMA='mydb';

#检查视图定义者

SELECT TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION,DEFINER

FROM information_schema.VIEWS where TABLE_SCHEMA='mydb';

#检查过程和函数

select name,type,definer

from mysql.proc where (type='PROCEDURE' or type='FUNCTION') and db='mydb';

#修改过程和函数定义者

update mysql.proc set DEFINER='myuser@%' WHERE (type='PROCEDURE' or type='FUNCTION') and db='mydb';

#修改视图定义者

SELECT concat("alter DEFINER=`myuser`@`%` SQL SECURITY DEFINER VIEW ",

TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";"

) FROM information_schema.VIEWS WHERE DEFINER <> 'myuser@%' and TABLE_SCHEMA='mydb' ;

7、优化MySQL数据表缓存参数；

table_open_cache 64=16384

table_definition_cache 256=16384

mysql set global table_open_cache=16384;

mysql set global table_definition_cache=16384;