海运的博客

使用PHP读取文件写入数据库遇到一个灵异的问题，第一行第一列内容插入后变成0，查看文件第一行开头为BOM格式：

head -n 1 file | hexdump -C
ef bb bf

删除BOM解决：

sed -i '1 s/^\xef\xbb\xbf//' file

Master配置：

log-bin = mysql-bin
server-id = 1
binlog-do-db = powerdns #要同步的数据库

Master新增slave账号：

mysql> grant replication slave on *.* to 'slave'@'192.168.1.2' identified by 'passwd';  
mysql> flush privileges; 

重启Master MySQL，查看Master状态信息，记住File名称，POS位置。

mysql> show master status;  
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      309 | powerdns     | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Slave配置：

server-id =2 
slave-skip-errors = 1062 #跳过重复字段错误

开始同步：

mysql> change master to master_host='192.168.1.1',master_user='slave',master_password='passwd',\
master_log_file='mysql-bin.000002',master_log_pos=309;  
mysql> start slave;

查看Slave同步状态：

mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1.当插入列为主健或单一索引（可多列）值重复时执行update操作：

INSERT INTO `records` (`domain_id`, `name`, `TYPE`, `content`, `ttl`) VALUES 
(:id, :name, :type, :content, :ttl) ON DUPLICATE KEY UPDATE `change_date` = '1';

2.重复时先删除之前数据再插入新数据：

REPLACE INTO `records` (`domain_id`, `name`, `TYPE`, `content`, `ttl`) VALUES 
(:id, :name, :type, :content, :ttl);

文本多列以,分隔，导入到指定字段：

load data local infile "file.txt" into table db.table FIELDS TERMINATED BY ',' (name,address);
load data local infile "file.txt" into table db.table (name,address) FIELDS TERMINATED BY ',';
update db.table SET aaa = 'test'; 

也可使用PHP以数组的方式逐行插入，自定义方便。

<?php  
   $hostname="localhost";
   $username="root";
   $password="passwd";  
   $dbname="dbname";
   mysql_connect($hostname,$username,$password);
   mysql_select_db("$dbname");
   $mydate=file("/root/m.txt");
   $n=count($mydate);
   for($i=0;$i<$n;$i++){
      $date=explode(",",$mydate[$i]);
      $str="insert into table (name,address) values ('$date[0]','$date[1]')";
      mysql_query($str);
   }
   mysql_close();
   echo "ok!";
?>