MySQL的安装和基本操作

一、MySQL的安装

• centos6 上的安装

# MySQL 是采用的是 C/S 模式，安装server包，自动回安装client包
yum install -y mysql-server

• centos7 上的安装

yum install -y mariadb-server

• centos7.6上二进制格式安装mariadb-10.2.12-linux-x86_64.tar.gz

# 下载二进制包 和 查看安装指导
https://downloads.mariadb.org/mariadb/10.2.23/
# 解压缩到 /usr/local 目录，然后创建一个软连接
tar -xf mariadb-10.2.12-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local
ln -s mariadb-10.2.12-linux-x86_64/ mysql
# 创建用户和用户组，并修改mariadb目录的所有者和所属主
ll mariadb-10.2.12-linux-x86_64/ -d
drwxrwxr-x 12 1021 1004 290 Jan  4  2018 mariadb-10.2.12-linux-x86_64/
useradd -r -d /data/mysql -s /sbin/nologin  mysql
chown -R mysql:mysql mariadb-10.2.12-linux-x86_64
# 修改 PATH 变量
echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh
# 创建 mysql 家目录
mkdir /data/mysql
chown mysql:mysql /data/mysql
# 初始化数据库
scripts/mysql_install_db  --datadir=/data/mysqldb --user=mysql
# 拷贝配置文件
cp  /usr/local/mysql/support-files/my-huge.cnf /etc/my.cnf
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
# 启动服务
cp  /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
# 允许安全加固脚本
mysql_secure_installation 

• 利用MySQL官方提供的yum源安装新版本的MySQL

# 配置yum源
vim /etc/yum.repos.d/base.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

sudo yum install MariaDB-server MariaDB-client

二、MySQL的基本操作

• 在shell 中通过 mysql 这个客户端工具登录数据库

# mysql 是一个交互式的工具
mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, 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发现，不用输入任何密码都可以登录进数据库

# 默认是允许匿名登录，而且root用户是没有密码的。
mysql> select user,host,password from user;
+------+------------------+----------+
| user | host             | password |
+------+------------------+----------+
| root | localhost        |          |
| root | cobler.study.com |          |
| root | 127.0.0.1        |          |
|      | localhost        |          |
|      | cobler.study.com |          |
+------+------------------+----------+
5 rows in set (0.00 sec)
# 关于MySQL的用户是由 user@host 构成，而不是通常的 user
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
# 关闭匿名用户和设置密码，运行下面的脚本
which mysql_secure_installation 
/usr/bin/mysql_secure_installation

• 创建其他用户

# 用户中的host 是支持通配符，%--任意长度任意字符，_ --匹配任意单个字符。
mysql> create user 'dog'@'192.168.30.%' identified by '123456';
mysql> select user,host,password from user;
+------+------------------+-------------------------------------------+
| user | host             | password                                  |
+------+------------------+-------------------------------------------+
| root | localhost        |                                           |
| root | cobler.study.com |                                           |
| root | 127.0.0.1        |                                           |
|      | localhost        |                                           |
|      | cobler.study.com |                                           |
| dog  | 192.168.30.%     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+------------------+-------------------------------------------+
6 rows in set (0.00 sec)

• MySQL可以允许的命令

客户端命令，可以不使用分号结尾：
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

服务端命令，SQL语句，必须要使用分号（；）结尾，例如：
show databases;
show tables;
select * from user;

• MySQL脚本模式

# 第一种方式：
mysql -u username -p password < /path/*.sql
# 第二种方式，使用使用msql的客户端命令 source ：
source /path/*.sql

• mysql 客户端工具的常见选项

mysql客户端可用选项：
-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e “SQL“ 执行SQL命令
-V, --version 显示版本
-v --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置