MySQL杂谈之一

mysql

  1. 重置root密码
  2. 乱码问题
  3. 主从备份
  4. 主键、唯一键与外键
    1. 唯一键创建与删除
    2. 外键创建与删除
    3. 自增主键重用问题
  5. 让MySQL支持表情符 - 将数据库类型转换为utf8mb4
  6. 关于数据库连接池
  7. 常见问题与错误处理
    1. mysqldump含有binary或blob类型的数据库
    2. MySQL 1449 Error: The user specified as a definer...
    3. character与collation的区别
    4. 限制访问
  8. 参考

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 username@'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类型的数据库

如果数据库含有binary或blob等类型的字段，在导出时应该给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表示字符编码，含有中文的数据库通常会使用utf8或utf8mb4。utf8mb4最多可使用4个字节表示一个字符，而utf8最多可使用3个字节表示一个字符。
  • collation用于定义MySQL对比字符串的规则，常见的有三种：
    1. _general表示按字符比较，区分大小写;
    2. _genearal_ci表示按字符比较，不区分大小写;
    3. _bin表示按字节比较，通常用于只需要判断等或不等的字段。

限制访问

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

参考

Comment