MySQL主从复制与读写分离

第三章 MySQL数据库系统

create database benet;

show database;

use benet;

create table zc (username CHAR(16) NOT NULL ,userage CHAR(20), useraddress CHAR(68));

show tables;

insert into zc VALUES (‘城管局’,‘12’,‘北京顺义区’);

select * from zc;

1.学会MySQL的安装及基本操作
2.学会MySQL数据库的备份和恢复
3.学会MySQL数据库的用户授权

	一、MySQL服务基础

（一）MySQL数据库概述

1.MySQL是开源的关系型数据库服务器软件

*目前由Oracle公司开发和维护
*官方站点：http://ww.mysql.com

2.MySQL的特点

*多线程、多用户
*基于C/S（客户端/服务器）架构
*简单易用、查询速度快
*安全可靠

---

（二）MySQL的编译安装、

1.准备工作

需要软件：
cmake-2.8.6.tar.gz
mysql-5.6.36.tar.gz

*确认没有使用RPM方式安装mysql-server、mysql
[root@www ~]# rpm -q mariadb mairadb-server
未安装软件包 mariadb
未安装软件包 mairadb-server

安装光盘自带的ncurses-devel包—mysql需要此功能支持

[root@www ~]# rpm -ivh /media/cdrom/Packages/ncurses-devel-5.9-13.20130511.el7.x86_64.rpm

安装cmake包—mysql编译安装需要此软件

[root@www ~]# tar zxf cmake-2.8.6.tar.gz

[root@www ~]# cd cmake-2.8.6

[root@www cmake-2.8.6]# ./configure

[root@www cmake-2.8.6]# gmake && gmake install

2.源码编译及安装
注意啦：如果系统有安装MySQL，需要将原有组及用户mysql删除（etc/passwd [] /etc/group)

[root@www ~]# groupadd mysql

为了加强数据库管理，使用专用用户来管理。此用户不需要家目录，不需要本地服务器登录

[root@www ~]# useradd -M -s /sbin/nologin -g mysql mysql

-g 基本组
-G 附加组

[root@localhost ~]# grep “^mysql” /etc/passwd

[root@www ~]# tar zxvf mysql-5.6.36.tar.gz -C /usr/src/

[root@www ~]# cd /usr/src/mysql-5.6.36/

[root@www mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc

---

指定数据库安装目录
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
初始化文件参数目录
-DSYSCONFDIR=/etc
默认字符集编码
-DDEFAULT_CHARSET=utf8
字符集校对规则
-DDEFAULT_COLLATION=utf8_general_ci
其他字符集编码（网站平台可能会有多种字符集的网页，因此数据库也要支持多种字符集
-DWITH_EXTRA_CHARSETS=all
【仔细检查，不要出错】

当ncurses-devel软件未安装时会报错，需要删除
CMakeCache.txt并安装软件ncurses-devel

[root@www mysql-5.6.36]# make

[root@www mysql-5.6.36]# make install

3.安装后的其他调整

[root@www ~]# chown -R mysql:mysql /usr/local/mysql

*centos7.4默认支持MariaDB数据库，因此系统默认的/etc/my.cnf配置文件是mariadb的配置文件
support-files目录中有适合不同负载数据库的配置文件。此处使用my-default.cnf替代/etc/my.cnf

[root@www mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf

为了正常使用数据库，以mysql用户执行初始化脚本，指定数据存放目录等

[root@wwwmysql-5.6.36]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/

设置全局环境变量—支持数据库命令在所有目录中使用

[root@www mysql-5.6.36]# echo “PATH=$PATH:/usr/local/mysql/bin” >> /etc/profile

[root@www mysql-5.6.36#source /etc/profile

验证：
[root@localhost ~]# echo $PATH

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin

4.添加为系统服务
*以便通过chkconfig进行管理

[root@www mysql-5.6.36]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

[root@www mysql-5.6.36]# chmod +x /etc/rc.d/init.d/mysqld

[root@www mysql-5.6.36]# chkconfig --add mysqld

5.启动mysqld服务，并查看运行状态

[root@www ~]# systemctl enable mysqld

[root@www ~]# systemctl start mysqld

[root@www ~]# netstat -anput | grep mysqld
tcp6 0 0 :::3306 ::😗 LISTEN 41898/mysqld

---

（三）访问MySQL数据库

登录及退出MySQL环境

注意；第一次设置root，可以用此命令

1.连接并登录到MySQL操作环境

mysql -u 用户名 [-p]
提示符：mysql>

2.设置数据库用户的密码
mysqladmin -u root [-p] password 新密码

3.退出MySQL操作环境
mysql> exit

[root@www ~]# which mysqladmin

[root@www ~]# mysqladmin -u root password 123

Warning: Using a password on the command line interface can be insecure.

[root@www ~]# mysql -u root -p123
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 3
Server version: 5.6.36 Source distribution

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> status;

mysql Ver 14.14 Distrib 5.6.36, 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.6.36 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: 5 min 27 sec

Threads: 1 Questions: 15 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.045

退出数据库
mysql> exit
Bye

二、数据库的基本管理

1.查看数据库结构
2.创建及删除库和表
3.管理表中的数据记录

（一）查看数据库结构

1.查看数据库列表信息
SHOW DATABASES

2.查看数据库中的数据表信息
USE 数据库名
SHOW TABLES

3.显示数据表的结构（字段）
DESCRIBE [数据库名.]表名

练习1：

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
±-------------------+
4 rows in set (0.03 sec)

mysql ----是mysql正常运行需要的库，包含了用户认证相关的表

mysql> use mysql;

mysql数据库的数据文件存放在/usr/local/mysql/data目录下。每个数据库对应一个子目录，用于存储数据表文件。每个数据表对应为3个文件，扩展名称分别为‘frm , myd , myi’

mysql> show tables;

mysql> describe user;

---

field 字段

type 数据类型

null 是否允许为空

key 设置主键

default 默认值

extra 备注

---

select * from user;

select User,Password from user;

（二）数据库的创建和删除

1.创建新的数据库
CREATE DATABASE 数据库名

2.创建新的数据表
CREATE TABLE 表名 (字段定义……)

3.删除指定的数据表

DROP TABLE [数据库名.]表名

4.删除指定的数据库

DROP DATABASE 数据库名

create —创建
drop—删除

database—数据库

table—表

练习2：

创建auth库

mysql> CREATE DATABASE auth;

分析表的结构，创建users表。
用户名： 为不超过16字节的字符串，不能为空，设置为主键
密码： 不超过48字节的字符串，默认值为空字串

mysql> USE auth;

mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT ‘’, PRIMARY KEY (user_name));

describe users;

删除users表

mysql> DROP TABLE auth.users;

删除auth库
mysql> DROP DATABASE auth;

******************************8
插入、查询数据记录

`1.向数据表中插入新的数据记录

INSERT INTO 表名(字段1, 字段2, ……) VALUES(字段1的值, 字段2的值, ……)

2.从数据表中查找符合条件的数据记录

SELECT 字段名1,字段名2 …… FROM 表名 WHERE 条件表达式

查询auth.users表记录

插入完整记录时可以省略指定字段的部分

练习3：
重新创建库、表进行练习

CREATE DATABASE auth;

USE auth;

CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT ‘’, PRIMARY KEY (user_name));

插入用户zhangsan的数据

select * from users;

insert into users(user_name,user_passwd) values(‘zhangsan’,password(‘123456’));

select * from users;

插入用户lisi的数据
insert into users values(‘lisi’,password(‘654321’));

查询所有
select * from auth.users;

查询名叫zhangsan的记录

select * from auth.users where user_name=‘zhangsan’;

T-sql语言

增 insert
删 delete
查 select
改 update

---

修改数据记录

1.修改、更新数据表中的数据记录

UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE 条件表达式

删除数据记录

1.在数据表中删除指定的数据记录

DELETE FROM 表名 WHERE 条件表达式

练习4：
设置lisi的密码为空

UPDATE auth.users SET user_passwd=PASSWORD(’’) WHERE user_name=‘lisi’;

删除用户lisi的记录

DELETE FROM auth.users WHERE user_name=‘lisi’;

mysql> select * from auth.users;
±----------±------------------------------------------+
| user_name | user_passwd |
±----------±------------------------------------------+
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
±----------±------------------------------------------+
1 row in set (0.00 sec)

---

	mysql数据库的优化

一、修改root用户密码

方法1：在数据库中修改root用户的密码

update mysql.user set password=PASSWORD(‘123456’) WHERE user=‘root’;

刷新用户授权
flush privileges;

方法2：在数据库外修改用户密码

[root@localhost ~]# mysqladmin -u root -p password

Enter password: 输入原密码
New password: 新密码
Confirm new password:确认

二、为了安全考虑，删除用户名、密码均为空的空用户

select user,host,password from mysql.user where user=’’;

delete from mysql.user where user=’’;

---

---

三、维护数据库及用户权限

grant --授权
revoke --撤权

identified by 设置密码

1.设置用户权限（用户不存在时，则新建用户）

GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY ‘密码’ ]

权限列表：select,insert,update 逗号隔开 all–所有权限

来源地址：%—匹配所有 ，可以为域名、ip地址等

密码选项：省略则为–用户密码为空

2.查看用户的权限

SHOW GRANTS FOR 用户名@来源地址；

flush privileges;

3.撤销用户的权限

revoke 权限列表 ON 数据库名.表名 from 用户名@来源地址

练习5：

授予tom用户密码为123456在服务器本地查询auth数据库中所有表的权限

grant select on auth.* to ‘tom’@‘localhost’ identified by ‘123456’;

exit;

使用tom用户登录进行验证
mysql -u tom -p123456

可以查询auth数据库的users表
select * from auth.users;

不能查询mysql数据库的user表
select * from mysql.user;

exit;

使用root登录
mysql -u root -p123456

查看tom在数据库服务器本地的权限
show grants for tom@localhost;

撤销tom用户的所有权限
revoke all on auth.* from ‘tom’@’%’;

查看tom在数据库服务器本地的权限
show grants for tom@localhost;

exit;

使用tom进行验证
mysql -u tom -p123456

select * from auth.users;

exit;

---

扩展练习：在其他主机登录数据库
1.mysql服务器
192.168.10.1

2.客户机
192.168.10.2
安装mysql客户端

查询rpm软件的关键字
yum search all 关键字

[root@localhost ~]# yum search all mysql

[root@localhost ~]# yum -y install mariadb

[root@localhost ~]# which mysql

数据库授权前访问：访问被拒绝，需要授权

[root@localhost ~]# mysql -u root -p123 -h 192.168.10.1

ERROR 1130 (HY000): Host ‘192.168.10.2’ is not allowed to connect to this MySQL server

[root@localhost ~]# mysql -u ly -p123.com -h 192.168.10.1

实验要求：
用户：ly
密码：123.com
登录主机：192.168.10.0网段
权限：auth数据库中所有表的插入insert、查询select权限

实验过程：
1.mysql服务器
[root@localhost ~]# mysql -uroot -p123

mysql> select * from auth.users;

授权
mysql> grant insert,select on auth.* to ‘ly’@‘192.168.10.%’ identified by ‘123.com’;

查询权限
mysql> show grants for ly@‘192.168.10.%’;

在mysql数据库的user表查询ly用户
mysql> select host,user,password from mysql.user where user=‘ly’;

刷新
mysql> flush privileges;

2.客户机进行验证

-u 指定用户
-p 指定密码
-P 指定端口
-h 指定服务器主机

[root@localhost ~]# mysql -u ly -p123.com -h 192.168.10.1

MySQL [(none)]> show databases;

验证查询
MySQL [(none)]> select * from auth.users;

验证插入功能
MySQL [(none)]> insert into auth.users values(‘wangwu’,‘123’);

MySQL [(none)]> select * from auth.users;

验证是否能够更新（没有授权被拒绝）
MySQL [(none)]> update auth.users set user_passwd=’’ where user_name=‘wangwu’;

ERROR 1142 (42000): UPDATE command denied to user ‘ly’@‘192.168.10.2’ for table ‘users’