MYSQL数据库学习-Shuttle.L-运维学习笔记

MYSQL数据库学习

mysql安装下载官网：https://dev.mysql.com/downloads/file/?id=494701

一、Mysql数据库安装

1、修改防火墙以及selinux状态
1.1、首先查看防火墙和selinux是否都关闭，若没有关闭请手动关闭
systemctl stop firewalld
Systemctl diable firewalld
Vim /etc/selinux/config
SELINUX=disabled
Setenforce 0
getenforce （查vi /etc看selinux的状态）

2、安装部署数据库
首先执行命令‘df -hT ’查看磁盘大小，确定数据库的安装目录，及数据目录
2.1、安装cmake（Mysql5.6.40系列需要使用cmake）
①首先通过以下命令创建目录
mkdir -p /tmp/cmake
②将cmake-3.0.2.tar.gz介质上传至/tmp/cmake
③将安装介质解压
#cd /tmp/cmake
#tar -zxvf cmake-3.0.2.tar.gz
④安装所需依赖包
#yum install gcc libgcc gcc-c++ libstdc+±devel ncurses*
⑤进入解压路径，进行编译cmake
#cd /tmp/cmake/cmake-3.0.2
执行#./configure
执行成功，如图

⑥安装cmake，开始执行一下步骤
执行：make
然后执行：make install
执行成功，如图

⑦验证cmake是否安装成功，#执行一下命令，#cmake
出现以下结果表示成功

2.2、数据库软件安装mysql
2.2.1、将mysql安装介质mysql-5.6.40.tar.gz拷贝到主机上
2.2.2、键入如下命令创建目录以及用户
①#mkdir -p /tmp/mysql
②#mkdir -p /usr/local/mysql
③#mkdir /usr/local/mysql/data
④#groupadd mysql
⑤#useradd -r -g mysql mysql
⑥#passwd mysql
⑦设置密码为mysql
⑧#chown -R mysql:mysql /usr/local/mysql

2.2.3、将mysql安装介质上传至/tmp/mysql，将安装介质解压，解包mysql的安装介质：
#cd /tmp/mysql，#tar -zxvf mysql-5.6.40.tar.gz，进入目录/tmp/mysql/mysql-5.6.40，执行以下命令，开始编译mysql：①# cd /tmp/mysql/mysql-5.6.40，②#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1
注：部署路径：/usr/local/mysql，数据路径：/usr/local/mysql/data
注：根据df -hT 确定系统磁盘的大小，确定安装的目录及数据的目录

2.2.4、安装mysql,开始执行一下步骤，首先执行：make，然后执行：make install
注意：这个执行需要很长时间！
当以上步骤执行成功后，进入mysql安装目录，开始初始化数据库
①yum -y install perl-Module-Build.noarch （linux7上需要执行，linux6不需要安装）
②#cd/usr/local/mysql/scripts/
③./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
安装完成后重新执行，如图

2.2.5、Mysql 脚本配置
①cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
②cp /usr/local/mysql/bin/mysql /bin/mysql
③cp /usr/local/mysql/bin/mysqldump /bin/mysqldump

2.2.6、修改mysql配置文件,调优
vi /etc/my.cnf, 之前的内容删除掉
[client]
port=3306
socket=/usr/local/mysql/mysql.sock
default_character_set=utf8
[mysqld]
port=3306
socket=/usr/local/mysql/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
skip-external-locking
key_buffer_size=16M
max_allowed_packet=1M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M
character-set-server=utf8
lower_case_table_names=1
log-error=/var/log/mysqld.log
skip-name-resolve
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=8192M #一般设置为主机内存的70%
max_connections=1000
max_connect_errors=10000

2.2.7、启动mysql进行验证
执行以下命令，如图
#/etc/init.d/mysqld restart

二、数据库主从模式

1.数据库主从同步原理

Mysql的主从同步就是当master（主库）发生数据变化的时候，会实时同步到slave（从库）

2.数据库主从配置（version5.6）

1)配置主库：
创建并授权从库同步所使用的账号：
GRANT REPLICATION SLAVE ON . to ‘repl’@‘192.168.8.11’ identified by ‘test123456’;
FLUSH PRIVILEGES;
修改配置文件,开启bin-log日志记录功能,设置serverid（记录对数据库增删改的操作）：
Vim my.cnf
[mysqld]
log-bin=/var/lib/mysql/binlog #这个目录要是mysql有权限写入的
server-id=1 #master端id号
binlog-do-db = cmdb #要同步的数据库名

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
…
重启主数据库
检查主服务器二进制日志及偏移量，从这个点开始进行数据恢复
Show master status;

2)配置从库
修改my.cnf
[mysqld]
server-id=2 #从库id
log-bin=mysql-bin
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
replicate-do-db=cmdb #要同步的数据库名
…
停止slave进程
Mysql > stop slave;
Mysql > change master to
master_host=’68.32.103.198’,
master_user=’repl’,
master_password=’123456’,
master_log_file=’binlog.000001’,
master_log_pos=120; #master_log_file和 master_log_pos分别对应步骤7中查询出来的 File和Position
查看slave状态
Show slave status\G; #\G排列
io线程（将二进制日志转为中继日志）及
sql线程（执行中继日志实现增删改）为yes则成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3)同步主从数据
主库操作：
对主库进行锁表，停止增删改操作
mysql>flush tables with read lock;
主库导出需要备份的数据库
mysqldump -uroot -ptest123 cmdb > cmdb.sql
将备份传输到从库
scp cmdb.sql root@192.168.8.11:/root/

从库操作：
mysql>slave stop;
新建需要同步的数据库
mysql> create database cmdb default charset utf8;
导入数据
mysql -uroot -ptest123 cmdb<cmdb.sql

从库备份导入后，重启slave进程，主库解锁
Slave start;
unlock tables;

3.用途：

(1)实时灾备，用于故障切换(2)读写分离，提供查询服务(3)备份,避免影响业务

4.同步时主从的操作

1)、在master机器上的操作：
　 当master上的数据发生变化时，该事件变化会按照顺序写入bin-log中。当slave链接到master的时候，master机器会为slave开启binlog dump线程。当master的binlog发生变化的时候，bin-log dump线程会通知slave，并将相应的binlog内容发送给slave。
2)、在slave机器上的操作：
当主从同步开启的时候，slave上会创建两个线程：I\O线程。该线程连接到master机器，master机器上的binlog dump 线程会将binlog的内容发送给该I\O线程。该I/O线程接收到binlog内容后，再将内容写入到本地的relay log（中继日志）；sql线程。该线程读取到I/O线程写入的ralay log。并且根据relay log。并且根据relay log 的内容对slave数据库做相应的操作。

原理图：

5.主从的多种形式

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的；
多主一从—5.7开始支持
联级复制—

6.延迟问题解决方案：
查看从数据库状态：Show slave status;
Master_Log_File：SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos：在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置
Relay_Log_File：SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos： 在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File：由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Slave_IO_Running： I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running： SQL线程是否被启动
Seconds_Behind_Master：从属服务器SQL线程和从属服务器I/O线程之间的时间差距，单位以秒计。

什么情况下会出现主从同步延迟：

show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大
show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

解决方案：
(1)架构方面：
1.采用分库分表的架构，多个数据库多个表，分散压力
2.单个库读写分离，一主多从，主写从读，分散压力，从库压力大于主库
3.服务架构上业务与mysql之间加入memcache或者redis的cache层。降低mysql的读压力
4.不同业务的mysql物理上放在不同机器，分散压力
5.使用比主库更好的硬件设备作为slave
总结，mysql压力小，延迟自然会变小
(2)硬件方面：
1.采用好服务器，cpu核心数多更好
2.采用磁盘阵列或san存储，提高读写性能
3.主从保证在统一交换机下，万兆环境，网络好

三、常用sql语句学习

对库的增删改查

1.增
create database 库名称;
CREATE DATABASE test;
create database 数据库名称 charset 编码方式;
CREATE DATABASE test CHARSET utf8;
2.删
drop database 库名称;
DROP DATABASE test;
3.改
修改编码方式:alter database 库名称 charset 编码方式;
ALTER DATABASE test CHARSET utf8;
4.查
查看所有库:show databases;

对表的增删改查

1.增
增加表单create table 表名称(字段1 数据类型(字符长度),字段2 数据类型(字符长度)…限制条件); #至少有一股字段和数据类型,最后一个字段已经最后一个数据类型结束后不加,
Create table test (name char,number varchar,…);
char固定长度varchar可变长
use test;
create TABLE test (
name CHAR(255) not null PRIMARY KEY,
msg VARCHAR(255),
number INT(255));
2.删
删除表:drop table 表名称;
清空表里面数据: truncate table 表名称;
3.改
字段
添加字段:alter table 表名称 add 字段 字段数据类型
ALTER TABLE test add number int;
多个字段：
ALTER TABLE test add (number int,name char(255),msg varchar(255));
删除字段:alter table 表名称 drop 字段
ALTER TABLE test drop column number;
修改字段数据类型:alter table 表名称 modify 字段 新的数据类型（字符长度)
ALTER TABLE test MODIFY number VARCHAR(255);
替换字段:alter table 表名称 change 旧字段名称 新字段 新字段数据类型

表
改表名称:rename table 表名称 to 新名称 或alter table a2 rename a1;
改表的编码:alter table 表名 charset 新编码;

查
查看所有表:show tables
查看指定表信息:describe a1;或desc a1 #a1为表名称
查看指定表创建信息:show create table 表名称

对于表里的数据增删改查

1.增
插入一个值
insert into 表名 values(v1,v2,…) # 该方式必须保证 插入的数据个数与 表格字段一 一对应
insert into 表名(字段名称1,字段名称2) values(v1,v2) # 该方式必须保证 插入的数据个数与 必须与指

插入多个值
insert into 表名 values(v1),(v2)
insert into 表名(字段名称1,字段名称2) values(v1,v1),(v2,v2)
INSERT INTO test1 (id, name, number, count, msg, time) VALUES (‘1’, ‘luo’, ‘1’, ‘90’, ‘test’, ‘2021-04-14’),(‘2’, ‘luoyb’, ‘2’, ‘90’, ‘test2’, ‘2021-04-15’);
删
删除所有:delete from 表名;
删除指定条件下:delete from 表名 where 条件;
Delete from table1 t1 where name in (select * from t1 where name like ‘123’);

改
修改所有数据:update 表名 set 字段名称=新的值,字段名称2=值2;
修改满足条件的数据:update 表名 set 字段名称=新的值,字段名称2=值2 where 条件

查
查看所有字段:select * from 表名;
查看指定字段:select 字段 from 表名 ;
查看某个条件所有的字段:select * from 表名 where 条件;
查看某个条件下的某个字段select 字段 from 表名 where 条件;

对用户增删改查

1.创建用户
Create user luoyb@’%’ indentified by ‘123456;
#创建luoyb用户，’%’允许任何主机访问通过账号连接数据库，密码123456
2.授权并创建用户
#授权并创建以localhost登录，拥有所有库的所有表的所有权限且密码为test3的用户test3
GRANT all ON . TO ‘test3’@localhost IDENTIFIED BY ‘test3’;
3.取消授权
#取消用户abc对所有库的所有表的drop,delete权限
Revoke drop,delete on . from abc@’%’;
4.删除用户
DROP USER ‘test1’@‘localhost’;
5.修改用户名称（需要update权限）
RENAME USER ‘test1’@‘localhost’
-> TO ‘testUser1’@‘localhost’;
6.查询用户
#查询用户及用户权限
SELECT * FROM mysql.user;
查看权限
SHOW GRANTS FOR ‘username’@‘hostname’;