dwt's life - mysql

【转载】MySql 外键约束之CASCADE、SET NULL、RESTRICT、NO ACTION分析和作用

2023-06-20T19:30:49+08:00

https://www.cnblogs.com/yzuzhang/p/5174720.htmlMySQL有两种常用的引擎类型：MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。InnoDB中外键约束定义的语法如下：ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]例如：ALTER TABLE `user_resource` CONSTRAINT `FKEEAF1E02D82D57F9` FOREIGN KEY (`user_Id`) REFERENCES `sys_user` (`Id`)InnoDB也支持使用ALTER TABLE来删除外键：ALTER TABLE user_resource DROP FOREIGN KEY FKEEAF1E02D82D57F9;CASCADE在父表上update/delete记录时，同步update/delete掉子表的匹配记录 SET NULL在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null) NO ACTION如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 RESTRICT同no action, 都是立即检查外键约束SET NULL父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别NULL、RESTRICT、NO ACTION删除：从表记录不存在时，主表才可以删除。删除从表，主表不变更新：从表记录不存在时，主表才可以更新。更新从表，主表不变CASCADE删除：删除主表时自动删除从表。删除从表，主表不变更新：更新主表时自动更新从表。更新从表，主表不变SET NULL删除：删除主表时自动更新从表值为NULL。删除从表，主表不变更新：更新主表时自动更新从表值为NULL。更新从表，主表不变外键约束属性： RESTRICT | CASCADE | SET NULL | NO ACTION 外键的使用需要满足下列的条件：两张表必须都是InnoDB表，并且它们没有临时表。建立外键关系的对应列必须具有相似的InnoDB内部数据类型。建立外键关系的对应列必须建立了索引。假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。NO ACTION: InnoDB拒绝删除或者更新父表。RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。SET DEFAULT: InnoDB目前不支持。外键约束使用最多的两种情况无外乎：1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；2）父表更新时子表也更新，父表删除时子表匹配的项也删除。前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。当执行外键检查之时，InnoDB对它照看着的子或父记录设置共享的行级锁。InnoDB立即检查外键约束，检查不对事务提交延迟。要使得对有外键关系的表重新载入转储文件变得更容易，mysqldump自动在转储输出中包括一个语句设置FOREIGN_KEY_CHECKS为0。这避免在转储被重新装载之时，与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量：mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> SOURCE dump_file_name; mysql> SET FOREIGN_KEY_CHECKS = 1;　　如果转储文件包含对外键是不正确顺序的表，这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN_KEY_CHECKS为0，对于在LOAD DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。InnoDB不允许你删除一个被FOREIGN KEY表约束引用的表，除非你做设置SET FOREIGN_KEY_CHECKS=0。当你移除一个表的时候，在它的创建语句里定义的约束也被移除。　　如果你重新创建一个被移除的表，它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型，并且如前所述，它必须对被引用的键有索引。如果这些不被满足，MySQL返回错误号1005 并在错误信息字符串中指向errno 150。

MySQL 主从恢复记录

2022-11-15T03:21:01+08:00

流程过程大致如下：从主库找到备份文件，放到从库服务器。恢复数据到从库设置MySQL还原点启动从库开始主从复制连接数据库先连接主库mysql -uroot -p切换数据库（或者不切换也行） use yourdatabase;停止主从复制stop slave; reset slave all;设置写入缓存大小。提高mysql导入速度。(和磁盘IO差不多就行)set global bulk_insert_buffer_size=128*1024*1024;恢复数据（根据自己的备份方式恢复）source /bakfile找到mysql binlog 备份点（在linux 下面执行，其他操作系统同理）less /bakfile 找到-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-190.000640', MASTER_LOG_POS=120; /*获取时间binlog文件名和pos*/注意，实际可能并不需要这样，直接在主库执行：show master status取得了bin-log和pos后直接修改从库，pos其实可以为0，会自动follow。修改从库同步位置CHANGE MASTER TO MASTER_HOST='10.251.192.18', MASTER_USER='sync', MASTER_PASSWORD='DBect98773!_sync', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin-190.000640', MASTER_LOG_POS=120;修改缓存。启动复制set global bulk_insert_buffer_size=8*1024*1024; start slave ;查看主从同步状态show slave status \G;如果有问题。查看mysql 错误日志。同步开始后可能会有少量的冲突出现，使用下面语句可以跳过一个事务并查看同步状态stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 ; start slave ; show slave status \G;

ActiveCMS数据库批量开启state和city

2022-08-24T21:52:27+08:00

UPDATE `states` SET `status`=1 where country_id = 13UPDATE `cities` as city join states state on city.state_id = state.id SET city.`status`=1 where state.country_id = 13

记录一次Mysql主从以及高可用操作【未完待续】

2022-03-09T17:26:00+08:00

之前托管的物理机组的Raid5出现了一块坏盘，介于最近的事情较多，暂时不打算对其做替换，不管硬盘是否可靠，数据备份也是必要的。之前本来是有做计划任务备份的，但是使用的宝塔面板并不支持排除某个数据库，只能选择所有，我有个裤子达到了60G，也懒得去修改宝塔自带的计划任务。因为出现事故第一时间是恢复业务，然而备份只能说是second choice，后续可能会对其进行修改来实现排除某些个数据库的功能。之前也做过主从，这次针对相关服务基于ipvs实现HA。拷贝数据用户添加grant replication slave on *.* to 'rep'@'81.69.%' identified by '123'; grant replication slave on *.* to 'rep'@'81.69.%' identified by '123';刷新并锁库flush tables with read lock; show master status;File的值是当前使用的二进制日志的文件名，Position是该日志里面的位置信息（不需要纠结这个究竟代表什么），记住这两个值，会在下面配置从服务器时用到。注意：如果之前的服务器并没有配置使用二进制日志，那么使用上面的sql语句会显示空，在锁表之后，再导出数据库里的数据（如果数据库里没有数据，可以忽略这一步）导出数据mysqldump -uroot -p'123456' -S /tmp/mysql.sock --all-databases > /www/server/backup/mysql_bak.$(date +%F).sql这里--all-databases会导出所有库，实际上我们并不希望如此，可使用xargs实现过滤。mysql -S /tmp/mysql.sock -uroot -p'123456' -e "show databases;" | grep -Ev "Database|information_schema|mysql|test" | xargs mysqldump -uroot -p'123456' --databases > /www/server/backup/mysql_bak.$(date +%F).sql如果数据量很大，可以在导出时就压缩为原来的大概三分之一mysql -S /tmp/mysql.sock -uroot -p'123456' -e "show databases;" -uroot -p| grep -Ev "Database|information_schema|mysql|test" | xargs mysqldump -uroot -p'123456' --databases | gzip > /www/server/backup/mysql_bak.$(date +%F).sql这时可以对数据库解锁，恢复对主数据库的操作unlock tables;配置主服务器[mysqld] log-bin=mysql-bin skip-slave-start server-id=1 binlog-ignore-db=test # 不记录某个库的binlog注意上面的log-bin和server-id的值都是可以改为其他值的，如果没有上面的配置，首先关闭mysql服务器，然后添加上去，接着重启服务器配置从服务器首先检查从服务器上的my.cnf文件中是否已经在[mysqld]模块下配置server-id[mysqld] server-id=2 replicate-ignore-db=test replicate-wild-do-table=test.% # 忽略某些库注意上面的server-id的值都是可以改为其他值的（建议更改为ip地址的最后一个字段），如果没有上面的配置，首先关闭mysql服务器，然后添加上去，接着重启服务器如果有多个从服务器上，那么每个服务器上配置的server-id都必须不一致。从服务器上没必要配置log-bin，当然也可以配置log-bin选项，因为可以在从服务器上进行数据备份和灾难恢复，或者某一天让这个从服务器变成一个主服务器如果主服务器导出了数据，下面就导入该文件，如果主服务器没有数据，就忽略这一步[root@localhost ~]# mysql -uroot -p'123456' -S /tmp/mysql.sock < /server/backup/mysql_bak.2022-03-09.sql如果从主服务器上拿过来的是压缩文件，就先解压再导入配置同步参数，登陆mysql，输入如下信息：mysql> CHANGE MASTER TO MASTER_HOST='ip地址', MASTER_USER='rep', MASTER_PASSWORD='rep密码', MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=120;启动主从同步进程start slave;检查状态show slave status \G上面的两个进程都显示YES则表示配置成功到此基本主从配置就完成了。需要注意的是，一旦主库发生重启、当机等情况，需要检测数据一致性后才能start slaveIPVS Keepalived配置关键服务部分服务是依赖其他服务进行改动的，所以当其他服务暂不可用的时候实际是不应影响这些服务的。所以需要组件一个小型的虚拟网络实现ipvs，或者是采用hosts的方式进行主机指定，但是并不适用于那些只允许ip的应用to be continue...参考MySQL主从复制mysqldump过滤数据库mysql主从配置忽略特定数据库mysql binlog 忽略和记录指定库

MySQL 用户权限管理

2021-12-02T05:43:00+08:00