MySQL

1. 安装MySQL及常用配置

推荐使用lnmp安装,线上主备及专业DBA运维未涉及。

关于建表时引擎的选择:MyISAM适合查询以及插入为主的应用,InnoDB适合频繁修改以及设计到安全性较高的应用。Innodb支持ACID事务,myISAM不支持。

使用慢查询分析(实用)
在my.ini中:

long_query_time=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log

把超过1秒的记录在慢查询日志中。可以用mysqlsla来分析之,也可以在mysqlreport中,有如DMS分别分析了select,update,insert,delete,replace等所占的百分比

MYSQL的事务配置项

innodb_flush_log_at_trx_commit=1
表示事务提交时立即把事务日志flush写入磁盘,同时数据和索引也更新,很费性能。
innodb_flush_log_at_trx_commit=0
事务提交时,不立即把事务日志写入磁盘,每隔1秒写一次,MySQL挂了可能会丢失事务的数据。
innodb_flush_log_at_trx_commit=2 ,在整个操作系统 挂了时才可能丢数据,一般不会丢失超过1-2秒的更新。

事务提交时,立即写入磁盘文件(这里只是写入到系统内核缓冲区,但不立即刷新到磁盘,而是每隔1秒刷新到磁盘,同时更新数据和索引),这种方案是不是性价比好一些,当然如何配置,决定于你对系统数据安全性的要求。

2. 数据库的备份和还原

备份所有的数据库内容到一个文件中,执行bash命令(注意:对于utf8mb4编号,必须加上--default-character-set=utf8mb4):

mysqldump --all-databases --default-character-set=utf8mb4 -u用户名 -p密码 | gzip > mysql_$(date +\%Y\%m\%d).gz

还原数据库,之前较老版本的mysql要mysql密码后面再空格指定数据库名,但现在测试的5.6不需要, 需要事先建好数据库:

gunzip < mysql_sql_****.gz  | mysql -u用户名 -p密码

还原数据库可多次执行,数据库表会被覆盖:比备份多的行会被删除,比备份多的表不会被删除。

对于utf8mb4编号,还原数据库后,客户端要重新连接才能让utf8mb4编码生效。

3. 常用客户端

很好用的工具是SQLyog,也是收费软件,也有破解版。它最大的特色是提供可编辑的SQL文本框,可以选择部分SQL语句执行,按F8快捷键就执行,非常方便。

常用的软件还有Navicat,这是收费软件,有很多破解版绿色版下载。它支持MySQL、Oracle、SQLite、PostgreSQL等。一般使用它来浏览数据块和表,很方便地创建表、修改表,支持流量表数据和修改,命令行也很方便。

4. emoji标签

emoji表情可以由一个unicode或两个unicode来表示,例如:

\u2764 表示的是

\ud83d\ude04 表示的是 😄 可以在这个页面测试转换。

mysql默认的utf8编码,只支持第一种单个unicode的存储。测试一下数据库字段是否支持emoji表情:复制这个表情😄到数据库字段值中,如果可以保存,说明已经支持utf8mb4编码了

如果还不能成功保存,那么(配置按照这篇文章进行):

  1. 配置my.cnf
[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

然后重启mysql。

  1. 实际上,只要修改表的字段为utf8mb4编码即可(对于已有的表也一样)。对于表,也可以整个表设置为utf8mb4编码,以后的字段就会默认是utf8mb4编码(当然也可以就某个单独字段修改为utf8)。

  2. 【非常重要】要重新链接mysql客户端连接,例如客户端重新连,Java程序重启。

  3. 若是Java,要确保mysql connector版本高于5.1.13。经过测试,5.1.30版本有问题,别用。当前最新的5.1.41版本没有问题。

5. 容易错的设置

  • mysql的decimal常用来表示准确的小数,在新建该字段时,要注意它的默认小数点数位是0位,即相当于整数。

6. 常见问题

Waiting for table metadata lock

常出现在修改表DDL时锁住,解决方法是kill掉卡在这个状态的session进程。相关文章:12

this is incompatible with sql_mode=only_full_group_by

一种简单的修改方式是mysql执行:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

最好重新启动应用就可以了。

如果要彻底关闭only_full_group_by,则修改/etc/mysql/my.cnf,增加:

[mysqld]  
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后重启mysql。

文档更新时间: 2018-11-10 17:04   作者:nick