zhujinhe – 第 3 页

Mysql删除某字段中有重复数据的行

如果mobile字段没有重复数据，可以执行下一行语句修改表结构：
Alter table web_user_mobile_time_limit add unique(mobile);

由于现有表的mobile字段的数据已经有一些是重复的。
mysql> select * from web_user_mobile_time_limit;
+----+-------------+---------------------+
| id | mobile | dateTime |
+----+-------------+---------------------+
| 1 | 13800138000 | 2014-09-23 20:18:17 |
| 2 | 13800138000 | 2014-09-23 20:18:57 |
| 3 | 13800138001 | 2014-09-23 20:19:04 |
| 4 | 13800138000 | 2014-09-23 20:19:18 |
| 5 | 13800138001 | 2014-09-23 20:19:22 |
| 6 | 13800138002 | 2014-09-23 20:19:25 |
| 7 | 13800138001 | 2014-09-23 20:19:29 |
| 8 | 13800138002 | 2014-09-23 20:19:34 |
| 9 | 13800138003 | 2014-09-23 20:19:39 |
| 10 | 13800138004 | 2014-09-23 20:19:43 |
| 11 | 13800138004 | 2014-09-23 20:19:46 |
+----+-------------+---------------------+
11 rows in set

在修改表结构的过会提示如下错误：
mysql> Alter table web_user_mobile_time_limit add unique(mobile);
ERROR 1062 : Duplicate entry '13800138000' for key 'mobile'

业务部门的需求是直接删除掉重复数据中更新时间较晚的数据，执行方法是这样:
delete from a using web_user_mobile_time_limit as a,web_user_mobile_time_limit as b where a.id>b.id and a.mobile = b.mobile;
如果想保留较旧的数据则执行：
delete from a using web_user_mobile_time_limit as a,web_user_mobile_time_limit as b where a.id<b.id and a.mobile = b.mobile;

再次查看结果如下：
mysql> select * from web_user_mobile_time_limit;
+----+-------------+---------------------+
| id | mobile | dateTime |
+----+-------------+---------------------+
| 1 | 13800138000 | 2014-09-23 20:18:17 |
| 3 | 13800138001 | 2014-09-23 20:19:04 |
| 6 | 13800138002 | 2014-09-23 20:19:25 |
| 9 | 13800138003 | 2014-09-23 20:19:39 |
| 10 | 13800138004 | 2014-09-23 20:19:43 |
+----+-------------+---------------------+
5 rows in set
至此，重复数据已经删除，现在修改表结构就不会报错了；

MySQL服务器合并

不中断web服务的前提下合并多个MySQL服务器到单独的一台服务器上

目标描述：
公司内部生产环境中有多个MySQL服务器系统配置过剩、管理复杂，计划将三台MySQL数据库服务器A1(A2从)、B1(B2从)、C1(C2从)合并到配置稍高Mysql服务器D1(D2从)上。前提要求是不中断web服务，不能丢失数据。
由于A1、B1、C1的数据库database_name并不重复，数据引擎为INNODB,合并的难度就小了很多。
A1中有DBA1、DBA2、DBA3,IP 172.16.1.101
B1中有DBB1,IP 172.16.1.102
C1中有DBC1,IP 172.16.1.103
D1 IP 172.16.1.104

合并思路：
1、在D1上创建账号并赋予权限；A1、B1、C1创建同步账号（曾创建）、开启二进制文件（曾开启）
2、设置D1为A1的从库：A1数据，导入D1、设置同步，切换web代码中的数据库连接文件。
3、导出B1数据，导入到D1中，重设D1为B1的从库，同步完成后切换web代码中数据库连接文件。
4、同理导出C1数据，导入到D1中，重设D1为C1的从库，同步完成后切换web代码中数据库连接文件。
5、重设D1为主服务器，创建同步账号，导出D1数据库并导入到D2，设置主从和备份。

mysqldump关键参数：
--master-data=2:记录数据导出时的数据库使用的日志文件名和position
--single-transaction:导出时不锁表
--databases/--B:同时导出多个数据库
具体释义请man mysqldump

实施过程(本例数据库名、IP、权限等已替换为非无意义单词，仅展示原理和步骤)：

mysql> select user,host from mysql.user;
+-----------+--------------+
| user | host |
+-----------+--------------+
| root | 127.0.0.1 |
| sync_user | 172.16.1.% |
| DBAU1 | 172.16.1.1 |
| root | localhost |
+-----------+--------------+
4 rows in set (0.00 sec)
mysql> show grants for 'sync_user'@'172.16.1.%';
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for sync_user@172.16.1.% |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'sync_user'@'172.16.1.%' IDENTIFIED BY PASSWORD '*****************************************' |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GRANT REPLICATION SLAVE [ ... ] 说明已经赋予sync_user账号在172.16.1.%的同步权限。

3、在D1服务器上创建DBAU1账号和权限与A1服务器的权限相同。
在A1服务器执行下列语句查看DBAU1的账号权限：
mysql> show grants for 'DBAU1'@'172.16.1.1';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for DBAU1@172.16.1.19 |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'DBAU1'@'172.16.1.19' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT ALL PRIVILEGES ON `DBA1`.`hl_city_exchange_record` TO 'DBAU1'@'172.16.1.1' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `DBA2`.`hl_community_detail` TO 'DBAU1'@'172.16.1.1' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `DBA3`.`hl_city_price_trend` TO 'DBAU1'@'172.16.1.1' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
在D1中执行如下命令赋权限：
mysql>GRANT ALL PRIVILEGES ON `DBA1`.`hl_city_exchange_record` TO 'DBAU1'@'172.16.1.1' IDENTIFIED BY 'YOUR PASSWORD' WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON `DBA2`.`hl_community_detail` TO 'DBAU1'@'172.16.1.1' WITH GRANT OPTION;
mysql>GRANT ALL PRIVILEGES ON `DBA3`.`hl_city_price_trend` TO 'DBAU1'@'172.16.1.1' WITH GRANT OPTION;

4、导出A1中的DBA1、DBA2、DBA3数据库，并修改导出时使用的二进制文件和日志位置；
shell>mysqldump -uroot -p --master-data=2 --single-transaction --database DBA1 DBA2 DBA3 >A1_DBA123.sql

将数据文件传送到D1上：
scp A1_DBA123.sql 172.17.1.104L/root/

--master-data=2：参数值为2时日志文件和日志位置时被注释的状态，需要根据实际情况修改
查找记录位置的行并增加主库信息:
cat -n A1_DBA123.sql | grep "CHANGE MASTER TO",找到如下
22 -- MASTER_LOG_FILE='mysql-bin.000044', MASTER_LOG_POS=627859448; 修改为下行(n为行数，file和pos值每个文件不同)
shell> sed -i "N,22a\CHANGE MASTER TO MASTER_HOST='172.16.1.101', MASTER_USER='sync_user', MASTER_PASSWORD='SYNC　USER PASSWORD', MASTER_LOG_FILE='mysql-bin.000044', MASTER_LOG_POS=627859448;"
导入数据：在D1上执行下行中的命令将修改后的SQL导入到D1中。
shell> mysql -uroot -p <A1_DBA123.sql

然后开启D1的slave.
mysql>start slave;
之后做一些必要的检查：
mysql>show slave status\G;

[ ... ]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[ ... ]
Seconds_Behind_Master: 0
[ ... ]

显示中如果包含以上三列代码值为YES、YES、0则说明同步成功，如果不为0需要等待从库同步完毕。

5、确认数据库同步成功后修改web代码中数据库连接模块，将数据库IP指向D1服务器，并观察数据运行情况；
查看mysql运行状态
mysql> show processlist;
至此A1数据库已无缝迁移完毕。
测试部门确认页面访问正常后停掉D1的slave(后续导入B1数据库时会提示停掉slave)
mysql> stop slave;

6、B1数据库导入的情况大体一致，根据步骤2、3、4、5做一些相应修改后导入到D1中，做必要检查数据库前已完毕后切换web代码中数据库连接模块。
7、C1数据库导入的情况大体一致，根据步骤2、3、4、5做一些相应修改后导入到D1中，做必要检查数据库前已完毕后切换web代码中数据库连接模块。

8、数据库导入完毕后等测试部门确认前台无误。进行下一步。

9、重设D1为主库；
重设D1为主库；
mysql> reset master;
(服务器端执行reset master会重设master，并清理掉作为master的二进制日志)
mysql> quit;
重启数据库
shell> service mysqld restart
测试查看D1的slave状态如下：
mysql> show slave status\G;
Empty set (0.00 sec)

ERROR:
No query specified
10、导出D1的所有数据库，并设置D2为D1的从库；
shell>mysqldump -uroot -p --master-data=2 --single-transaction --all-databases >D1_all-databases.sql
查找记录位置的行并增加主库信息:
cat -n D1_all-databases.sql | grep "CHANGE MASTER TO",找到如下
22 -- MASTER_LOG_FILE='mysql-bin.00001', MASTER_LOG_POS=627859448; 修改为下行(n为行数，file和pos值每个文件不同)
shell> sed -i "N,22a\CHANGE MASTER TO MASTER_HOST='172.16.1.101', MASTER_USER='sync_user', MASTER_PASSWORD='SYNC　USER PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=627859448;"
("N,22a..." 此中22为行数，和cat中的行数对应)
导入数据：在D2上执行下行中的命令将修改后的SQL导入到D2中。
然后开启D2的作为从库：
shell> start slave;
并做一些必要的检查。

至此，三台MySQL数据库服务器A1(A2从)、B1(B2从)、C1(C2从)成功合并到D1(D2从)。

在D1上设置备份(略写)
shell>echo "password of backup user" > backup.pass
shell> chown 600 backup.pass
这样设置目的是只有当前用户能够读取到backup_user的密码。
备份的关键部分是：
mysqldump -uroot -p`cat backup.pass` --master-data --single-transaction --all-databases > BackupDate`date +%Y%m%d-%H%M%S`.sql
也可以扩展比如清理mtime大于30天的文件等。不再详述。

记一次Mysql数据库某个表误truncate恢复

状态描述:
公司的某个系统的数据库疏于维护,今天(2014/8/25)有个项目组PHPer不小心把线上的Mysql主库的某个表truncate掉了,询问是否有办法恢复数据.
恰好不巧这个唯独这个数据库从未做过备份,由于truncate(不同于delete)是不记录日志的,看来这次要悲剧了.后又与PHPer沟通了解到,原来这个项目新上不到一个月,好像又有了一线希望,马上设置好定时备份该数据表的计划任务后开始着手恢复.

恢复思路:尝试在测试库中通过mysql的二进制日志重放日志来恢复数据,然后稍加处理导入到生产数据库中;

数据库已开启二进制日志,并且设置的过期日期是30天且未被清理,由此推断被truncate的数据仍完整的存在于二进制文件中,这是本次通过mysqlbinlog进行恢复的前提.

step 2:查找需要使用的binary log文件,以及恢复的start-position(或者start-datetime)和end-position(或者stop-position)
mysqlbinlog有5个常用的参数:
-d:指定数据库名
--start-position:指定开始位置,
--stop-position:指定结束为止
--start-datetime:指定开始时间,格式如"2010-01-07 11:25:56"
--stop-position:指定结束时间
本例实际情况涉及到的文件列表如下:
-rw-rw---- 1 mysql mysql 1073760033 Aug 20 18:04 mysql-bin.002333
-rw-rw---- 1 mysql mysql 1073741925 Aug 21 11:30 mysql-bin.002334
-rw-rw---- 1 mysql mysql 1073741919 Aug 22 06:00 mysql-bin.002335
-rw-rw---- 1 mysql mysql 1073741936 Aug 22 22:30 mysql-bin.002336
-rw-rw---- 1 mysql mysql 1073741995 Aug 23 16:00 mysql-bin.002337
-rw-rw---- 1 mysql mysql 1073742017 Aug 24 09:00 mysql-bin.002338
-rw-rw---- 1 mysql mysql 1073742030 Aug 25 00:00 mysql-bin.002339
-rw-rw---- 1 mysql mysql 281269618 Aug 25 18:30 mysql-bin.002340

开始位置的确定:本例中情况比较特殊,开始日期设置为项目创建日期之前的任意时间点即可.
结束为止的确定:结束位置一定要特别注意,结束position一定要设置为truncate语句的上一条的结束位置,保证数据完整的同时又不能执行truncate语句.具体查找方法如下:

shell>mysqlbinlog -d db_mobi mysql-bin.002340 | grep -A5 -B 5 -E "truncate|agent_select_resblock_detail"
输出如下:
--
BEGIN
/*!*/;
# at 618699837
#140825 11:12:05 server id 1 end_log_pos 618699946 Query thread_id=258628603 exec_time=0 error_code=0
SET TIMESTAMP=1408936325/*!*/;
TRUNCATE agent_select_resblock_detail
/*!*/;
# at 618699946
#140825 11:12:05 server id 1 end_log_pos 618699973 Xid = 9541176706
COMMIT/*!*/;
# at 618699973

由此可以确定truncate操作日期大约在11:12:05
进一步确定:
shell>mysqlbinlog -d homelink_mobi mysql-bin.002340 --start-datetime "2014-08-25 11:12:00" --stop-datetime="2014-08-25 11:12:10"
输出:

...(省略)...
# at 618699733
#140825 11:12:03 server id 1 end_log_pos 618699760 Xid = 9541176633
COMMIT/*!*/;
# at 618699760
#140825 11:12:05 server id 1 end_log_pos 618699837 Query thread_id=258628603 exec_time=0 error_code=0
SET TIMESTAMP=1408936325/*!*/;
BEGIN
/*!*/;
# at 618699837
#140825 11:12:05 server id 1 end_log_pos 618699946 Query thread_id=258628603 exec_time=0 error_code=0
SET TIMESTAMP=1408936325/*!*/;
TRUNCATE agent_select_resblock_detail
/*!*/;
# at 618699946
#140825 11:12:05 server id 1 end_log_pos 618699973 Xid = 9541176706
COMMIT/*!*/;
# at 618699973
...(省略)...

由此可以得出结束position为618699760;

step 3:导出生产环境中的表结构,处理自增长字段(如果需要),并将表结构导入到测试数据库中;

注意:本次恢复的过程只需要被误truncate的表所属数据库的表结构,并设定取消自增长;
shell>mysqldump -uroot -p -d db_mobi --no-data | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > biaojiegou.sql
并传递到测试数据库机器:
scp biaojiegou.sql Test_db_IP:/root/

step 4:在测试数据库中创建db-mobi数据库并导入表结构;

mysql>drop database db_mobi;
mysql>create database db_mobi;
mysql> use db_mobi;
mysql> source /root/biaojiegou.sql;
至此,数据表结构导入完毕.

step4:使用mysqlbinlog将日志导入到测试数据库中;

shell>mysqlbinlog -d db_mobi mysql-bin.002333 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002334 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002335 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002336 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002337 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002338 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002339 |mysql -uroot -ppassword
shell>mysqlbinlog -d db_mobi mysql-bin.002340 --stop-position 618699760 |mysql -uroot -ppassword

mysql-bin.00233[3-9]的开始时间和结束时间在本例中可以省略,其中最后一条命令中position由第二步得到.

导入完毕后查看被误truncate的表数据自增长字段最小值和最大值:
mysql> select min(id) from db_mobi.agent_select_resblock_detail;
+---------+
| min(id) |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

mysql> select max(id) from db_mobi.agent_select_resblock_detail;
+---------+
| max(id) |
+---------+
| 2593 |
+---------+
1 row in set (0.01 sec)
子增长字段由1增长到2593;

step5:step4结束后,将被误truncate表的数据导出(注意:只导数据,不导出表结构,参数-t)

mysqldump -uroot -p -t db_mobi agent_select_resblock_detail >/root/db_mobil.agent_select_resblock_detail.sql

将导出的数据传送到生产服务器上(如/root/目录)(略）

step6:导入生产环境时候处理生产环境中的子增长字段,并导入step5的表数据;

因为db_mobil.agent_select_resblock_detail表被truncate过,所以子增长字段最小值应该为1,这时候需要把生产环境的自增长字段增加2593(step 4得出)
mysql> update db_mobi.agent_select_resblock_detail set id=id+2593;
Query OK, 2593 rows affected (0.10 sec)
Rows matched: 2593 Changed: 2593 Warnings: 0
将测试数据库中导出的表数据导入生产环境;
mysql> use db_mobi;
mysql> source /root/db_mobil.agent_select_resblock_detail.sql;

此时,数据表恢复完毕.最后再做一些必要的检查.

可能遇见的错误:
1、mysqlbinlog导入数据时提示:/usr/bin/mysqld: unknown variable 'default-character-set=utf8' ;
这个错误可能是mysqlbinlog的一个错误,临时注释掉/etc/my.cnf中default-character-set = utf8行(不需要重启数据库),待导入完毕后取消注释即可.
2、mysqlbinlog导入数据时提示类似:table_name does not exists ;
需要注意linux中对mysql表名的大小写是敏感的
3、mysqlbinlog导入到测试数据被truncate的表数据仍为空;
需要注意,mysqlbinlog导入数据的时候的结束的position一定要在truncate的上一条语句的结束点(一定不能包括truncate这条语句).
4、提示auto_increment相关错误;
需要注意在生产数据库导出表结构后需要删除auto_increment或者设置auto_increment=0
5、测试数据库导出的数据导入到生产环境后,生产环境中原有表数据丢失;
需要注意测试数据库中导出数据时不要带表结构,带带表结构导出会在导入到生产库的时候先drop table;

参考资料：
http://melikedev.com/2011/06/01/mysql-remove-auto_increment-from-schema-dumps-mysqldump/
linux:man mysqlbinlog

设置mysql主从复制

主机：172.16.0.108
从机：172.27.4.31
步骤如下：
1、主从服务器分别作以下操作：
1.1、版本一致
1.2、初始化表，并在后台启动mysql
1.3、修改root的密码
2、修改主服务器master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=222 //[必须]服务器唯一ID，默认是1，一般取IP最后一段
3、修改从服务器slave:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=226 //[必须]服务器唯一ID，默认是1，一般取IP最后一段
4、重启两台服务器的mysql
/etc/init.d/mysql restart
5、在主服务器上建立帐户并授权slave:
#/usr/local/mysql/bin/mysql -uroot -p
mysql>GRANT REPLICATION SLAVE ON *.* to 'sync_user'@'172.27.%.%' identified by 'homelink';
//一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。
6、主数据库锁表操作，不让数据再进行写入动作。
mysql>use dbname;
mysql>flush tables with read lock;
7、登录主服务器的mysql，查询master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 21420 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记下File以及Position的值，以备从服务器使用。
8、以导入所有数据库为例。
shell>mysqldump -uroot -p --all-databases >all.sql
(只导出某个数据库：mysqldump -uroot -p dbname >dbnamebackup.sql)
9、将all.sql导入到从库中。
shell>mysql -uroot -p < all.sql
（只导如某个database：mysql -uroot -p dbname <dbnamebackup.sql)
10、配置从服务器Slave：
mysql>change master to master_host='172.16.0.108', master_user='sync_user', master_password='homelink',master_log_file='mysql-bin.000001',master_log_pos=21420; //注意不要断开，master_user='sync_user'后面不需要@'172.27.%.%'，21420”无单引号。
返回类似：Query OK, 0 rows affected (0.05 sec)表示正常。

Mysql>start slave; //启动从服务器复制功
11、检查从服务器复制功能状态：
mysql> show slave status\G;//查看从库的状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.108 //主服务器地址
Master_User: sync_user //授权帐户名，尽量避免使用root
Master_Port: 3306 //数据库端口，部分版本没有此行
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 数值 //#同步读取二进制日志的位置，大于等于>=Exec_Master_Log_Pos
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 84996
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
…………
Exec_Master_Log_Pos: 3614785
…………
Seconds_Behind_Master: 0
…………
1 row in set (0.00 sec)
注：
Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
当从库出现性能问题或主从网络延迟时候Seconds_Behind_Master将不为0。
以上操作过程，主从服务器配置完成。
12、取消主数据库锁定
mysql>use dbname;
mysql>unlock tables;
13、验证可以对比show master status;查看Position是否与从库show slave status\G;中的Exec_Master_Log_Pos数值相同。
14、从库如果同步失败，可以通过查看查看从库状态和show slave status\G;或者当前进程show processlist;根据错误提示处理错误后stop slave,然后start slave；

记一次发布疏忽造成源码暴露(.svn)隐患

说明：
公司有个自己开发的PHP项目，平时测试环境部署的步骤是：
Step1：代码从SVN checkout到本地的某个目录；
Step2：通过rsync排除掉.svn目录、上传目录等后发送到web根目录；
当然这些都是用脚本自动执行（也可以结合svn hook自动部署，不详述）。
隐患产生原因：
有次特殊需求单独手动更新了某个文件夹(假设为article目录)的代码，手动同步时忘记排除掉.svn目录，造成了源码暴露隐患：
假如某请求的URL为：http://www.example.com/article/index.php
这种情况下通过http://www.example.com/article/.svn/text-base/index.php.svn-base是可以下载到源码的。
修复方法：
1、删除web目录.svn文件夹：
find /opt/html/ -name .svn -type d -print |xargs rm -rf
2、nginx排除过滤包含./svn/的请求：
将如下代码插入到nginx配置文件中的server{}模块
# Filter request that contains /.svn/
location ~ ^(.*)\/\.svn\/
{
deny all;
}
备注：
location的模块匹配时有先后顺序的，需要把此段代码插入到其他location之前；
建议站点开启禁止通过IP直接访问；
安全无小事，希望各位看官同样引以为戒。

linux关闭windows电脑

net rpc shutdown
简单又不简单的命令.
利用这个命令每天晚上定时关闭公司上千家门店共约1万台台式机. 按照待机功率10W计算, 这个命令大概每年可以为公司能节省40多万.
小命令的大作用(顺带因为这个操作,公司奖励给部门25k经费,哈哈).