MySQL杂谈之一

MySQL杂谈第一部分:root密码重置、乱码问题、主从备份、表情符支持、常见问题等。

重置root密码

忘记MySQL root用户密码时可以用下面的方法找回:

  • 取决于系统注册服务的方式,选择一个命令停止MySQL服务:
service mysqld stop # init.d
systemctl stop mysql.service # systemd
  • 用下面的命令重启
mysqld_safe --skip-grant-tables &
  • root用户身份免密码进入MySQL终端并修改密码
mysql -u root mysql
  • 进入MySQL终端后,重置root用户密码(将new_pwd换成你的新密码)
use mysql;
update user set password=password('new_pwd') where user='root'; flush privileges; exit;
  • 重启MySQL服务器
service mysqld stop # init.d
systemctl start mysql.service # systemd

乱码问题

出现乱码一般是因为服务器不支持当前字符(比如用ascii编码集存中文),或服务器编码与连接编码不一致。[如果是web应用,还应该先检查下当前应用默认编码是否正确(对于Java应用可通过JVM参数-Dfile.encoding=UTF8设置)。]

遇到乱码时,首先要确认服务器的默认编码已正确设置:

show variables where variable_name like 'character%';

返回如下查询结果:

| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |

如果发现默认编码有问题,修改my.cnf,记得修改后要重启MySQL服务:

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

在MySQL终端里测试是否还有乱码现出,必要时用下面的命令修改本次session使用的编码,以utf8为例:

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

程序里出现乱码时,通常只需要在Jdbc连接参数添加useUnicode=true即可解决,以HickariCP为例:

HikariConfig#setJdbcUrl(".....&useUnicode=true")

当我们的数据库使用的不是my.cnf配置的默认编码时,在建立连接时应先调用SET names xxx,比如:

HikariConfig#setConnectionInitSql("SET names utf8mb4")

主从备份

注意:本节内容只适用于使用InnoDB的数据库。

创建主从备份的步骤,

  • 在主库上运行(将db_host, db_user分别换成你的主库IP,用户名。db1, db2, db3是需要备份的数据库的名字。
mysqldump -h'db_host' -u'db_user' --quick --routines --master-data=2 --single-transaction --databases db1 db2 db3 > mydb.dump 

如果出现mysqldump: Error: Binlogging on server not active则可能是你的主库还没有启用binlog。这时需要修改主库的my.cnf文件,在[mysqd]部分添加如下配置

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
  • 在从库上导入数据库。
mysql -h'slave_db' -u'slave_db_user' -p < mydb.dump
  • 修改从库的my.cnf文件,在[mysqld]下面增加server-id=2。注意其值必须与主库或其它从库的不一样。重新加载配置sudo service mysql reload
  • 在主库上授权:
grant replication slave on *.* to [email protected]'x.x.x.x' identified by 'xxxx';
flush privileges;
  • 在从库上启动备份:打开之前导出的备份文件mydb.dump,找到CHANGE MASTER TO一行,如
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
  • 使用主库的信息补全这个命令,并在从库上执行:
change master to master_log_file='mysql-bin.000001',master_log_pos=107,master_host='x.x.x.x', master_port=xxx,master_user='xxx',master_password='xxx';
  • 在从库启动备份
slave start
  • 检查从库的备份状态
show slave status\G;

主键、唯一键与外键

建立主键、唯一键与外键约束也就意味着建立了索引。用show index from mytable;查看某个表上的索引。

唯一键创建与删除

-- 创建
alter table t1 add constraint uk_mf_rf unique key (rtfile);
-- 删除方法1
alter table t1 drop index uk_t1_rf;
-- 删除方法2
drop index uk_t1_rf on t1;

外键创建与删除

注意删除时要执行两条命令,若只执行drop foreign key那么原来的外键列变为普通索引列。

-- 创建
alter table t2 add constraint fk_test_mid foreign key (mid) references t1(id);
-- 删除
alter table t2 drop foreign key fk_test_mid;
alter table t2 drop index fk_test_mid;

自增主键重用问题

自增主键重用是指删除数据后,新生成的主键与之前删除的主键相同。服务器重启时就有可能出现这种情况,所以要尽量避免使用如下的语句移动数据:

insert trgt_table select * from src_table d where ....

让MySQL支持表情符 - 将数据库类型转换为utf8mb4

MySQL的utf8并不能表示所有的UTF8字符集,比如这个符号:👽。解决办法是使用utf8mb4表示字符串。下面介绍如何修改,使utf8mb4成为默认字符集。

  • 修改数据库、表格、字符串类型字段的编码。
-- 修改数据库的编码
alter database mydb character set = utf8mb4 collate = utf8mb4_unicode_ci;
-- 修改表格的编码
alter table mydb convert to character set utf8mb4 collate utf8mb4_unicode_ci;
-- 修改字段的编码
alter table mydb change summary summary text character set utf8mb4 collate utf8mb4_unicode_ci;
  • 修改my.cnf
[mysqld]
character_set_client_handshake = FALSE
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
[client]
default_character_set = utf8mb4
[mysql]
default_character_set = utf8mb4
  • 使用service mysql restart重启MySQL服务后,在MySQL终端里查看是否修改成功:
show variables where variable_name like 'character\_set\_%' or variable_name like 'collation%';
  • 最后不要忘记修复数据库:
mysqlcheck -u root -p --auto-repair --optimize --all-databases

注1: 使用utf8mb4编码后,导出数据库时最好给mysqldump加上–default-character-set=utf8mb4参数。

注2: 实际应用中,往往只需要让一个字段支持全UTF8字符集,

  • 这时只需要修改此字段的字符集:
alter table mydb change summary summary text character set utf8mb4 collate utf8mb4_unicode_ci;
  • 然后在连接数据库时,指定默认编码:
SET names utf8mb4

关于数据库连接池

创建数据库连接池的注意事项:

  • 如果系统即有耗时的数据库操作,也有许多短暂的数据库操作,最好创建两个独立的连接池。
  • 如果系统有许多耗时的数据库操作,通常会在应用里使用队列限流。连接池的大小应该与这个队列的大小匹配。

常见问题与错误处理

mysqldump含有binary或blob类型的数据库

如果数据库含有binaryblob等类型的字段,在导出时应该给mysqldump加上–hex-blob参数。否则导出的sql会出现乱码,导入时也可能出现错误。

mysqldump --hex-blob --default-character-set=utf8mb4 --single-transaction -h xx.xx.xx.xx mydb -r mydb.dump

MySQL 1449 Error: The user specified as a definer...

这个问题通常因为导入含有trigger/procedure的数据库时,创建者不存在引起的。导入数据库后建立需要的用户即可:

grant all on *.* to 'my_user'@'%' identified by 'my_pwd'; flush privileges;

character与collation的区别

可以通过MySQL命令show character set;查看服务器支持的所有字符集(character set)与对应的默认collation

  • character表示字符编码,含有中文的数据库通常会使用utf8utf8mb4utf8mb4最多可使用4个字节表示一个字符,而utf8最多可使用3个字节表示一个字符。
  • collation用于定义MySQL对比字符串的规则,常见的有三种:
    1. _general表示按字符比较,区分大小写;
    2. _genearal_ci表示按字符比较,区分大小写;
    3. _bin表示按字节比较,通常用于只需要判断等或不等的字段。

限制访问

修改my.cnf,设置允许访问的客户端IP:bind-address=your-host-ip。如果MySQL服务器暴露在外网,强烈建议使用此配置增强安全性。

参考

留言