Questions in category: MySQL (MySQL)
软件 >> MySQL

1. 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 张表.

 

 

2. 系统中已经安装了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)


 

3. 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

4. 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

 

5. 下载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

 

6. WampServer 设置

Posted by haifeng on 2014-05-04 14:45:01 last update 2014-05-04 15:32:19 | Answers (0) | 收藏


WampServer 中的 MySQL 设置文件位于

C:\wamp\bin\mysql\mysql5.5.24\my.ini:

打开后找到 [mysqld]  加入下面两行, 然后重新启动

character-set-server=utf8
collation-server=utf8_general_ci

7. mysql 设置字符集

Posted by haifeng on 2014-05-02 22:25:47 last update 2014-05-04 15:28:41 | Answers (0) | 收藏


mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

8. 在 Shell 下执行 mysql 的 SQL 查询.

Posted by haifeng on 2012-10-08 15:14:19 last update 2012-10-08 15:20:06 | Answers (0) | 收藏


[haifeng@arch ~]$ mysql -u user -p -e \'SQL statements;\' database

其中

-u : 指定 mysql 数据库的用户名
-p : 提示输入密码
-e : 执行 sql 查询
database : 指定那个数据库


Reference

http://www.cyberciti.biz/faq/run-sql-query-directly-on-the-command-line/

9. 学习 MySQL 的站点

Posted by haifeng on 2012-09-24 15:32:46 last update 2013-12-17 14:50:26 | Answers (0) | 收藏


http://www.mysql.cn/

http://www.mysqltutorial.org/

http://codingrecipes.com

http://www.tutorialspoint.com/mysql

 

sqlfiddle.com 提供了在线测试工具, 来测试和分享数据库的问题与解决方案.

http://sqlfiddle.com/

 

SQL 学习

http://www.sql-tutorial.com/

SQL 在线测试

http://www.sqlquiz.com/

 

 

10. MySQL 中的保留字

Posted by haifeng on 2012-09-18 09:15:12 last update 2012-09-18 09:16:25 | Answers (0) | 收藏


ADD
ALTER
COLUMN
CREATE
DESC/DESCRIBE
DISTINCT
DROP
FROM
LIMIT
ORDER BY
PRIMARY KEY
SELECT
SHOW
TABLE
UNIQUE
WHERE