Questions in category: MySQL (MySQL)
软件 >> MySQL
<[1] [2] >

1. 优化MySQL

Posted by haifeng on 2019-05-01 10:40:01 last update 2019-05-01 13:57:15 | Answers (0) | 收藏


要进行优化, 必须先知道问题在哪里.

如果使用 phpMyAdmin, 可以查看【状态】-->【建议】


开启慢查询

 

一般建议启动慢查询日志, 因为 MySQL 默认是关闭的.

如果是 Linux 系统, 则配置文件 my.cnf 一般位于 /etc/mysql/my.cnf

使用 root 用户或其他有权限的账号编辑此文件, 找到 [mysqld] (如果使用 vim, 则键入 /\\[mysqld\\], 而不是 /[mysqld])

在这一段中找到

#log_slow_queries        = /var/log/mysql/mysql-slow.log
#log_query_time = 2

将前面的注释符#去掉, 保存.

注意这里 log_query_time = n, n 指的是秒数, 超过这个时间的查询语句将被记录. 根据具体使用的环境, 一般设置 n为 1~5秒

 

查询缓存使用情况

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 3        |
| Qcache_free_memory      | 16373216 |
| Qcache_hits             | 322      |
| Qcache_inserts          | 283      |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 59       |
| Qcache_queries_in_cache | 175      |
| Qcache_total_blocks     | 391      |
+-------------------------+----------+
8 rows in set (0.00 sec)

 

References:

https://www.cnblogs.com/sunss/archive/2010/10/13/1850407.html

 


 

InnoDB 日志文件大小设置的问题

 

问题:
InnoDB 日志文件大小不合适,此关系到 InnoDB 缓冲池。

建议:
在一个 InnoDB 表写入很多的系统上您应该将 innodb_log_file_size 设为 innodb_buffer_pool_size 的 25% 。因为该值越大,当数据库崩溃时恢复的时间就越长,所以该值不应高于 256 MB 。请注意您不能简单的修改该变量的值。您需要关闭服务器,删除 InnoDB 日志文件,在 my.cnf 中设置新的值,启动服务器,一切正常后再检查错误日志。参见这篇博客

现状:
您的 InnoDB 日志大小为 InnoDB 缓冲池大小的 3.9%,该值不应低于 20%

使用的变量/公式:
innodb_log_file_size / innodb_buffer_pool_size * 100

检验:
value < 20 && innodb_log_file_size / (1024 * 1024) < 256

mysql> show variables like 'innodb_log%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_log_buffer_size    | 8388608 |
| innodb_log_file_size      | 5242880 |
| innodb_log_files_in_group | 2       |
| innodb_log_group_home_dir | ./      |
+---------------------------+---------+
4 rows in set (0.00 sec)

mysql> show variables like 'innodb_buffer%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| innodb_buffer_pool_instances | 1         |
| innodb_buffer_pool_size      | 134217728 |
+------------------------------+-----------+
2 rows in set (0.00 sec)


打开 /etc/mysql/my.cnf
找到 InnoDB 所在行(一些描述),
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

增加下面两行

innodb_log_file_size=32M
innodb_buffer_pool_size=128M

关闭mysqld,
# service mysqld stop

删除 /var/lib/mysql/ 下面的 ib_logfile0, ib_logfile1, ..., ib_logfileN
# cd /var/lib/mysql
# rm ib_logfile0 ib_logfile1

启动 mysqld
# service mysqld start

 

2. 修改ubuntu系统中MySQL用户debian-sys-maint的权限

Posted by haifeng on 2019-05-01 10:09:44 last update 2019-05-01 13:08:06 | Answers (0) | 收藏


在 Debian 或 Ubuntu 系统中, 如果安装了 MySQL, 则自动设置了一个超级用户 debian-sys-maint .

其密码存储在 /etc/mysql/debian.cnf 中. 当MySQL出现问题, 也可以使用 debian-sys-maint 进行维护.

 

可以使用 phpMyAdmin 查看所有用户, 发现 debian-sys-maint 拥有的权限与 root 一样, 是 ALL PRIVILEGES

因此, 为安全起见, 有必要更改其权限. 下图是默认拥有所有权限.

 

由于 debian-sys-maint 用户只是偶尔用于管理MySQL之用, 因此只需赋予其下面的权限.

SUPER, PROCESS, RELOAD, SHUTDOWN

使用 phpMyAdmin 图形界面操作, 或者在 MySQL 中执行下面的命令.

REVOKE ALL PRIVILEGES ON *.* FROM 'debian-sys-maint'@'localhost';

REVOKE GRANT OPTION ON *.* FROM 'debian-sys-maint'@'localhost';

GRANT RELOAD, SHUTDOWN, PROCESS, SUPER ON *.* TO 'debian-sys-maint'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

 

 

References:

http://tech.it168.com/a2011/0704/1212/000001212802.shtml

 

3. MySQL 中授权

Posted by haifeng on 2018-11-29 09:14:53 last update 2018-11-29 09:18:21 | Answers (0) | 收藏


首先创建一个用户,

mysql> CREATE USER haifeng@localhost IDENTIFIED BY 'TheSecretPasswd';

 

然后赋予其权限, 比如使得用户 haifeng 可以管理 info 和 math 两个数据库.


mysql> GRANT ALL ON info.* TO haifeng@localhost;

mysql> GRANT ALL ON math.* TO haifeng@localhost;
 

也可以一开始就使用 GRANT 语句创建用户并授予权限:

mysql> GRANT ALL ON info.* TO haifeng@localhost IDENTIFIED BY 'TheSecretPasswd';

 

一般的用法是:

GRANT privileges ON db.table TO user@host [IDENTIFIED BY 'password'] [, user [IDENTIFIED BY 'password']] [WITH GRANT OPTION];

 

这里 privileges 表示赋予用户的权限类型.

 

4. 将数据库 ss_algebra 重命名为 summer_school

Posted by haifeng on 2018-10-31 08:06:51 last update 2019-01-11 10:35:03 | Answers (2) | 收藏


将数据库 ss_algebra 重命名为 summer_school.

注意 ss_algebra 中已经有很多表, 并且存了很多数据.

 


[hint]

重命名某张表可以使用 RENAME 或 RENAME TO

以下两个 SQL 语句都可以将原来表的名称 old_table 重命名为 new_table.

ALTER TABLE old_table RENAME TO new_table;

ALTER TABLE old_table RENAME  new_table;

 

也可以使用

RENAME TABLE  old_table TO new_table;

5. 在数据库 math 中显示所有以 "ss_" 开头的数据表.

Posted by haifeng on 2018-10-31 07:56:57 last update 2018-10-31 08:20:16 | Answers (1) | 收藏


(1) 在数据库 math 中显示所有以 "ss_" 开头的数据表.

(2) 显示所有以 "test_" 开头的数据库.

6. MySQL 8 中的 sys 数据库

Posted by haifeng on 2018-09-13 15:59:09 last update 2018-09-13 15:59:57 | Answers (0) | 收藏


MySQL 8.0.12 版本中有一个名为 sys 的数据库, 其中有 101 张表.

 

 

7. 系统中已经安装了MySQL, 如何在U盘中安装另一个MySQL服务?

Posted by haifeng on 2018-09-13 15:44:42 last update 2018-09-13 15:45:40 | Answers (1) | 收藏


我们假设某一台电脑的 Windows 7 系统中已经安装了 MySQL5.5. 

随身携带了一个 U 盘, 其中安装了 MySQL community 8.0.12 zip 版本, 且已经配置好.  (详见问题2159)


 

8. mysql8.0 的密码问题Unable to load authentication plugin 'caching_sha2_password'

Posted by haifeng on 2018-09-05 17:55:53 last update 2018-09-05 17:55:53 | Answers (0) | 收藏


 

Unable to load authentication plugin 'caching_sha2_password'

MySQL在8.0后验证方式由mysql_native_password变为caching_sha2_password

 

解决办法

mysql> alter user root@localhost identified with mysql_native_password by 'thenewpasswd';
Query OK, 0 rows affected (0.03 sec)

 

 


References:

https://blog.csdn.net/zeal4rea/article/details/80542658

9. net start mysql

Posted by haifeng on 2018-09-05 15:54:58 last update 2018-09-05 16:02:09 | Answers (0) | 收藏


F:\programs\mysql-8.0.12-winx64\bin>net start mysql
MySQL 服务正在启动 ..
MySQL 服务无法启动。

服务没有报告任何错误。

请键入 NET HELPMSG 3534 以获得更多的帮助。

 


出现这种情况可能的原因是:

(1) 系统中已经有 MySQL 在运行.  比如已经安装了 WampServer, 则可以先停止其所有服务, 然后执行上面的 net start mysql 命令.

(2) 如果系统中未安装其他的 MySQL 系统, 则参考问题2159, 执行以下命令

mysqld --initialize-insecure

这将自动生成无密码的 root 用户.

然后执行

mysqld --install

net start mysql

 

10. 下载MySQL community 8.0.12 zip 版本并配置

Posted by haifeng on 2018-09-04 22:50:32 last update 2018-09-05 17:52:41 | Answers (0) | 收藏


(1) 下载 mysql-8.0.12-win64.zip

解压缩到某个文件夹, 比如 U 盘 F:\programs\mysql-8.0.12-winx64

一般没有 data 这个文件夹, 也没有 my-default.ini 这个文件.

新建一个 my.ini 文件, 或者从其他地方拷贝一个 my-default.ini 文件到当前目录 (F:\programs\mysql-8.0.12-winx64) 然后改名为 my.ini

my.ini 的内容为:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
basedir = F:/programs/mysql-8.0.12-winx64
datadir = F:/programs/mysql-8.0.12-winx64/data
port = 3306
# server_id = .....

bind-address = 127.0.0.1


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

 

显然拷贝自 mysql 5.6 版本.

 

(2)以管理员身份打开命令提示符( cmd 命令行窗口)

执行下面的命令:

mysqld --initialize-insecure

这将自动生成无密码的 root 用户.

然后执行

mysqld --install

net start mysql

 

 

 


References:

https://blog.csdn.net/liuwenbiao1203/article/details/53243487

 

<[1] [2] >