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;