MySQL数据库基础、进阶、运维、读写分离数据库集群看这篇就够了

最新推荐文章于 2024-11-13 22:17:16 发布

两张不够花

最新推荐文章于 2024-11-13 22:17:16 发布

阅读量967

点赞数 22

文章标签：数据库 mysql 运维 linux

本文链接：https://blog.csdn.net/2401_86247463/article/details/142843555

版权

MySQL数据库

一．MySQL的概述

1.1数据库(DB）：存放数据的仓库，数据是有组织的进行存储

数据库管理系统（DBMS)：操纵和管理数据库的大型软件

SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

1.2MySQL数据库

关系型数据库（RDBMS)

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

1.使用表存储数据，格式统一，便于维护

id	name
1	研发部
2	总经办
3	财务部

2.使用SQL语言操作，标准统一，使用方便

示例：

id	name	job	Dept_id
1	金庸	总裁	2
2	张无忌	项目经理	1
3	洋洋	开发	3

前面的是员工表后面是部门表

那MySQL数据库是如何来存储数据的呢？

1.3更改root密码#passwd

数据模型

数据库的安装

yum安装

----安装mariadb 、mariadb-server软件包安装完成之后，查询是否安装成功

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

启动mysql服务

[root@localhost ~]# systemctl start mariadb

运行mysql_secure_installation 脚本，这个脚本可以自动执行一些常见的安全任务，以提高 MySQL 服务器的安全性。

[root@localhost ~]# mysql_secure_installation

以下是该命令运行部分过程截图

移除匿名用户：脚本会检查并移除任何匿名用户，这些用户不需要密码就可以连接到数据库

。

禁止 root 用户远程访问：脚本会禁止 root 用户

更改 root 用户密码

删除并移除测试数据库权限

重新加载权限表

源码安装

---首先要将下载好的压缩包存放到/usr/loca目录下

安装相关依赖包

[root@localhost opt]# yum -y install gcc gcc-c++ make pcre-devel expat-devel perl pcre zlib-devel zlib

解压所上传的压缩包

[root@localhostlocal]# xz -d mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz

[root@localhostlocal]# tar -xvf mysql-8.0.17-linux-glibc2.12-x86_64.ta

用cmake来安装编译源码

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql

-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock

-DSYSCONFDIR=/etc

-DSYSTEMD_PID_DIR=/usr/local/mysql

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

-DWITH_INNOBASE_STORAGE_ENGINE=1

-DWITH_ARCHIVE_STORAGE_ENGINE=1

-DWITH_BLACKHOLE_STORAGE_ENGINE=1

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

-DMYSQL_DATADIR=/usr/local/mysql/data

-DWITH_BOOST=boost

-DWITH_SYSTEMD=1

编译安装

[root@www mysql-5.7.20]# make

[root@www mysql-5.7.20]# make install

更改mysql目录权限

[root@localhost mysql]# chown -R mysql .

[root@localhost mysql]# chgrp -R mysql .

启动mysql服务

[root@localhost mysql]# ./support-files/mysql.server start

登录数据库

[root@localhost ~]# mysql -uroot -p000000

1.4创建MySQL密码

#mysqladmin -uroot password ‘123456’

1.5更改环境变量#export PATH=$PATH:/usr/local/mysql/bin/

1.6完成初始密码登录#mysql -uroot -p123456

1.7密码重置#vi /etc/my.cnf

在编辑界面添一行skip-grant

1.8修改完成重启MySQL

此处的命令是#systemctl restart mariadb

1.9切换到MySQL库#mysql -uroot

1.10连接 MySQL

#mysql -uroot -p123456

#mysql -uroot -p123456 -h127.0.0.1 -P3306

#mysql -uroot -p123456 -e “show databases”

1.11切换到库，对user表进行更新操作

修改完成确认新密码登录有效

二．MySQL常用命令

2.1查询库 #show databases；

2.2切换库#use mysql；

2.3查看库里的表 #show tables；

2.4查看表里的字段 #desc tb_name;

2.5查看建表语句 #show creat table tb_name\G;

2.6查看当前用户select user（）；

2.7查看当前使用的数据库 #select database（）；

2.8创建库#create database db1；

2.9创建表 #use db1; create table t1(`id` int(4), `name`char(40));

2.10查看当前数据库版本 #select version（）；

2.11查看数据库状态 #show status；

2.12查看各参数#show variables; show variables like 'max_connect%';

2.13修改参数#set global max_connect_errors=1000;

2.14查看表内行数 #select count（*） from mysql.user;

2.15查看队列 #show processlist； show full processlist；

MySQL创建用户以及授权

进行授权

#grant all on *.* to 'user1' identified by 'passwd';

#grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by

'passwd';

#grant all on db1.* to 'user3'@'%' identified by 'passwd';

查看授权表

#show grants

#show grants for user2@192.168.213.137;

三．MySQL常用sql短句

3.2查看db表内的内容 select * from mysql.db;

3.3查看db表内含有db字段的内容

#select db，user from mysql.db;

3.4搜索多个字段

#select db，user from mysql.db;

3.5查询host为127.0的内容

#select * from mysql.db where host like ’192.168.%‘；

3.6向db1.t1中插入内容 insert into db1.t1 values (1, ‘abc’);

3.7 把 id=1 的字段内容更新成 aaa update db1.t1 set name='aaa' where id=1;

3.8清空 db1.t1 表内的内容 truncate table db1.t1;

注释：清空后表的结构依然存在

3.9删除 db1.t1 表内的内容 drop table db1.t1;

3.10清空 db1.t1 数据库 drop database db1;

案例：根据合理需求创建表（设计合理的数据类型和长度）

设计一张员工信息表，要求如下：

1.编号（纯数字）

2.员工工号（字符串类型，长度不超过十位）

3.员工姓名（字符串类型，长度不超过十位）

4.性别（男/女，存储一个汉字）

5.年龄（正常人年龄，不可能存在负数）

6.身份证号（二代身份证均为18位，身份证中有x这样得字符）

7.入职时间（取值年月日即可）

具体实操如下

四．MySQL 数据库的备份与恢复

4.2 恢复库

mysql -uroot -plinux mysql < /tmp/mysql.sql

4.3 备份表

mysqldump -uroot -plinux mysql user > /tmp/user.sql

4.4 恢复表

mysql -uroot -plinux mysql < /tmp/user.sql

4.5 备份所有库

mysql dump -uroot -p -A > /tmp/123.sql

4.6 只备份表结构

mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql

三．MySQL主从配置

主配置

基础配置#vi/etc/my.cnf

重启MySQL服务

#/etc/init.d/mysqld restart

备份mysql库（加入环境变量）

# mysqldump -uroot mysql > /tmp/mysql.sql

创建一个库保存数据

# mysql -uroot -e "create database kei"

5.1、了解MySQL数据库以及MySQL主从服务

MySQL数据库：顾名思义，一个可以提供数据存储服务的软件。

MySQL主从服务：在A、B两机器做主从后，在A上写数据，B也会跟着写数据，两者数据同步。

MySQL主从数据库配置

5.2环境配置

5.2.1关闭防火墙

[root@localhost ~]# setenforce 0

[root@localhost ~]# systemctl stop firewalld

[root@localhost ~]#

4.1备份库

mysqldump -uroot -p123456 mysql > /tmp/mysql.sql

5.2.3编辑hosts文件

[root@slave ~]# vi /etc/hosts

5.2.4配置主节点

编辑master主节点的数据库配置文件my.cnf并添加如下内容

[root@master ~]# vi /etc/my.cnf

5.2.5编辑完成后重启mysql服务

[root@master ~]# systemctl restart mysql

5.2.6设置主节点数据库权限

登录主节点master数据库，设置权限在任何客户端均可以root登录数据库

[root@master ~]# mysql -uroot -p123456

Mysql> grant all privileges on *.* to root@'%' identified by "123456";

5.2.7在master数据库中创建user用户让从节点slave连接，并赋予从节点同步主节点数据库的权限

5.2.8配置从节点

编辑slave主节点的数据库配置文件my.cnf并添加如下内容

[root@slave ~]# vi /etc/my.cnf

5.2.2修改主机名

根据节点要求修改3台主机的主机名

Master主节点主机名修改

[root@localhost ~]# hostnamectl set-hostname master

[root@localhost ~]# bash

Slave从节点主机名修改

[root@localhost ~]# hostnamectl set-hostname slave

[root@localhost ~]# bash

5.2.9编辑完成后重启mysql服务

[root@master ~]# systemctl restart mysql

5.2.10登录从节点slave数据库，配置从节点连接主节点的连接信息

[root@slave ~]# mysql -uroot -p123456

Mysql>change master to master_host='master',master_user='user',master_password='mysql';

5.2.111启动slave节点服务并查看节点状态

Mysql> start slave;

Mysql> show slave status\G

5.3主从测试

在master节点的MySQL中创建test数据库，并在test库中创建company表，并向表中添加数据（创建非空表并创建name和addr两个列，在表中添加shenyang china）

Mysql> create database test;

Mysql> use test;

Mysql> create table company(id int not null primary key,name varchar(50),addr varchar(255));

Mysql> insert into company values(1,"shenyang","china");

从slave节点查看数据库的内容，从节点db2的数据库就会同步主节点数据库创建的test库，可以在从节点查询test数据库与表company

六．MySQL数据库运维

6.1创建数据库

建一个名称为“test”数据库。命令如下所示：

[root@mysql ~]# mysqladmin -uroot -p123456 create test

在“test”数据库中创建一个名为“tables”数据表。命令如下所示：

6.2 数据库备份

导出整个数据库，命令如下所示。

[root@mysql ~]# mysqldump -uroot -p123456 test > test.sql

[root@mysql ~]# ls

test.sql

6.3导出一个表，命令如下所示：

[root@mysql ~]# mysqldump -uroot -p123456 test tables > test_tables.sql

[root@mysql ~]# ls

test.sql test_tables.sql

6.4删除test数据库进行导入测试，用mysqldump备份的文件是一个可以直接导入的SQL脚本。有两种方法可以将数据导入，一种用msql命令，把数据库文件恢复到指定的数据库，命令如下所示：

[root@mysql ~]# mysqladmin -uroot -p123456 drop test

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the 'test' database [y/N] y

Database "test" dropped

[root@mysql ~]# mysql -uroot -p123456

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 26

Server version: 10.3.18-MariaDB-log MariaDB Server

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

MariaDB [(none)]> create database test;

Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> quit

Bye

[root@mysql ~]# mysql -uroot -p123456 test < test.sql

6.4.1第二种，可以使用source语句方法导入数据库，把数据库文件恢复到指定的数据库，命令如下所示：

[root@mysql ~]# mysqladmin -uroot -p123456 drop test

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the 'test' database [y/N] y

Database "test" dropped

[root@mysql ~]# mysql -uroot -p123456

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 30

Server version: 10.3.18-MariaDB-log MariaDB Server

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

Mysql> create database test;

Query OK, 1 row affected (0.027 sec)

Mysql> use test

Database changed

Mysql> source /root/test.sql;

6.5添加用户并授权

授权root用户可以在任何节点访问test数据库下所有表，“%”代表所有节点机器，命令如下所示：

Mysql]> GRANT ALL PRIVILEGES ON test.* TO 'root'@'%' IDENTIFIED BY '123456' ;

Query OK, 0 rows affected (0.001 sec)

Mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY '123456' ;

Query OK, 0 rows affected (0.001 sec)

添加root用户对test数据库授增、删、改、查的权限，命令如下所示：

Mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO 'root'@'%' IDENTIFIED BY '123456' ;

Query OK, 0 rows affected (0.001 sec)

6.6MySQL数据库优化

修改数据库配置文件，添加参数，命令如下所示：

# vi /etc/my.cnf

在文件中添加以下命令，优化数据库：

[mysqld]

thread_concurrency = 64 #CPU核数 * 2

max_connections=1500 #最大连接（用户）数。每个连接MySQL的用户均算作一个连接

max_connect_errors=30 #最大失败连接限制

bulk_insert_buffer_size = 32M #批量插入数据缓存大小

query_cache_type=1 #查询缓存 (0 = off、1 = on、2 = demand)

query_cache_size = 64M #指定mysql查询缓冲区大小

max_allowed_packet = 128M #通信缓冲大小

read_buffer_size = 8M #顺序读取数据缓冲区使用内存

read_rnd_buffer_size = 32M #随机读取数据缓冲区使用内存

参数优化解析，见表3-10-2。

命令	解析
thread_concurrency	并发线程数，建议为CPU核心数乘以2
max_connections	最大连接（用户）数。每个连接MySQL的用户均算作一个连接
max_connect_errors	最大失败连接限制
bulk_insert_buffer_size	批量插入数据缓存大小，可以有效提高写入效率，默认为8 MB
query_cache_type	控制着查询缓存功能的开启的关闭。0时表示关闭，1时表示打开，2表示只要select 中明确指定SQL_CACHE才缓存
query_cache_size	指定MySQL查询缓冲区的大小，用来缓冲select的结果，并在下一次同样查询的时候不再执行查询而直接返回结果，根据Qcache_lowmem_prunes的大小，来查看当前的负载是否足够高，在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建议把该功能禁掉。属重点优化参数（主库增删改-MyISAM）
max_allowed_packet	设定在网络传输中一次可以传输消息的最大值，系统默认为1 MB，最大可1 GB
read_buffer_size	来做MYISAM表全表扫描的缓冲大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且用户认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
read_rnd_buffer_size	随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

七．构建读写分离得数据库集群

6.1.解读读写分离数据库集群

6.2 基础准备

节点规划

IP	主机名	节点
192.168.213.151	Mycat	Mycat中间件服务节点
192.168.213.146	Slave	Mysql数据库集群从节点
192.168.213.153	Master	Mysql数据库集群主节点

其中2台虚拟机master和slave部署MariaDB数据库服务，搭建主从数据库集群；master作为主节点，负责写入数据库信息；slave作为从节点，负责读取数据库信息。

虚拟机Mycat部署Mycat数据库中间件服务，将用户提交的读写操作识别分发给相应的数据库节点。

此外Mycat所使用的压缩包为Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

6.3环境配置

6.3.1 关闭防火墙

在配置之前3台虚拟机都要关闭防火墙

[root@mycat ~]# setenforce 0

[root@mycat ~]# systemctl stop firewalld

[root@mycat ~]#

6.3.2修改主机名

根据节点要求修改3台主机的主机名

Master 主节点主机名修改

[root@localhost ~]# hostnamectl set-hostname master

[root@localhost ~]# bash

Slave从节点主机名修改

[root@localhost ~]# hostnamectl set-hostname slave

[root@localhost ~]# bash

Mycat节点主机名修改

[root@localhost ~]# hostnamectl set-hostname mycat

[root@localhost ~]# bash

6.3.3编辑hosts文件

3台虚拟机的 /etc/hosts 均填加如下内容

[root@slave ~]# vi /etc/hosts

6.3.4 安装JDK环境

再部署Mycat中间件服务时需要先安装JDK软件，注意JDK1.7软件版本需要及以上

Mycat节点安装java环境

[root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel

[root@mycat ~]# java -version

6.4 部署主从数据库集群服务

1.安装Mariadb服务

在master主节点和slave从节点上分别安装mariadb服务

[root@slave ~]# yum install -y mariadb mariadb-server

安装完成后,master和slave节点均启动mariadb服务，并设置开机自启动

[root@master ~]# systemctl start mariadb

[root@master ~]# systemctl enable mariadb

2.初始化mysql服务

在master和slave节点上初始化mariadb数据库，设置数据库root访问用户密码为123456

[root@master ~]# mysql_secure_installation

3.配置主节点

编辑master主节点的数据库配置文件my.cnf并添加如下内容

[root@master ~]# vi /etc/my.cnf

编辑完成后重启mysql服务

[root@master ~]# systemctl restart maysql

4.主节点数据库权限

登录主节点master数据库，设置权限在任何客户端均可以root登录数据库

[root@master ~]# mysql -uroot -p123456

mysql> grant all privileges on *.* to root@'%' identified by "123456";

在master数据库中创建user用户让从节点slave连接，并赋予从节点同步主节点数据库的权限

[root@master ~]# Mysql> grant replication slave on *.* to 'user'@'slave' identified by '123456';

5.配置从节点

编辑slave主节点的数据库配置文件 my.cnf并添加如下内容

[root@slave ~]# vi /etc/my.cnf

6.编辑完成后重启mysql服务

[root@master ~]# systemctl restart mysql

7.登录从节点slave数据库，配置从节点连接主节点的连接信息

[root@slave ~]# mysql -uroot -p123456

Mysql>change master to master_host='master',master_user='user',master_password='123456';

注意：这里要关闭从库的复制线程这将停止从库的l/o线程和SQL线程

8.连接主从数据库

其中master_host为主节点名、master_user为创建的主从连接的用户user

mysql>change master to master_host='master',master_user='user',master_password='123456';

9.启动slave节点服务并查看节点状态

Mysql> start slave;

Mysql> show slave status\G

6.5.主从测试

在master节点的MySQL中创建test数据库，并在test库中创建company表，并向表中添加数据（创建非空表并创建name和addr两个列，在表中添加shenyang china）

Mysql> create database test;

Mysql> use test;

Mysql> create table company(id int not null primary key,name varchar(50),addr varchar(255));

Mysql> insert into company values(1,"shenyang","china");

前面已经创建了所以直接使用

从slave节点查看数据库的内容，从节点db2的数据库就会同步主节点数据库创建的test库，可以在从节点查询test数据库与表company

若可以查询到信息，验证主从数据库集群功能在正常运行

至此，MySQL主从连接全部结束

6.6部署mycat中间件服务

1.安装mycat中间件服务

将下载好的mycat服务软件包上传解压到mycat节点的/uesr/local 目录下，并赋予mycat目录权限

[root@mycat ~]# chown -R 777 /usr/local/mycat/

将mycat服务变量添加至系统/etc/profile 变量文件中，并使其生效

[root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile

[root@mycat ~]# source /etc/profile

2.编辑mycat逻辑库配置文件

Mycat读写分离服务配置文件 schema.xml 在/usr/local/mycat/conf/目录下，标红得位置要注意修改自己实际得IP地址

[root@mycat ~]# vi /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

</mycat:schema>

给以下代码说明

<?xml version="1.0"?> XML文件的版本

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

指定文档类型和DTD文件，用于XML文件验证

<mycat:schema xmlns:mycat="http://io.mycat/"> 定义mycat的命名

定义逻辑库USERDB

Name 指定了逻辑库的名称

checkSQLschema 表示是否检查SQL语句的逻辑库

SqlMaxLimit 定义了SQL语句中允许的最大数量限制

DataNode 指定逻辑库映射到的数据节点

定义了一个数据节点 dn1

name 属性指定了数据节点的名称。

dataHost 指定了数据节点关联的数据主机。

database 指定了数据节点关联的具体数据库

定义一个数据主机localhost1

name 属性指定了数据主机的名称。

maxCon 定义了最大连接数。

minCon 定义了最小连接数。

balance 定义了负载均衡策略。

dbType 指定了数据库类型。

dbDriver 指定了数据库连接驱动。

writeType 指定了写操作的类型。

switchType 指定了切换类型。

slaveThreshold 定义了从库的阈值。

<heartbeat>select user()</heartbeat> 心跳检查SQL语句，检测连接的可用性

writeHost 定义了主写主机的连接信息，包括主机名、URL、用户名和密码。

readHost 定义了从读主机的连接信息，同样包括主机名、URL、用户名和密码

</writeHost>

</dataHost>

</mycat:schema>

3.修改配置文件

[root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml

4.编辑mycat访问用户

修改/usr/local/mycat/conf/server.xml文件

在文档最后部分，修改标注的字段即可

[root@mycat ~]# vi /usr/local/mycat/conf/server.xml

<property name="schemas">USERDB</property>

此段编辑完成后，删除一下字段

<property name="schemas">TESTDB</property>

</user>

更改完成确认无误后，保存退出

5.启动mycat服务

[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start

[root@mycat ~]# netstat -ntpl

启动成功后，查看端口的开放状态，若有8066和9066端口，表示mycat服务开启成功

6.7验证数据库集群服务读写分离功能

（1）用Mycat服务查询数据库信息

先在Mycat虚拟机上使用Yum安装mariadb-client服务。

[root@mycat ~]# yum install -y MariaDB-client

在Mycat虚拟机上使用mysql命令查看Mycat服务的逻辑库USERDB，因为Mycat的逻辑库USERDB对应数据库test（在部署主从数据库时已安装），所以可以查看库中已经创建的表company。命令如下。

[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p123456

MySQL [(none)]> show databases;

MySQL [(none)]> use USERDB

MySQL [USERDB]> show tables;

MySQL [USERDB]> select * from company;

（2）用Mycat服务添加表数据

在Mycat虚拟机上使用mysql命令对表company添加一条数据(2,"basketball","usa")，添加完毕后查看表信息。命令如下。

MySQL [USERDB]> insert into company values(2,"bastetball","usa");

（3）验证Mycat服务对数据库读写操作分离

在Mycat虚拟机节点使用mysql命令，通过9066端口查询对数据库读写操作的分离信息。可以看到所有的写入操作WRITE_LOAD数都在db1主数据库节点上，所有的读取操作READ_LOAD数都在db2主数据库节点上。由此可见，数据库读写操作已经分离到db1和db2节点上了。命令如下。

[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p123456 -e 'show @@datasource;'

查询结果如图4-1-3所示。

两张不够花

关注

22
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫