Mysql 数据库安装部署
mysql架构详解
引用博客:https://blog.csdn.net/qq_33745102/article/details/88392220
mysql主从复制原理
MySQL实际生产都会数据库多数据库实例部署架构保持数据容灾,例如一主一从、一主多从、主从从等结构,如下采取一主一从的主从复制架构进行部署实验测试
什么是mysql主从复制?
MySQL 主从复制是指数据可以从一个MySQL主数据库服务器复制到一个或多个从MySQL节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。Binary log:主数据库的二进制日志。Relay log:从服务器的中继日志。
(1)master服务器将数据的改变记录二进制binlog日志,当master上的数据发生改变时,则将其改变写入二进制日志中;
(2)slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/OThread请求master二进制事件
(3)同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中,从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后I/OThread和SQLThread将进入睡眠状态,等待下一次被唤醒。
如下采取一主一从部署实验环境:
Centos7 主从部署Mysql5.7
获取mysql包
wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
安装mysql
yum install mysql-community-server
修改配置文件
创建日志所在目录
mkdir -p /var/log/mysql
chown -R mysql.mysql /var/log/mysql
创建数据所在目录
mkdir -p /database/mysql
chown -R mysql.mysql /database/mysql
[HTML]: 说明
数据库所在目录: /database/mysql
sock文件路径: /var/lib/mysql/mysql.sock
配置文件路径: /etc/my.cnf
慢查询日志路径: /var/log/mysql/mysql_slow_query.log
错误日志路径: /var/log/mysql/mysqld.log
pid文件: /var/run/mysqld/mysqld.pid
binlog日志文件: /database/mysql-bin/mysql_bin.log
vi /etc/my.conf
主配置
**service_id=**保持唯一性,一般可设置节点IP地址,此处主用1备为2
[mysqld]
port=3306
datadir=/database/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=1
log_bin=/database/mysql-bin/mysql_bin
binlog_ignore_db=mysql
binlog_cache_size=1M
binlog_format=mixed
备配置
[mysqld]
port=3306
datadir=/database/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server_id=2
binlog_ignore_db=mysql
binlog_cache_size=1M
binlog_format=mixed
重启Mysql服务,设置自启
systemctl enable mysqld.service && systemctl restart mysqld.service && systemctl status mysqld.service
数据库初始化
mysql_install_db --user=mysql --datadir=/database/mysql
mysql_secure_installation
创建数据同步账号
#1、登录mysql
mysql -uroot -p
授权给192.168.0.所有网段的主机以test用户名密码test访问数据库
mysql>grant all on *.* to 'test'@'192.168.0.*% ' identified by 'test'; [授权管理所用库]
mysql>FLUSH PRIVILEGES; [刷新权限,写入磁盘]
2、在主库上执行
show master status ;记录bin-log位置
#3、全备数据库
mysqldump -uroot -p -A -B -R --master-data=2 --events --single-transaction >/root/all.sql
主从数据库同步
#1、在slave上执行
mysql –uroot –p </root/all.sql 把主库数据导入备库
#2、登陆从库的数据库执行同步信息的账号位置点的录入
根据第2步查询主库的信息执行如下命令
change master to master_host='192.168.136.137',master_port=3306,master_user='text',master_password='test',MASTER_LOG_FILE='mysql_bin.000002',MASTER_LOG_POS=154;
3、开始同步
mysql>start slave;
** Slave_IO_Running和Slave_SQL_Running必须为“yes”,中继日志Relay_Master_Log_File与主同步,至此mysql主从数据搭建完毕**
Mysql常用指令示例教程
操作mysql数据库
数据备份
mysqldump一次性备份所有数据库数据
mysqldump -u用户名 -p密码 --all-databases > /保存路径/文件名.sql
mysqldump 一次性备份指定的多个数据库数据
mysqldump -u用户名 -p密码 --databases 数据库1 数据库2… > 保存路径/文件名.sql
例如:备份如下备份mysql和sys数据库
数据恢复
使用source 命令恢复数据库
使用source命令恢复数据库,需要先登录数据后再执行如下命令。
source /root/all01.sql;
创建数据库
create database 库名直接创建基本数据库
**create database 库名 character set=utf8;**创建字符串为utf8的数据库
查询数据库
**show databases;**查询数据库
**show databases like ''wujian%;**查询以wujian为开头的数据库
**show variables like ‘%char%’;**查询mysql数据库编码
**set character_set_database =utf8;**设置为utf8编码
#通常新建库或表的时候没有设置为utf为utf8时,后续insert into往表中插入数据带有中文会报错
选择数据库
**use 数据库名;**使用数据库
修改数据库
alter databasen 库名 default character set gbk default collate gbk_chinese_cli;修改数据库字符集为GBK,修改字符集的校对规则为简体中文
删除数据库
drop database 库名
操作数据表
查询数据库的表
show tables;
创建数据库的表
create table 数据库表名 (列名1 属性,列名2 属性…);
use wujian;
create table mima
(
id int auto_increment primary key,
user varchar(30) not null,
password varchar(30) not null,
createtime datetime
); 在数据wujain中创建mima表
查询数据库表结构
desc 表名;
修改数据库表
修改数据库表结构
ALTER TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型; (在旧字段基础上修改为新字段)
修改数据库表字段
**alter table 表名 add email varchar(50) not null;**添加新字段级修改字段定义,添加一个新的字段email,类型为varchar(50),not null,将字段user的类型由varchar(30)改为varchar(40)。
修改数据表中数据
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;修改表的列名(字段名)
update student set name=‘吴健’,age=30,tel=1234 where id=1;
删除数据库表字段
alter table 表名 drop 字段名;
alter table mima drop email;
修改数据库表名
alter table <旧表名> rename [TO] <新表名>; 修改表名 或
alter table <新表名> rename as <旧表名>; 修改表名
重命名数据库表名
rename table 数据库表名 1 to 数据库表名 2 ; 该语句可以同时对多个数据表进行重命名,多个表之间以","分隔。
rename table mima to password;
复制数据库表
create table 新表名 like 被复制表名;(此种方法只复制表结构不会复制表中数据)
create table 新表名 as select * from 被复制表名; (即可复制表结构也会复制表中数据)
create table passwordNew like password;
往数据表中新增数据
insert into student (id,name,sex,age,tel) values (‘4’,‘王刚’,‘男’,‘23’,‘123’);往表student中加入列id,name,sex,age.tel数据
删除数据库表
drop table 数据库表名;(整个表)
删除数据库表中数据
delete from 表名 where id=id名称; 删除表student中部分数据/删除整个表数据为delete from 表名;
如:delete from student where id=1;
表数据的增、删、改、查
往表中插入数据
**insert into tables_name ()values()**插入多条数据
insert into studentNew (id,name,sex,age,tel) values (6,‘老朱’,‘男’,32,123),(3,‘徐斌’,‘男’,33,123456),(4,‘冯泽威’,‘男’,34,12345);
**insert into table_name ()values ()**插入完整数据
insert into studentNew (name,sex,age,tel) values (‘冯侃’,‘男’,33,123);
**insert into set **语句插入数据
插入查询结果
insert into student (name,sex,age,tel) select name,sex,age,tel from studentNew ;
从studentNew表中查询出name,sex,age插入到student表中
修改数据
update 数据表名 set 字段1=值1,字段2=值2,… [where 表达式 order by … limit 行数](可选)
where子句:可选项,用于限定表中要修改的行,如果不指定该子句,update语句会更新表中的所有行。
order by子句:可选项,用于限定表中要修改的行被修改的次序。
limit子句:可选项,用于限定被修改的行数
update studentNew set sex=‘女’,age=30 where id=6;
删除数据
delect语句删除说明见上面的 删除数据库表中数据章节
truncate table 数据表名(也可以用数据库名.数据表名格式来判断删除数据隶属于那个数据库) 删除数据库数据,改语句为清空数据库表所有数据,慎用。
基本查询语句
select 字段1,字段2 from 数据表名 查询表中一列或多列
select name,sex from student;
select 数据库01名.数据表01名,数据库02名.数据表02名… from 数据库01,数据库02,…; 从一个或多个表中获取数据
select studentNew.id,studentNew.name,studentNew.sex,student.id,student.name,student.tel from studentNew,student;
单表查询
select * from 数据表名; 查询所有字段
查询所有字段是指查询表中所有字段的数据,通常可以使用“*”代表所有的列,使用最频繁不再赘述。
select 字段名 from 表名; 查询指定字段
select name,sex from student;
查询指定数据
如果要从很多记录中查询出指定的记录,那么就需要一个查询的条件。设定查询条件应用的是where子句。通过它可以实习很多复杂的条件查询。在使用where子句时,需要使用一些比较运算符来确定查询的条件。其常用的比较运算符如下:
select * from 表名 where 表达式;根据where子句查询
带关键字IN的查询
select * from 表名 where 表达式 in (元素1,元素2,元素3…);
关键字in可以判断某个字段的值是否在指定的集合中。如果字段的值字在集合中,则满足查询条件,该记录将被查询出来;如果不在集合中,则不满足查询条件。
select * from student where name in(‘吴健’); in:查询表里字段是带有元素的数据
select * from student where name not in(‘吴健’); not in :查询表里字段不为元素的数据
带关键字between and的范围查询
关键字between and 可以判断某个字段的值是否在指定的范围内。如果字段的值在指定范围内,则满足查询条件,该记录将被查询出来。如果不在指定范围内,则不满足查询条件。
select * from 表名 where 条件 [not] between 取值1 and 取值2;
(1)[NOT]:可选项,表示不在指定范围内满足条件
(2)取值1:表示范围的起始值。
(3)取值2:表示范围的终止值。
select * from student where id between 3 and 4 ;
select * from student where id not between 3 and 4 ;
带like的字符匹配查询
like常用的模糊查询,它又两种通配符:‘’%‘‘和下划线’’‘’。
(1)“%”可以匹配一个或多个字符,可以代表任意长度的字符串。例如,“冯%狗”,以“冯”结尾,以“狗”开头的任意长度的字符串。
(2)“”只匹配一个字符。例如,1_3表示以1开头,以3结尾的3个字符。中间“_”可以代表任意一个字符。
select * from 表名 where 表达式 like ;
select * from student where tel like (‘1234%’) ;
select * from student where tel like (‘1_3’) ;