数据库

发表于 | 分类于

Mysql

主键、超键、候选键、外键

数据库事务的四个特性

原子性、一致性、隔离性、持久性

MySQL中，事务是在引擎层实现的，MyISAM不支持事务，InnoDB支持

隔离级别

隔离级别不同，可能会出现脏读、幻度和不可重复读，标准的隔离级别包括读未提交、读提交、可重复读和串行化

读未提交

事务执行的时候，会读到其他未提交的事务的数据，称之为脏读

读提交

事务执行的时候，会读到其他已提交的事务的数据，称之为不可重复读

可重复读

同一个事务里,select语句的结果是基于事务开始的时间点的状态，但是其他操作可能会出现和select结果不一致的情况，称之为幻读。

mysql默认的事务隔离级别就是这个，原因是5.0以前的mysql在主从复制的时候，如果使用读提交的隔离级别，会导致在从数据库使用binlog同步数据的时候不一致的问题

串行化

事务全部串行化，会阻塞，但是避免了以上的问题

设置隔离级别

在配置文件中设置

1
2
[mysqld]
transaction-isolation = READ-COMMITTED #可选值是READ-UNCOMMITTED，READ-COMMITTED，REPEATABLE-READ，SERIALIZABLE

或者通过命令设置

1
2
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
# isolation-level的可选值是READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ，SERIALIZABLE

查询隔离级别

1
2
select @@tx_isolation;
select @@global.tx_isolation;

开启事务

1
2
set autocommit=off
start transaction

视图

视图只包含使用时动态检索数据的查询

drop、delete、truncate

1
2
3
4
5
drop table table_name; #直接删除表，不可恢复

delete from table_name (where);#删除表中的内容，可以恢复rollback，不会删除索引，返回删除的记录数

truncate table table_name; #删除表格中的所有数据，不可恢复，删除后悔重建索引，返回0/-1

在删除存在主外键约束的表格的内容的时候，可以先取消主外键约束，删除之后就可以恢复主外键约束

1
2
3
SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=1;

给表格添加字段

1
alter table table_name add column debug_pack_longest_cost varchar(255) after Android

外键约束

查看外键约束关系

1
2
#列出表test_a所有的外键关系，即其说明他表的那些字段是以test_a的字段为外键
 select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  where REFERENCED_TABLE_NAME='test_a';

删除外键约束

1
2
# cons是外键约束名称
alter table test_a drop foriegn key cons;

添加外键约束

1
2
#cons是外键约束名称，fields1是test_a的字段，fields2是test_b的字段
alter table test_a add constraint cons foreign key(fields1) references test_b(fields2);

索引

创建索引

1
2
3
4
5
6
7
8
9
create index indexName on mytable(username(length)); #如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length
alter table mytable add index indexName on username(length);
drop index indexName on mytable;

#唯一索引
create unique index indexName on mytable(username(length));

#组合索引
alter table mytable add index name_city_age(name(10),city,age);

mysql主从

优点：1.热备份 2，读写分离

原理

  1. 将master改变记录到二进制日志
  2. slave将master的二进制日志拷贝到中继日志(relay log)
  3. slave重做中继日志的事件，改变自己的数据

配置

  1. 打开binary log并设置id

修改配置文件,并重启服务

1
2
3
[mysqld]
log-bin=mysql-bin
server-id=1
  1. 创建一个同步用户
create user 'rep1'@'slave address' indetified by 'password';
grant replication slave on *.* to 'rep1'@'slave address'; #slave可以复制
  1. 获取master的二进制日志和数据库备份

将数据库全局读锁定

flush tables with read lock;

退出当前连接会自动解锁

查看二进制日志和当前坐标

show master status;

记住File和Position

备份数据库

在read lock的状态下，使用mysqldump将主数据库中的数据备份

1
mysqldump --all-databases --master-data > dbdump.db

释放read lock

unlock tables;

slave节点的配置

修改配置文件，增加server-id，不能和master的一样

1
2
[mysqld]
server-id=2

将master上的备份的数据恢复到slave节点

1
mysql -uroot -p < dbdump.sql

设置slave和master的通信

change master to
MASTER_HOST='master_host_ip',
MASTER_PORT='master_port',
MASTER_USER='replication_user_name',
MASTER_PASSWORD='replocation_password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS='recorded_log_position';

启动slave

START SLAVE;

查看同步状态,确认Slave_IO_Running和Slave_SQL_Running都是Yes即可

show slave status;

读写分离

写位于master，读位于slave，用一个代理去判断是读还是写

  1. MySQL_Proxy
  2. MaxScale 推荐
  3. Atlas
  4. MyCat

安装好MaxScale之后，在master数据库中进行如下配置

1
2
3
4
5
6
7
8
create user 'username'@'maxscalehost' indentified by 'password';
grant select on mysql.user to 'username'@'maxscalehost';
grant select on mysql.db to 'username'@'maxscalehost';
grant select on mysql.tables_priv to 'username'@'maxscalehost';
grant select on mysql.colums_priv to  'username'@'maxscalehost';
grant select om mysql.proxies_priv to  'username'@'maxscalehost';
grant show databases on *.* to 'username'@'maxscalehost';
grant replication client on *.* to 'username'@'maxscalehost'; #复制用户可以使用 SHOW MASTER STATUS, SHOW SLAVE STATUS和 SHOW BINARY LOGS来确定复制状态

修改MaxScale中的配置文件

/etc/maxscale.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
[maxscale]
threads=auto
log_info=1

[master]
type=server
address=192.168.10.2
port=3306
protocol=MySQLBackend

[slave1]
type=server
address=192.168.10.3
port=3306
protocol=MySQLBackend

[MySQL-Monitor]
type=monitor
module=mysqlmon
servers=master,slave1
user=maxscale
password=password
monitor_interval=2000

[Read-Write-service]
type=service
router=readwritesplit
servers=master,slave1
user=maxscale
password=password
max_slave_connections=100%
max_slave_replication_lag=30

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MySQLClient
port=4006

[CLI]
type=service
router=cli

[CLI-Listener]
type=listener
service=CLI
protocal=maxscaled
socket=default

启动

1
2
sudo systemctl start maxscale
sudo systemctl enable maxscale