Linux_Mysql 安装笔记

环境：Vmware Workstation 10，CentOS-7-x86_64-DVD-1511.iso，Xshell 4.0，ip：192.168.216.140
http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm 对应版本5.6.36。
https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm对应版本5.7.18。

安装Mysql 5.6.36

[root@localhost ~]# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm #下载mysql5.6.36对应的yum repo源

[root@localhost ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm #rpm安装mysql的repo源

[root@localhost ~]# yum install mysql-community-server #yum 安装mysql

[root@localhost ~]# mysql -V #查看mysql版本号

mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper

[root@localhost ~]# service mysqld restart #service启动mysql

Redirecting to /bin/systemctl restart mysqld.service

[root@localhost ~]# mysql -uroot #登录mysql并设置root密码

mysql> set password for ‘root’@‘localhost’ = password(‘root’);
mysql> exit

[root@localhost ~]# mysql -uroot -p #重新登录mysql
Enter password:

安装Mysql 5.7.18
Mysql 5.7.18安装手册

[root@localhost ~]# yum list mysql-community-server #查看mysql安装信息

已加载插件：fastestmirror
Loading mirror speeds from cached hostfile

• base: mirrors.tuna.tsinghua.edu.cn
• extras: mirror.bit.edu.cn
• updates: mirrors.tuna.tsinghua.edu.cn
  错误：没有匹配的软件包可以列出

[root@localhost ~]# wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm #下载mysql5.7.18对应的yum repo源

[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm #rpm安装软件包

[root@localhost ~]# yum list mysql-community-server #再次查看mysql安装情况

已加载插件：fastestmirror
mysql-connectors-community | 2.5 kB 00:00:00
mysql-tools-community | 2.5 kB 00:00:00
mysql57-community | 2.5 kB 00:00:00
(1/3): mysql-connectors-community/x86_64/primary_db | 14 kB 00:00:00
(2/3): mysql-tools-community/x86_64/primary_db | 33 kB 00:00:00
(3/3): mysql57-community/x86_64/primary_db | 106 kB 00:00:00
Loading mirror speeds from cached hostfile

• base: mirrors.tuna.tsinghua.edu.cn
• extras: mirror.bit.edu.cn
• updates: mirrors.tuna.tsinghua.edu.cn
  可安装的软件包
  mysql-community-server.x86_64 5.7.18-1.el7 mysql57-community

[root@localhost ~]# yum install mysql-community-server mysql-community-devel #yum安装mysql

[root@localhost ~]# yum list mysql-community-server #再次查看mysql安装情况

已加载插件：fastestmirror
Loading mirror speeds from cached hostfile

• base: mirrors.tuna.tsinghua.edu.cn
• extras: mirror.bit.edu.cn
• updates: mirrors.tuna.tsinghua.edu.cn
  已安装的软件包
  mysql-community-server.x86_64 5.7.18-1.el7 @mysql57-community

[root@localhost ~]# service mysqld start #启动mysql

Redirecting to /bin/systemctl start mysqld.service

[root@localhost ~]# service mysqld status #查看mysql运行状态

Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 五 2017-06-09 10:59:39 CST; 10s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2761 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 2688 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2764 (mysqld)
CGroup: /system.slice/mysqld.service
└─2764 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

6月 09 10:59:36 localhost systemd[1]: Starting MySQL Server…
6月 09 10:59:39 localhost systemd[1]: Started MySQL Server.

[root@localhost ~]# grep ‘temporary password’ /var/log/mysqld.log #查看mysql root用户默认登录密码

2017-06-09T02:59:36.824856Z 1 [Note] A temporary password is generated for root@localhost: BG/qK8?skrzs

[root@localhost ~]# mysql -uroot -p #root用户，默认密码登录

Enter password: BG/qK8?skrzs
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.18

Copyright © 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘Root123456!’;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[root@localhost ~]# mysql -uroot -p #更改密码后重新登录

Enter password: Root123456!
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright © 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

配置Mysql远程访问

mysql> CREATE DATABASE test2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> grant select, insert, update, delete on 数据库.* to ‘用户’@’%’ identified by ‘密码’;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

---

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

mysql> show global variables like ‘wait_timeout’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| wait_timeout | 28800 |
±--------------±------+
1 row in set (0.00 sec)

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
wait_timeout=31536000
interactive_timeout=31536000

[root@localhost ~]# systemctl restart mysqld

mysql> show global variables like ‘wait_timeout’;
±--------------±---------+
| Variable_name | Value |
±--------------±---------+
| wait_timeout | 31536000 |
±--------------±---------+
1 row in set (0.00 sec)

---

数据库编码

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     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

查看特定数据库编码

mysql> show create database jforum;

+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| jforum   | CREATE DATABASE `jforum` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库编码

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
character-set-server=utf8 

[client]
default-character-set=utf8 

[mysql]
default-character-set=utf8

然后重启数据库即可

[root@localhost ~]# systemctl restart mysqld

Mysql 5.7升级到Mysql8.0

[root@localhost ~]# rpm -e mysql57-community-release

安装mysql8成功后，登录还是使用mysql5.7的root密码

[root@localhost ~]# mysql -uroot -p
Enter password:

mysql> SET GLOBAL innodb_fast_shutdown = 1;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@localhost ~]# mysql_upgrade -u root -p

Enter password: 新密码
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.

mysql8.0添加远程访问用户

mysql> use mysql

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> CREATE USER ‘visitor’@’%’ IDENTIFIED BY ‘Visitor@’;

Query OK, 0 rows affected (0.02 sec)

mysql> ALTER USER ‘visitor’@’%’ IDENTIFIED WITH mysql_native_password BY ‘Visitor@’;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON . TO ‘visitor’@’%’ WITH GRANT OPTION;

Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

解决触发器错误：You do not have the SUPER privilege and binary logging is enabled

mysql> set global log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

解决 this is incompatible with sql_mode=only_full_group_by 报错问题

mysql> SELECT @@GLOBAL.sql_mode;
±----------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
±----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
±----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
±----------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode |
±----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
±----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在/etc/my.cnf文件中添加一行

sql_mode
=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

然后重启mysql服务。

注意：不能在同一张表上建立2种同类型的触发器，一张表最多创建6个触发器(6种类型)：即BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

创建用户并添加权限

CREATE USER ‘root’@’%’ IDENTIFIED BY ’111111’;
GRANT ALL ON . TO ‘root’@’%’;
ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ’111111’;
flush privileges;

使之具有创建触发器的权限

set global log_bin_trust_function_creators=1;
flush privileges;