【mysql日常使用及主从部署】

本文介绍了MySQL的事务隔离级别,包括未授权读取、读提交、可重复读取和序列化。详细阐述了日常MySQL使用中的备份与还原方法,以及库表操作,如创建、修改和删除。还涉及了外键的设置、用户权限的创建和管理,密码策略调整,二进制日志和慢查询日志的管理和主从复制的配置步骤,强调了主从复制中需要注意的事项和常见错误代码的解释。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、原理性概念

1、事物隔离级别

【MySQL】事务与隔离级别(99%的人存在误区)_哔哩哔哩_bilibili

(1)事物执行过程会遇到的几个问题
 ---脏读:A事物还未提交,B事物就读到了数据;
 ---不可重复读:A事物在本次事物中,对自己未操作过的数据,进行了多次读取,结果出现了不一致或记录不存在的情况。(破坏了一致性 update delete)
 ---幻读:A事物在本次事物中,对自己未操作过的数据,进行了多次读取,第一次读取时记录不存在;第二次读取时记录出现了(破坏了一致性 insert)

(2)定义标准--四种隔离级别
RU---Read uncommitted(未授权读取、读未提交)
RC---Read committed(授权读取、读提交)
RR---Repeatable read(可重复读取)
SE---Serializable(序列化)
(3)各厂商实现并不是完全按照这个标准来做,举例InnoDB的实现方案
RU:事物读取的时候,不加锁
RC:事物读取的时候加行级共享锁(读到才加锁),一旦读完、立即释放(并不是事物结束)
RR:事物读取的时候加行级共享锁,事物结束时才释放;
SE:事物读取的时候加表记共享锁,事物结束才释放;

二、mysql的日常使用

1、mysql备份还原

(1)备份导出数据

备份某一个库 mysqldump -u root -p --database testdb > /tmp/testdb.sql ​

备份所有库 mysqldump -u root -p --all-databases >/tmp/alldb.sql

备份某一张表 mysqldump -u root -p testdb table1 >/tmp/table1.sql

(2)还原导入数据

外部导入 mysql -u root -p </tmp/testdb.dump

进入数据库下导入 source /tmp/testdb.dump

mysql官方yum配置库下载 https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm 192

2、库表操作

(1)展示库、进入库,操作表;

show datebases; drop datebase t1; use t1; show tabels;

(2)对表的操作变更

alter table test add(sex varchar(2));

alter table test drop sex;

alter table test modify age int(3);

alter table test change age old int(3);

alter table test rename testtable;

(3)对表数据的操作变更

create datebase db1 default character set utf8;

create table test(id int(3),name varchar(20),age varchar(3));

insert into test values(1,'longyakun','22');

delete from test where id=1;

select * from test; update test set name='marry' where name='jack';

(4)删除表数据、删除表结构

truncate table testtable; 删除表数据 drop table testtable; 删除表结构

设置别名 select sal as 'gongzi',name as 'xingming' from qa where sal>'2500';

3、设置外键

create table userinfo( id int auto_increment primary key, name varchar(32) not null default '', depart_id int not null default 1, CONSTRAINT fk_user_dep FOREIGN KEY (depart_id) REFERENCES dep(id));

4、创建用户及权限设置

(1)创建用户

create user 'qfuser'@'localhost' identified by 'Qf/123com' ;

grant all on *.* to 'admin'@'localhost' identified by 'qF/12lcoQ';

重置用户密码

①set password for zk@'%'=password('zookeeper');

②update mysql.user set authentication_string=password('zabbix')   where user='zabbixt' and host='%';

(2)给用户授权

grant select,update,insert on db.table to 'qfuser'@'localhost';

(3)回收用户权限

查看用户权限信息 show grants for 'qfuser'@'localhost';

回收存在的权限 revoke insert on db.table from 'qfuser'@'localhost';

5、密码策略

查看当前密码策略配置 show variables like 'validate%';

修改配置 set global validate_password_length=9;

刷新配置信息 flush privileges;

6、日志管理

(1)二进制日志

查看二进制日志配置 show variables like '%log_bin%';

查看二进制日志文件 show binary logs;

查看当前使用的二进制日志文件 show master status;

查看二进制日志事件 show binlog events in 'mysql-bin.000001';

①、开启二进制日志

选择合理空间足够的目录作为binlog存储位置,修改mysql配置( selinux是对deamon进程有操作权限的限制的,提前将selinux设置为disabled【vi /etc/selinux/config 修改SELINUX=disabled 】,重启机器生效

#创建binlog存储目录

mkdir -p /data/3306/binlog

#将目录权限赋予给mysql用户

chown -R mysql:mysql /data/

#修改mysql配置 vi /etc/my.cnf

server_id=1

log-bin=/data/3306/binlog/mysql-bin

#重启mysqld服务

②、通过binlong恢复数据

#查看事件对应pos

show binlog events in 'mysql-bin.000001';

#CLI模式下截取日志

mysqlbinlog --start-position=pos1 --stop-position=pos2 mysql-bin.000001 > recover.sql

#进入mysql下

mysql>source recover.sql

(2)慢日志

a、开启慢日志

#查看慢日志是否开启

show variables like '%slow_query%';

#慢日志开启配置 vim /etc/my.cnf

slow_query_log=1 slow_query_log_file=/data/3306/slow.log long_query_time=1 log_queries_not_using_indexes=1

#重启mysql服务

systemctl restart mysqld

7、传统主从部署(Mysql复制最好确保master和slave服务器上的Mysql版本相同;如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本

(1)主库配置

#主数据库创建远程登录slave用户,并授权

mysql>create user 'slave'@'192.168.%.%' identified by 'Qzmp@2020';
mysql>grant replication slave on *.* to 'slave'@'192.168.%.%';

#获取主库的日志信息,file表示当前日志文件名称,position表示当前日志的配置

mysql>show master status;

(2)同步主库数据到从库

#对主库上的所有表加锁,停止修改,即在从库复制的过程中主库不能执行

update\delete\insert语句;
mysql>flush tables with read lock;
#备份master库数据,同步给slave数据库
主库 mysqldump -uroot -p --all-databases > /tmp/alldb.sql
备份文件复制到从库
从库执行还原 mysql -uroot -p </tmp/alldb.sql

#主库解锁

mysql>unlock tables;

(3)从数据库配置

#添加配置信息 vi my.cnf

default-storage-engine=INNODB

symbolic-links=0

#注意server_id不同于主库

server_id=2

#开启二进制日志

log_bin=/database/mysql/binlog/mysql-bin

#指定relay-log日志目录(千万要指定从slave的relay log日志),新指定目录提前创建并授权给mysql用户;

relay_log=/database/mysql/relay-log/mysql-relay
 

①、从库建立复制关系,即从库指定主库的日志信息和连接信息

mysql > change MASTER TO MASTER_HOST='192.168.4.6',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='Q88hh#2020', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1353;

②、从库启动复制进程

mysql > START SLAVE;

#查看slave状态是否正常

mysql >show slave status\G

(4)注意几点:


     1--master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。
     2--slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。
     3--Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
     4--Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本常见错误码-Error_code: 2003
     5--master和slave两节点间时间需同步

Last_IO_Errno错误代码说明(此部分引用转载):

1005:创建表失败
1006:创建数据库失败
1007:数据库已存在,创建数据库失败
1008:数据库不存在,删除数据库失败
1009:不能删除数据库文件导致删除数据库失败
1010:不能删除数据目录导致删除数据库失败
1011:删除数据库文件失败
1012:不能读取系统表中的记录
1020:记录已被其他用户修改
1021:硬盘剩余空间不足,请加大硬盘可用空间 
1022:关键字重复,更改记录失败
1023:关闭时发生错误
1024:读文件错误
1025:更改名字时发生错误
1026:写文件错误
1032:记录不存在
1036:数据表是只读的,不能对它进行修改
1037:系统内存不足,请重启数据库或重启服务器
1038:用于排序的内存不足,请增大排序缓冲区
1040:已到达数据库的最大连接数,请加大数据库可用连接数
1041:系统内存不足
1042:无效的主机名
1043:无效连接
1044:当前用户没有访问数据库的权限
1045:不能连接数据库,用户名或密码错误
1048:字段不能为空
1049:数据库不存在
1050:数据表已存在
1051:数据表不存在
1054:字段不存在
1065:无效的SQL语句,SQL语句为空
1081:不能建立Socket连接
1114:数据表已满,不能容纳任何记录
1116:打开的数据表太多
1129:数据库出现异常,请重启数据库
1130:连接数据库失败,没有连接数据库的权限
1133:数据库用户不存在
1141:当前用户无权访问数据库
1142:当前用户无权访问数据表
1143:当前用户无权访问数据表中的字段
1146:数据表不存在
1147:未定义用户对数据表的访问权限
1149:SQL语句语法错误
1158:网络错误,出现读错误,请检查网络连接状况
1159:网络错误,读超时,请检查网络连接状况
1160:网络错误,出现写错误,请检查网络连接状况
1161:网络错误,写超时,请检查网络连接状况
1062:字段值重复,入库失败
1169:字段值重复,更新记录失败
1177:打开数据表失败
1180:提交事务失败
1181:回滚事务失败
1203:当前用户和数据库建立的连接已到达数据库的最大连接数,请增大可用的数据库连接数或重启数据库
1205:加锁超时
1211:当前用户没有创建用户的权限
1216:外键约束检查失败,更新子表记录失败
1217:外键约束检查失败,删除或修改主表记录失败
1226:当前用户使用的资源已超过所允许的资源,请重启数据库或重启服务器
1227:权限不足,您无权进行此操作
1235:MySQL版本过低,不具有本功能

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值