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 =
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;