mysql常用命令

mysqldump -uroot -p --all-databases --flush-privileges --single-transaction --flush-logs --triggers --routines --events --hex-blob > 名字.sql   

mysqldump -uroot -p123456 --default-character-set=utf8 -q --flush-privileges --single-transaction --master-data=1 --flush-logs --routines --triggers --events --hex-blob -E -R -B testdb | gzip > ${backupdir}/${dateDIR}/mysql_back_${i}_${dateDIR}.sql.gz

　　mysqldump -uroot -pRCR8mGxnaNsb --opt -B -R -E -F --single-transaction --master-data=2 --default-character-set=utf8  --set-gtid-purged=off TM_MiniTrademark > TM_MiniTrademark.sql

 　　mysqlbinlog  --base64-output=decode-rows -v --start-date='2014-09-16 14:00:00' --stop-date='2014-09-16 14:20:00'  /opt/mysql/log/mysql-bin.000017 >/opt/mysql_bak/mysqlbinlogsql_restore_2014091614.sql

 　　mysqlbinlog mysql-bin.000030 -d yourdbName > yourdbName_binlog.sql

 

UPDATE user SET Select_priv='Y',Insert_priv='Y',Update_priv='Y',Grant_priv='N',Delete_priv='N',Create_priv='N',
Drop_priv='N',References_priv='N',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='N',Create_tmp_table_priv='Y',
Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',
Create_routine_priv='Y', Alter_routine_priv='Y',Create_user_priv='Y' WHERE user='username';
and host='%'

--triggers 触发器
--master-data=2 导出不指定CHANGE MASTER TO
R表示导出function和procedure
-E表示导出事件

 

 

CREATE USER 'guazi_uat'@'118.107.44.245' IDENTIFIED BY 'lfPecRxUMHM749?rsR';

GRANT ALL PRIVILEGES ON feifeicms.* TO 'guazi_uat'@'118.107.44.245';

mysql> UPDATE mysql.user
-> SET
-> Select_priv = 'Y',
-> Update_priv = 'Y',
-> Delete_priv = 'Y',
-> Alter_priv = 'Y',
-> Index_priv = 'Y',
-> Create_priv = 'Y',
-> Create_view_priv = 'Y',
-> Show_view_priv = 'Y',
-> Show_db_priv = 'Y',
-> Insert_priv = 'Y'
-> WHERE user = 'guazi_uat' AND host = '118.107.44.245';