Hexo
0%

mysql

Posted on Edited on

数据库基本操作

1
数据库的增删改查

库操作

创建库 

1
2
mysql > create database db1;

查看库

1
2
mysql > show databases; 
mysql > show create database db1;  //更详细的查看库

使用库

1
mysql > use db1;

表操作

创建表

1
mysql > create tables 表名(字段 类型(修饰符))

查看表

1
2
3
4
查看所有表：mysql > show tables ;
查看表记录：mysql > show table status like '表名' ；
查看表结构：mysql > desc '表名' ；
查看表记录：mysql > select * from  '表名' ;

修改表

1
修改表名：mysql > rename table  '源表名'  to  '新表名' ；

字段记录操作

字段

1
2
3
添加字段：mysql > alter table '表名'  add '字段' '修饰符';
删除字段：mysql > alter table '表名'  drop '字段' ；
修改字段：mysql > alter table  '表名' change '旧字段' '新字段'  '修饰符';

记录

1
2
3
4
添加记录：insert into '表名(记录，记录)'  values (),(),();
        ：insert into '表名'  values  (),(),();
修改记录：update '表名' set 字段=''  where 字段='';
删除记录：delete from '表名' where  '字段'='' ;

查询操作

查询

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
简单查询：select * from '表名';
避免重复：select distinct '字段' from '表名';
条件查询：select 字段,字段 from 表名 where id<=5(条件);
四则运算查询：select id,dep_id,id*dep_id from company.employee5  where id<=5;
定义显示格式一：select id*dep_id as "id and dep_id's sum" from company.employee5  where id<=5;
定义显示格式：SELECT CONCAT(name, ' annual salary: ', salary*14)  AS Annual_salary FROM employee5;   //定义格式+四个运算，CONCAT是关键字
多条件：select '字段，字段‘ from '表名' WHERRE '条件一' AND '条件二';
关键字between and：
select  '字段，字段' from '表名'  where 字段  BETWEEN
'条件一' AND '条件二';


----------
排序查询：
select '字段' from  '表名' ORDER BY  '排序字段'；//字段后加DESC正序，ASC反序

限制查询的记录数：
select '字段' from '表名' ORDER BY  '字段，DESC|ACS' LIMIT  '数字'; //数字有两种的是(5,从初始位置到第五个)(2,5,第三个开始，共显示五个)

使用集合的查询：
select COUNT(*)  from '表名'；
select COUNT(*) FROM '表名' WHERE dep_id=101;
select MAX(salary) FROM '表名';
select MIN(salary) FROM '表名';
select AVG(salary) FROM '表名';
select SUM(salary) FROM '表名';
select SUM(salary) FROM '表名' WHERE dep_id=101;

分组查询：
select '字段' from  '表名' group by 字段;   //可参考下列面试题

模糊查询：
select '字段' from  '表名'  LIKE  '关键字'；

正则表达式查询：
select * from '表名' where '字段'  REGEXP '关键字'；

删库

1
2
删库：drop database '库名'；
删表：drop table   '表名'

高级操作

连接数据库

1
# mysql -uroot  -p  -h10.18.44.209  -p3306

修改数据库密码

1
2
3
4
5
6
7
8
9
10
11
# vim  /etc/my.cnf 追加
validate_password=off
# systemctl restart mysqld
方法一：
mysql > SET PASSWORD FOR  user3@'localhost'='new_password';
mysql > flush privileges;
方法二：
mysql > update mysql.user set password=password('newpassword') where user='root';
mysql > flush privileges;
方式三：
mysql > set password for 'root'@'localhost'=password('newpassord');

编译安装需要参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

[root@mysql-5.7.17 ~]# cmake . \
-DWITH_BOOST=boost_1_59_0/ \                    指定boost目录
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \       指定安装目录
-DSYSCONFDIR=/etc \                             配置文件位置  
-DMYSQL_DATADIR=/usr/local/mysql/data \         数据目录（非常重要 错误日志、数据存放位置）
-DINSTALL_MANDIR=/usr/share/man \               man手册
-DMYSQL_TCP_PORT=3306 \                         默认端口3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \             sock文件位置，用来做网络通信的  
-DDEFAULT_CHARSET=utf8 \                        字符集的支持  默认
-DEXTRA_CHARSETS=all \                          字符集的支持  扩展支持
-DDEFAULT_COLLATION=utf8_general_ci \           字符集的支持   设定默认排序规则
-DWITH_READLINE=1 \                             可以上下泛历史命令  
-DWITH_SSL=system \                             使用证书登陆   （安全 但是影响传输速度）      
-DWITH_EMBEDDED_SERVER=1 \                      编译嵌入式服务器支持  
-DENABLED_LOCAL_INFILE=1 \                      支持从本机导入
-DWITH_INNOBASE_STORAGE_ENGINE=1                默认引擎   数据如何存储的库

msyql中now()、sysdate()、curdate()区别

1
2
3
4
5
6
7
SELECT date_sub(date_sub(date_format(now(),'%y-%m-%d '),interval extract( day from now())-1 day),interval 1 month)
上个月第一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(  day from now()) day),interval 0 month)
上个月最后一天

select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(  day from now())-1 day),interval 0 month)
这个月第一天

Mysql日志管理

1
2
3
4
5
error log    错误日志        排错    /var/log/mysqld.log【默认开启】
bin log      二进制日志      备份    增量备份 DDL DML DCL
Relay log    中继日志        复制    接收 replication master 
slow log     慢查询日志       调优    查询时间超过指定值

1
2
Error Log
log-error=/var/log/mysqld.log
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
Binary Log(用于备份恢复数据)
产生binlog日志：
log-bin=/var/log/mysql-bin/slave2
serve-id=2

# mkdir /var/log/mysql-bin/slave2
#chmod mysql.mysql /var/log/mysql-bin/slave
#systemctl restart mysqld


1. 重启mysqld 会截断旧日志产生新的日志
2. 刷新日志会截断旧日志产生新的日志
mysql> flush logs 
3. 删除所有binlog（禁用）
mysql> reset master
4. 删除部分日志
mysql> PURGE BINARY LOGS TO 'mysql-bin.010';
mysql> PURGE BINARY LOGS BEFORE '2016-04-02 22:46:26';
5. 暂停binlog日志功能（仅对当前会话生效）
mysql> SET SQL_LOG_BIN=0;
mysql> SET SQL_LOG_BIN=1;

读取binlog日志：
# mysqlbinlog mysql.000002

按datetime读取：
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2018-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" --stop-datetime="2018-12-05 11:02:54" 
 
按position读取：
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

查看带加密的binlong日志
mysqlbinlog --base64-output=decode-rows  -v  日志文件

根据binlog恢复数据：
根据时间点恢复数据
# mysqlbinlog --start-datetime='2014-11-25 11:56:54'  --stop-datetime='2014-11-25 11:57:41'  tiger-bin.000001 | mysql -u root -p1

根据位置点恢复数据
# mysqlbinlog --start-position 106 --stop-position  527 tiger-bin.000001 | mysql -u root -p1  

刷新bin-log日志:
#mysqladmin  flush-logs

去除binlog加密：
transaction_isolation=repeatable-read
binlog_format=mixed

1
2
3
4
5
6
7
8
9
10
11
12
13
慢查询：
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3

# mkdir /var/log/mysql-slow/
# chown mysql.mysql /var/log/mysql-slow/
# systemctl restart mysqld

查看慢查询日志
测试:BENCHMARK(count,expr)
SELECT BENCHMARK(50000000,2*3);

mysql中TIMESTAMPDIFF和TIMESTAMPADD函数的用法

今天在处理工单的时候,其中的一个需求是某商品的发货时效(即下单时间和发货时间的时间差),接触到了TIMESTAMPDIFF函数

TIMESTAMPDIFF

1
2
3
4
5
6
7
8
9
10
11
12
13
14
TIMERSTAMPDIFF语法:
TIMERSTAMPDIFF(interval,datetime_expr1,datetime_expr2)

说明:
该函数是返回datetime_expr1和datetime_expr2之间的整数差,其中单位有interval参数决定,interval的常用参数有:
FRAC_SECOND   时间间隔是毫秒
SECOND        时间间隔是秒
MINUTE        时间间隔是分钟
HOUR          时间间隔是小时
DAY           时间间隔是天
WEEK          时间间隔是周
MOUTH         时间间隔是月
QUARTER       时间间隔是季度
YEAR          时间间隔是年

示例:

1
2
3
4
5
6
7
8
MariaDB [(none)]> select timestampdiff(hour,'2018-06-05 09:00:00','2018-06-15 09:00:00');
+-----------------------------------------------------------------+
| timestampdiff(hour,'2018-06-05 09:00:00','2018-06-15 09:00:00') |
+-----------------------------------------------------------------+
|                                                             240 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

示例二:

1
2
3
4
5
6
7
8
MariaDB [(none)]> select timestampdiff(day,'2018-06-05 09:00:00','2018-06-15 09:00:00');
+----------------------------------------------------------------+
| timestampdiff(day,'2018-06-05 09:00:00','2018-06-15 09:00:00') |
+----------------------------------------------------------------+
|                                                             10 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

TIMESTAMPADD

1
2
3
4
5
TIMESTAMPADD语法:
TIMESTAMPADD(interval,int_expr,datetime_expr)

说明:
将整型表达式int_expr 添加到日期或日期时间表达式 datetime_expr中。式中的interval和TIMESTAMPDIFF中列举的取值是一样的。

示例:

1
2
3
4
5
6
7
8
MariaDB [(none)]> select timestampadd(second,86400,'2018-06-15 09:00:00');
+--------------------------------------------------+
| timestampadd(second,86400,'2018-06-15 09:00:00') |
+--------------------------------------------------+
| 2018-06-16 09:00:00                              |
+--------------------------------------------------+
1 row in set (0.00 sec)

msyql中CASE WHEN语法

1
MySQL中case when语句，用于计算条件列表并返回多个可能表达式之一。

CASE具有两种格式：简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果。CASE搜索函数计算一组布尔表达式以确定结果。两种都支持可选的ELSE函数。

1）简单CASE函数
语法如下：

1
2
3
4
5
6
CASE input_expression  
WHEN when_expression THEN  
    result_expression [...n ] [  
ELSE  
    else_result_expression  
END

参数介绍

1
2
3
4
5
6
7
8
9
10
11
input_expression是使用简单 CASE 格式时所计算的表达式。Input_expression 是任何有效的 Microsoft SQL Server 表达式。
WHEN when_expression使用简单 CASE 格式时 input_expression 所比较的简单表达式。When_expression 是任意有效的 SQL Server 表达式。Input_expression 和每个 when_expression 的数据类型必须相同，或者是隐性转换。
占位符，表明可以使用多个 WHEN when_expression THEN result_expression 子句或 WHEN Boolean_expression THEN result_expression 子句。
THEN result_expression 当 input_expression = when_expression 取值为 TRUE，或者 Boolean_expression 取值 TRUE 时返回的表达式。
result expression 是任意有效的 SQL Server 表达式。
ELSE else_result_expression当比较运算取值不为 TRUE 时返回的表达式。如果省略此参数并且比较运算取值不为 TRUE，CASE 将返回 NULL 值。else_result_expression 是任意有效的 SQL Server 表达式。else_result_expression 和所有 result_expression 的数据类型必须相同，或者必须是隐性转换。

简单 CASE 函数：返回结果值介绍：

计算 input_expression，然后按指定顺序对每个 WHEN 子句的 input_expression = when_expression 进行计算。
返回第一个取值为 TRUE 的 (input_expression = when_expression) 的 result_expression。如果没有取值为 TRUE 的 input_expression = when_expression，则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression；若没有指定 ELSE 子句，则返回 NULL 值。

2)CASE搜索函数
语法如下：

1
2
3
4
5
6
7
CASE  
WHEN Boolean_expression THEN  
    result_expression [...n ] [  
ELSE  
    else_result_expression  
END

参数介绍：

1
2
3
4
5
WHEN Boolean_expression 使用 CASE 搜索格式时所计算的布尔表达式。Boolean_expression 是任意有效的布尔表达式。结果类型从 result_expressions 和可选 else_result_expression 的类型集合中返回最高的优先规则类型。有关更多信息，请参见数据类型的优先顺序。

CASE 搜索函数：返回结果值介绍：
按指定顺序为每个 WHEN 子句的 Boolean_expression 求值。返回第一个取值为 TRUE 的 Boolean_expression 的 result_expression。
如果没有取值为 TRUE 的 Boolean_expression，则当指定 ELSE 子句时 SQL Server 将返回 else_result_expression；若没有指定 ELSE 子句，则返回 NULL 值。

3)CASE WHEN例子介绍
1、仅带简单case的select语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select 
    CASE good_type
WHEN '0' THEN
    '食品类'
WHEN '1' THEN
    '饮料类'
WHEN '2' THEN
    '日用品'
WHEN '3' THEN
    '鲜果类'
END AS good_type_now,
good_type,user_id,user_name
FROM 
    express.t_main_order
1
2
3
4
5
6
7
8
9
10
11
12
+---------------+-----------+---------+-----------+
| good_type_now | good_type | user_id | user_name |
+---------------+-----------+---------+-----------+
| 食品类        |         0 | 1       | tina      |
| 食品类        |         0 | 2       | tige      |
| 饮料类        |         1 | 3       | five      |
| 日用品        |         2 | 4       | wate      |
| 鲜果类        |         3 | 5       | fiww      |
| 鲜果类        |         3 | 6       | www       |
| 鲜果类        |         3 | 7       | wfiw      |
+---------------+-----------+---------+-----------+

2、使用带有简单CASE函数和CASE搜索函数的select语句
在select语句中，CASE搜索函数允许根据比较值

1
2
3
4
5
6
7
8
9
select 
    CASE 
WHEN good_type<2 
    THEN '<2' 
WHEN good_type>=2 AND good_type<3 
    THEN '>=2 && <3' 
    ELSE '>=3' END AS good_now_type,
good_type,user_id,user_name 
FROM t_main_order;
1
2
3
4
5
6
7
8
9
10
11
12
+---------------+-----------+---------+-----------+
| good_now_type | good_type | user_id | user_name |
+---------------+-----------+---------+-----------+
| <2            |         0 | 1       | tina      |
| <2            |         0 | 2       | tige      |
| <2            |         1 | 3       | five      |
| >=2 && <3     |         2 | 4       | wate      |
| >=3           |         3 | 5       | fiww      |
| >=3           |         3 | 6       | www       |
| >=3           |         3 | 7       | wfiw      |
+---------------+-----------+---------+-----------+

3)CASE的其他用法

1
2
3
4
5
6
7
8
9
10
11
select 
    CASE 
WHEN good_type<2 
    THEN '<2' 
WHEN good_type>=2 AND good_type<3 
    THEN '>=2 && <3' ELSE '>=3' 
END AS good_now_type,
    count(*) AS num_count 
FROM t_main_order 
GROUP BY good_now_type 
ORDER BY num_count;
1
2
3
4
5
6
7
8
+---------------+-----------+
| good_now_type | num_count |
+---------------+-----------+
| >=2 && <3     |         1 |
| <2            |         3 |
| >=3           |         3 |
+---------------+-----------+

left join、right join、inner join的区别

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

举例如下：

表A记录如下：
aID　　　　　aNum
1　　　　　a20050111
2　　　　　a20050112
3　　　　　a20050113
4　　　　　a20050114
5　　　　　a20050115

表B记录如下:
bID　　　　　bName
1　　　　　2006032401
2　　　　　2006032402
3　　　　　2006032403
4　　　　　2006032404
8　　　　　2006032408

1.left join

sql语句如下:
select * from A
left join B
on A.aID = B.bID

结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404
5　　　　　a20050115　　　　NULL　　　　　NULL

（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.

2.right join

sql语句如下:
select * from A
right join B
on A.aID = B.bID

结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404
NULL　　　　　NULL　　　　　8　　　　　2006032408

（所影响的行数为 5 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join

sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID

结果如下:
aID　　　　　aNum　　　　　bID　　　　　bName
1　　　　　a20050111　　　　1　　　　　2006032401
2　　　　　a20050112　　　　2　　　　　2006032402
3　　　　　a20050113　　　　3　　　　　2006032403
4　　　　　a20050114　　　　4　　　　　2006032404

结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

注:
LEFT JOIN操作用于在任何的 FROM 子句中，组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即使在第二个（右边）表中并没有相符值的记录。

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2

说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。
compopr参数指定关系比较运算符：”=”， “<”， “>”， “<=”， “>=” 或 “<>”。
如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误.

其他常用函数

1
2
3
4
5
6
7
8
9
10
11
DATE_FORMAT
GROUP BY
ORDER BY
LIMIT
AS
DATE
UNION
UNION ALL
AND
IN

数据库备份

基本概念

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
备份过程中必须考虑因素：
1. 数据的一致性
2. 服务的可用性

======================================

逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。
mysqldump
binlog
mydumper
phpmyadmin

物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
tar,cp
mysqlhotcopy  只能用于备份MyISAM。
xtrabackup
inbackup
lvm snapshot

======================================

完全备份
增量备份
差异备份
======================================
1
2


面试题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
将测试数据表导入数据库，其中name字段代表“姓名”，score字段代表“分数”。

create table `t1` (
    `id` double ,
    `name` varchar (300),
    `score` double
);

insert into `t1` (`id`, `name`, `score`) values('1','n1','59');
insert into `t1` (`id`, `name`, `score`) values('2','n2','66');
insert into `t1` (`id`, `name`, `score`) values('3','n3','78');
insert into `t1` (`id`, `name`, `score`) values('4','n1','48');
insert into `t1` (`id`, `name`, `score`) values('5','n3','85');
insert into `t1` (`id`, `name`, `score`) values('6','n5','51');
insert into `t1` (`id`, `name`, `score`) values('7','n4','98');
insert into `t1` (`id`, `name`, `score`) values('8','n5','53');
insert into `t1` (`id`, `name`, `score`) values('9','n2','67');
insert into `t1` (`id`, `name`, `score`) values('10','n4','88');
1
2
3
1. 单分数最高的人和单分数最低的人。
mysql> select name,score from t1 group by score desc limit 1;
mysql> select name,score from t1 group by score ASC limit 1;
1
2
3
4
5
6
7
8
9
10
2. 两门分数加起来的第2至5名。
mysql> select name,sum(score) from t1 group by name order by sum(score) desc limit 1,4;
+------+------------+
| name | sum(score) |
+------+------------+
| n3   |        163 |
| n2   |        133 |
| n1   |        107 |
| n5   |        104 |
+------+------------+
1
2
3. 两门总分数在150分以下的人。
select name,sum(score) from t1 group by name having sum(score) < 150;
1
2
3
4. 两门平均分数介于60和80的人。
select name,avg(score) from t1 group by name having avg(score) between 60 and 80;
select name,avg(score) from t1 group by name having avg(score) >60 and avg(score) <80;
1
2
3
4
5
6
7
8
5. 总分大于150分，平均分小于90分的人数。
mysql> select name,sum(score),avg(score) from t1 group by name having sum(score)>150 and avg(score)<90;
+------+------------+------------+
| name | sum(score) | avg(score) |
+------+------------+------------+
| n3   |        163 |       81.5 |
+------+------------+------------+
1 row in set (0.00 sec)
1
2
6. 总分大于150分，平均分小于90分的人数有几个。
select count(distinct name) from t1 group by name having sum(score) > 150 and avg(score) < 90;

主从切换

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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
正常切换

1.检查slave同步状态

在开始切换之前先对主库进行锁表：

flush tables with read lock

（在执行完成后生产环境必须等待所有语句执行完成）

在flush tables with read lock成功获得锁之前，必须等待所有语句执行完成（包括SELECT）。所以如果有个慢查询在执行，或者一个打开的事务，或者其他进程拿着表锁，flush tables with read lock就会被阻塞，直到所有的锁被释放。请看下面的例子：


mysql> show processlist;
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
| Id | User | Host      | db   | Command    | Time | State             | Info                                                                 |
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
|  4 | root | localhost | test | Query      |   80 | Sending data      | select count(*) from t t1 join t t2 join t t3 join t t4 where t1.b=0 |
|  5 | root | localhost | test | Query      |   62 | Flushing tables   | flush tables with read lock                                          |
|  6 | root | localhost | test | Field List |   35 | Waiting for table |                                                                      |
|  7 | root | localhost | test | Query      |    0 | NULL              | show processlist                                                     |
+----+------+-----------+------+------------+------+-------------------+----------------------------------------------------------------------+
4 rows in set (0.00 sec)

复制代码

flush data

切换完成后可以释放锁

unlock tables

1）在master执行：show processlist;

显示Master has sent all binlog to slave; waiting for binlog to be updated

 

2）在slave执行：show processlist；

显示Slave has read all relay log; waiting for the slave I/O thread to update it

mysql> show slave status \G;

 

检查IO及SQL线程是否正常，如果为NO表明同步不一致，需要重新将slave同步保持主从数据一致。

3）停止slave io线程

在slave执行：mysql> STOP SLAVE IO_THREAD

mysql> SHOW PROCESSLIST;

确保状态为：has read all relay log

 

以上都执行完成后可以把slave提升为master：

4）提升slave为master

Stop slave；

Reset master;

Reset slave all; 在5.6.3版本之后

Reset slave; 在5.6.3版本之前

查看slave是否只读模式：show variables like 'read_only';

只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。

或者不重启使用命令关闭只读，但下次重启后失效：set global read_only=off;

mysql> show master status \G;

备注：reset slave all 命令会删除从库的 replication 参数，之后 show slave status\G 的信息返回为空。

5）将原来master变为slave

在新的master上创建同步用户：

grant replication slave on *.* repl@'IP of slave' identified by 'replpwd';

在新的slave上重置binlog：

Reset master;

change master to master_host='192.168.0.104', //Master 服务器Ip

master_port=3306,

master_user='repl',

master_password=’replpwd’,

master_log_file='master-bin.000001',//Master服务器产生的日志

master_log_pos=?;//master binlog pos

以上最后两步可以在master执行：show master status

 

启动slave：start slave； 并查看slave状态：show slave status\G;

           
           异常切换

主机故障或者宕机：

1)   在salve执行：

stop slave；

reset master；

查看是否只读模式：show variables like 'read_only';

只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。

或者不重启使用命令关闭只读，但下次重启后失效：set global read_only=off;

查看show slave status \G;

查看show master status \G;

将从库IP地址改为主库IP地址，测试应用连接是否正常。