mysql 大杂烩

做个笔记,一些常用的 'MySQL' 知识点

QQ截图20180912102913.png

开启MySQL远程访问权限 允许远程连接


mysql -u root -p
mysql> use mysql;
mysql> select host,user,password from user;
mysql> grant all privileges  on *.* to root@'%' identified by "password";
mysql> flush privileges;
-----------------------------------------
#删除用户为 "" 空的数据
mysql> delete from user where user='';

mysql 修改用户密码

以 "root" 为例

mysql -u root -p
mysql> use mysql;
mysql> update user set password=password('123456') where user = 'root';
mysql> flush privileges;

解除正在死锁的状态有两种方法:

第一种:

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程(如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程)

show processlist

3.杀死进程id(就是上面命令的id列)

kill id

第二种:

1.查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id(就是上面命令的trx_mysql_thread_id列)

kill 线程ID

例子:

查出死锁进程:SHOW PROCESSLIST
杀掉进程 KILL 420821;

其它关于查看死锁的命令:

1:查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2:查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3:查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

批量处理编码

SELECT
CONCAT(
    'ALTER TABLE ',
    TABLE_NAME,
    ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
)
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMA = 'kqbiz-plus';


    ALTER TABLE merchant CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

未完待续。。。。。。


   版权声明:本文为原创文章,版权归本站所有,欢迎分享本文,转载请保留出处!

发表留言

人生在世,错别字在所难免,无需纠正。

icon_mrgreen.gificon_neutral.gificon_twisted.gificon_arrow.gificon_eek.gificon_smile.gificon_confused.gificon_cool.gificon_evil.gificon_biggrin.gificon_idea.gificon_redface.gificon_razz.gificon_rolleyes.gificon_wink.gificon_cry.gificon_surprised.gificon_lol.gificon_mad.gificon_sad.gificon_exclaim.gificon_question.gif