centos7 安装 MySQL数据库 及 MySQL 存储表情

MySQL安装

安装包地址

安装包历史地址

安装MySQL

安装依赖

 yum install -y cmake make gcc gcc-c++ libaio ncurses ncurses-devel

安装MySQL

下载解压

cd /guaoran/installPack
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz 

将解压的复制到 /usr/local/mysql 目录

mkdir -pv /usr/local/mysql
cp -r mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql/

添加系统mysql组和mysql用户

groupadd mysql
useradd -r -g mysql mysql

安装数据库

切换到 mysql 目录，并修改该目录拥有者为MySQL用户

cd /usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/
chown -R mysql:mysql ./
mkdir -pv /guaoran/mysql/data
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64 --datadir=/guaoran/mysql/data
mkdir -pv /guaoran/mysql/data/log

安装后会生成临时密码，修改密码时会用到,最后面是密码

[Note] A temporary password is generated for root@localhost: ejStoWO/i7Fs

配置my.cnf

vi /etc/my.cnf 设置数据库，表，字段默认是 utf8mb4 ，支持存储表情

[client]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

basedir=/usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64
datadir=/guaoran/mysql/data
socket=/tmp/mysql.sock
#不区分大小写 
lower_case_table_names = 1

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

###慢查询
#slow_query_log=on
#long_query_time=1
#log_output='TABLE'

## 查询缓存开启：
### 0：不启用查询缓存，默认值
### 1：启用查询缓存，加上 SQL_NO_CACHE将不缓存
### 2：启用查询缓存，加上 SQL_CACHE 后符合条件将缓存
#query_cache_type=1

## 最大连接数
max_connections=2000

## 默认的隔离级别
#transaction_isolation = REPEATABLE-READ

max_allowed_packet=128M

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[mysql]
default-character-set=utf8mb4

添加开机启动

cp /usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/support-files/mysql.server  /etc/init.d/mysqld
vi /etc/init.d/mysqld

查看 序号 :set number

在46行-47行 修改

basedir=/usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64
datadir=/guaoran/mysql/data

启动MySQL ，加入开机启动

service mysqld start 
chkconfig --add mysqld 

创建引用链接

ln -s /usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin/mysql /usr/bin/
ln -s /usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin/mysqldump /usr/bin/
ln -s /usr/local/mysql/mysql-5.7.24-linux-glibc2.12-x86_64/bin/mysqldumpslow /usr/bin/

查看引用

cd /usr/bin
find mysql*  -exec ls -l {} \;

修改密码

临时密码是在安装数据库时生成的密码

使用临时密码进行登录：

mysql -u root -p

登录成功修改新密码为 root ：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

问题

远程连接出现Error 1130问题

本地登录MySQL mysql -uroot -proot

use mysql;
select host, user from user where user = 'root';

发现 host = localhost ,所以要改成 %

use mysql;
update user set host = '%' where user = 'root';
flush privileges;

mysql 存储表情

修改 MySQL 服务器 字符编码 utf8mb4

查看字符集情况

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

修改字符集 vim /etc/my.cnf

[client]
default-character-set=utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

[mysql]
default-character-set=utf8mb4

重启MySQL

service mysqld restart

再次查看字符集，已经改变

修改已经存在库表编码

1. 修改库的编码为 utf8mb4

2. 修改表的编码为 utf8mb4

   ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
   

3. 修改表字段编码为 utf8mb4

   ALTER TABLE 表名 CHANGE 字段名 字段名 该字段原来的数据类型 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;