首頁 >資料庫 >mysql教程 >mysql的死锁等6个实战问题解决_MySQL

mysql的死锁等6个实战问题解决_MySQL

WBOY
WBOY原創
2016-06-01 12:58:491055瀏覽

mysql的死锁等6个实战问题解决

目录:

锁表后的解锁 mysql连接数不够 mysql的root密码修改 密码正确却登录不进 datetime类型有0问题 查看表占用空间

锁表后的解锁

当对表做dml操作时卡住,很可能是表被锁。
到数据库主机,查看进程命令:
show processlist;
找到有锁的进程id,杀掉:
kill id;

processlist是全部的进程,比较多,有时候,从processlist里能看到哪个锁住,但有时候它和其他进程看上去一样。
这时需要查看数据表:

<code class="language-sql hljs ">SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;</code>

看trx_mysql_thread_id字段,这个是死锁的进程id,然后到主机那里kill掉即可。

经验提示:最好不要用客户端界面修改表结构,有可能会锁住。最好使用语句。(不过,其实我也经常贪图方便,直接用SQLyog直接修改表结构,方便嘛。偶尔锁住了kill掉即可,毕竟只是开发环境,生产环境必须不能如此)<br> 增加字段:ALTER TABLE tf_b_depart ADD (PARENT_MAJOR VARCHAR(6));<br> 修改字段:alter table tf_f_task_target modify VALUE decimal(16,2);<br> 建表:CREATE TABLE td_s_salary_index (index_id VARCHAR(8));

mysql连接数不够

陆续开发人员的eclipse里突然报错信息:

MySQLNonTransientConnectionException<br> Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: “Too many connections”<br> at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

原因:max_connections mysql 默认值为100,超过了就会报错。重启mysql即可。为了防止后面再出现,需要把max_connections改为1000或更多。

<code class="language-sql hljs "><code class="hljs makefile">[mysql@paas03 ~]$more my.cnf
[mysqld_multi]
mysqld = /app/mysql/bin/mysqld_safe
mysqladmin = /app/mysql/bin/mysqladmin
user = mysql
password = a@Aug22
log=/app/log/mysqld_multi.log

[mysqld01]
port = 3010
socket = /tmp/mysql.sock01
pid-file = /app/data_paas/db-app.pid
basedir = /app/mysql
datadir = /app/data_paas
user = mysql
symbolic-links=0
character-set-server=utf8
lower_case_table_names=1
innodb_log_file_size=128M
innodb_log_buffer_size=4M
innodb_buffer_pool_size=1G
event_scheduler=1
explicit_defaults_for_timestamp
max_connections=1500
join_buffer_size = 128M
sort_buffer_size = 10M
read_rnd_buffer_size = 2M</code></code>

<code class="hljs makefile">参考:http://www.cnblogs.com/S-E-P/archive/2011/04/29/2045050.html

<code class="hljs makefile">mysql的root密码修改

<code class="language-sql hljs "><code class="hljs makefile"><code class="language-sql hljs ">SELECT * FROM mysql.user WHERE USER=&#39;root&#39;;
SET PASSWORD FOR &#39;root&#39;@&#39;localhost&#39; = PASSWORD(&#39;root123&#39;);
SET PASSWORD FOR &#39;root&#39;@&#39;paas03&#39; = PASSWORD(&#39;root123&#39;);
SET PASSWORD FOR &#39;root&#39;@&#39;%&#39; = PASSWORD(&#39;root123&#39;);</code></code></code>

<code class="hljs makefile"><code class="language-sql hljs ">注:亲测OK。

<code class="hljs makefile"><code class="language-sql hljs ">密码正确却登录不进

<code class="hljs makefile"><code class="language-sql hljs ">Caused by: java.sql.SQLException: Access denied for user ‘zplat_cen1’@’aifs1’ (using password: YES)

<code class="hljs makefile"><code class="language-sql hljs ">其他机器可以登录,就是安装了这个数据库的本机不能登录。然后发现不需要密码就可以登录了(去掉-p),但是只有test库。<br> http://www.bitsCN.com/article/19326.htm这里里面说的delete from user where user is NULL;是扯淡的。<br> 真正的原因是dba没创建本主机的用户,mysql和oracle不太一样,同一个用户,需要在三台机器分别创建,包括localhost、本主机名、%(表示通配符)。应该CREATE USER 'zplat_cen1'@'aifs1' IDENTIFIED BY 'XXX';就可以了。

<code class="hljs makefile"><code class="language-sql hljs ">datetime类型有.0问题

<code class="hljs makefile"><code class="language-sql hljs ">mysql datetime类型,后面会有.0<br> 2015-07-21 16:37:47.0<br> 有两种解决方法:<br> 1. 写sql时增加DATE_FORMAT(RECEIVE_TIME, '%Y-%m-%d %H:%i:%s'),这个非常麻烦,每个sql都得加<br> 2. 修改你公司的框架代码,统一处理一下

<code class="language-sql hljs "><code class="hljs makefile"><code class="language-sql hljs "><code class="language-java hljs ">if (type == Types.TIMESTAMP) { // 增加对时间类型的支持,修复mysql显示.0问题 2015.7.21
    Timestamp t = rs.getTimestamp(name);
    if (t == null)
        return null;
    SimpleDateFormat sDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    String date = sDateFormat.format(t);
    return date;
}</code></code></code></code>

<code class="hljs makefile"><code class="language-sql hljs "><code class="language-java hljs ">参考:http://blog.csdn.net/zhanghaotian2011/article/details/7721551

<code class="hljs makefile"><code class="language-sql hljs "><code class="language-java hljs ">同样的decimal类型也有这个问题,前台显示全部加.00

<code class="language-sql hljs "><code class="hljs makefile"><code class="language-sql hljs "><code class="language-java hljs "><code class="language-java hljs ">if (type == Types.DECIMAL) { // 增加对decimal类型的支持,修复mysql显示.00问题 2015.7.22
    String decimal = rs.getString(name);
    if (decimal == null)
        return null;
    if(decimal.indexOf(".") > 0){  
        decimal = decimal.replaceAll("0+?$", "");//去掉多余的0
        decimal = decimal.replaceAll("[.]$", "");//如最后一位是.则去掉
    }
    return decimal;
}</code></code></code></code></code>

<code class="hljs makefile"><code class="language-sql hljs "><code class="language-java hljs "><code class="language-java hljs ">PS:学好正则表达式是多么的重要!否则去0的这个代码你怎么??

<code class="hljs makefile"><code class="language-sql hljs "><code class="language-java hljs "><code class="language-java hljs ">查看表占用空间

<code class="language-sql hljs "><code class="hljs makefile"><code class="language-sql hljs "><code class="language-java hljs "><code class="language-java hljs "><code class="language-sql hljs ">SELECT table_name,data_length/1024/1024 MB FROM  information_schema.tables 
WHERE table_schema=&#39;zplatdb&#39; ORDER BY data_length DESC;</code></code></code></code></code></code>
陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn