mysql性能优化

文章目录

• 1.为什么要进行sql优化
  * 2.Mysql安装启动配置(CentOS7)

• mysql版本与性能
  * mysql安装-rpm
  * mysql常用命令
  * msyql常用配置
  * 开放远程连接

  
  1
  2
  1  * 3.逻辑分层,存储引擎,解析过程
  
  2
  
  

• 逻辑分层
  * 存储引擎
  * 解析过程

  
  1
  2
  1  * 4.索引,执行计划
  
  2
  
  

• 索引
  * 执行计划
  * 优化总结
  * 常用sql语句

  
  1
  2
  3
  4
  1  * 5.慢查询
  
  2  * 6.创建，分析海量数据
  
  3  * 7.主从复制
  
  4
  
  

1.为什么要进行sql优化

因为没有进行sql优化的语句执行性能低下。而性能低下的原因:sql语句欠佳，索引失效，服务器参数设置不合理（缓冲、线程数）

整个优化过程 主要是围绕索引进行

详情参考官网： https://dev.mysql.com/doc/refman/5.5/en/optimization.html

2.Mysql安装启动配置(CentOS7)

mysql版本与性能

1. 版本介绍与选择

目前主流版本 5.x：

5.0-5.1：相当于4.x版本的延续，升级维护

5.4 -5.x: Mysql整合了三方公司的新存储引擎（推荐使用5.5版本，当前比较稳定的版本）

2、硬件与性能

mysql安装-rpm

检查服务器msyql安装情况，有就先卸载自带的mysql【Centos7 web服务版系统 默认安装mariadb】

rpm -qa|grep 软件名字 【检查命令】

rpm -e –nodeps 软件包名 【卸载命令】

yum remove 软件包名 【卸载命令】

1
2
3
4
5
6
7
8
9
1rpm -qa|grep mariadb

2

3//卸载方式一

4rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64

5

6//卸载方式二(建议使用yum卸载,可自动处理依赖关系)

7yum remove mariadb-libs-5.5.44-2.el7.centos.x86_64

8

9

安装，先安装服务端，再安装客户端

rpm -ivh 软件名字 【安装命令】

安装过程中可能会出现缺少依赖或者依赖冲突的问题，只要对依赖进行相应的安装和卸载即可

1
2
3
4
1rpm -ivh MySQL-server-5.5.49-1.linux2.6.x86_64.rpm

2rpm -ivh MySQL-client-5.5.49-1.linux2.6.x86_64.rpm

3

4

验证是否安装成功【查看mysql版本】

1
2
3
1mysqladmin --version

2

3

mysql常用命令

1. 常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
1启动： service msyql start

2

3关闭： service mysql stop

4

5重启： service mysql restart

6

7设置开机自启： chkconfig mysql on

8

9检查开机自启是否成功： ntsysv / chkconfig --list 

10

11mysql清屏： ctrl+L  , system clear

12

13mysql安装成功启动时，肯会报 “var/lib/mysql/mysql.sock不存在”的错误，是     因为mysql服务没有启动，需要先手动启动服务： /etc/init.d/msyql start

14

15

msyql常用配置

• 设置密码

1
2
3
1/usr/bin/mysqladmin -u root password 'new-password'

2

3

• 登录

1
2
3
1mysql -u root -p

2

3

• mysql核心目录

数据库目录： datadir=/var/lib/mysql

pid文件目录：–pid-fire= /var/lib/mysql/bigdata01.pid

配置文件： /usr/share/msyql

命令目录： /usr/bin 【msyqladmin,mysqldump等命令】

mysql启动脚本: /etc/init.d/mysql

• mysql配置文件（/var/share/msyql目录下）

my-huge.cnf 高端服务器 1-2G内存就算是高端服务器可以使用

my-large.cnf 中等规模

my-medium.cnf 一般

my-smqll.cnf 较小

但是，MySQL默认使用的配置文件是 /etc/my.cnf想要采用my-huge.cnf,复制替换即可

注意：MySQL版本不同，默认配置文件名称不同，如：mysql5.6 默认配置为叫 /etc/mysql-default.cnf

1
2
3
1cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

2

3

• mysql字符编码

统一编码为utf8

注意：修改编码只对修改之后创建的数据库生效

1
2
3
4
1/*查看编码*/

2mysql> show variables like '%char%';

3

4

1
2
3
4
5
6
7
8
9
10
11
12
13
1#修改编码

2vi /etc/my.cnf

3

4[msyql]

5default-character-set=utf8

6[client]

7default-character-set=utf8

8[msyqld]

9character_set_server=utf8

10character_set_client=utf8

11collation_server=utf8_general_ci

12

13

开放远程连接

设置root用户远程连接权限和密码

1
2
3
4
1mysql> grant all privileges on  *.*  to  'root'  @'%'  identified  by  'remote-password';

2mysql> flush  privileges; 

3

4

开放端口

1
2
3
4
1firewall-cmd --zone=public --add-port=3306/tcp --permanent

2firewall-cmd --reload

3

4

3.逻辑分层,存储引擎,解析过程

逻辑分层

服务层 – 1.提供各种用户使用的接口(增删改查，等) 2.提供sql优化器（mysql query optimizer）
引擎层 – 提供了各种存储数据的方式（InnoDB,MyISAM）
存储层 – 存储数据

存储引擎

常用引擎（InnoDB， MyISAM）

MyISAM：ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合

InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况

区别

MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。

1
2
11. 

2

MyISAM只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据

InnoDB：支持行级锁和事务，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在有索引时是有效的，无索引或索引失效都会锁全表的

1
2
11. 

2

InnoDB支持外键，MyISAM不支持。

1
2
11. 

2

InnoDB的主键范围更大，最大是MyISAM的2倍。

1
2
11. 

2

InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。

1
2
11. 

2

MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。

1
2
11. 

2

没有where的count()使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count()时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count()时一般要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count()的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。

存储结构

InnoDB 和 Myisam 都是用 B+Tree 来存储数据的。

一般情况3层B+Tree可以存放上百万条数据

常用命令

查询支持的引擎

1
2
3
1mysql>  show engines;

2

3

查看当前使用的引擎

1
2
3
1mysql>  show variables like '%storage_engine%';

2

3

指定表使用的引擎 engine

1
2
3
4
5
6
7
8
1mysql>  create table tb(

2            id  int(4)  auto_increment,

3            name varchar(5),

4            dept varchar(5),

5            primary key(id)

6        )engine=MyISAM  DEFAULT CHARSET=utf8 ;

7

8

解析过程

sql编写过程：

dinstinct 去重

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

2

3

sql解析过程：

详解可参考 https://www.cnblogs.com/annsshadow/p/5037667.html

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

2

3

4.索引,执行计划

索引

什么是索引

索引：相当于书的目录，是帮助mysql高效获取数据的数据结构

索引类型

​ 普通索引 ： index idx_ 字段名

​ 唯一索引： unique uk_字段名

​ 主键索引： primary key pk_字段名

​ 复合索引： 多个字段组成的索引（name,age） (按索引顺序字段名命名 idx_name_age)

复合索引并不一定所有索引字段都会用到。当name已经查询出结果是，不会再去查询age索引

但为了性能考虑建议在sql语句中用到所有的索引字段

创建索引

方式一 ： create 索引类型 索引名 on 表(字段);

方式二 ： alter table 表名 add constraint 索引名 索引类型(字段)；

1
2
3
4
5
6
7
8
9
10
11
12
13
1//简单索引

2mysql>  create index idx_dept on tb(dept);

3

4//唯一索引

5mysql>  create unique index uk_name on tb(name);

6

7//复合索引

8mysql>  create index idx_dept_name on tb(dept,name);

9

10//主键索引

11mysql>  alter table tb add constraint pk_id primary key(id);

12

13

删除索引

drop index 索引名 on 表名；

1
2
3
4
5
1mysql>  drop index uk_name on tb;

2mysql>  drop index idx_dept on tb;

3mysql>  drop index idx_dept_name on tb;

4

5

查询索引

show index from 表名；

1
2
3
1mysql> show index from tb;

2

3

不适合创建索引的字段

索引本身需要的存储空间很大的字段

频繁需要修改的字段

很少使用的列（sql语句中用不到的列）

重复值多的列

执行计划

数据准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
1mysql> 

2     create table course(

3            cid int(3),

4            cname varchar(20),

5            tid int(3)

6        );

7      create table teacher(

8            tid int(3),

9            tname varchar(20)

10        );

11        create table teacherCard(

12            tcid int(3),

13            tid int(3),

14            tcdesc varchar(200)

15        );

16

17mysql> 

18

19insert into course values (1,'java',1),(2,'html',1),(3,'sql',2),(4,'web',3);

20insert into teacher values (1,'zhangsan'),(2,'lishi'),(3,'wangwu');

21insert into teacherCard values (1,1,'zhangsan card'),(2,2,'lisi card'),(3,3,'wangwu card');

22

23

语法

explain sql语句；

1
2
3
1explain select * from tb;

2

3

explain信息详解 id

id：sql语句执行的编号

id值相同，从上往下顺序执行。这个顺序受表数据量的大小影响，先查数据量小的，后查数据量大的

id值不同：先执行id值大的

1
2
3
4
5
1#查询课程编号为2 或 教师证编号为3 的老师信息

2explain

3select t.tname from teacher t, course c, teacherCard tc where t.tid = c.tid  and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);

4

5

1
2
3
4
5
1#查询教授sql课程老师的描述信息

2explain

3select tc.tcdesc from teacherCard tc where tc.tid = (select c.tid from course c where c.cname = 'sql' );

4

5

explain信息详解 select_type

select_type 查询类型

PRIMARY : 主查询，sql中包含有子查询

SUBQUERY : 子查询

SIMPLE： 普通查询 （不含有子查询和union 连接查询的查询）

DERIVED: 衍生查询 （使用到了临时表）

union ： 使用到了union 连接查询

union result: 显示拿些表之间使用了union

explain信息详解 table

表名

explain信息详解 type

type 索引类型

常用到的类型system > const > eq_ref > ref > range > index > all

system 性能最高，all性能最低。 实际项目中达到 ref > range 性能就行

system ： 表只有中一条数据的主查询

const ： 查询结果只有一条数据的sql ,并且索引类型必须为主键索引或者唯一索引

eq_ref ： 查询结果可以有多条数据，但满足where判断条件的每一条数据必须是唯一的一条数据(不能多条也不能为0条)。

1
2
3
4
1explain

2select t.tname from teacher t,teacherCard tc where t.tid = tc.tid;

3

4

ref : 索引查询返回匹配所有行（0条，多条）

1
2
3
4
1explain

2select t.tname from teacher t where t.tname = 'ta';

3

4

range : 检索指定范围的行，where后面是一个范围查询（between , in , > , < 等 其中in可能会导致索引失效而变成 all）

1
2
3
1explain select tc.tcdesc from teacherCard tc where tc.tid between 1 and 2;

2

3

index : 查询全部索引的数据

1
2
3
1explain select t.tid from teacher t;

2

3

all ：查询全部表的数据(sql 将表的所有数据都查了一遍) ，没有用到索引时常出现

1
2
3
1explain select c.cname  from course c;

2

3

explain信息详解 possible_keys

可供选择的索引

explain信息详解key

实际用到的索引

explain信息详解key_len

实际使用到索引的长度（utf8 1个字符3个字节，）

explain信息详解ref

表之间的引用

指明当前表所参照的字段

const : 判断条件中用到了常量

或者显示用到了其他表的那些字段

explain信息详解rows

估计查询了表中的数据行数，（MySQL认为必须检查以执行查询的行数）

explain信息详解extra

准备工作

1
2
3
4
5
6
7
8
9
10
1create table t (

2   a1 char(3),

3    a2 char(3),

4    a3 char(3),

5    index idx_a1(a1),

6    index idx_a2(a2),

7    index idx_a3(a3)

8);

9

10

using filesort : 性能损耗大，需要额外的查询(排序) ，常见于 order by 语句中

1
2
3
4
5
6
7
8
9
10
11
1#当排序和查找不是同一个字段就会出现using filesort

2explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a2;  //反例

3explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a1;   //正例

4

5#复合索引不能跨列（最佳左前缀）否则会出现using filesort

6create index idx_a1_a2_a3 on t(a1,a2,a3);

7explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a3;  //反例(跨了 a2)

8explain select t.a1,t.a2,t.a3 from t where t.a2 = '' order by a3;  //反例(跨了 a1)

9explain select t.a1,t.a2,t.a3 from t where t.a1 = '' order by a2;  //正例

10

11

using temporary:性能损耗大，用到了临时表，一般出现与 group by 语句中

1
2
3
4
1explain select t.a1,t.a2,t.a3 from t where a1 in ('1','2','3') group by a2;  //反例

2explain select t.a1,t.a2,t.a3 from t where a1 in ('1','2','3') group by a1;  //正例

3

4

using index: 性能提升，只从索引中查询数据，不需要回表查询

using where: 进行了回表查询

1
2
3
4
5
6
7
1drop index idx_a1_a2_a3 on t；

2create index idx_a1_a2 on t(a1,a2);

3

4explain select a1,a2 from t where a1='' and a2 = '';  //正例

5explain select a1,a3 from t where a1='';  //反例

6

7

impossible where : where 查询条件永远为fasle

1
2
3
1explain select a1 from t where a1='a' and a1 = 'b';

2

3

Using join buffer : MySQL引擎使用了连接缓存，表示sql语句太烂，性能低下

优化总结

优化一般不能一次就优化到最佳效果，需要在开发过程中根据使用情况多次逐步优化

复合索引保证最佳左前缀原则

1
2
11. 

2

小表驱动大表

1
2
11. 

2

索引建立在经常查询的字段上

1
2
11. 

2

复合索引，尽量使用全索引匹配（说明：假设使用了三个字段建立了一个复合索引，在sql查询中尽量让三个索引都用到）

1
2
11. 

2

不要在索引上进行任何操作（计算、函数、类型转换）,否则索引失效

1
2
11. 

2

复合索引不能使用不等于（ != , <> ）和 is null, is not null ,否则索引失效

1
2
11. 

2

like 尽量以 ‘常量’ 开头，不要使用 ‘%x%’ ，否则索引失效

1
2
11. 

2

尽量不要使用or ，否则索引失效

1
2
11. 

2

如果必须使用到索引失效的情况，尽量使用索引覆盖（using index）,可能会使索引生效，达到性能优化

1
2
11. 

2

将含有in的范围查询放到where条件的最后面，防止索引失效（尽量不使用in）

1
2
11. 

2

连接查询 a.t = b.t 的情况下，将表数据量小的放在左边，表数据量大的放在右边会提高性能

1
2
11. 

2

连接查询 a.t = b.t 的情况下，将 a 表 t 字段加索引会提高性能

1
2
11. 

2

对于左外连接给左表加索引，右外连接给右表加索引

1
2
11. 

2

exist 和 in，如果主查询的数据集大，则使用in，如果子查询的数据集大,使用exist

1
2
11. 

2

提高 order by 查询的策略

​ a、选择使用单路、双路；调整buffer的容量大小

​ b、避免使用 select * …

​ c、保证排序字段的 排序一致性

常用sql语句

查看数据库

1
2
3
1mysql>  show databases;

2

3

选中数据库

1
2
3
1mysql>  use test; 

2

3

查看表

1
2
3
1mysql>  show tables; 

2

3

查看表结构信息

1
2
3
1mysql>  desc tb;

2

3

删除表

1
2
3
1mysql>  drop table tb;

2

3

5.慢查询

检查是否开启了慢查询

1
2
3
1mysql> show variables like '%slow_query_log%';

2

3

开启慢查询

1
2
3
4
1//临时开启 - mysql重启后失效

2mysql> set global slow_query_log = 1;

3

4

1
2
3
4
5
6
7
8
1//永久开启 -  修改my.cnf 文件

2vim /etc/my.cnf

3

4[mysqld]

5slow_query_log = 1

6slow_query_log_file = /var/lib/mysql/localhost-slow.log

7

8

慢查询阈值

1
2
3
4
5
6
7
1//查看

2mysql> show variables like '%long_query_time%';

3

4//临时设置 (单位秒) - 重新登陆起效

5mysql> set global long_query_time = 2;

6

7

1
2
3
4
5
6
7
1//永久设置 - 修改my.cnf 文件

2vim /etc/my.cnf

3

4[mysqld]

5long_query_time = 2

6

7

查看超过慢查询阈值的sql次数

1
2
3
1mysql> show global status like '%slow_queries%';

2

3

查看超过慢查询阈值具体的sql信息

1. 查看slow_query_log_file日志文件
2. 使用mysqldumpslow工具

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1mysqldumpslow --help

2-s : 排序方式  (r 逆序)

3-l ：锁定时间

4-t ：查询多少条

5-g : 正则表达式

6

7//获取返回记录最多的3个SQL

8mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

9

10//获取访问次数最多的3个SQL

11mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log

12

13//按照时间排序，查询前10条包含left join查询语句的sql

14mysqldumpslow -s t -t 10 -g 'left join' /var/lib/mysql/localhost-slow.log

15

16

6.创建，分析海量数据

创建海量数据

测试用表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
1create database testdata;

2use testdata;

3

4#部门表

5CREATE TABLE `dept` (

6   `dno` int(5) NOT NULL AUTO_INCREMENT,

7   `dname` varchar(20) NOT NULL DEFAULT '',

8   `loc` varchar(30) DEFAULT '',

9   PRIMARY KEY (`dno`)

10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

11

12#员工表

13 CREATE TABLE `emp` (

14   `eid` int(5) NOT NULL AUTO_INCREMENT,

15   `ename` varchar(20) NOT NULL DEFAULT '',

16   `job` varchar(20) NOT NULL DEFAULT '',

17   `deptno` int(5) NOT NULL DEFAULT '0',

18   PRIMARY KEY (`eid`)

19 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

20

21

创建存储函数 ，用于插入海量数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
1#修改MySQL 结束分割符

2delimiter $

3

4#产生一个随机字符串

5create function randstring(n int) returns varchar(255)

6begin

7declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTVUWXYZ';

8declare return_str varchar(255) default '';

9declare i int default 0;

10    while i<n

11    do

12    set return_str = concat(return_str,substring(all_str,floor(1+rand()*52),1));

13    set i=i+1;

14    end while;

15return return_str;

16end $

17

18#尝试一个随机整数

19create function ran_num() returns int(5)

20begin

21  declare i int default 0;

22  set i = floor(rand()*30 + 1);

23  return i;

24end $

25

26#向emp表插入数据-存储过程

27#autocommit = 0 关闭自动提交

28create procedure insert_emp(in data_times int(10))

29begin

30    declare i int default 0;

31    set autocommit = 0;  

32    repeat

33        insert into emp (ename,job,deptno) values(randstring(5),randstring(10),ran_num());

34        set i= i+1;

35        until i = data_times

36    end repeat;

37    commit;

38end $

39

40#向dept表插入数据 - 存储过程

41create procedure insert_dept(in data_times int(10))

42begin

43    declare i int default 0;

44    set autocommit = 0;

45    repeat

46        insert into dept (dname,loc) values(randstring(5),randstring(20));

47        set i = i+1;

48        until i = data_times

49    end repeat;

50    commit;

51end $

52

53

使用存储过程/函数，插入数据

1
2
3
4
5
6
7
1#创建30个部门

2call insert_dept(30);

3

4#创建80万个员工

5call insert_emp(800000);

6

7

想要慢查询和存储过程/函数 同时启用，需要开启 log_bin_trust_function_creators

1
2
3
4
5
6
7
1#查询 log_bin_trust_function_creators 状态

2mysql> show variables like '%log_bin_trust_function_creators%';

3

4#设置 log_bin_trust_function_creators

5mysql> set global log_bin_trust_function_creators = 1;

6

7

分析海量数据

profiling

1
2
3
4
5
6
7
8
9
10
11
12
13
14
1#查询 profiling 状态

2mysql> show variables like '%profiling%';

3

4#开启profiling

5mysql> set profiling = on;

6

7#查看被记录的sql

8#profiling 会记录开启之后的 所有执行的sql,

9#精确分析： show profile all for query 查询记录id;

10mysql> show profiles;

11mysql> show profile all for query 3;

12mysql> show profile cpu,block io for query 3;

13

14

全局日志

全局日志表 mysql.general_log

开启全局日志后所有的sql执行都会被记录到 mysql 库中的 general_log 表中

1
2
3
4
5
6
7
8
1#查询全局日志 状态

2mysql> show variables like '%general_log%';

3

4#开启全局日志

5mysql> set global general_log = 1;

6mysql> set global log_output = 'table'; //将sql记录在表中，设置后将不会写入日志文件而是写入表中

7

8

7.主从复制

原理

1. master将改变的数据 记录在本地的二进制文件中（binary log）,该过程称为: 二进制日志事件
   1. slave将master的binary log拷贝到自己的 realay log 中
   2. slave从 realay log 中将数据读取到自己的数据库中

主数据库配置

修改配置文件 my.cnf

1
2
3
4
5
6
7
8
9
10
1vi /etc/my.cnf

2

3[mysqld]

4server-id = 1    #数据库id

5log-bin  =   /var/lib/mysql/mysql-bin   #二进制日志文件地址

6log-error  =   /var/lib/mysql/mysql-error  #错误日志文件地址

7binlog-ignore-db = mysql    #忽略同步的数据库

8binlog-do-db = test         #指定需要同步的数据库 - 可选

9

10

授权从计算机

1
2
3
4
1mysql> GRANT REPLICATION slave,reload,super ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY 'root';

2mysql> flush privileges;

3

4

查看主数据库状态 – 并记录 File，postion 对应的值

1
2
3
1mysql> show master status;

2

3

从数据库配置

修改配置文件 my.cnf

1
2
3
4
5
6
7
8
1vi /etc/my.cnf

2

3[mysqld]

4server-id = 2   #数据库id

5relay-log = slave-relay-bin    #二进制日志文件

6replicate-do-db = test   #指定需要同步的数据库        

7

8

授权主计算机

1
2
3
4
5
6
7
8
9
10
1mysql>

2   CHANGE MASTER TO

3   MASTER_HOST = '192.168.0.77',

4   MASTER_USER = 'root',

5   MASTER_PASSWORD = 'root',

6   MASTER_PORT = 3306,

7   master_log_file = 'mysql-bin.000011',

8   master_log_pos = 345;

9

10

开启主从同步

1
2
3
1mysql> start slave;

2

3

检查主从同步状态

确保 Slave_IO_Runing , Slave_SQL_Runing 都为 yes

这里出现的Slave_IO_Running 为no 是因为配置文件my.cnf中server-id 没起到作用。具体什么原因导致暂时还不清楚。如果有了解的博友望告知！临时的解决方式 – 通过命令行的形式设置server-id

1
2
3
1mysql> show slave status \G

2

3

常用命令

查看server-id

1
2
3
1mysql> show variables like 'server_id';

2

3

手动设置server-id

1
2
3
1mysql> set global server_id = 2;

2

3

停止主从同步

1
2
3
1mysql> stop slave;

2

3