单表优化
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
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:
将表,插入数据
观察从数据库中该表的数据