MySQL备份指令 1、mysql备份单个数据库命令mysqldump --column-statistics=0 -h主机ip -P端口号 -u数据库用户名 -p数据库密码 数据库名称 > 想要备份的文件名称.sql2、直接将MySQL数据库压缩备份mysqldump -h主机ip -u数据库用户名 -p数据库密码 数据库名称| gzip > 想要备份的文件名称.sql.gz3、备份MySQL数据库某个(些)表mysqldump -h主机ip -u数据库用户名 -p数据库密码 数据库名称 表1 表2 > 想要备份的文件名称.sql4、同时备份多个MySQL数据库mysqldump -h主机ip -u数据库用户名 -p数据库密码 数据库1 数据库2 数据库3 > 想要备份的文件名称.sql5、仅备份数据库结构mysqldump –no-data 数据库1 数据库2 数据库3 > 想要备份的文件名称.sql6、备份服务器上所有数据库mysqldump –all-databases > 想要备份的文件名称.sql7、还原MySQL数据库的命令mysql -h主机ip -u数据库用户名 -p数据库密码 数据库名称 < 备份文件.sql8、还原压缩的MySQL数据库gunzip < 备份文件.sql.gz | mysql -u用户 -p密码 数据库名称9、将数据库转移到新服务器mysqldump -u用户 -p密码 数据库名称 | mysql –host=127.0.0.1 -C 数据库名称其他一些报错【unknown option ‘-b’ 错误】解决方式: 将 -database 改为 --database【Unknown table ‘column_statistics’ in information_schema 错误】错误原因: 因为新版的mysqldump默认启用了一个新标志, 通过 --column-statistics=0 来关闭它.#解决方案: mysqldump --column-statistics=0 -h主机ip -P端口号 -u数据库用户名 -p数据库密码 数据库名称 > 想要备份的文件名称.sql原文链接：https://blog.csdn.net/ZRHZRHH/article/details/110134222

MySQL 主从同步（实操） 介绍本文章实操基于 从机读取主机binlog二进制文件，执行相同操作实现数据一致原理(关于从机slave数据与主机master数据不一致不同步等故障不在本文介绍中)关于主从服务器的环境不限要求 Centos或者Windows server均可 主服务器配置1.修改my.cnf配置文件[mysqld] # 主数据库端ID号 server-id = 1 # 开启二进制日志 log-bin = mysql-bin # 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可 binlog-do-db = test_db # 以下配置按需添加 #将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中 log-slave-updates #控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) sync_binlog = 1 #这个参数一般用在主同步中，用来错开自增值, 防止键值冲突 auto_increment_offset = 1 #这个参数一般用在主同步中，用来错开自增值, 防止键值冲突 auto_increment_increment = 1 #二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除 expire_logs_days = 10 #将函数复制到slave log_bin_trust_function_creators = 1log-slave-updates该参数我的理解是告诉slave是否将master中binlog日志中的操作同步到本身binlog日志中。该参数不写 默认是FALSE Slave读取到master中binlog操作不记录本身binlog。所以说主从同步设置成 A>B>C三层级形式。 A是master ， B是slave 读取A的数据同步，C是slave 读取B的数据同步。此时就需要该参数。binlog-do-db 二进制日志记录的数据库（多库逗号分割）binlog-ignore-db 二进制日志中忽略数据库（多库逗号分割）2.重启mysql服务#手动重启 #指令重启(系统环境配置过mysql才适用) systemctl restart mysqld3.创建允许从服务器同步数据的账号#进入mysql mysql -uroot -p123456 grant replication slave on *.* to 'slave'@'%' identified by '123456'; # 刷新权限 flush privileges; 4.查看主服务器数据库状态show master status\G;需要记录下 File 文件（binlog日志记录所在券）和 Position 逻辑位置（file是一个二进制文件该参数记录当前数据插入位置）这两个参数在从机设置中需要告知从服务器配置1.编辑数据库配置文件vim /etc/my.cnf # 在[mysqld]后添加以下配置 # 主数据库端ID号 server-id = 2 # 开启二进制日志 log-bin = mysql-bin # [可选]如果从数据库名称要和主数据库名称不一样的话一定要加上这一条配置 replicate-rewrite-db = test1 -> test2 # [可选]同步指定数据库 replicate-do-db = test1 # [可选]排除指定数据库 replicate-ignore-db = test22.重启从数据库3.进入数据库执行同步命令/** 配置同步服务器 master_host 主服务器ip master_user 主服务器同步账户（在主服务器配置时创建的） master_log_file 同步文件位置 */ change master to master_host='xx.xx.xx.xx',master_user='slave',master_password='123456',master_log_file='mysql-bin.000011',master_log_pos=7418; # 启动同步功能 start slave; # 查看同步状态，Slave_IO_Running及Slave_SQL_Running的值都是YES代表配置成功 show slave status\G;在此处master\_log\_file参数填写主库同步状态的 File参数，master\_pos\_pos参数填写主库同步状态的 Position参数或者0（自动获取）。查看从库同步状态只需要关注 Slave\_IO\_Running参数 和 Slave\_SQL\_Running参数 均是 Yes即可 表示主从配置成功

MySQL统计数据库使用容量 直接上代码select table_schema as '表名字', concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as '数据使用/MB', concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as '索引使用/MB', concat(round((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024,2),'MB') as '总数据容量/MB' from information_schema.TABLES group by table_schema; MySQL自带访问数据库元数据的方式：information_schema.tables 获取所有数据库#查看Mysql所有数据库中数据表的名称、数据库注释、创建时间（mysql自带的和自定义的都会查询出来） select table_name, table_comment, create_time, update_time from information_schema.tablestable_schema 是数据库的名称table_name 是具体的表名。table_type 表的类型。#获取某个数据库中的所有表的表名、表类型、引擎等等 select table_name, table_type, engine from information_schema.tables where table_schema = '{数据库名}' order by table_name desc;select database() 获取当前数据库下的所有表#获取 当前数据库下的所有表名、表注释、创建及更新时间。 select table_name, table_comment, create_time, update_time from information schema.tables where table_schema (select database());concat(str1, str2, str3)将多个字符串连接成一个字符串注意其中有一个null,返回值也是null。data_length : 存储数据大小index_length : 索引数据大小

MySQL索引系列问题 mysql高性能索引问题真题：简单描述mysql中，索引，主键，唯一索引，联合索引的区别对数据库的性能有什么影响？mysql索引的基础和类型什么是索引：在索引中找到对应的值，然后依据匹配的索引找到对应的数据行常用的数据引锁：主键索引、唯一索引、普通索引、全文索引、组合索引索引对性能的影响大大减少服务器需要扫描的数据量帮助服务器避免排序和临时表将随机I/O变顺序I/O大大提高查询速度，降低写的速度、占用磁盘索引的使用场景对非常小的表，大部分情况下全表扫描效率更高中到大型表，索引非常有效特大型的表，建立和使用索引代价将随之增长，可以使用分区技术来解决索引类型普通索引：最基本的索引，没有任何约束限制；唯一索引：与普通索引类似，但是具有唯一性约束；主键索引：特殊的唯一索引，不允许为空值；唯一索引和主键索引的区别：一个表只能有一个主键索引，可以有多个唯一索引。主键索引一定是唯一索引，唯一索引不是主键索引。主键可以与外键构成参照完整性约束，防止数据不一致。组合索引：将多个列组合在一起创建索引，可以覆盖多个列；外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性，完整性和实现级联操作；全文索引：MySQL自带的全文索引只能用于MyISAM,并且只能对英文进行全文检索mysql索引的创建原则最适合索引的列是出现在where子句中的列，或连接子句中的列而不是出现在select关键字后的列；索引列的基数越大，索引的效果越好；对字符串进行索引，因该指定一个前缀长度，可以节省大量的索引空间；根据情况创建复合索引，复合索引可以提高查询效率；避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率；主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率；mysql索引的注意事项复合索引遵循前缀原则;like查询，%不能在前，可以使用全文索引；https://www.bilibili.com/video/BV1hE411Y7sB?p=6https://blog.csdn.net/b_x_p/article/details/86434387待补充...

MySQL系列基础知识 mysql基础知识整型类型整数字段有tinyint,smallint,mediumint,int,bigint属性有:unsigned(不能为负)INT（11）长度是11位，INT（3）中如果指定zerofill（0填充）输入 1234也是可以存进数据库中的，输入12存入数据库时数据个数不足用0填充,即012实数类型常见实数有float,double,decimalfloat和double小数点之后是有范围的，而decimal无论小数点后面有多少位都可以保存下来。字符串类型常见字符串类型有varchar,char,text,blobvarchar存储可变长度字符串（优点节省空间），使用1/2个额外字节记录字符长度，小于255字节使用1个字节。超出指定长度会截取舍去（有的版本会报错）；char存固定长度，使用空格进行填充。超出指定长度也会被截断。（优点：1对于经常变更的数据比varchar不容易产生碎片。2对非常短的列比varchar储存空间更有效率）；避免使用text,blob，因为查询使用临时表导致性能开销；时间和日期类型尽量使用timestamp,比datetime空间效率高用整数保存时间戳格式通常不方便处理。储存微秒可以使用bigint（整形）存储mysql基础操作连接和关闭：mysql -u(指定用户名) -p（密码） -h（主机） -P（端口）\G(打印结果垂直显示)，\c(取消当前操作)，\q（退出mysql），\s(显示mysql状态)，\h（帮助信息），\d（特殊符号转换符）mysql数据表引擎InnoDB特点：事务性引擎，最重要最广泛的存储引擎，性能非常优秀。数据储存在共享表空间中（默认叫ibdata1），可以通过配置分开对主键查询的性能高于其他类型的储存引擎内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引，插入数据时自动构建插入缓冲区支持：通过一些机制和工具支持热备份支持奔溃后的安全恢复支持行级锁支持外键MyISAM在5.1版本前，是默认存储引擎拥有全文索引，压缩，空间函数不支持事务和行级锁（支持表锁），不支持崩溃后的安全恢复表存储在两个文件，MYD和MYI设计简单，某些场景下性能很好（比如查询）其他表引擎Archive,Blackhole,CSV,Memorymysql锁机制表锁是日常开发当中常见的问题，当多个查询同一时刻进行数据修改时，就会产生并发控制的问题。分为共享锁（读锁）和排他锁（写锁）读锁共享的，不堵塞，多个用户可以同时读一个资源，互不干扰写锁排他的，一个写锁会堵塞其他的写锁和读锁，这样可以只允许一个人进行写入，防止其他用户读取正在写入的资源。锁粒度表锁，系统性能开销最小，会锁定整张表，MyISAM使用表锁行锁，最大程度地支持并发处理，但是也来了最大的锁开销，InnoDB实现行级锁mysql事务处理mysql提供事务处理的表引擎，InnoDB服务层不管理事务，由下层的引擎实现，所以在同一个事务中使用多种存储引擎不靠谱在非事务的表上执行事务操作不会发出提醒不会报错。mysql存储过程为以后的使用而保存的一条或多条mysql语句的集合存储过程就是有业务逻辑和流程的集合可以在储存过程中创建表，更新数据，删除等等使用场景：通过把处理封装在容易使用的单元中，简化复杂的操作。保证数据的一致性简化对变动的管理mysql触发器触发器提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊存储过程。使用场景：可通过数据库中的相关表实现级联更改（比如：修改一个表触发另一个表同样修改）实时监控某张表中的某个字段的更改而需要做出相应的处理某些业务编号的生成。滥用会造成数据库及应用程序的维护困难

MySQL储存过程 存储过程：指定特定功能的一条/多条sql语句/批处理语句。函数：执行特定功能的一段代码进行封装。储存过程 PHP里面的函数 方法 类似局部变量：declare 变量名 数据类型 default 默认值；变量的作用范围：begin end;之间设置变量：set 变量名=变量值传参方式：in 传入参数out 传出参数select ：相当于echo 输出 打印out: 返回调用结果 相当于returninout 传入传出参数条件判断：if caseif 判断条件 then //执行sql elseif 判断条件 then //执行sql else //执行sql end if;case 变量 when 比较的值 then //执行sql else //执行sql end case;常见的3中循环while循环栗子#在新版本navicat要写第一行语句，老版则省略不写 create definer='root'@'localhost' procedure sp_24_while() #开始创建过程 begin #定义一个变量 declare i int default 0; #条件为真 则执行代码 while i<10 do insert into user(age) values(i); set i=i+1; end while; end执行新建的过程 call sp_24_while();repeat循环栗子repeat #循环语句 until i=10 end repeat; #判断条件为真实停止循环loop无限循环loop #循环语句 #要使用leave或iterate跳出 end loop 游标游标相似于数组1.创建游标 declare 游标名称 cursor for sql语句 2.打开游标 open 游标名称 3.取出游标值 fetch 游标名称 into 变量名//取出单个游标值 //结果集字段的顺序 fetch 游标名称 into 变量名1,变量名2,变量名3//取多个值 4.循环 停止条件 5.关闭游标 close 游标名称栗子BEGIN DECLARE uman VARCHAR(20); DECLARE i INT DEFAULT 0; DECLARE sale_man_cur CURSOR FOR SELECT DISTINCT sale_man FROM xk_order WHERE sale_man <> ''; #检测异常 当游标中数据都取出来时i=1 DECLARE CONTINUE HANDLER FOR NOT found SET i=1; #开启游标 OPEN sale_man_cur; #取出游标 FETCH sale_man_cur into uman; #循环 WHILE i = 0 DO INSERT INTO user(name) VALUES(uman); FETCH sale_man_cur INTO uman; END WHILE; #关闭游标 CLOSE sale_man_cur; END函数create definer=current_user funcion 'func_25_getName'() returns integer begin #定义变量 return 0; end高级特性：1.contains sql 表明子程序包含sql语句（默认）2.no sql 表明子程序不包含sql语句3.reads sql data:说明子程序包含读数据的语句4.modiffes sql data:表明子程序包含写数据的语句执行命令保存在binlog中（只要用于复制和回复）【栗子】获取字符串中随机字符查询：SELECT fun_18_string255(展示随机字符串的长度);事件查看当前事件是否开启查询：show variables like '%event_scheduler%';开启：set GLOBAL event_scheduler = ON;事件的创建简单方法：事件的创建语句create event 事件名称 on schedule at(单次)/every(多次) at 单次执行事件 every 多次执行事件 on completion preserve/not preserve（-选填） preserve 事件到期后保留事件 not preserve 事件到期后删除事件 disable/enable 关闭/开启（-选填） commit '注释' do 执行内容视图视图是一个虚拟存在的逻辑表视图的优点：简单，安全，数据独立事务事务的特性：acid原子性，持久性，隔离性，一致性事务的隔离级别read uncommitted(读未提交RU)会产生脏读现象read committed(数据库的读以提交RC)产生不可重复读，幻读现象repeatable read(可重复读RR-默认级别)避免脏读，不可重复读和幻读现象serializable(串行化)类似队列一个一个执行，效率低最安全#查看事务隔离级别 select @@session.tx_isolation; #设置当前mysql隔离级别 set session transaction isolation level read uncommitted; #设置数据库系统的全局隔离级别 set global transaction isolation level read committed;显式1.开启事务start transaction;2.提交事务commit;3.回滚事务rollback;隐式事务自动提交 show variables like 'autocommit';事务自动提交关闭set autocommit=0;mysql语句mysql -u root -p #输入密码 --使用数据库 use 库名 --展示所有表 show tables; --设置编码 set names gbk; --查看表数据 select * from 表名;ps:什么时候使用索引1.百万级数据2.经常查询的字段6个索引，普通索引唯一索引全文索引单列/多列索引组合索引注意索引失效，失效的情况补充：使用过程来添加测试数据begin #定义一个变量 declare i int default 0; #条件为真 则执行代码 while i<500 do insert into test(uname,age,r_number) values(md5(rand()*900),floor(rand()*90+10),floor(rand()*90000+10000)); set i=i+1; end while; end

InnoDB与MyISAM的区别 InnoDB与MyISAM的区别存储引擎是数据库的核心，对mysql来说存储引擎是以插件形式运行的。【不同的功能】M最大可以存256TB的数据，I最大可以存64TBM不支持事务，I支持M支持全文索引，I不支持M不支持数据索引，I支持M支持数据压缩，I不支持M空间使用率低，I使用率高M不支持外键，I支持全文索引 以关键字去找行（数据）数据索引 Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引【存储引擎】myisam在磁盘上存储成三个文件分别是：1.frm表的定义数据。2.MYD表记录数据。3.MYI存储索引。缺点不支持事务，优点存储速度快。innodb的特点：支持事务（默认隔离级别为可重复度）。使用锁为行级锁，可以支持并发。支持外键约束，缺点是降低了表的查询速度，优点增加表之间的耦合度来自牛客网友的精彩回复链接：https://www.nowcoder.com/questionTerminal/d5739672e78c498885ba2a540ab53500?source=relative 来源：牛客网 问题：请介绍一下，数据库索引，以及，什么时候用Innodb什么时候用MyISAM。 用户提问 数据库 何时使用MyISAM和InnoDB？ MyISAM的设计理念是： 你的数据库被查询得远远超过其更新，因此它执行非常快速的读取操作。 如果你的读写比例（插入|更新）小于15％，则最好使用MyISAM。 InnoDB使用行级锁定，提交，回滚和崩溃恢复功能来保护用户数据。 它支持事务和容错 MyISAM和InnobDB之间的上述差异是否正确？ 如果MYISAM和InnobDB有任何其他限制，请指导。我应该何时使用MyiSAM或Innodb？谢谢！ 关注问题 写回答 2 个回答 热门排序 用户回答 MyISAM数据： MySQL中的MyISAM存储引擎。 设计和创建更简单，因此对初学者更好。不用担心表格之间的外部关系。 整体而言，由于结构更简单，服务器资源成本更低，因此总体上比InnoDB更快。 大部分不再是真的。 全文索引。- InnoDB现在拥有它 特别适合读密集（选择）表格。 - 大部分不再是真的。 磁盘占用空间比InnoDB少2至3倍。 - 从5.7版本开始，这可能是MyISAM唯一的真正优势。 InnoDB的： MySQL中的InnoDB存储引擎。 支持交易（为您提供对ACID资产的支持）。 行级锁定。与例如MyISAM相比，拥有更细粒度的锁定机制可为您提供更高的并发性。 外键约束。允许您让数据库确保数据库状态的完整性以及表之间的关系。 InnoDB比MyISAM更能抵抗表损坏。 支持数据和索引的大型缓冲池。 MyISAM密钥缓冲区仅用于索引。 MyISAM停滞不前; 所有未来的增强将在InnoDB中进行。随着8.0版的推出，这一点非常明确。 MyISAM限制： 没有外键和级联删除/更新 没有交易完整性（符合ACID） 没有回滚能力 4,284,867,296行限制（2 ^ 32） - 这是旧的默认值。可配置的限制（对于许多版本）是2 ** 56字节。 每个表最多有64个索引 nnoDB限制： 没有全文索引（低于5.6的mysql版本） 无法压缩为快速，只读（5.5.14介绍ROW_FORMAT=COMPRESSED） 您无法修复InnoDB表MyISAM和InnoDB在索引上的差别及其它区别　　

MySQL主从复制 【介绍】需求：A库（MyISAM）读操作，B库(InnDB)写操作读写分离，两个数据一致。主从复制：两个数据库，一个主服务，一个从服务​ 主服务 用来执行写操作，从服务用来执行写【第一步】配置以下配置 需要放置在mysqld服务ID 主从服务ID 需要不同server-id = 1 #启动二进制日志 log-bin=mysql-bin 时则不需要修改log-bin= 当前文件所在目录/data/mysqlbinlog-err= 当前文件所在目录/data/mysqlerr 例如log-bin=D:/phpstudy/PHPTutorial/MySQL/data/mysqlbinlog-err= D:/phpstudy/PHPTutorial/MySQL/data/mysqlerr若两个库的名字不一致replicate-rewrite-db=zxkang_sync->test【第二步】创建主从账号1.创建用于主从复制的连接账号GRANT REPLICATION SLAVE ON . TO 'slave'@'%' IDENTIFIED BY '123456';2.主库运行SQL语句 5.6版本set global binlog_checksum=NONE;3.刷新缓存flush privileges;3.查看日志文件和管道号show master status【第三步】连接副从账号1.建立主从复制连接CHANGE MASTER TO master_host = '39.96.86.74',master_port = 3306,master_user = 'slave',master_password = '123456',master_log_file = 'mysql-bin.000006',//第二步得到的管道号master_log_pos = 410;//第二步得到的端口号2.开始主从复制服务START SLAVE;3.查看主从复制服务是否已经连接show slave statusSlave_IO_Running Yes Slave_SQL_Running Yes关闭主从复制服务stop SLAVE【第四步】测试在主数据库中新建一个表并保存，去副数据看是否存在。