学习数据库知识
一、思路和学习方法
本文学习于:B站平台UP主 IT 小当家,学习 MySQL 数据库,如里面加以自己的一些学习复现和理解,如有侵权会进行删除。学习Oracle以后也可以参考之前学习的UP主 C语言技术网(www.freecplus.net),在 b 站学习于 C 语言技术网。以后再继续学习Oracle部分,现在先学习 MySQL。
网址如下:http://www.freecplus.net/81c9c27c78764042af7aff5b532df9a2.html。
注意:我是用的 mysql 数据是 8.0.
在这一个章节中,UP 作为一个运维人员,讲解知识比较深入,对于我一个入门的不太友好,能学多少算多少吧。视频模拟了企业中常用的工具,主从备份等相关知识,需要配置很多东西,对于我没有操作出来的部分,都给出了整个过程的步骤,这里就当作初次学习吧,把重要的链接和重要语句记录放在上面,为了方便以后使用再次查阅和学习。
二、mysql 知识学习
2.1 MySQL 设置 UTF-8 方法
首先查看 mysql 有没有启动,
ps -ef |grep mysql
查看其启动的情况,结果如下,
然后查看字符集,登录 mysql 以后,使用下面语句
mysql>show variables like "%char%";
结果如下,
client 和 connection 和 UP 的不一样,我的已经是 utf8mb4 了,其中 utf8 和 utf8mb4 区别参考链接:https://www.cnblogs.com/cuiqq/p/11045487.html,UP主的是 latin1 形式。
修改方式 1 是在 linux 命令行输入
vi /etc/my.cnf
接着在里面补充加入这些语句,
[mysqld]
character-set-server=utf8
[client]
default-set-server=utf8
[mysql]
default-set-server=utf8
接着通过 kill -9 num 杀死 mysql 相应进程,重启就可以看到效果。接着插入一个中文相关的看效果,
use my_db
insert into tb1 values ('0005', '子金');
select * from tb1;
接着观察效果,可以显示中文的,
2.2 MySQL 密码破解
如果 mysql 用了很久,忘记了密码,该怎么处理呢。首先杀死 mysql 进程,注意在管理员模式下执行,执行如下指令,
ps -ef |grep mysql
kill -9 6045(关于mysql进程号)
// 如果要执行 mysqld_safe 指令,需要安装
yum install mysql-community-test
// 跳过输入密码执行方式
mysqld_safe --skip-grant-tables --user=mysql &
// 进入了 mysql 中,使用 mysql 语句,
select * from user;
desc user;
select * from user where User='root';
----> 接着复制这里面的 字段 部分 | localhost | root | *字段
update user set password=password('123456') where User='root';
quit
ps -ef |grep mysql
kill -9 6045(关于mysql进程号)
mysql -uroot -p
接着在跳过权限方式进行后台启动,按照 UP 教的方式,不通过密码的方式启动依旧不成功,可能是版本不一样的原因吧。不过在 vi /etc/my.cnf 文件中添加 skip-grant-tables 依旧不能解决问题。在记得密码的情况下,通过这个指令可以输入知道的密码后更改密码,
mysqladmin -uroot -p password 123456
2.3 MySQL 远程工具及导入
首先介绍一些常用的指令
// 如果需要备份 mysql 数据库,我创建的数据库为 my_db.sql, 在 linux 命令行输入
mysqldump -uroot -pAa1010155952** --events my_db >my_db.sql
就可以把 my_db.sql 导入到当下文件夹,其格式如下,
如果使用 mysqldump 方式导入,速度会比较慢,因此使用一个远程管理数据库工具 Navicat ,在 Windows 下面下载安装。
安装好配置以后,对 mysql 进行配置远程访问方式,方法如下,
// 更改加密方式
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Aa1010155952' PASSWORD EXPIRE NEVER;
// 更改root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Aa1010155952**';
// 没有这个下面运行显示不存在 mysql.user
use mysql;
// 开启root被远程访问
update user set host = '%' where user = 'root';
// 刷新权限
FLUSH PRIVILEGES;
然后查了网上很多教程,都显示出现 “error 2003: can’t connect to mysql server on ‘localhost’ (10061)”,因为我的 mysql 是虚拟机里面的,也已经启动了,和一般回答不一样,按照配置也没成功,解决方法可以参考这个博客,他说如果你是虚拟主机用户(购买的空间),则联系空间商检查 mysql 是否正常启动,并确认 mysql 的配置信息(是否为 localhost,这里不会,没有思路,后面学到再配置吧,链接
https://www.cnblogs.com/zhaohonghao-blog/p/6201664.html。这部分以后用到再进行学习吧。
应该按照道理,这样配置就可以连接上了。后面再学习了问吧,要是有人可以问一下应该可以很快解决或者换一台本地服务器,只为了学习方法吧。
2.4 Xtrabackup 备份 MySQL 数据库
Xtrabackup 用来备份大数据,效率更快更可靠,因此用它来模拟备份、删除、恢复真实的案例。
一般数据库都有上百 G 或者上 T 级别,虽然 mysqldump 也可以备份数据,但是需要时间特别多,因此引入 Xtrabackup 工具。一般备份的方法,
1. 用 mysqldump 备份
2. 直接 cp 复制
3. sqlhotcopy
4. 主从同步复制
5. Xtrabackup 备份
接下来讲解 mysqldump 和 xtrabackup 两个工具:一般 mysqldump 备份都会锁表,索表直接导致数据库只能访问 select, 不能执行 insert, update 等操作。如果是 myisam 表,也可以执行参数 --lock-tables=false 禁用锁表,但是可能导致数据信息不一致。如果支持事务的表,例如 InnoDB 和 BDB,–single-transaction 参数是一个更好的选择,因为它不锁定表。备份数据库指令,
// --opt 选项能够让指令快速导出数据,用到可以进行查阅,导入大数据库可能会出现问题。
mysqldump -uroot -pAa1010155952** --all-databases --opt --single-transaction --events >2014all.sql
接着就可以把数据库保存在当前目录下。MySQL 冷备份,mysqldump,MySQL 热拷贝均不能实现增量备份,在实际环境中使用较多,percona-xtrabackup 就是为了实现增量备份,Xtrabackup 有两个主要的工具:Xtrabackup 和 innobackupex,其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种数据表,而不能备份 MyISAM 数据表;innobackupex 封装了 Xtrabackup,是一个脚本封装,所以能同时备份 innodb 和 myisam 需要加一个读锁。
接着安装 xtrabackup ,按照视频安装一直出错,接着参考这两个链接:
https://blog.csdn.net/weixin_38339601/article/details/110197098 和 https://www.cnblogs.com/xuliuzai/p/10726001.html 进行安装,还有查看 https://blog.csdn.net/qq_29974229/article/details/119993421,实现了相应的效果,但是好像还是不好使。
注意工具兼容情况:mysql5.6及以前的版本适用于percona-xtrabackup-2.3.*版本,mysql5.7版本需安装percona-xtrabackup-2.3.*版本,mysql8.0版本需安装percona-xtrabackup-8.0.*版本,因此视频教程不能给出详细指导。
// 1. 首先对初始环境进行配置
yum -y install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bison libtool zlib-devel libgcrypt-devel libcurl-devel crypt* libgcrypt* openssl libxml2-devel expat-devel ncurses5-devel ncurses-devel vim-common libgpg-error-devel libidn-devel perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
// 2. 首先下载安装包
wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.25-17/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.25-17.1.el8.x86_64.rpm
最后安装效果如下,
可以看出已经安装成功了,下面是在网上找到的使用方法,链接如下:https://www.modb.pro/db/121579,按照上面进行操作,在 mysql 中语句如下,
//创建备份用户、配置参数及数据准备
mysql>CREATE USER 'back_user'@'localhost' IDENTIFIED BY 'Aa1010155952**';
mysql>GRANT BACKUP_ADMIN,PROCESS,RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO 'back_user'@'localhost';
mysql>GRANT SELECT ON performance_schema.log_status TO 'back_user'@'localhost';
mysql>GRANT SELECT ON performance_schema.keyring_component_status TO 'back_user'@'localhost';
mysql>FLUSH PRIVILEGES;
接下来运行基本都是错误的,看来数据库学习,很需要一个人带着弄啊,自己做非常费时间,而且方法很多是固定的,市场上应用也应该是比较成熟的,我觉得自己暂时没必要在这花太多时间, 先继续学习下去吧。
2.5 MySQL 数据库主从原理讲解
MySQL 是一个开放源码的小型关联式数据库管理系统,广泛应用于 Internet 上的中小型网站。由于体积小、速度快、总体拥有成本低,源码开放,许多中小网站为了降低网络总体拥有成本选择 MySQL 网站数据库。
随着访问量的不断增加,MySQL 数据库压力不断增加,需要对 MySQL 进行优化和架构改造,可以使用高可用、主从复制、读写分离、拆分库、拆分表进行优化,其主从原理如下,
MySQL 主从同步其实就是一个异步复制的过程,要实现复制首先需要在 master 上开启 bin-log 日志功能,整个过程需要开启 3 个线程,分别是主服务器 master 开启 IO 线程,从服务器 slave 开启 IO 线程和 SQL 线程。
1. 在从服务器执行 slave start, 从服务器上 IO 线程会通过授权的用户连接上 master,并请求 master 从指定的文件和位置之后发送 bin-log 日志内容。
2. Master 服务器接收到来自 slave 服务器的 IO 线程的请求后,master 服务器上的 IO 线程根据 slave 服务器发送的指定 bin-log 日志之后的内容,然后返回给 slave 端的 IO 线程。(返回的信息中除了 bin-log 日志内容外,还有本次返回日志内容后在 master 服务端的新 bin-log 文件名及在 bin-log 中的下一个指定更新位置。)
3. slave 的 IO 线程接收到信息后,将接收到的日志内容依次添加到 slave 端的 relay-log 文件的最末端,并将读取到的 Master 端的 bin-log 的文件名和位置记录到 master-info 文件中,以便在下一次读取的时候能够清楚的告诉 Master “我需要从某个 bin-log 的哪个位置开始往后的日志内容,发送给我”;
4. slave 的 SQL 线程检测到 relay-log 中新增加了内容后,会马上解析 relay-log 的内容成为在 Master 端真实执行时候的那些可执行内容,并在自身执行。
接下来是模拟的步骤,环境需要准备两个服务器,一个是主服务器,一个是从服务器,两个服务器都要安装 mysql 相关软件,注意两个服务器的 mysql 版本要一致,指令如下,
yum install -y mysql mysql-devel mysql-server mysql-libs
这里后面进行补充,听 UP 主关于数据库如果能理解很深刻,薪资方面也有很大的提升,特别是主从原理部分的理解深刻,这就是我学习的动力,先跟着 UP 主走马观花过一遍,然后再按照需求进行深入学习!后面文档没有展示出来,他说这里在后面课程会继续学习到,那继续了解吧。
2.6 MySQL 高效结构读写分离配置
Amoeba 是一个以 MySQL 为底层数据存储,并对应用提供 MySQL 协议接口的 proxy,它集中地响应应用的请求,依据用户事先设置的规则,将 SQL 请求发送到特定的数据库上执行,基于此实现负载均衡、读写分离,高可用性的需求。
Amoeba 相当于一个 SQL 请求的路由器,目的是负载均衡、读写分离、高可用性提供机制。常见的读写分离方案:Amoeba 读写分离,MySQL-Proxy 读写分离,基于程序读写分离。接着进行配置 MySQL 读写分离,配置主从服务器,首先修改主机 master 中 /etc/my.cnf 配置文件,然后修改从服务器 slave 中 /etc/my.cnf 的配置文件,
注意需要搭建两个服务器,一个作为主服务器,一个作为从服务器,两个服务器 mysql 版本需要一致。
1. 首先配置主服务器 master 的 cnf 文件,在 linux 输入指令 vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
security risks
symbolic-links=0
log-bin=mysql-bin
server-id = 1
auto_increment_offset=1
auto_increment_increment=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
replicate-do-db=all
2. 然后配置从服务器 slave 的 cnf 文件,在 linux 输入指令 vi /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
user=mysql
security risks
symbolic-links=0
log-bin=mysql-bin
server-id = 2
auto_increment_offset=2
auto_increment_increment=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
master-connect-retry=60
replicate-do-db=all
然后配置 change master 和授权即可,最后保证两端可以正常同步数据即可。
三、总结
现在觉得暂时学习停止吧,下面内容基本听不懂了,而且自己还没有应用需求,也不知道应用场景,后面使用到再学习吧。现在开始看着项目内容了,哪里不懂查哪里,然后也开始学习算法了。