一、mysql数据库介绍
1.1、简介
1)MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),MySQL数据库系统使用最常用的数据库管理语言--结构化查询语言(SQL)进行数据库管理。
2)MySQL的历史最早可以追溯到1979年,那时Oracle也才刚刚开始,微软的SQL Server影子都没,在2008年1月16号 MySQL被Sun公司收购。2009年,SUN又被Oracle收购
3)体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,使得许多企业选择了MySQL作为数据库
1.2、官网介绍
1)主页:MySQL Database Service | Oracle
2)下载主页面:MySQL :: MySQL Downloads
3)社区资源下载页面:MySQL :: MySQL Community Downloads
4)MySQL社区版下载页面:MySQL :: Download MySQL Community Server
1.3、mysql相关产品介绍
1)Oracle MySQL Cloud Service (commercial):商业付费软件,基于MySQL企业版和Oracle云服务提供企业级 的MySQL数据库服务
2)MySQL Enterprise Edition(commercial):商业付费软件,除了提供MySQL数据库服务之外,又包含了connector(程序连接接口),partition(表分区),企业级的monitor(监控),HA(高可用),backup(备份),Scalability(扩展)等服务
3)MySQL Cluster CGE(commercial):商业付费软件,基于MySQL Cluster和企业版拥有的各项功能提供企业级的高并发 、高吞吐的数据库服务
4)MySQL Community Edition:免费社区软件,提供基础的数据库服务和其他衍生服务
5)MySQL Community Server:最流行的开源数据库管理软件,当前最新版本是8.0
6)MySQL Cluster:基于MySQL数据库而实现的集群服务,自身能提供高并发高负载等特性
7)MySQL Fabric:MySQL官方提供的关于MySQL数据库高可用和数据分片的解决方案
8)MySQL Connectors:为应用程序提供JDBC/ODBC等访问MySQL数据库的接口服务
1.4、社区版个版本区别
MySQL4.0版本:增加了子查询的支持,字符集增加UTF-8,GROUP BY语句增加了ROLLUP,mysql.user表采用了更好的加密算法,InnoDB开始支持单独的表空间
MySQL5.0版本:增加了Stored procedures、Views、Cursors、Triggers、XA transactions的支持,增加了INFORATION_SCHEMA系统数据库
MySQL5.5版本:默认存储引擎更改为InnoDB,提高性能和可扩展性,增加半同步复制
MySQL5.6版本:提高InnoDB性能,支持延迟复制
MySQL5.7版本:提升数据库性能和存储引擎,更健壮的复制功能,增加sys系统库存放数据库管理信息
二、mysql5.7安装
2.1、安装前准备
1)检查操作系统和MySQL版本的适配度
2)选择安装的MySQL版本
- 首先判断是否要和公司其他已经安装好的MySQL保持版本一致
- 如果没有上述要求,则一般会安装最新版本(目前是5.7)
- 如果不是实验新功能性质,则不要选择development release,而要安装General Availability (GA) release(代表稳定版本,可在生产系统使用)
3)选择安装MySQL的方式
- 二进制安装包的方式(RPM,ZIP,Tar等)
- 源码方式(source code)
一般会选择二进制安装方式,如果有特殊需求,比如修改一部分源码或修改MySQL深层次的配置,则会选择源码方式
2.2、二进制安装mysql5.7
1)进入安装目录,上传mysql5.7二进制安装包
[root@db01 ~]# cd /usr/local/
[root@db01 local]# ll mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 mysql mysql 641127384 Jan 27 2019 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
2)解压二进制安装包,并命名mysql
01
02
|
[root@db01 local ] # tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@db01 local ] # mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
|
3)创建mysql用户及组
01
02
|
[root@db01 local ] # groupadd mysql
[root@db01 local ] # useradd mysql -g mysql
|
4)创建mysql的data目录,并授权
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
[root@db01 local ] # cd /usr/local/mysql/
[root@db01 mysql] # mkdir data
[root@db01 mysql] # chown -R mysql.mysql /usr/local/mysql
[root@db01 mysql] # ll /usr/local/mysql
total 36
drwxr-xr-x 2 mysql mysql 4096 Oct 5 17:19 bin
-rw-r--r-- 1 mysql mysql 17987 Sep 13 2017 COPYING
drwxr-xr-x 2 mysql mysql 6 Oct 5 17:20 data
drwxr-xr-x 2 mysql mysql 55 Oct 5 17:19 docs
drwxr-xr-x 3 mysql mysql 4096 Oct 5 17:19 include
drwxr-xr-x 5 mysql mysql 229 Oct 5 17:19 lib
drwxr-xr-x 4 mysql mysql 30 Oct 5 17:19 man
-rw-r--r-- 1 mysql mysql 2478 Sep 13 2017 README
drwxr-xr-x 28 mysql mysql 4096 Oct 5 17:19 share
drwxr-xr-x 2 mysql mysql 90 Oct 5 17:19 support-files
|
5)初始化mysql,记住临时密码
01
02
03
04
05
06
07
08
09
10
|
[root@db01 mysql] # bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data
2019-10-05T09:34:35.351239Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-10-05T09:34:36.234993Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-10-05T09:34:36.326759Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-10-05T09:34:36.386932Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5874e7c6-e753-11e9-91e7-000c29db13e4.
2019-10-05T09:34:36.388725Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-10-05T09:34:36.389921Z 1 [Note] A temporary password is generated for root@localhost: Nj1ojjql/)f<
#报错:bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决方法:yum install libaio -y
|
初始化方式:
01
02
03
04
05
06
07
08
09
10
11
12
13
|
#1、初始化数据,初始化管理员的临时密码
/app/mysql/bin/mysqld --initialize --user=mysql --basedir= /app/mysql --datadir= /data/mysql
5.7开始,MySQL加入了全新的 密码的安全机制:
1.初始化完成后,会生成临时密码(显示到屏幕上,并且会往日志中记一份)
2.密码复杂度:长度:超过12位? 复杂度:字符混乱组合
3.密码过期时间180天
#2、初始化数据,初始化管理员的密码为空
/app/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir= /app/mysql --datadir= /data/mysql
#5.6初始化
/application/mysql/mysql/scripts/mysql_install_db --user=mysql --datadir= /application/mysql/mysql/mydata --basedir= /application/mysql/mysql
|
6)更改配置文件(默认配置文件为maraidb的,删除或备份即可)
01
|
[root@db01 mysql] # mv /etc/my.cnf{,.bak}
|
7)通过命名启动mysql
01
02
03
04
05
06
|
[root@db01 mysql] # bin/mysqld_safe --datadir=/usr/local/mysql/data --user=mysql &
[root@db01 mysql] # ps -ef|grep mysql
root 1733 1633 0 17:37 pts /0 00:00:00 /bin/sh bin /mysqld_safe --datadir= /usr/local/mysql/data --user=mysql
mysql 1818 1733 1 17:37 pts /0 00:00:00 /usr/local/mysql/bin/mysqld --basedir= /usr/local/mysql --datadir= /usr/local/mysql/data --plugin- dir = /usr/local/mysql/lib/plugin --user=mysql --log-error=db01.err --pid- file =db01.pid
root 1848 1633 0 17:37 pts /0 00:00:00 grep --color=auto mysql
|
8)配置环境变量
01
02
03
|
[root@db01 mysql] # vim ~/.bash_profile
PATH=$PATH:$HOME /bin : /usr/local/mysql/bin
[root@db01 mysql] # source ~/.bash_profile
|
为了可以在任意目录上都可以使用mysql命令登录mysql,将mysql安装目录配置到环境变量中,在vim /etc/profile文件的末尾添加以下代码
export PATH=$PATH:/usr/local/mysql/bin
使配置文件的配置立即生效
[root@localhost mysql]# source /etc/profile
9)拷贝启动文件到/etc/init.d/,开机自启动
01
|
[root@db01 mysql] # cp support-files/mysql.server /etc/init.d/
设置开机启动
|
10)登录mysql,使用临时密码
01
02
|
[root@db01 mysql] # mysql -uroot -p
Enter password: #临时密码
|
11)修改密码
01
02
03
04
05
06
07
|
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> set password=password( 'mysql' );
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
|
12)重新登录mysql,密码为mysql
01
02
03
04
05
06
07
08
09
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
|
[root@db01 mysql] # mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 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> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
|
三、mysql5.7数据目录结构
3.1、建库结构变化
执行create database语句后,会在数据目录下生成一个文件夹
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
|
mysql> create database course;
[root@ db01 ~] # cd /usr/local/mysql/data/
[root@ db01 data] # ll
total 122944
-rw-r----- 1 mysql mysql 56 Feb 5 13:48 auto.cnf #UUID(创建mysql集群是有用)
drwxr-x--- 2 mysql mysql 20 Feb 9 20:55 course #创建的每一个数据库都会生成一个对应的文件夹
-rw-r----- 1 mysql mysql 313 Feb 5 14:04 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 9 20:05 ibdata1 #共享表空间信息
-rw-r----- 1 mysql mysql 50331648 Feb 9 20:05 ib_logfile0 #日志相关
-rw-r----- 1 mysql mysql 50331648 Feb 5 13:48 ib_logfile1 #日志相关
-rw-r----- 1 mysql mysql 12582912 Feb 9 20:05 ibtmp1 #临时表存放
drwxr-x--- 2 mysql mysql 4096 Feb 5 13:48 mysql
-rw-r----- 1 mysql mysql 24429 Feb 9 20:05 db01.err #错误日志
-rw-r----- 1 mysql mysql 5 Feb 9 20:05 db01.pid #pid文件
drwxr-x--- 2 mysql mysql 8192 Feb 5 13:48 performance_schema
drwxr-x--- 2 mysql mysql 8192 Feb 5 13:48 sys
|
3.2、建表结构变化
01
02
03
04
05
06
07
08
09
10
11
12
13
14
|
mysql> use course;
Database changed
mysql> create table tmp( id int);
Query OK, 0 rows affected (0.02 sec)
[root@ db01 data] # cd course/
[root@ db01 course] # ll
total 112
-rw-r----- 1 mysql mysql 65 Feb 9 20:55 db.opt #字符集及排序规则(创建数据库后自动生成)
-rw-r----- 1 mysql mysql 8556 Feb 9 20:58 tmp.frm #表结构信息
-rw-r----- 1 mysql mysql 98304 Feb 9 20:58 tmp.ibd #数据信息,索引信息
[root@ db01 course] # cat db.opt
default-character- set =latin1
default-collation=latin1_swedish_ci
|
3.3、mysql库结构
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
[root@ db01 data] # cd mysql/
[root@ db01 mysql] # ls
columns_priv.frm gtid_executed.ibd proc.MYD slow_log.CSV
columns_priv.MYD help_category.frm proc.MYI slow_log.frm
columns_priv.MYI help_category.ibd procs_priv.frm tables_priv.frm
db.frm help_keyword.frm procs_priv.MYD tables_priv.MYD
db.MYD help_keyword.ibd procs_priv.MYI tables_priv.MYI
db.MYI help_relation.frm proxies_priv.frm time_zone.frm
db.opt help_relation.ibd proxies_priv.MYD time_zone.ibd
engine_cost.frm help_topic.frm proxies_priv.MYI time_zone_leap_second.frm
engine_cost.ibd help_topic.ibd server_cost.frm time_zone_leap_second.ibd
event.frm innodb_index_stats.frm server_cost.ibd time_zone_name.frm
event.MYD innodb_index_stats.ibd servers.frm time_zone_name.ibd
event.MYI innodb_table_stats.frm servers.ibd time_zone_transition.frm
func.frm innodb_table_stats.ibd slave_master_info.frm time_zone_transition.ibd
func.MYD ndb_binlog_index.frm slave_master_info.ibd time_zone_transition_type.frm
func.MYI ndb_binlog_index.MYD slave_relay_log_info.frm time_zone_transition_type.ibd
general_log.CSM ndb_binlog_index.MYI slave_relay_log_info.ibd user.frm
general_log.CSV plugin.frm slave_worker_info.frm user.MYD
general_log.frm plugin.ibd slave_worker_info.ibd user.MYI
gtid_executed.frm proc.frm slow_log.CSM
tables_priv.frm #表结构信息
tables_priv.MYD #表数据信息(myisam引擎)
tables_priv.MYI #表索引信息(myisam引擎)
|
四、mysql5.6与5.7小区别(持续)
4.1、用户信息相关
01
02
03
04
05
06
07
08
09
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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
#5.6版本:
select user,password,host from mysql.user;
#5.7版本
select user,authentication_string,host from mysql.user;
desc mysql.user;
mysql> select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
+---------------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Insert_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Update_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Delete_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Create_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Drop_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Reload_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Shutdown_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Process_priv | enum( 'N' , 'Y' ) | NO | | N | |
| File_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Grant_priv | enum( 'N' , 'Y' ) | NO | | N | |
| References_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Index_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Alter_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Show_db_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Super_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Create_tmp_table_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Lock_tables_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Execute_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Repl_slave_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Repl_client_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Create_view_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Show_view_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Create_routine_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Alter_routine_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Create_user_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Event_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Trigger_priv | enum( 'N' , 'Y' ) | NO | | N | |
| Create_tablespace_priv | enum( 'N' , 'Y' ) | NO | | N | |
| ssl_type | enum( '' , 'ANY' , 'X509' , 'SPECIFIED' ) | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum( 'N' , 'Y' ) | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum( 'N' , 'Y' ) | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
|
五、root密码忘记处理
5.1、停数据库
01
02
|
[root@db01 mysql] # /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS!
|
5.2、跳过授权表启动数据库
01
02
03
04
05
06
07
08
09
10
11
|
[root@db01 mysql] # mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
[root@db01 mysql] # ps -ef|grep mysql
root 2775 1633 0 18:23 pts /0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults- file = /etc/my .cnf --skip-grant-tables --skip-networking
mysql 2865 2775 2 18:23 pts /0 00:00:00 /usr/local/mysql/bin/mysqld --defaults- file = /etc/my .cnf --basedir= /usr/local/mysql --datadir= /usr/local/mysql/data --plugin- dir = /usr/local/mysql/lib/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=db01.err --pid- file =db01.pid
root 2895 1633 0 18:23 pts /0 00:00:00 grep --color=auto mysql
#参数说明
--defaults- file = /etc/my .cnf #指定配置文件(要写在前面)
--skip-grant-tables #跳过授权表(mysql.user)
--skip-networking #跳过网络用户连接
|
5.3、直接mysql登录数据库修改密码
方式一:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
|
[root@db01 mysql] # mysql
mysql> flush privileges; #需要先刷新授权表,否则下面的grant命令执行不成功
mysql> grant all on *.* to root@ 'localhost' identified by 'abc' with grant option;
mysql> \q
Bye
#重启
[root@db01 mysql] # pkill mysqld
[root@db01 mysql] # /etc/init.d/mysql.server start
Starting MySQL. SUCCESS!
#使用新密码登录
[root@db01 mysql] # mysql -uroot -pabc
mysql>
|
如何不刷新授权表,会报错如下:
方式二:不推荐
01
02
03
04
05
06
07
08
09
10
11
12
13
14
|
[root@db01 mysql] # /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@db01 mysql] # mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
[root@db01 mysql] # mysql
mysql> update mysql.user set authentication_string=PASSWORD( '123' ) where user= 'root' and host= 'localhost' ;
mysql> \q
Bye
[root@db01 mysql] # mysqladmin shutdown
[root@db01 mysql] # /etc/init.d/mysql.server start
Starting MySQL. SUCCESS!
[root@db01 mysql] # mysql -uroot -p123
mysql>
|
5.4、补充:将密码改为空
01
02
03
04
05
|
update mysql.user set authentication_string=PASSWORD( '' ) where user= 'root' and host= 'localhost' ;
flush privileges;
#或者重启数据库也会生效。
#注意: mysql.user 在每次数据库启动时会自动加载到内存,如果使用update语句直接修改表内容,mysql不会立即将变化更新内存,需要手工触发。
|
六、MySQL启动相关参数
6.1、相关参数说明
01
02
03
04
05
06
07
08
09
10
11
12
13
|
basedir = /usr/local/mysql #代表MySQL安装路径
datadir = /usr/local/mysql/data #代表MySQL的数据文件路径
port = 3306 #指定MySQL的侦听端口
log-error= /usr/local/mysql/data/M00006 .err #记录MySQL启动日志和运行错误日志
bind-address #(默认是*)*代表接受所有来自IPV4、IPV6主机网卡的TCP/IP连接,0.0.0.0代表接受所有来自IPV4主机网卡的TCP/IP的连接,指定的IP如127.0.0.1,代表只接受此地址请求的TCP/IP连接
character- set -server #(默认是latin1)指定MySQL的字符集
collation-server #(默认是latin1_swedish_ci)指定MySQL的排序规则
default-storage-engine (默认是InnoDB)指定MySQL的默认存储引擎
default- time -zone #指定默认时区,如果没有指定则和系统默认时区一致
open -files-limit #(默认5000)指定Mysqld运行过程中可以打开的文件数,避免出现” Too many open files”报错
pid- file = /usr/local/mysql/data/M00006 .pid #指定Mysqld进程对应的程序ID文件,默认是在数据文件目录里
Skip-grant-tables #指定避开MySQL内部的权限表启动服务
Tmpdir #指定临时表文件的存放路径
|
6.2、示例:修改字符集及变更数据目录
01
02
03
04
05
06
07
08
09
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
39
|
#停库
[root@db01 mysql] # /etc/init.d/mysql.server stop
#移动数据目录
[root@db01 mysql] # mv /usr/local/mysql/data/ /data/
[root@db01 mysql] # ll /data/
total 0
drwxr-xr-x 6 mysql mysql 205 Oct 5 19:33 data
#编辑/etc/my.cnf
[root@db01 mysql] # cat /etc/my.cnf
[mysqld]
datadir = /data/data
port = 3306
character- set -server = utf8
collation-server = utf8_unicode_ci
#重启mysql
[root@db01 mysql] # ps -ef|grep mysql
root 20000 1 0 19:37 pts /0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir= /data/data --pid- file = /data/data/db01 .pid
mysql 20144 20000 3 19:37 pts /0 00:00:00 /usr/local/mysql/bin/mysqld --basedir= /usr/local/mysql --datadir= /data/data --plugin- dir = /usr/local/mysql/lib/plugin --user=mysql --log-error=db01.err --pid- file = /data/data/db01 .pid --port=3306
root 20174 1633 0 19:37 pts /0 00:00:00 grep --color=auto mysql
[root@db01 mysql] # mysql -uroot -pmysql
mysql> show variables like 'character_set_server%' ;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | utf8 |
+----------------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'collation_server%' ;
+------------------+-----------------+
| Variable_name | Value |
+------------------+-----------------+
| collation_server | utf8_unicode_ci |
+------------------+-----------------+
1 row in set (0.00 sec)
|
七、mysql多实例配置
启动多个mysqld实例
- 多个配置文件
- 多个端口
- 多套数据
- 多个socket
- 多个server_id
7.1、单实例配置
1)创建软件目录,上传软件至此目录
01
02
03
04
|
[root@mysql ~] # mkdir -p /app/
[root@mysql ~] # cd /app/
[root@mysql app] # ls
mysql-5.7.20-linux-glibc2.12-x86_64. tar .gz
|
2)解压并改名为mysql
01
02
03
04
05
06
07
08
09
10
11
12
13
|
[root@mysql app] # tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql app] # mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
[root@mysql app] # ll /app/mysql
total 36
drwxr-xr-x 2 root root 4096 Oct 5 21:32 bin
-rw-r--r-- 1 7161 31415 17987 Sep 13 2017 COPYING
drwxr-xr-x 2 root root 55 Oct 5 21:32 docs
drwxr-xr-x 3 root root 4096 Oct 5 21:31 include
drwxr-xr-x 5 root root 229 Oct 5 21:32 lib
drwxr-xr-x 4 root root 30 Oct 5 21:32 man
-rw-r--r-- 1 7161 31415 2478 Sep 13 2017 README
drwxr-xr-x 28 root root 4096 Oct 5 21:32 share
drwxr-xr-x 2 root root 90 Oct 5 21:32 support-files
|
3)建立mysql用户和组
01
|
[root@mysql app] # useradd mysql
|
4)创建相关目录并修改权限
01
02
03
|
[root@mysql app] # mkdir /data/mysql -p
[root@mysql app] # chown -R mysql.mysql /app/*
[root@mysql app] # chown -R mysql.mysql /data/*
|
5)初始化
01
|
[root@mysql app] # /app/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql
|
6)书写默认配置文件
01
02
03
04
05
06
07
08
09
10
|
[root@mysql app] # cat /etc/my.cnf
[mysqld]
basedir= /app/mysql
datadir= /data/mysql
socket= /tmp/mysql .sock
log_error= /var/log/mysql .log
user=mysql
[client]
socket= /tmp/mysql .sock
prompt=[\\d]>
|
7)创建日志文件并授权(易出错)
01
02
|
[root@mysql app] # touch /var/log/mysql.log
[root@mysql app] # chown -R mysql.mysql /var/log/mysql.log
|
8)替换
01
|
[root@mysql app] # sed -i 's#mysqld_safe#mysqld#g' /app/mysql/support-files/mysql.server
|
9)启动
01
02
03
04
05
|
[root@mysql app] # /app/mysql/support-files/mysql.server start
Starting MySQL. SUCCESS!
[root@mysql app] # ps -ef|grep mysql
mysql 1742 1 2 21:43 pts /0 00:00:00 /app/mysql/bin/mysqld --datadir= /data/mysql --pid- file = /data/mysql/mysql .pid
root 1775 1601 0 21:43 pts /0 00:00:00 grep --color=auto mysql
|
10)原始sys-v管理启动(方式一)
01
02
03
04
05
06
|
[root@mysql ~] # cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@mysql ~] # pkill mysql
[root@mysql ~] # ps -ef|grep mysql
root 1797 1601 0 21:44 pts /0 00:00:00 grep --color=auto mysql
[root@mysql ~] # /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
|
11)使用systemd管理mysql(方式二)
注意:将原来模式启动mysqld先关闭,然后再用systemd管理。
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[root@mysql ~] # /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@mysql ~] # cat /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation= man :mysqld(8)
Documentation=http: //dev .mysql.com /doc/refman/en/using-systemd .html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart= /app/mysql/bin/mysqld --defaults- file = /etc/my .cnf
LimitNOFILE = 5000
[root@mysql ~] # systemctl start mysqld.service
[root@mysql ~] # ps -ef|grep mysql
mysql 1893 1 3 21:46 ? 00:00:00 /app/mysql/bin/mysqld --defaults- file = /etc/my .cnf
root 1923 1601 0 21:46 pts /0 00:00:00 grep --color=auto mysql
|
12)配置环境变量
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
[root@mysql ~] # echo 'export PATH=/app/mysql/bin:$PATH' >> /etc/profile && source /etc/profile
[root@mysql ~] # mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 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.
[(none)]>
|
7.2、多实例配置
1)准备多个目录
01
|
[root@mysql ~] # mkdir -p /data/330{7,8,9}/data
|
2)准备配置文件
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
vim /data/3307/my .cnf
[mysqld]
basedir= /app/mysql
datadir= /data/3307/data
socket= /data/3307/mysql .sock
log_error= /data/3307/mysql .log
port=3307
server_id=7
---------------------------
vim /data/3308/my .cnf
[mysqld]
basedir= /app/mysql
datadir= /data/3308/data
socket= /data/3308/mysql .sock
log_error= /data/3308/mysql .log
port=3308
server_id=8
---------------------------
vim /data/3309/my .cnf
[mysqld]
basedir= /app/mysql
datadir= /data/3309/data
socket= /data/3309/mysql .sock
log_error= /data/3309/mysql .log
port=3309
server_id=9
|
3)初始化三套数据
01
02
03
04
|
[root@mysql ~] # mv /etc/my.cnf /etc/my.cnf.bak
[root@mysql ~] # mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
[root@mysql ~] # mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/mysql
[root@mysql ~] # mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/mysql
|
4)systemd管理多实例
01
02
03
04
05
06
07
08
09
10
11
|
cd /etc/systemd/system
cp mysqld.service mysqld3307.service
cp mysqld.service mysqld3308.service
cp mysqld.service mysqld3309.service
vim mysqld3307.service
ExecStart= /app/mysql/bin/mysqld --defaults- file = /data/3307/my .cnf
vim mysqld3308.service
ExecStart= /app/mysql/bin/mysqld --defaults- file = /data/3308/my .cnf
vim mysqld3309.service
ExecStart= /app/mysql/bin/mysqld --defaults- file = /data/3309/my .cnf
|
5)授权
01
|
[root@mysql system] # chown -R mysql.mysql /data/*
|
6)启动
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
[root@mysql system] # systemctl start mysqld3307.service
[root@mysql system] # systemctl start mysqld3308.service
[root@mysql system] # systemctl start mysqld3309.service
[root@mysql system] # netstat -lnp|grep 330
tcp6 0 0 :::3307 :::* LISTEN 2179 /mysqld
tcp6 0 0 :::3308 :::* LISTEN 2186 /mysqld
tcp6 0 0 :::3309 :::* LISTEN 2249 /mysqld
tcp6 0 0 :::3306 :::* LISTEN 1979 /mysqld
unix 2 [ ACC ] STREAM LISTENING 25099 2186 /mysqld /data/3308/mysql .sock
unix 2 [ ACC ] STREAM LISTENING 25100 2179 /mysqld /data/3307/mysql .sock
unix 2 [ ACC ] STREAM LISTENING 25201 2249 /mysqld /data/3309/mysql .sock
[root@mysql system] # mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@mysql system] # mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@mysql system] # mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
|