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;

總結

以上是生活随笔為你收集整理的mysql输出代码_mysql常用代码的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。