mysql 外键 与slave_sql_running_mysql实战总结

应用层面的操作数据库主要需要了解的内容有:

安装启动和连接数据库，修改数据库的管理密码管理权限

数据库的备份与恢复，全量备份，增量备份，物理备份，逻辑备份

数据库的高可用部署：集群

数据库的优化：数据库操作系统硬件层的优化，数据库软件配置层面的优化，sql语句层面的优化，数据索引层面的优化。

-

安装数据库：

可以下载官网已经编译好的安装包，环境centos

yum 安装：mysql-community

先下载npm包

安装npm包： rpm -Uvh mysql57-community-release-el7-9.noarch.rpm

安装mysql：yum install mysql-community-server

启动mysql：systemctl start mysqld

初始安装后，修改root用户密码

启动服务后，root用户会创建一个临时密码存放在/var/log/mysqld.log里面，可以通过

grep 'temporary password' /var/log/mysqld.log 进行查看

然后使用 mysql -uroot -p进行登录

登录时如果临时密码有特殊符号的，可以用单引号括起来'myPassword'

然后

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

刷新权限：flush privileges;

-

授权某个用户访问数据库

grant all privileges on 数据库.表 to '用户名'@'ip' identified by '密码'

grant all privileges on . to 'root'@'%' identified by '密码'//%百分号表示任意地址

-

备份与还原

使用mysqldump备份：

使用sql格式备份：mysqldump --all-databases > my.sql

mysqldump --databases db1 db2 > my.sql

通过sql文件重新加载数据：mysql < my.sql 或者mysql>source my.sql

使用分隔符形式备份数据：

mysqldump --tab=/tmp db1会使用两种格式保存两个文件，一种txt一种sql

参数：--fields-terminated-by=str，--fields-enclosed-by=char，--fields-optionally-enclosed-by=char，--fields-escaped-by=char，--lines-terminated-by=str

恢复分隔符形式的备份文件：

mysql db1

mysqlimport db1 t1.txt

或者

mysql>use db1;

mysql> load data infile 't1.txt' into table t1;

如何复制一个数据库

mysqldump db1 > dump.sql

mysql db2 < dump.sql

不要再mysqldump中使用--database，这会导致use db1的语句存在dump文件中，从而覆盖db2的数据库名。

如何复制一个服务器的数据库到另外一个服务器：

mysqldump --databases db1 > dump.sql

在服务器二：mysql>dump.sql

使用--databases是因为会包含create database语句

如何备份存储过程(存储过程，函数，触发器，事件)

加上参数 --events --routines(存储过程和函数) --triggers

如何存储定义和数据分隔：

--no-data不会复制表格数据，--no-create-info：

升级兼容性测试：

mysqldump --all-databases --no-data --routines --events > dump-defs.sql

在升级的服务器中mysql < dump-defs.sql

使用二进制日志进行时间点恢复(增量恢复)：

查看二进制日志列表：show binary logs

查看二进制日志的名字：show master status

恢复二进制日志：mysqlbinlog mylog | mysql-uroot -p

查看二进制日志信息：mysqlbinlog mylog | more

如果要处理多个二进制日志，每个文件单独一条语句时一种危险的做法，应该在一条语句中执行

mysqlbinlog log1 log2 log3 | mysql -uroot -p

或者使用追加的方式：

mysqlbinlog log1 >my.sql

mysqlbinlog log2 >>my.sql

然后使用：mysql -uroot -p -e "source my.sql"

可以使用--skip-gtids忽略全局事务

恢复时间点：

使用--start-datetime和--stop-datetime来指定恢复的时间：

mysqlbinlog --stop-datetime="2017-04-20 9:59:59" mylog | mysql -uroot -p

mysqlbinlog --start-datetime="2017-04-20 9:59:59" mylog | mysql -uroot -p

使用--start-position和--stop-position来指定恢复的位置：

mysqlbinlog --stop-position=154 mylog | mysql -uroot -p

mysqlbinlog --start-position=209 mylog | mysql -uroot -p

主从配置

原理就是使用二进制日志，一主两从，主服务器在保存数据之前会写入一个binlog记录，里面包含了数据操作的记录，这个记录会被同步到从服务器，然后从服务器会启动sql线程将这些记录同步进数据库

操作过程：

1：分别配置主从服务器的my.cnf文件，添加server-id=1,server-id=2,server-id=3....

主节点上添加log-bin=mylog，开启二进制文件

2：主节点上添加复制用的用户

grant replication slave on . to 'repl'@'%' identified by 'myPassword';

查看主节点状态：show master status;此时会显示日志文件名和position

3：给各个服务器打开3306端口：

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

4：在各个从节点上输入

change master to

master_host='主节点ip', //逗号要有

master_user='repl',

master_password='myPassword',

master_log_file='mylog.000001', //这里的文件名是从主节点上查看到的文件名

master_log_pos=154; //这里的位置是从主节点上查看到的

成功后使用start slave;进行启动

然后查看从节点信息：show slave status\G，如果

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

都为是则说明正常，如果是否则可能有不对的配置，查看端口，log_pos位置等。

主从复制的具体配置my.cnf

server-id=1

log-bin=/var/log/mysql/mysql-bin //二进制日志的存放位置

binlog-do-db=test //需要同步的库

binlog-ignore-db=mysql //不记录日志的库，即不需要同步的库

log-slave-updates //开启从节点上的日志功能

sync_binlog=1 //经过1次日志写操作把日志文件写入硬盘一次，最安全但效率最低，默认是0

#auto_increment //控制自增列的行为

优化

单表优化：

字段：

1：尽量使用tinyint，smallint，medium_int作为整数类型而非INT，非负的加上UNSIGNED

2：varchar长度只分配需要的空间

3：使用枚举或整数代替字符串类型

4：使用timestamp，不使用datetime

5：单表字段建议在20以内

6：用整型来存ip

索引：

1：考虑在where和order by的列上建立索引

2：值分布很稀少的字段不适合建索引

3：字符字段不要做主键

4：不用外键，由程序保证约束

5：不用unique，由程序保证约束

优化sql语句的执行效率的方法：

1：尽量选择较小的列

2：将where中用的比较频繁的字段建立索引

3：select子句中避免使用 *

4：避免在索引列上使用计算，not， in 和<>操作

5：当只需要一行数据的时候使用limit 1

6：保证表单数据不超过200W，适时分隔表

不做列运算，任何对列的操作都导致表扫描，查询时把操作移到等号右边。sql语句尽可能简单，一条sql只能在一个cpu上运行，大语句拆小语句。or改成in，or的效率是n级别，in的效率是log(n)的级别，in的个数建议在200内

-

行子查询：可以使用多个列作为判断条件

select * from t1 where (col1,col2) in (select col3,col4 from t2)

exist：如果子查询有返回，则返回数据，如果子查询没有内容则没有返回

优化子查询：

1：使用可以影响子查询行数或顺序的子查询语句，就是让子查询语句过滤多点数据

2：使用子查询来替换join连接

3：把外部语句移入内部子查询

SELECT FROM t1

WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

替换：

SELECT FROM t1

WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

又如：

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

替换

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

4：使用一个行查询来替代相关子查询：

SELECT FROM t1

WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

替换：

SELECT FROM t1

WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1

AND t2.column2=t1.column2);

5：使用not (a = any()) 比 a <> all()好

6：使用x = any (table containing (1,2)) 比x=1 or x =2好

7：使用 = any比exists好

8：如果记录只返回一行，那么使用=比in好

explain查询分析器

explain的内容：

id select_type table type possible_keys key key_len ref rows Extra

id：执行编号，如果没有嵌套或者子查询，只有唯一的select，那么每行都显示1

select_type：显示本行查询类型：

simple：简单子查询，不包含子查询和union

primary：包含union或子查询

subquery：子查询

derived：派生表

union：

union result：匿名临时表

dependent union：

subquery：

dependent subquery：

table：对应行赈灾访问的表名或别名

type：访问类型：

all：全表扫描

index：扫描表的时候按索引此埙扫描，而不是逐行

range：范围扫描，key列会显示使用了哪个索引。使用=，<>，>，》=,《,<=， BETWEEN或I时可以使用range

ref：索引访问，返回所有所有匹配的单个值的行，只有使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。

eq_ref：使用唯一索引或主键查询时才出现

const：确定最多只有一行匹配的时候，mysql优化器会在查询前读取且只读取一次

system：const连接的一种特列，表仅有一行满足条件

Null：在执行节点甚至用不到访问表或索引(高效)

possible_keys：显示使用了哪些索引

key：实际决定使用的键(索引)，如果没有选择索引则为Null

key_len：使用的键长度，长度越短越好

ref：贤惠使用哪个列或常熟与key一起从表中选择行

rows：执行查询必须检查的行数，这是一个预估值

extra：重要补充信息：

using filesort：使用索引排序

using temporary：用临时表保存中间结果

not exists：一旦匹配了left join就不再搜索

using index：使用覆盖索引，不需要读取数据文件，如果同时出现了using where，表明

using index conditon：

using where：

using join buffer：

impossible where：

select tables optimized away：

distinct：