二、MySql优化

最新推荐文章于 2023-02-07 16:52:28 发布

Liao_Xiang

最新推荐文章于 2023-02-07 16:52:28 发布

阅读量264

点赞数

分类专栏： MySql

本文链接：https://blog.csdn.net/a2231476020/article/details/103737993

版权

MySql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

单表优化

CREATE TABLE book (
  bid INT (4) PRIMARY KEY,
  NAME VARCHAR (20) NOT NULL,
  authorid INT (4) NOT NULL,
  publicid INT (4) NOT NULL,
  typeid INT (4) NOT NULL
) ;

INSERT INTO book VALUES(1,'tjava',1,1,2) ;
INSERT INTO book VALUES(2,'tc',2,1,2) ;
INSERT INTO book VALUES(3,'wx',3,2,1) ;
INSERT INTO book VALUES(4,'math',4,2,3) ;	
COMMIT;

查询authorid=1且typeid为2或3的bid

EXPLAIN 
SELECT 
  bid 
FROM
  book 
WHERE typeid IN (2, 3) 
  AND authorid = 1 
ORDER BY typeid DESC ;

在这里插入图片描述 优化type: ALL：
加索引：alter table book add index idx_bta (bid,typeid,authorid);
优化索引顺序：
根据sql语句的执行顺序调整索引的顺序：先where后select

from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit

删除之前的索引：drop index idx_bta on book;
修改索引顺序：alter table book add index idx_tab (typeid,authorid,bid);
虽然可以回表查询bid，但是将bid放到索引中可以提升使用using index
在这里插入图片描述
优化type: index：

#删除索引
DROP INDEX idx_tab ON book;`
#修改
alter table book add index idx_atb (authorid,typeid,bid);

#将范围查询放在后面
EXPLAIN 
SELECT 
  bid 
FROM
  book 
WHERE authorid = 1 
  AND typeid IN (2, 3) 
ORDER BY typeid DESC ;

在这里插入图片描述小结：

最佳左前缀，保持索引的定义和使用的顺序一致性
索引需要逐步优化
将含In的范围查询放到where条件的最后，防止失效。

本例中同时出现了Using where(需要回原表); Using index(不需要回原表)，原因如下：
where authorid=1 and typeid in(2,3)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表(直接在索引表中能查到)，而typeid虽然也在索引(authorid,typeid,bid)中，但是含in的范围查询已经使该typeid索引失效(通过最后图中的key_len证明In可以使索引失效)，因此相当于没有typeid这个索引，所以需要回原表(using where)，例如以下没有使用In，则不会出现Using where

EXPLAIN 
SELECT 
  bid 
FROM
  book 
WHERE authorid = 1 
  AND typeid = 3 
ORDER BY typeid DESC ;

在这里插入图片描述

双表优化

CREATE TABLE teacher2 (
  tid INT (4) PRIMARY KEY,
  cid INT (4) NOT NULL
) ;

INSERT INTO teacher2 VALUES(1,2);
INSERT INTO teacher2 VALUES(2,1);
INSERT INTO teacher2 VALUES(3,3);

CREATE TABLE course2 (cid INT (4), cname VARCHAR (20)) ;

INSERT INTO course2 VALUES(1,'java');
INSERT INTO course2 VALUES(2,'python');
INSERT INTO course2 VALUES(3,'kotlin');
COMMIT;

在这里插入图片描述
执行查询

SELECT 
  * 
FROM
  teacher2 t 
  LEFT OUTER JOIN course2 c 
    ON t.cid = c.cid 
WHERE c.cname = 'java' ;

在这里插入图片描述

EXPLAIN 
SELECT 
  * 
FROM
  teacher2 t 
  LEFT OUTER JOIN course2 c 
    ON t.cid = c.cid 
WHERE c.cname = 'java' ;

在这里插入图片描述
加索引
索引往哪张表加？小表驱动大表

索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） 
一般情况对于左外连接，给左表加索引；右外连接，给右表加索引

小表：10
大表：300

小表.x(10条) = 大表.y(300条)   循环了10次
大表.y=小表.x                 循环了300次


小表:10
大表:300

select ...where 小表.x10=大表.x300 ;
for(int i=0;i<小表.length10;i++)
{
	for(int j=0;j<大表.length300;j++)
	{
		...
	}
}


select ...where 大表.x300=小表.x10 ;
for(int i=0;i<大表.length300;i++)
{
	for(int j=0;j<小表.length10;j++)
	{
		...
	}
}

以上2个for循环，最终都会循环3000次；但是对于双层循环来说：一般建议将数据小的循环放外层，数据大的循环放内层
给两个表加索引

ALTER TABLE teacher2 ADD INDEX index_teacher2_cid(cid);
ALTER TABLE course2 ADD INDEX index_course2_cname(cname);

在这里插入图片描述

三表优化

小表驱动大表(放在等式的左边)
索引建立在经常查询的字段上

避免索引失效的一些原则

在这里插入图片描述

复合索引：不要跨列、无序使用(最佳左前缀)、尽量使用全索引匹配
不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效
select …where A.x = … ; --假设A.x是索引，不要：select …where A.x3 = … ;
explain select * from book where authorid = 1 and typeid = 2 ;–用到了at2个索引
explain select * from book where authorid = 1 and typeid2 = 2 ;–用到了a1个索引
explain select * from book where authorid2 = 1 and typeid2 = 2 ;----用到了0个索引
explain select * from book where authorid*2 = 1 and typeid = 2 ;----用到了0个索引,
原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。
复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效，复合索引中如果有>，则自身和右侧索引全部失效。
SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测。
体验概率情况(< > =)：原因是服务层中有SQL优化器，可能会影响我们的优化。
alter table book add index idx_book_at (authorid,typeid);
复合索引at全部使用
explain select * from book where authorid = 1 and typeid =2 ;
复合索引中如果有>，则自身和右侧索引全部失效。
explain select * from book where authorid > 1 and typeid =2 ;
复合索引at全部使用，明显的概率问题
explain select * from book where authorid = 1 and typeid >2 ;
复合索引at只用到了1个索引
explain select * from book where authorid < 1 and typeid =2 ;
复合索引全部失效
explain select * from book where authorid < 4 and typeid =2 ;
我们学习索引优化，是一个大部分情况适用的结论，但由于SQL优化器等原因该结论不是100%正确。一般而言，范围查询（> < in），之后的索引失效。
补救：尽量使用索引覆盖（Using index）
（a,b,c）select a,b,c from xx…where a= … and b =… ;
like尽量以“常量”开头，不要以’%'开头，否则索引失效，如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖挽救一部分。
尽量不要使用类型转换（显示、隐式），否则索引失效
explain select * from teacher where tname = 123 ; 程序底层将 123 -> ‘123’，即进行了类型转换，因此索引失效
尽量不要使用or，否则索引失效，explain select * from teacher where tname =’’ or tcid >1 ; or左侧的tname 失效。

其他的一些优化方法

（1）
exist和in
select ..from table where exist (子查询) ;
select ..from table where 字段 in  (子查询) ;

如果主查询的数据集大，则使用In   ,效率高。
如果子查询的数据集大，则使用exist,效率高。	

exist：
exist语法：将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据则校验成功）  ，如果符合校验，则保留数据

select tname from teacher where exists (select * from teacher) ; 
#等价于：
select tname from teacher

#子查询为空，则校验失败，主查询为空
select tname from teacher where exists (select * from teacher where tid =9999) ;

in:
select ..from table where tid in  (1,3,5) ;

（2）order by 优化
using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）
MySQL4.1之前 默认使用 双路排序
双路：扫描2次磁盘--IO较消耗性能
1：从磁盘读取排序字段，对排序字段进行排序（在buffer中进行的排序）
2：扫描其他字段

MySQL4.1之后 默认使用 单路排序
只读取一次（全部字段），在buffer中进行排序。但种单路排序会有一定的隐患
不一定真的是“单路|1次IO”，有可能多次IO
原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”
注意：单路排序 比双路排序 会占用更多的buffer

单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：
set max_length_for_sort_data = 1024  单位byte

如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路   
（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）

提高order by查询的策略：
a.选择使用单路、双路 ；调整buffer的容量大小；
b.避免select * …
c.复合索引 不要跨列使用 ，避免using filesort
d.保证全部的排序字段 排序的一致性（都是升序 或 降序）

SQL排查

慢查询日志：MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句（long_query_time，默认10秒）
慢查询日志默认是关闭的，建议：开发调优是打开，而最终部署时关闭。

检查是否开启了 慢查询日志 ：
	show variables like '%slow_query_log%' ;

临时开启：
	set global slow_query_log = 1 ;  --在内存中开启
	exit --退出mysql
	service mysql restart  --重启mysql

永久开启：配置中追加日志配置：
	/etc/my.cnf
	vi /etc/my.cnf 
	[mysqld]--添加的地方
	slow_query_log=1--新添加内容
	slow_query_log_file=/var/lib/mysql/localhost-slow.log--慢查询日志放入文件中


慢查询阀值：
	show variables like '%long_query_time%' ;

临时设置阀值：
	set global long_query_time = 5 ; --设置完毕后，重新登陆后起效 （不需要重启服务）

永久设置阀值：
	/etc/my.cnf 中追加配置：
	vi /etc/my.cnf 
	[mysqld]
	long_query_time=3

模拟慢查询：
	select sleep(4);
	select sleep(5);
	select sleep(3);
	select sleep(3);
	
查询超过阀值的SQL：  
	show global status like '%slow_queries%' ;

(1)慢查询的sql被记录在了日志中，因此可以通过日志 查看具体的慢SQL。
	cat /var/lib/mysql/localhost-slow.log

(2)通过mysqldumpslow工具查看慢SQL,可以通过一些过滤条件 快速查找出需要定位的慢SQL
	mysqldumpslow --help
	s：排序方式
	r:逆序
	l:锁定时间
	g:正则匹配模式		


--获取返回记录最多的3个SQL
	mysqldumpslow -s r -t 3  /var/lib/mysql/localhost-slow.log

--获取访问次数最多的3个SQL
	mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

--按照时间排序，前10条包含left join查询语句的SQL
	mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

语法：
	mysqldumpslow 各种参数  慢查询日志的文件

分析海量数据(使用存储函数和存储过程)

插入数据

模拟海量数据存储过程（无return）/存储函数（有return）

CREATE DATABASE testdata ;

USE testdata CREATE TABLE dept (
  dno INT (5) PRIMARY KEY DEFAULT 0,
  dname VARCHAR (20) NOT NULL DEFAULT '',
  loc VARCHAR (30) DEFAULT ''
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

CREATE TABLE emp (
  eid INT (5) PRIMARY KEY,
  ename VARCHAR (20) NOT NULL DEFAULT '',
  job VARCHAR (20) NOT NULL DEFAULT '',
  deptno INT (5) NOT NULL DEFAULT 0
) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

通过存储函数插入海量数据
创建存储函数(randstring(6) ->aXiayx 用于模拟员工名称)：

#声明语句以$ 结尾，为不是以;分号结尾
DELIMITER $

CREATE FUNCTION randstring(n INT)   RETURNS VARCHAR(255) 
BEGIN
	DECLARE  all_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ;
	DECLARE return_str VARCHAR(255) DEFAULT '' ;
	DECLARE i INT DEFAULT 0 ; 
	WHILE i<n		 
	DO									
		SET return_str = CONCAT(return_str, SUBSTRING(all_str, FLOOR(1+RAND()*52), 1));
		SET i=i+1 ;
	END WHILE ;
	RETURN return_str;
END $

1.如果报错：You have an error in your SQL syntax，说明SQL语句语法有错，需要修改SQL语句，SQL语句的申明变量和java的语法是相反的，是先申明变量，在放类型。
2.如果报错：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)，是因为存储过程/存储函数在创建时与之前的开启慢查询日志冲突了

解决冲突：

临时解决
( 开启log_bin_trust_function_creators )
show variables like ‘%log_bin_trust_function_creators%’;
set global log_bin_trust_function_creators = 1;
永久解决：
/etc/my.cnf
[mysqld]
log_bin_trust_function_creators = 1

SQL语句中的存储过程和存储函数

mysql语句中的Repeat循环语句的语法

REPEAT
Statements;
UNTIL expression
END REPEAT

在REPEAT语句中不管是否满足给定条件，首先会执行一次statements, 然后再在UTILE中判断给定的条件是否成立，如果条件不成立会继续执行，如果条件成立则退出REPEAT循环。

	--产生随机整数，注意returns 和return之间的区别，前者是声明返回值的类型，后者是具体的返回值。
	delimiter $
	create function ran_num() returns int(5)
	begin
		declare i int default 0;
		set i =floor( rand()*100 ) ;
		return i ;
	end $
	
    --通过存储过程插入海量数据：emp表中  ，  10000,   100000
	delimiter $
	create procedure insert_emp(in eid_start int(10), in data_times int(10))
	begin 
		declare i int default 0;
		set autocommit = 0 ;  //关闭自动提交。
		repeat
			insert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;
			set i=i+1 ;
			until i=data_times  //条件不成立的时候， 才会继续循环。
		end repeat ;   //需要用它来进行结束标志。
		commit ;
	end $


	--通过存储过程插入海量数据：dept表中  
		delimiter $
		create procedure insert_dept(in dno_start int(10) ,in data_times int(10))
		begin
			declare i int default 0;
			set autocommit = 0 ;
			repeat
				insert into dept values(dno_start+i ,randstring(6),randstring(8)) ;
				set i=i+1 ;
				until i=data_times
			end repeat ;
		commit ;
		end$


	--插入数据
		delimiter ; 
		call insert_emp(1000,800000) ;
		call insert_dept(10,30) ;

分析

（1）profiles
	show profiles ; --默认关闭
	show variables like '%profiling%';
	set profiling = on ; 
	show profiles  ：会记录所有profiling打开之后的  全部SQL查询语句所花费的时间。 
	缺点：不够精确，只能看到 总共消费的时间，不能看到各个硬件消费的时间（cpu  io ）

(2)--精确分析:sql诊断
	 show profile all for query + 上一步查询的的Query_Id
	 show profile cpu,block io for query +上一步查询的的Query_Id

(3)全局查询日志 ：记录开启之后的 全部SQL语句。 （这种全局的记录操作 仅仅在调优、开发过程中打开即可，在最终的部署实施时 一定关闭）
	show variables like '%general_log%';
	
	--执行的所有SQL记录在表中
	set global general_log = 1 ;--开启全局日志
	set global log_output='table' ; --设置 将全部的SQL 记录在表中

	--执行的所有SQL记录在文件中
	set global log_output='file' ;
	set global general_log = on ;
	set global general_log_file='/tmp/general.log' ;
	

	开启后，会记录所有SQL ： 会被记录 mysql.general_log表中。
		select * from  mysql.general_log ;

锁机制

示例：买最后一件衣服X
A:  	X	买 ：  X加锁 ->试衣服...下单..付款..打包 ->X解锁
B:	X       买：发现X已被加锁，等待X解锁，   X已售空

分类：
操作类型：
	a.读锁（共享锁）： 对同一个数据（衣服），多个读操作可以同时进行，互不干扰。
	b.写锁（互斥锁）： 如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作

操作范围：
	a.表锁 ：一次性对一张表整体加锁。如==MyISAM存储引擎使用表锁==，开销小、加锁快；无死锁；
			但锁的范围大，容易发生锁冲突、并发度低。
	b.行锁 ：一次性对一条数据加锁。如==InnoDB存储引擎使用行锁==，开销大，加锁慢；
			容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。
	c.页锁

示例：自增操作 MYSQL/SQLSERVER 支持；oracle需要借助于序列来实现自增

CREATE TABLE tablelock (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (20)
) ENGINE MYISAM ;

INSERT INTO tablelock(NAME) VALUES('a1');
INSERT INTO tablelock(NAME) VALUES('a2');
INSERT INTO tablelock(NAME) VALUES('a3');
INSERT INTO tablelock(NAME) VALUES('a4');
INSERT INTO tablelock(NAME) VALUES('a5');
COMMIT;

表锁

增加表锁：
locak table 表1  read/write  ,表2  read/write   ,...

查看加锁的表：
show open tables ;

会话：session :每一个访问数据的dos命令行、数据库客户端工具  都是一个会话

加读锁：
	会话0：
		lock table  tablelock read ;
		select * from tablelock; --读（查），可以
		delete from tablelock where id =1 ; --写（增删改），不可以

		select * from emp ; --读，不可以
		delete from emp where eid = 1; --写，不可以
		结论1：
			如果某一个会话 对A表加了read锁，则 该会话 可以对A表进行读操作、不能进行写操作；  
		且该会话不能对其他表进行读、写操作，即如果给A表加了读锁，则当前会话只能对A表进行读操作。

	会话1（其他会话）：
		select * from tablelock;   --读（查），可以
		delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放

	会话2（其他会话）：
		select * from emp ;  --读（查），可以
		delete from emp where eno = 1; --写，可以
		结论2：
			会话0给A表加了锁；其他会话的操作： 
			a.可以对其他表（A表以外的表）进行读、写操作
			b.对A表：读-可以；  写-需要等待释放锁。
	释放锁: unlock tables ;


加写锁：
	会话0：
		lock table tablelock write ;

	当前会话（会话0）：可以对加了写锁的表进行任何操作（增删改查）；但是不能操作（增删改查）其他表
	其他会话：对会话0中加写锁的表 可以进行增删改查的前提是：等待会话0释放写锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。所以对MyISAM表进行操作，会有以下情况：

a、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，
但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
b、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

分析表锁定：
查看哪些表加了锁： show open tables ; 1代表被加了锁
分析表锁定的严重程度： show status like ‘table%’ ;
Table_locks_immediate ：即可能获取到的锁数，当前可以加锁的表，加的次数
Table_locks_waited：需要等待的表锁数(当前会话要操作A表，其他会话加了锁的数量，值越大，存在越大的锁竞争)
一般建议：Table_locks_immediate / Table_locks_waited > 5000，建议采用InnoDB引擎，否则MyISAM引擎

行锁

CREATE TABLE linelock (
  id INT (5) PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (20)
) ENGINE = INNODB ;

INSERT INTO linelock(NAME) VALUES('1') ;
INSERT INTO linelock(NAME) VALUES('2') ;
INSERT INTO linelock(NAME) VALUES('3') ;
INSERT INTO linelock(NAME) VALUES('4') ;
INSERT INTO linelock(NAME) VALUES('5') ;

mysql默认自动commit，oracle默认不会自动commit 。
为了研究行锁，暂时将自动commit关闭，set autocommit =0 ; 以后需要通过commit

操作同一行的数据
	会话0： 写操作，不提交(id:6 name:a6)
		insert into linelock values('a6') ;
	   
	会话1： 写操作,尝试修改刚刚插入的这条数据(id:6 name:ax)
		update linelock set name='ax' where id = 6;
		#执行语句后，处于等待状态，直到会话0执行commit
	
	对行锁情况：
		1.如果会话x对某条数据a进行 DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后，才能对数据a进行操作。
		2.表锁是通过unlock tables，也可以通过事务解锁，行锁是通过事务解锁。

	

操作不同行数据：

	会话0： 写操作
		insert into linelock values(8,'a8') ;
	会话1： 写操作
		update linelock set name='ax' where id = 5;
		#行锁，一次锁一行数据；因此 如果操作的是不同数据，则不干扰。


行锁的注意事项：
（1）如果没有索引，则行锁会转为表锁
	show index from linelock ;
	alter table linelock add index idx_linelock_name(name);

	会话0： 写操作
		update linelock set name = 'ai' where name = '3' ;
		
	会话1： 写操作， 不同的数据
		update linelock set name = 'aiX' where name = '4' ;
		#以上互不干扰


	会话0： 写操作
		update linelock set name = 'ai' where name = 3 ;
		
	会话1： 写操作， 不同的数据
		update linelock set name = 'aiX' where name = 4 ;
		
	#可以发现，会话2被阻塞了（加锁）
	#原因：如果索引类发生了类型转换，则索引失效，因此此次操作中，行锁转为了表锁

（2）行锁的一种特殊情况：间隙锁：值在范围内，但却不存在
	 #此时linelock表中 没有id=7的数据，即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。
	 update linelock set name ='x' where id > 1 and id < 9;
	 
	 #执行语句后会阻塞，直到上面的语句commit
	 insert into linelock values(7, 'a7');
	 
	 间隙：Mysql会自动给间隙加锁：间隙锁。即本题会自动给id=7的数据加间隙锁（行锁）(据说可能也是表锁，操作id>9的也不行)
	 行锁：如果有where，则实际加索的范围 就是where后面的范围（不是实际的值）


如何仅仅是查询数据，能否加锁？ 可以   for update 
	研究学习时，将自动提交关闭：
	set autocommit =0;
	start transaction;
	begin;
	
	select * from linelock where id =2 for update ;
	
	通过for update对query语句进行加锁。

行锁：
	InnoDB默认采用行锁
	缺点：比表锁性能损耗大。
	优点：并发能力强，效率高。
	因此建议，高并发用InnoDB，否则用MyISAM。

行锁分析：
	show status like '%innodb_row_lock%';
	Innodb_row_lock_current_waits：当前正在等待锁的数量  
	Innodb_row_lock_time：等待总时长。从系统启到现在 一共等待的时间
	Innodb_row_lock_time_avg：平均等待时长。从系统启到现在平均等待的时间
	Innodb_row_lock_time_max：最大等待时长。从系统启到现在最大一次等待的时间
	Innodb_row_lock_waits：等待次数。从系统启到现在一共等待的次数

主从同步（集群在数据库的一种实现）

windows：mysql 主
linux：mysql从

安装windows版mysql:
	如果之前计算机中安装过Mysql，要重新再安装  则需要：先卸载 再安装
	先卸载：
		通过电脑自带卸载工具卸载Mysql (电脑管家也可以)
		删除一个mysql缓存文件C:\ProgramData\MySQL
		删除注册表regedit中所有mysql相关配置
		--重启计算机
		
	安装MYSQL：
		安装时，如果出现未响应，则重新打开D:\MySQL\MySQL Server 5.5\bin\MySQLInstanceConfig.exe

	图形化客户端： SQLyog, Navicat


	如果要远程连接数据库，则需要授权远程访问。 
	授权远程访问 :(A->B，则在B计算机的Mysql中执行以下命令)
	GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
	FLUSH PRIVILEGES;

	如果仍然报错：可能是防火墙没关闭：在B关闭防火墙  service iptables stop 

实现主从同步（主从复制）：图
	1.master将改变的数 记录在本地的 二进制日志中（binary log） ；该过程 称之为：二进制日志件事
	2.slave将master的binary log拷贝到自己的 relay log（中继日志文件）中
	3.中继日志事件，将数据读取到自己的数据库之中
MYSQL主从复制 是异步的，串行化的， 有延迟

master:slave = 1:n

配置： 
windows(mysql: my.ini)
linux(mysql: my.cnf)

配置前，为了无误，先将权限(远程访问)、防火墙等处理：
	关闭windows/linux防火墙： windows：右键“网络”   ,linux: service iptables stop
	Mysql允许远程连接(windowos/linux)：
		GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
		FLUSH PRIVILEGES;

主机（以下代码和操作 全部在主机windows中操作）：
my.ini文件中：
[mysqld]
#id
server-id=1
#二进制日志文件（注意是/ 不是\）
log-bin=“D:/MySQL/MySQL Server 5.5/data/mysql-bin”
#错误记录文件
log-error=“D:/MySQL/MySQL Server 5.5/data/mysql-error”
#主从同步时 忽略的数据库
binlog-ignore-db=mysql
#(可选)指定主从同步时，同步哪些数据库
binlog-do-db=test

windows中的数据库授权哪台计算机中的数据库是自己的从数据库：
GRANT REPLICATION slave,reload,super ON . TO ‘root’@‘192.168.2.%’ IDENTIFIED BY ‘root’;
flush privileges ;

查看主数据库的状态（每次在做主从同步前，需要观察 主机状态的最新值）
show master status;  （mysql-bin.000001、 107）

从机（以下代码和操作 全部在从机linux中操作）：

my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
replicate-do-db=test

linux中的数据授权哪台计算机中的mysql 是自己的主计算机
CHANGE MASTER TO
MASTER_HOST = ‘192.168.2.2’,
MASTER_USER = ‘root’,
MASTER_PASSWORD = ‘root’,
MASTER_PORT = 3306,
master_log_file=‘mysql-bin.000001’,
master_log_pos=107;
如果报错：This operation cannot be performed with a running slave; run STOP SLAVE first
解决：STOP SLAVE ;再次执行上条授权语句

开启主从同步：
从机linux:
start slave ;
检验： show slave status \G 
主要观察： Slave_IO_Running 和 Slave_SQL_Running，确保二者都是yes；如果不都是yes，则看下方的 Last_IO_Error。
本次通过 Last_IO_Error发现错误的原因是 主从使用了相同的server-id
检查：在主从中分别查看serverid: show variables like ‘server_id’ ;
可以发现，在Linux中的my.cnf中设置了server-id=2，但实际执行时 确实server-id=1
原因：可能是 linux版Mysql的一个bug，也可能是 windows和Linux版本不一致造成的兼容性问题。
解决改bug： set global server_id =2 ;
stop slave ;
set global server_id =2 ;
start slave ;
show slave status \G

演示：
	主windows =>从
	windows:
	将表，插入数据  
	观察从数据库中该表的数据

Liao_Xiang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
二、MySql优化

单表优化CREATE TABLE book ( bid INT (4) PRIMARY KEY, NAME VARCHAR (20) NOT NULL, authorid INT (4) NOT NULL, publicid INT (4) NOT NULL, typeid INT (4) NOT NULL) ;INSERT INTO book VALUES(1,'tj...
复制链接

扫一扫