MySQL指南——环境搭建

macOS系统

安装

下载地址,下载dmg直接安装,注意选择macOS对应的系统,一个很重要的分界点是10.13系统,是不兼容之前的。

下载dmg文件,注意在成功后,记下临时密码!

如何修改密码

或者在安装时,会提示输入root密码:12345678

另外,安装之后,会在系统偏好设置中,增加MySQL的的设置,可以启动停止MySQL服务。

定义别名

1
2
$ alias mysql=/usr/local/mysql/bin/mysql
$ alias mysqladmin=/usr/local/mysql/bin/mysqladmin

Unbutu 系统

安装

1
2
3
$ apt-get install mysql-server
$ apt-get isntall mysql-client
$ apt-get install libmysqlclient-dev

启动MySQL

1
$ service mysql start  

查看是否已经启动该服务

1
$ sudo netstat -tap | grep mysql

结果如下,证明MySQL安装成功

tcp 0 0localhost:mysql : LISTEN 25671/mysqld

进入MySQL

1
$ mysql -u root -p 

修改数据库字符集

  1. 查询数据库编码方式(红框部分需要修改)
1
mysql> show variables like "char%";

  1. 修改红色标注的字符集

在Ubuntu Server 16.04 之后,使用 apt 安装的 MySQL Server 会默认对配置文件进行分类。 /etc/mysql/my.cnf 负责声明配置文件的引用目录。

1
2
!includedir /etc/mysql/conf.d/                                                                                                            
!includedir /etc/mysql/mysql.conf.d/

其中引入两个配置文件。所以配置需要在这两个文件中配置。

修改mysqld.cnf文件,其路径为/etc/mysql/mysql.conf.d

1
$ vi /etc/mysql/mysql.conf.d/mysqld.cnf 

将字符集修改为utf8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#                                                                                                                                         
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
character-set-server = utf8mb4
collation-server = utf8mb4_bin

Ubuntu Server 14.04 之前的版本,MySQL 的配置文件默认不分类。通常情况下,/etc/mysql/my.cnf 文件中会包含 MySQL Server 的全部配置信息。

1
$ vi /etc/mysql/my.cnf 

将内容修改为:

1
2
3
4
5
6
7
8
#在[client]标签下,增加客户端的字符编码                                                                                                   
[client]
default-character-set=utf8mb4

#在[mysqld]标签下,增加服务器端的字符编码
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin

远程访问

1
$ vi /etc/mysql/mysql.conf.d/mysqld.cnf 

1) 同时,注释bind-address字段,就可以同外部访问数据库:

2)授权

授权如下,注意root为用户,password为用户。

1
2
3
$ mysql -uroot -p
mysql> grant all privileges on . to 'root'@'%' identified by 'password';
mysql> flush privileges;

另外,针对某一个数据库可以使用:

1
2
3
4
5
6
>mysql -uroot -p;
>show databases;
>use mysql;
>update user set host='%' whereuser='root' and host='localhost';
#将host设置为%表示任何ip都能连接mysql,当然您也可以将host指定为某个ip
>flushprivileges; #刷新权限表,使配置生效

3)重启服务器

重启服务

1
2
3
$ sudo service mysql restart
#or
$ /etc/init.d/mysql restart

然后就可以通过Navicat直接登录了

修改密码

Mac

  1. 苹果->系统偏好设置->最下边点MySQL

      在弹出窗口关闭MySQL服务。
    
  2. 关闭权限验证

    在终端输入:

    1
    2
    3
    4
    5
    $ cd /usr/local/mysql/bin/
    $ sudo ./mysqld_safe --skip-grant-tables
    Password:
    Logging to '/usr/local/mysql-5.7.21-macos10.13-x86_64/data/Weng-MacPro.local.err'.
    2018-03-25T02:50:43.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql-5.7.21-macos10.13-x86_64/data

    以上执行完,MySQL会默认重启,假如没有重启,手动在系统偏好设置中启动服务。

  3. 修改密码

    另外打开一个终端,输入:

    1
    2
    3
    4
    5
    6
    $ mysql
    mysql> FLUSH PRIVILEGES;
    # Query OK, 0 rows affected (0.01 sec)
    mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('ipad');
    # Query OK, 0 rows affected, 1 warning (0.01 sec)
    mysql>

Unbutu

  1. 首次安装后密码会输入错误

    一般安装之后会生成随机密码: root@localhost:Vgt:N<P%n8S/

但是如果直接在terminal中输入:

1
$ mysql -u root -p

随后输入密码始终报错:

Enter password:

ERROR 1045 (28000): Access denied for user’root’@’localhost’ (using password: YES)

  1. 解决方法:输入
1
2
$ mysql -u root -h 127.0.0.1 -p
Enter password: (enter the random password here)

进入mysql,接着修改密码。

1
> ALTER USER 'root'@'localhost'IDENTIFIED BY 'new-password';

将new-password替换成新密码,再次进入就可以输入:

$ mysql-u root -p 输入密码会正确进入。

参考:stackover

MySQL字符设置

在数据的存储上,MySQL提供了不同的字符集支持。而在数据的对比操作上,则提供了不同的字符序支持。

MySQL提供了不同级别的设置,包括server级、database级、table级、column级,可以提供非常精准的设置。

什么是字符集、字符序?简单的来说:

  1. 字符集(character set):定义了字符以及字符的编码。
  2. 字符序(collation):定义了字符的比较规则。

1. 查看支持的字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
.........

支持where及like子句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show character set where charset="utf8";
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
1 row in set (0.01 sec)

mysql> show character set like "utf8%";
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.01 sec)

2. 查看支持的字符序

1
2
3
4
5
6
7
mysql> show collation where charset='utf8';
+--------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+--------------------------+---------+-----+---------+----------+---------+---------------+
| utf8_bin | utf8 | 83 | | Yes | 1 | PAD SPACE |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 | PAD SPACE
.......

3. server的字符集、字符序

若要修改character_set_server字符:

/etc/mysql/my.cnf 增加如下:

1
2
[mysqld]
character-set-server=utf8mb4

如果没有my.cnf,手动添加:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/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]
character-set-server=utf8mb4

#备份数据库需要更改的
[mysqldump]
user=root
password=12345678

# 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 = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# 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

当然,可以针对上面的设置更多的变量:

• 系统变量:

character_set_server:默认的内部操作字符集

character_set_client:客户端来源数据使用的字符集

character_set_connection:连接层字符集

character_set_results:查询结果字符集

character_set_database:当前选中数据库的默认字符集

character_set_system:系统元数据(字段名等)字符集

4. database的字符集、字符序

1
mysql> ALTER DATABASE MyDB CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'

5. table的字符集、字符序

创建表格时指定:

1
2
3
4
5
> CREATE TABLE My_Table_Name (
twitter_id_str VARCHAR(255) NOT NULL UNIQUE,
twitter_screen_name VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
.....
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

修改表格:

1
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4;

针对某一column如下:

1
mysql> ALTER TABLE database.table MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

错误

  1. java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x91\xBD\xF0\x9F…’

    utf-8编码可能2个字节、3个字节、4个字节的字符,但是MySQL的utf8编码只支持3字节的数据,而移动端的表情数据是4个字节的字符。如果直接往采用utf-8编码的数据库中插入表情数据,MySQL将出错,将其修改为utf8mb4。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    mysql> show variables like '%char%';
    +--------------------------+-----------------------------------------------------------+
    | Variable_name | Value |
    +--------------------------+-----------------------------------------------------------+
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8mb4 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | utf8mb4 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/local/mysql-5.7.22-macos10.13-x86_64/share/charsets/ |
    +--------------------------+-----------------------------------------------------------+

    表格如上。

    并且在针对此次出现问题的列修改:

参考

  1. Chapter 10 Character Sets, Collations, Unicode
  2. https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html