##

小技巧帮你用好MySQL

1、巧用prompt

有时候我们在执行一些操作时，需要知道我们在哪个库，还有执行时间等等。比如

drop table t1;

但是t1表同时存在db01和db02两个数据库，也有可能出现误删除的情况.当然也可以在表前加库名来解决，比如db01.t1;熟悉Oracle的同学也许经常用到PS1这环境变量，那么MySQL也有类似的方法，这里给出三种方法，可以必要的时候帮助我们。

1.1、登录mysql直接设置prompt

mysql> prompt \u@\h [\U+1F4C1\d] (\U+1F551\R:\m:\s) >
PROMPT set to '\u@\h [📁\d] (🕑\R:\m:\s) >'

root@localhost [(none)] (15:54:44) >use db01
root@localhost [db01] (15:54:51) >

1.2、配置参数文件

#这种操作需要注意，mysql只能识别/etc/my.cnf这个路径下的prompt，如果自定义参数文件路径，那么需要如下操作：

[root@mydb01 ~]# vi /mysql/my.cnf
[mysql]
prompt="\u@\h [<U+1F4C1>\d] (<U+1F551>\R:\m:\s) >"

[root@mydb01 ~]# mysql --defaults-file=/mysql/my.cnf
root@localhost [(none)] (16:00: ) >use mysql
root@localhost [mysql] (16:00: ) >

指定参数文件路径登录

1.3、使用用户环境变量

[root@mydb01 ~]# vi ~/.bash_profile
export MYSQL_PS1="\u@\h [📁\d] (🕑\R:\m:\s) > "

[root@mydb01 ~]# source ~/.bash_profile

2、参数文件校准

有时候我们更改了my.cnf文件中的参数，但是因为拼写错误等因素，导致数据库无法启动。那么我们需要去检查错误日志，进行更改。并再次启动数据库。这里mysql在8.0.16提供了一个参数来校准参数文件。以帮助我们检查参数文件中参数的正确与否。

vi /mysql/my.cnf
max_connection = 800

此时启动数据库是失败的，错误日志中也提示我们如下信息：

2023-08-06T16:12:21.188933+08:00 0 [ERROR] [MY-000067] [Server] unknown variable 'max_connection=800'.
2023-08-06T16:12:21.189026+08:00 0 [ERROR] [MY-010119] [Server] Aborting

那么我们可以在启动之前使用该参数

[root@mydb01 ~]# mysqld --defaults-file=/mysql/my.cnf --validate-config
2023-08-06T16:14:41.674669+08:00 0 [Note] [MY-013667] [Server] Error-log destination "stderr" is not a file. Can not restore error log messages from previous run.
2023-08-06T16:14:41.671499+08:00 0 [Warning] [MY-011070] [Server] 'binlog_format' is deprecated and will be removed in a future release.
2023-08-06T16:14:41.671519+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_type' is deprecated and will be removed in a future release. Please use replica_parallel_type instead.
2023-08-06T16:14:41.671524+08:00 0 [Warning] [MY-011069] [Server] The syntax '--replica-parallel-type' is deprecated and will be removed in a future release.
2023-08-06T16:14:41.671534+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_workers' is deprecated and will be removed in a future release. Please use replica_parallel_workers instead.
2023-08-06T16:14:41.671541+08:00 0 [Warning] [MY-011068] [Server] The syntax 'slave_preserve_commit_order' is deprecated and will be removed in a future release. Please use replica_preserve_commit_order instead.
2023-08-06T16:14:41.671557+08:00 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2023-08-06T16:14:41.671569+08:00 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2023-08-06T16:14:41.671575+08:00 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2023-08-06T16:14:41.671741+08:00 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2023-08-06T16:14:41.678312+08:00 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2023-08-06T16:14:41.678417+08:00 0 [Note] [MY-010747] [Server] Plugin 'ndbcluster' is disabled.
2023-08-06T16:14:41.678457+08:00 0 [Note] [MY-010747] [Server] Plugin 'ndbinfo' is disabled.
2023-08-06T16:14:41.678472+08:00 0 [Note] [MY-010747] [Server] Plugin 'ndb_transid_mysql_connection_map' is disabled.
2023-08-06T16:14:41.678848+08:00 0 [ERROR] [MY-000067] [Server] unknown variable 'max_connection=800'.
2023-08-06T16:14:41.678873+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2023-08-06T16:14:41.678987+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'MyISAM'
2023-08-06T16:14:41.679012+08:00 0 [Note] [MY-010733] [Server] Shutting down plugin 'CSV'

甚至帮我们打印了很多不兼容的参数，这是一个有用的小工具，尤其我们从5.7升级8.0的时候，沿用以前的参数会导致很多问题，当然也可以使用mysql shell，这里不展开。

(也可以在参数后面加上–log-error-verbosity=2，以显示更多的信息)