二、mysql数据库的安装

1、yum安装

centos6:
yum install -y mysql-server mysql mysql-devel
centos7:
yum install -y mariadb mariadb-server mariadb-devel

以centos7为例:

yum install -y mariadb mariadb-server mariadb-devel

检查是否安装成功

[root@web04 ~]# rpm -qa mariadb mariadb-server mariadb-devel
mariadb-5.5.64-1.el7.x86_64
mariadb-devel-5.5.64-1.el7.x86_64
mariadb-server-5.5.64-1.el7.x86_64

启动mariadb

systemctl start mariadb
[root@web04 ~]# systemctl start mariadb 
[root@web04 ~]# ps -ef |grep mysql
mysql     50485      1  0 19:55 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     50647  50485  4 19:55 ?        00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      50682  50370  0 19:55 pts/0    00:00:00 grep --color=auto mysql

登陆mysql

[root@web04 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> 

2、源码安装mysql-5.5

①安装依赖包

yum install -y gcc c ncurses-devel libaio bison gcc-c++  git cmake  ncurses-devel ncurses 

②创建mysql的虚拟用户

useradd -s /sbin/nologin -M mysql

③创建源码包存放目录,并下载mysql5.5

mkdir -p /server/tools
cd /server/tools
wget -c http://mirrors.163.com/mysql/Downloads/MySQL-5.5/mysql-5.5.62.tar.gz

④解压源码包并编译安装

tar -xf mysql-5.5.62.tar.gz
cd mysql-5.5.62
cmake  .  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql55/ \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_DATADIR=/data/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DWITH_XTRADB_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EXTRA_CHARSETS=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_BIG_TABLES=1 \
-DWITH_DEBUG=0


make  && make install

⑤复制配置文件

#复制主配置文件my.cnf到/usr/local/mysql55目录(mysql默认读取/etc/my.cnf,可以直接复制到/etc/my.cnf)
cd /server/tools/mysql-5.5.62
\cp support-files/my-large.cnf /usr/local/mysql55/
#复制启动文件到/etc/init.d/mysqld
\cp support-files/mysql.server /etc/init.d/mysqld
#给启动文件添加执行权限
chmod +x /etc/init.d/mysqld
#/etc/init.d/mysqld启动文件加载的conf文件的默认路径为/etc/my.cnf,更改为/usr/local/mysql55/my.cnf
sed -i 's#conf=/etc/my.cnf#conf=/usr/local/mysql55/my.cnf#g' /etc/init.d/mysqld

⑥创建mysql的数据目录,把属主和属组更改为mysql

mkdir -p /data/mysql
chown -R mysql.mysql /data/mysql

⑦初始化mysql数据库

/usr/local/mysql55/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql55

⑧启动mysqld

/etc/init.d/mysqld start

mysql数据库启动报错

[root@web04 mysql-5.5.62]# /etc/init.d/mysqld start
Starting MySQL.191221 20:27:25 mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/var/lib/mysql/web04.pid).
[root@web04 mysql-5.5.62]# 

缺少/var/log/mariadb/mariadb.log

处理方法:创建/var/log/mariadb/mariadb.log,并把权限更改为mysql

mkdir -p /var/log/mariadb/
touch /var/log/mariadb/mariadb.log
chown -R mysql.mysql /var/log/mariadb/mariadb.log

启动成功

[root@web04 mysql-5.5.62]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 
[root@web04 mysql-5.5.62]# ps -ef |grep mysql
root      62491      1  0 20:30 pts/0    00:00:00 /bin/sh /usr/local/mysql55/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/web04.pid
mysql     62838  62491  2 20:30 pts/0    00:00:00 /usr/local/mysql55/bin/mysqld --basedir=/usr/local/mysql55 --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql55/lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/lib/mysql/web04.pid --socket=/tmp/mysql.sock --port=3306
root      62860  50370  0 20:30 pts/0    00:00:00 grep --color=auto mysql
[root@web04 mysql-5.5.62]# 

⑨mysql数据库的命令优化

ln -s /usr/local/mysql55/bin/* /usr/bin/
或者
echo 'export PATH=/usr/local/mysql55/bin:$PATH' >>/etc/profile
source /etc/profile

 ⑩登陆测试(mysql5.5版本默认没有密码)

[root@web04 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.62-log Source distribution

Copyright (c) 2000, 2018, 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> 

3、源码安装mysql-5.7

①安装依赖包

yum install -y gcc c ncurses-devel libaio bison gcc-c++  git cmake  ncurses-devel ncurses 

②创建mysql的虚拟用户

useradd -s /sbin/nologin -M mysql

③创建源码包存放目录,并下载mysql5.7和boost

mkdir -p /server/tools
cd /server/tools
wget -c http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.26.tar.gz
wget -c wget -c https://sourceforge.net/projects/boost/files/boost/1.59.0/boost_1_59_0.tar.gz/download

④解压源码包并编译安装

#解压boost,并移动到/usr/local/boost,Boost库是为C++语言标准库提供扩展的一些C++程序库的总称,由Boost社区组织开发、维护
cd /server/tools
tar -xf boost_1_59_0.tar.gz
mv boost_1_59_0 /usr/local/boost
#解压编译安装mysql5.7
tar -xf mysql-5.7.26.tar.gz
cd mysql-5.7.26
cmake  .  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql57 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DWITH_XTRADB_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EXTRA_CHARSETS=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_BIG_TABLES=1 \
-DWITH_DEBUG=0 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost

make && make install

⑤复制配置文件

cd /server/tools/mysql-5.7.26
#复制启动文件到/etc/init.d/mysqld
\cp support-files/mysql.server /etc/init.d/mysqld
#给启动文件添加执行权限
chmod +x /etc/init.d/mysqld
#/etc/init.d/mysqld启动文件加载的conf文件的默认路径为/etc/my.cnf,更改为/usr/local/mysql57/my.cnf
sed -i 's#conf=/etc/my.cnf#conf=/usr/local/mysql57/my.cnf#g' /etc/init.d/mysqld

⑥在/usr/local/mysql57/目录下先写my.cnf配置文件

 
cat > /usr/local/mysql57/my.cnf << EOF
[mysqld] 
basedir=/usr/local/mysql57/ 
datadir=/data/mysql/ 
port=3306 
pid-file=/data/mysql/mysql57.pid 
socket=/tmp/mysql.sock
[mysqld_safe] 
log-error=/data/mysql/mysql57.log
EOF

⑦创建mysql的数据目录,把属主和属组更改为mysql

 

mkdir -p /data/mysql
chown -R mysql.mysql /data/mysql

⑧初始化mysql数据库

/usr/local/mysql57/bin/mysqld --initialize --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql57/
root@web04 ~]# /usr/local/mysql57/bin/mysqld --initialize --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql57/
2019-12-21T14:10:31.471193Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-12-21T14:10:34.391275Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-12-21T14:10:34.695408Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-12-21T14:10:34.757342Z 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: a7d277a4-23fb-11ea-8305-000c293f7398.
2019-12-21T14:10:34.758436Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-12-21T14:10:34.759084Z 1 [Note] A temporary password is generated for root@localhost: jO8_hI:jL/O>

###########################################################
#root@localhost: jO8_hI:jL/O>为数据库的密码

⑨启动数据库

/etc/init.d/mysqld start
[root@web04 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@web04 ~]# ps -ef |grep mysql
root      84847      1  0 22:13 pts/0    00:00:00 /bin/sh /usr/local/mysql57//bin/mysqld_safe --datadir=/data/mysql/ --pid-file=/datamysql/mysql57.pid
mysql     85077  84847  1 22:13 pts/0    00:00:00 /usr/local/mysql57/bin/mysqld --basedir=/usr/local/mysql57/ --datadir=/data/mysql --plugin-dir=/usr/local/mysql57//lib/plugin --user=mysql --log-error=/data/mysql/mysql57.log --pid-file=/data/mysql/mysql57.pid --socket=/tmp/mysql.sock --port=3306
root      85107  50370  0 22:14 pts/0    00:00:00 grep --color=auto mysql
[root@web04 ~]# 

⑩登录mysql-5.7

/usr/local/mysql57/bin/mysql -uroot -pjO8_hI:jL/O>
#mysql5.7在初始后最后会自动生成root@localhost的密码
[root@web04 ~]#/usr/local/mysql57/bin/mysql -uroot -pjO8_hI:jL/O> 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26

Copyright (c) 2000, 2019, 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> 

⑪登陆后更改mysql5.7的密码

更新密码为空

mysql> update mysql.user set authentication_string=password('') where user="root" and host="localhost";
mysql> alter user "root"@"localhost" identified by "";
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

密码更改为空后,重新登陆

 
[root@web04 ~]# /usr/local/mysql57/bin/mysql -uroot -p
Enter password: #空密码,直接回车
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26 Source distribution

Copyright (c) 2000, 2019, 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> 

⑫mysql命令优化

ln -s /usr/local/mysql57/bin/* /usr/bin/
或者
echo 'export PATH=/usr/local/mysql57/bin:$PATH' >> /etc/profile
source /etc/profile

4、二进制安装mysql5.7

①下载mysql5.7二进制包

www.mysql.com官方站点下载

mkdir /app
cd /app
wget -c https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

②解压安装包,并创建软连接

tar -xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.20-linux-glibc2.12-x86_64 mysql

③修改mysql命令的环境变量

echo 'export PATH=/app/mysql/bin:$PATH' >> /etc/profile
source /etc/profile

④创建mysql用户和组(如果已经创建则忽略改步骤)

useradd -M -s /sbin/nologin mysql

⑤创建mysql数据目录并修改权限

 
mkdir -p /data/mysql
chown -R mysql.mysql /data/mysql/*
chown -R mysql.mysql /app/mysql/*

⑥初始化数据库

方案一:

初始化后自动创建mysql的登录密码

mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/data/mysql
############下面为执行结果信息
[root@vm01 ~]# mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/data/mysql
2020-11-02T14:08:57.462362Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-11-02T14:08:58.354620Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-11-02T14:08:58.481635Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-11-02T14:08:58.541801Z 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: f36be4b6-1d14-11eb-b88a-000c29d16f12.
2020-11-02T14:08:58.542895Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-11-02T14:08:58.543526Z 1 [Note] A temporary password is generated for root@localhost: 6Q,VjB.vnuF< #登录密码

#新特性重要说明:
#5.7开始,MySQL加入了全新的 密码的安全机制:
#1.初始化完成后,会生成临时密码(显示到屏幕上,并且会往日志中记一份)
#2.密码复杂度:长度:超过12位? 复杂度:字符混乱组合
#3.密码过期时间180天
[root@vm01 ~]# mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/data/mysql
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory


初始化过程中如果 Linux系统中缺少libaio和libaio-devel 软件包,会报错
解决:
yum install -y libaio-devel libaio

方案二:

初始化后不自动创建mysql的登录密码

rm -rf /data/mysql/*  #重新初始化mysql需要清空mysql的数据目录
mysqld --initalize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql

####下面为执行信息
[root@vm01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/mysql
2020-11-02T14:15:26.700765Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-11-02T14:15:27.499593Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-11-02T14:15:27.604125Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-11-02T14:15:27.673616Z 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: db5cb229-1d15-11eb-840f-000c29d16f12.
2020-11-02T14:15:27.674639Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-11-02T14:15:27.675122Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
##结尾处没有密码

⑦编写配置文件my.cnf

#移除其他版本的my.cnf
mv /etc/my.cnf /etc/my.cnf.bak
#编写my.cnf
cat >> /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
port=3306
server_id=201
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
prompt 3306 [\\d]>

#prompt 3306 [\\d]> 登录数据库的显示信息,[\\d]当前所在库的库名

⑧启动数据库

方法1:直接启动

[root@vm01 ~]# /app/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/data/mysql/vm01.err'.
 SUCCESS! 

方法2:使用service启动

 
cd /app/mysql/support-files/
cp mysql.server /etc/init.d/mysqld
[root@vm01 ~]# service mysqld start
Starting MySQL. SUCCESS! 

方法3:使用systemctl来启动

vim /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@vm01 ~]# systemctl start mysqld
[root@vm01 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Mon 2020-11-02 22:26:25 CST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 23434 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─23434 /app/mysql/bin/mysqld --defaults-file=/etc/m...

Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.762892Z...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.762972Z...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.762988Z...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.763009Z...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.773596Z...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.773735Z...
Nov 02 22:26:25 vm01 mysqld[23434]: Version: '5.7.20'  socket: ...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.773747Z...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.773756Z...
Nov 02 22:26:25 vm01 mysqld[23434]: 2020-11-02T14:26:25.785533Z...
Hint: Some lines were ellipsized, use -l to show in full.
[root@vm01 ~]# netstat -antlp |grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      23434/mysqld        
[root@vm01 ~]# ps -ef |grep mysqld
mysql    23434     1  0 22:26 ?        00:00:03 /app/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root     23514 23491  0 23:46 pts/1    00:00:00 grep --color=auto mysqld

 

三、数据库的链接

TCP/IP方式(远程、本地):
mysql -uroot -p123456 -h 192.168.32.201 -P3306
Socket方式(仅本地):
mysql -uroot -p123456 -S /tmp/mysql.sock

mysql 常用参数:

 
-u                   用户
-p                   密码
-h                   IP
-P                   端口
-S                   socket文件
-e                   免交互执行命令
<                    导入SQL脚本

案例

# 通过unix套接字连接 直接通过mysql 或者mysql -uroot -p 登录
mysql -uroot -p

mysql> status#查看当前版本信息
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:        4
#当前所在数据库,空表示没有进入数据库
Current database:
#登陆的用户信息
Current user:        root@localhost
#使用加密
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
#使用分号“;”为结束符号
Using delimiter:    ;
Server version:        5.7.26 Source distribution
Protocol version:    10
#连接方式
Connection:        Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /tmp/mysql.sock
Uptime:            17 min 21 sec

Threads: 1  Questions: 13  Slow queries: 0  Opens: 112  Flush tables: 1  Open tables: 105  Queries per second avg: 0.012
--------------
# 通过tcp套接字连接 通过mysql  -h127.0.0.1 登录服务器,查看状态:
mysql -h127.0.0.1 -uroot -p
mysql> status
--------------
/usr/local/mysql57/bin/mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:        6
Current database:    
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.26 Source distribution
Protocol version:    10
Connection:        127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:        3306
Uptime:            21 min 49 sec

Threads: 1  Questions: 20  Slow queries: 0  Opens: 112  Flush tables: 1  Open tables: 105  Queries per second avg: 0.015

可以看到连接id不同,套接字也不同,使用的是tcp/ip的套接字通信。
如果有时候遇到无法通过本地套接字连接,可以使用指定服务器ip连接。

四、mysql的安装目录说明

# 源码安装mysql 版本:
mysql 主配置目录:/usr/local/mysql5 
mysql 数据目录:/data/mysql mysql 
命令目录:/usr/local/mysql5/bin/* 比如:mysql、mysqld等。 
mysql 默认配置文件:/etc/my.cnf mysql 
启动文件:/usr/local/mysql5/support-files/mysql.server 或者 是/etc/init.d/mysqld mysql 
日志文件:/data/mysql
# yum 安装mariadb程序:
mariadb 主配置目录:/var/lib/mariadb mariadb 
数据目录:/var/lib/mariadb mariadb 
命令目录:/usr/bin mariadb 
默认配置文件:/etc/my.cnf mariadb 
启动文件:/usr/bin mariadb 
日志文件:/var/log/mariadb

五、mysql的配置文件my.cnf详解

初始配置文件的读取顺序

[root@vm01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
                      my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
注:
默认情况下,MySQL启动时,会依次读取以上配置文件,如果有重复选项,会以最后一个文件设置的为准。
但是,如果启动时加入了--defaults-file=xxxx时,以上的所有文件都不会读取.

配置文件的书写方式:

[标签]
配置项=xxxx

标签类型:服务端、客户端
服务器端标签:
[mysqld]
[mysqld_safe]
[server]

客户端标签:
[mysql]
[mysqldump]
[client]

配置文件的示例展示:
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
server_id=6
port=3306
log_error=/data/mysql/mysql.log
[mysql]
socket=/tmp/mysql.sock
prompt=Master [\\d]>

配置文件案例

[mysqld]                               //服务器端配置
datadir=/data/mysql                       //数据目录
socket=/var/lib/mysql/mysql.sock         //socket通信设置  
user=mysql                             //使用mysql用户启动;
symbolic-links=0                      //是否支持快捷方式;
log-bin=mysql-bin                      //开启bin-log日志;
server-id = 1                          //mysql服务的ID;
auto_increment_offset=1                //自增长字段从固定数开始;
auto_increment_increment=2                //自增长字段每次递增的量;
socket = /tmp/mysql.sock                 //为MySQL客户程序与服务器之间的本地通信套接字文件;
port             = 3306                  //指定MsSQL监听的端口;
key_buffer       = 384M              //key_buffer是用于索引块的缓冲区大小;
table_cache      = 512                   //为所有线程打开表的数量;
sort_buffer_size = 2M                    //每个需要进行排序的线程分配该大小的一个缓冲区;
read_buffer_size = 2M                    //读查询操作所能使用的缓冲区大小。
query_cache_size = 32M                   //指定MySQL查询结果缓冲区的大小
read_rnd_buffer_size    = 8M             //改参数在使用行指针排序之后,随机读;
myisam_sort_buffer_size = 64M            //MyISAM表发生变化时重新排序所需的缓冲;
thread_concurrency      = 8              //最大并发线程数,取值为服务器逻辑CPU数量×2;
thread_cache            = 8              //缓存可重用的线程数;
skip-locking                            //避免MySQL的外部锁定,减少出错几率增强稳定性。 
default-storage-engine=INNODB         //设置mysql默认引擎为Innodb;
#mysqld_safe config            
[mysqld_safe]                          //mysql服务安全启动配置;
log-error=/var/log/mysqld.log              //mysql错误日志路径;
pid-file=/var/run/mysqld/mysqld.pid         //mysql PID进程文件;
key_buffer_size = 2048MB                //MyISAM表索引缓冲区的大小;
max_connections = 3000                //mysql最大连接数;
innodb_buffer_pool_size    = 2048MB    //InnoDB内存缓冲数据和索引大小;
basedir      = /usr/local/mysql55/          //数据库安装路径;
[mysqldump]                            //数据库导出段配置;
max_allowed_packet      =16M         //服务器和客户端发送的最大数据包;

[mysql]   //客户端配置
socket = /tmp/mysql.sock    //配置与服务端一样
prompt=3306 [\\d]>   //客户端显示信息

六、mysql的启动与关闭

1、mysql启动

 无论那种启动方式,最终都是数据库的mysqld守护进程

cd /server/tools/mysql-5.7.26
\cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
/etc/init.d/mysqld start(mysql-server,mysql的启动脚本)
或者
mysqld_save --defaults-file=/usr/local/mysql/y.cnf & (启动多实例的方法)
或者
systemctl start mysqld.service (centos7专有的systemctl方式)

centos7专有的systemctl方式

cat >>/etc/systemd/system/mysqld.service <<EOF
[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
EOF

2、mysql的关闭

/etc/init.d/mysqld stop
或者
mysqladmin -u root -p123456 shutdown
或者
systemtl stop mysqld  #centos7专有

关闭多实例的方法:

mysqladmin -u root -p123456 -h 127.0.0.1 -p 3307 shutdown
或者
mysqladmin -u root -p123456 -S /data/mysql/3307/tmp/mysql.sock shutdown

3、mysql登陆

mysql -uroot -p123456 -S /tmp/mysql.sock
mysql -uroot -p123456 -S /data/mysql/3307/mysql.sock
mysql -uroot -p123456 -S /data/mysql/3308/mysql.sock
或
mysql -uroot -p123456 -h 127.0.0.1 -p 3306
mysql -uroot -p123456 -h 127.0.0.1 -p 3307

 

posted @ 2020-11-02 14:41  yaowx  阅读(207)  评论(0编辑  收藏  举报