mysql主从同步

在 2015年4月6日2015年4月11日上张贴由 BestLove

设备：两台虚拟机 master 192.168.2.103 slave 192.168.2.104

环境：centos6.3 lnmp集成环境 mysql版本5.5.37

在学习mysql主从同步之前，我们首先得了解mysql授权、mysql的bin-log日志、mysqldump

//授权
//通过以下sql可以查询当前mysql的所有用户权限
select user,host,password from mysql.user
//在master上为slave开一个账号
//grant表示授权 replication slave表示主从复制权限 on表示关于 *.*表示所有数据库的所有表(数据库.表)
//to表示给 user2表示用户名 @192.168.2.104表示允许这个地址的主机登陆
//identified by '123'表示设置密码为123
grant replication slave on *.* to user2@192.168.2.104 identified by '123';
//在slave上登录master，如果登录成功，表示授权成功
mysql -u user2 -h 192.168.2.103 -p 123
//如果不了解如何授权，可使用 ? grant; 查看帮助

//mysql bin-log日志
//开启bin-log日志
vim /etc/my.cnf 设置 log-bin=mysql-bin
//可通过show variables like '%bin%';查看开启情况
//二进制日志 保存 增删改的日志，通常存放在/usr/local/mysql/var目录下,实际位置查看my.cnf
//刷新bin-log日志 此时就会多一个最新的bin-log日志
flush logs;
//查看最后一个bin日志
show master status;
//清空所有的bin-log日志
reset master;
//查看bin-log日志 bin-log日志是二进制的 需要通过mysqlbinlog来查看
//utf8字符集查看的时候好像有个bug需要加上 --no-defaults
//mysql-bin.00001 mysql-bin.index 是mysql原始保留的二进制日志，不要随意删除
/usr/local/bin/mysqlbinlog --no-defaults mysql-bin.000001
//恢复指定mysql-bin.000002到指定的数据库 --start-position --stop-position可以根据pos点指定恢复区间
//也可以使用时间点来恢复 --start-date='xxxxx' --stop-date="xxxx" 例如：2015-05-01 12:12:12
mysqlbinlog --no-defaults --start-position="107" --stop-position="280" mysql-bin.000002 | mysql -u用户名 -p密码 数据库名

//mysqldump备份 -l表示读锁 -F表示重新生成bin-log日志
mysqldump -uroot -p密码 数据库 -l -F > /dir/xxx.sql
//恢复备份数据 -f表示遇到错误继续执行
mysql -uroot -p密码 数据库 -f < /dir/xxx.sql

//授权

//通过以下sql可以查询当前mysql的所有用户权限

select user,host,password from mysql.user

//在master上为slave开一个账号

//grant表示授权 replication slave表示主从复制权限 on表示关于 *.*表示所有数据库的所有表(数据库.表)

//to表示给 user2表示用户名 @192.168.2.104表示允许这个地址的主机登陆

//identified by '123'表示设置密码为123

grant replication slave on *.* to user2@192.168.2.104 identified by '123';

//在slave上登录master，如果登录成功，表示授权成功

mysql -u user2 -h 192.168.2.103 -p 123

//如果不了解如何授权，可使用 ? grant; 查看帮助

//mysql bin-log日志

//开启bin-log日志

vim /etc/my.cnf 设置 log-bin=mysql-bin

//可通过show variables like '%bin%';查看开启情况

//二进制日志保存增删改的日志，通常存放在/usr/local/mysql/var目录下,实际位置查看my.cnf

//刷新bin-log日志此时就会多一个最新的bin-log日志

flush logs;

//查看最后一个bin日志

show master status;

//清空所有的bin-log日志

reset master;

//查看bin-log日志 bin-log日志是二进制的需要通过mysqlbinlog来查看

//utf8字符集查看的时候好像有个bug需要加上 --no-defaults

//mysql-bin.00001 mysql-bin.index 是mysql原始保留的二进制日志，不要随意删除

/usr/local/bin/mysqlbinlog --no-defaults mysql-bin.000001

//恢复指定mysql-bin.000002到指定的数据库 --start-position --stop-position可以根据pos点指定恢复区间

//也可以使用时间点来恢复 --start-date='xxxxx' --stop-date="xxxx" 例如：2015-05-01 12:12:12

mysqlbinlog --no-defaults --start-position="107" --stop-position="280" mysql-bin.000002 | mysql -u用户名 -p密码数据库名

//mysqldump备份 -l表示读锁 -F表示重新生成bin-log日志

mysqldump -uroot -p密码数据库 -l -F > /dir/xxx.sql

//恢复备份数据 -f表示遇到错误继续执行

mysql -uroot -p密码数据库 -f < /dir/xxx.sql

了解以上知识后，就可以配置mysql主从了，步骤如下：

注：为了保证测试的顺利，可以在配置前先清理一下bin-log日志 reset master

master服务器配置

1.授权一个账号给从服务器使用
2./etc/my.cnf中开启bin-log日志并设置一个唯一的server-id（需要跟从服务器不同）
3.使用mysqldump备份数据建议加上 -l读锁 -F重新生成bin-log日志
4.把master的数据备份传输到slave上 scp /tmp/test.sql 192.168.2.104:/tmp/
5.重启mysql

slave服务器配置

1.恢复传输过来的备份数据
2./etc/my.cnf 开启bin-log 设置唯一的server-id 开启master-host=192.168.2.103 master-user = user2 master-
password=123 master-port=3306
3.重启mysql

配置完成，通过show slave status \G查看 Slave_Io_Running Slave_Sql_Running 都为yes表示主从同步配置完成

//从数据库常用命令
//启动复制线程 通常默认已启动
start slave
//停止复制线程
stop slave
//查看从数据库状态
show slave status
//查看主数据库bin-log日志
show master logs
//动态改变到主服务器的配置
change master to
//查看从数据库的运行进程
show processlist

//从数据库常用命令

//启动复制线程通常默认已启动

start slave

//停止复制线程

stop slave

//查看从数据库状态

show slave status

//查看主数据库bin-log日志

show master logs

//动态改变到主服务器的配置

change master to

//查看从数据库的运行进程

show processlist

从服务器无法同步时，使用以下方法进行修复

//方法一：
slave stop;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
slave start;

//方法二：
slave stop;
show master status;
change master to
master_host="192.168.2.103",master_user="user2",master_password="123",master_port=3306,master_log_file="mysql-
bin.000003",master_log_pos=107;
slave start;

//方法一：

slave stop;

set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

slave start;

//方法二：

slave stop;

show master status;

change master to

master_host="192.168.2.103",master_user="user2",master_password="123",master_port=3306,master_log_file="mysql-

bin.000003",master_log_pos=107;

slave start;

mysql存储过程

在 2015年3月30日2015年3月30日上张贴由 BestLove

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([IN|OUT|INOUT] 参数名数据类型,…,…)[特性 …] 过程体

注意事项：
MySQL默认以”;”为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用”,”分割开。

过程体的开始与结束使用BEGIN与END进行标识。

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

调用存储过程用必须使用”@”符号用户变量一般以@开头

set @param='xx'; //如果需要设置参数
--select 'xx' into @param; //或者使用select设置 "--"表示mysql的注释 "/*..*/"为多行注释
--select concat(@param,'xx'); //select可使用concat函数追加
call 存储过程名(@param); //有参数则传入，没有就不传
select @param; //打印输出参数

set @param='xx'; //如果需要设置参数

--select 'xx' into @param; //或者使用select设置 "--"表示mysql的注释 "/*..*/"为多行注释

--select concat(@param,'xx'); //select可使用concat函数追加

call 存储过程名(@param); //有参数则传入，没有就不传

select @param; //打印输出参数

定义变量
declare 变量名[多个用”,”隔开] 变量类型 [default value]; 变量类型如：int float date varchar(20)等
例如：
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT ‘1999-12-31’;
DECLARE l_datetime datetime DEFAULT ‘1999-12-31 23:59:59’;
DECLARE l_varchar varchar(20) DEFAULT ‘hello world’;

变量赋值
set 变量名=表达式值如：set test=test+1;

查看某库有哪些存储过程
select name from mysql.proc where db=’数据库名’;

查看存储过程详情
show create procedure 数据库名.存储过程名

//输出test表的总记录数
delimiter//
create PROCEDURE proc_count(OUT test int)
BEGIN
	SELECT count(1) INTO test FROM test;
END;
//
delimiter;

CALL proc_count(@test);
SELECT @test;


//调用存储过程后输出13、3 然后打印输入参数输出13 可以看出IN参数在过程体中修改后只在过程体中有效，不会对原值产生影响
delimiter//
create PROCEDURE proc_lookParamsIn(IN test INT)
BEGIN
	SELECT test;
	SET test=3;
	SELECT test;
END;
//
delimiter;

SET @test=13;
CALL proc_lookParamsIn(@test);
SELECT @test;

//同理，以上参数类型改成OUT，则分别输出null、3、3，可以看出外部设置对OUT参数无效，但在过程体中修改会对原值产生影响
//同理，以上参数类型改成INOUT，则分别输出13、3、3，可以看出外部设置对INOUT参数有效，并且在过程体中修改也会对原值产生影响

//while 条件 do ... end while  |   if 条件 then  ... else ... end if   |   case 变量 when 值1 then ... when 值2 then ... end case
delimiter//
create PROCEDURE proc_demo()
BEGIN
declare i INT;
SET i=1;
WHILE i<50 DO
	IF i<25 THEN
		INSERT INTO test (name) VALUES(i+100);
	ELSE
		CASE i WHEN 47 THEN
			INSERT INTO test (name) VALUES(i+1000);
		WHEN 48 THEN
			INSERT INTO test (name) VALUES(i+10000);
		ELSE
			INSERT INTO test (name) VALUES(i+5000);
		END CASE;
	END IF;
SET i=i+1;
END WHILE;
END;
//
delimiter;

call proc_demo();

//repeat...end repeat |  loop...end loop  |  lables标号  |  iterate迭代等不详述

//输出test表的总记录数

delimiter//

create PROCEDURE proc_count(OUT test int)

BEGIN

SELECT count(1) INTO test FROM test;

END;

delimiter;

CALL proc_count(@test);

SELECT @test;

//调用存储过程后输出13、3 然后打印输入参数输出13 可以看出IN参数在过程体中修改后只在过程体中有效，不会对原值产生影响

delimiter//

create PROCEDURE proc_lookParamsIn(IN test INT)

BEGIN

SELECT test;

SET test=3;

SELECT test;

END;

delimiter;

SET @test=13;

CALL proc_lookParamsIn(@test);

SELECT @test;

//同理，以上参数类型改成OUT，则分别输出null、3、3，可以看出外部设置对OUT参数无效，但在过程体中修改会对原值产生影响

//同理，以上参数类型改成INOUT，则分别输出13、3、3，可以看出外部设置对INOUT参数有效，并且在过程体中修改也会对原值产生影响

//while 条件 do ... end while | if 条件 then ... else ... end if | case 变量 when 值1 then ... when 值2 then ... end case

delimiter//

create PROCEDURE proc_demo()

BEGIN

declare i INT;

SET i=1;

WHILE i<50 DO

IF i<25 THEN

INSERT INTO test (name) VALUES(i+100);

ELSE

CASE i WHEN 47 THEN

INSERT INTO test (name) VALUES(i+1000);

WHEN 48 THEN

INSERT INTO test (name) VALUES(i+10000);

ELSE

INSERT INTO test (name) VALUES(i+5000);

END CASE;

END IF;

SET i=i+1;

END WHILE;

END;

delimiter;

call proc_demo();

//repeat...end repeat | loop...end loop | lables标号 | iterate迭代等不详述

注：mysql中处理字符串时，默认第一个字符下标为1，php中为0

常用函数:

//字符串类：
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) //同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格

//数字类：
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
//注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143
//也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
SQRT(number2) //开平方

//日期时间类：
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0

//字符串类：

CHARSET(str) //返回字串字符集

CONCAT (string2 [,... ]) //连接字串

INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0

LCASE (string2 ) //转换成小写

LEFT (string2 ,length ) //从string2中的左边起取length个字符

LENGTH (string ) //string长度

LOAD_FILE (file_name ) //从文件读取内容

LOCATE (substring , string [,start_position ] ) //同INSTR,但可指定开始位置

LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length

LTRIM (string2 ) //去除前端空格

REPEAT (string2 ,count ) //重复count次

REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str

RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length

RTRIM (string2 ) //去除后端空格

STRCMP (string1 ,string2 ) //逐字符比较两字串大小,

SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符

UCASE (string2 ) //转换成大写

RIGHT(string2,length) //取string2最后length个字符

SPACE(count) //生成count个空格

//数字类：

ABS (number2 ) //绝对值

BIN (decimal_number ) //十进制转二进制

CEILING (number2 ) //向上取整

CONV(number2,from_base,to_base) //进制转换

FLOOR (number2 ) //向下取整

FORMAT (number,decimal_places ) //保留小数位数

HEX (DecimalNumber ) //转十六进制

//注：HEX()中可传入字符串，则返回其ASC-11码，如HEX('DEF')返回4142143

//也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19

LEAST (number , number2 [,..]) //求最小值

MOD (numerator ,denominator ) //求余

POWER (number ,power ) //求指数

RAND([seed]) //随机数

ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

SQRT(number2) //开平方

//日期时间类：

ADDTIME (date2 ,time_interval ) //将time_interval加到date2

CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区

CURRENT_DATE ( ) //当前日期

CURRENT_TIME ( ) //当前时间

CURRENT_TIMESTAMP ( ) //当前时间戳

DATE (datetime ) //返回datetime的日期部分

DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间

DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime

DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间

DATEDIFF (date1 ,date2 ) //两个日期差

DAY (date ) //返回日期的天

DAYNAME (date ) //英文星期

DAYOFWEEK (date ) //星期(1-7) ,1为星期天

DAYOFYEAR (date ) //一年中的第几天

EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分

MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串

MAKETIME (hour ,minute ,second ) //生成时间串

MONTHNAME (date ) //英文月份名

NOW ( ) //当前时间

SEC_TO_TIME (seconds ) //秒数转成时间

STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示

TIMEDIFF (datetime1 ,datetime2 ) //两个时间差

TIME_TO_SEC (time ) //时间转秒数]

WEEK (date_time [,start_of_week ]) //第几周

YEAR (datetime ) //年份

DAYOFMONTH(datetime) //月的第几天

HOUR(datetime) //小时

LAST_DAY(date) //date的月的最后日期

MICROSECOND(datetime) //微秒

MONTH(datetime) //月

MINUTE(datetime) //分返回符号,正负或0

参考地址：http://mp.weixin.qq.com/s?__biz=MjM5NDMwNjMzNA==&mid=203926493&idx=1&sn=6538524997d1df8c6e8563fe3d5d564e&scene=2#rd

MYSQL查询优化

在 2015年2月16日2015年2月26日上张贴由 BestLove

面试曾被问及过这么一个问题 select * from test order by id limit 9000000,100 如何优化。当时脑子短路了，只想着mysql是否有自带的方法能将游标定位到9000000。其实只需要用where条件来限制即可。

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8;

//当id连续且没有status状态限制的情况下，使用以下SQL，性能最快 0.001ms
select * from test where id >=9000000 order by id limit 100;

//当id非连续或有status状态限制的情况下，使用以下SQL，性能有略微提升 1.420ms
//在实际环境中，如果子查询过慢的话，可将SQL拆分成2句即可
select * from test where id >= (select id from test order by id limit 9000000,1) order by id limit 100;

CREATE TABLE `test` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8;

//当id连续且没有status状态限制的情况下，使用以下SQL，性能最快 0.001ms

select * from test where id >=9000000 order by id limit 100;

//当id非连续或有status状态限制的情况下，使用以下SQL，性能有略微提升 1.420ms

//在实际环境中，如果子查询过慢的话，可将SQL拆分成2句即可

select * from test where id >= (select id from test order by id limit 9000000,1) order by id limit 100;

唐晓锋–BLOG

This is just a personal note.

分类：mysql

mysql主从同步

mysql存储过程

MYSQL查询优化