Mysql数据库主从复制、读写分离、增删改查、正则表达

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’) ;

使用正则表达式查询