分类：mysql

注意事项
1、主从服务器操作系统版本和位数一致。
2、MySQL版本一致。

服务器配置
master:192.168.1.120
slave:192.168.1.119

Master(192.168.1.120)服务器配置

vim /etc/my.cnf

[mysqld]
# 唯一的服务辨识号,数值位于 1 到 2^32-1之间.
# 此值在master和slave上都需要设置.
# 如果 “master-host” 没有被设置,则默认为1, 但是如果忽略此选项,MySQL不会作为master生效.
server-id=1

# 打开二进制日志功能.
# 在复制(replication)配置中,作为 MASTER 主服务器必须打开此项
# 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志.
log-bin=master-bin
log-bin-index=master-bin.index

#需要备份的数据库
binlog-do-db=orders

#不需要备份的数据库
#若没有配置binlog-do-db和binlog_ignore_db，表示备份全部数据库。
binlog-ignore-db=mysql

2、重启mysqld服务

service mysqld restart

3、为从MySQL创建用户

#登录
mysql -uroot -p
Enter password: 

#创建用户
mysql> create user 'slave'@'192.168.1.119(从机ip)' identified by '1';
        
#配置主从复制权限
mysql> grant replication slave on *.* to 'slave'@'192.168.1.119(从机ip)' identified by '1';

4、若orders中已有数据，还需要锁定主服务器数据库，然后将数据导入到从数据库

#锁定
mysql> flush tables with read lock;

#数据复制到从数据库后，查看主数据库master状态并解锁：
mysql> show master status;  
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 |      648 | orders       | mysql            |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)  
  
mysql> unlock tables;

Slave(192.168.1.119)服务器配置
1、配置服务ID

vim /etc/my.cnf 

[mysqld]
#server_id是必须的，而且唯一
server-id=2

# 使得slave只读.只有用户拥有SUPER权限和在上面的slave线程能够修改数据.
# 你可以使用此项去保证没有应用程序会意外的修改slave而不是master上的数据
#read_only

# 如果你在使用链式从服务器结构的复制模式 (A->B->C),
# 需要打开slave的二进制日志
#log_bin=mysql-bin

# 如果你在使用链式从服务器结构的复制模式 (A->B->C),
# 你需要在服务器B上打开此项.
# 此选项打开在从线程上重做过的更新的日志, 并将其写入从服务器的二进制日志.
# 如果打开log_bin，却没有设置log_slave_updates，这是一种错误的配置。
#log_slave_updates

2、重启MySQL服务
3、复制配置

#登录
mysql -uroot -p
Enter password: 
       
#执行
mysql> change master to master_host='192.168.1.120',
       master_user='slave',
       master_password='1',
       master_port=3306,
       master_log_file='master-bin.000004',
       master_log_pos=648,
       master_connect_retry=10;

参数详解：
master_host:主服务器的IP。
master_user：配置主服务器时建立的用户名
master_password：用户密码
master_port：主服务器mysql端口，如果未曾修改，默认即可。
master_log_file：日志文件名称，填写查看master状态时显示的File
master_log_pos：日志位置，填写查看master状态时显示的Position
master_connect_retry：重连次数

4、启动进程

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.120
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 648
               Relay_Log_File: magic-VirtualBox-relay-bin.000003
                Relay_Log_Pos: 507
        Relay_Master_Log_File: master-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

若Slave_IO_Running和Slave_SQL_Running均为Yes，则表示配置成功。

5、中间遇到了一些问题，解决如下

Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 1146
Last_Error: Error 'Table 'orders.user_orders' doesn't exist' on query. Default database: 'orders'. Query: 'INSERT INTO `orders`.`user_orders` (`id`, `user_id`, `user_name`) VALUES (NULL, '2', 'magic.shen')'

# 首先停止主从同步，
# stop slave;
# 检查 master status,中 Position 是否和配置的一致
# 手动将master上的orders库结构、数据导入到slave上
# 重启slave
# start slave;

首先我们建立一张带有逗号分隔的字符串。
CREATE TABLE test(id int(6) NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),pname VARCHAR(20) NOT NULL,pnum VARCHAR(50) NOT NULL);

然后插入带有逗号分隔的测试数据
INSERT INTO test(pname,pnum) VALUES('产品1','1,2,4');
INSERT INTO test(pname,pnum) VALUES('产品2','2,4,7');
INSERT INTO test(pname,pnum) VALUES('产品3','3,4');
INSERT INTO test(pname,pnum) VALUES('产品4','1,7,8,9');
INSERT INTO test(pname,pnum) VALUES('产品5','33,4');

查找pnum字段中包含3或者9的记录
mysql> SELECT * FROM test WHERE find_in_set('3',pnum) OR find_in_set('9',pnum);
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 3 | 产品3 | 3,4 |
| 4 | 产品4 | 1,7,8,9 |
+----+-------+---------+
2 rows in set (0.03 sec)

使用正则
mysql> SELECT * FROM test WHERE pnum REGEXP '(3|9)';
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 3 | 产品3 | 3,4 |
| 4 | 产品4 | 1,7,8,9 |
| 5 | 产品5 | 33,4 |
+----+-------+---------+
3 rows in set (0.02 sec)
这样会产生多条记录，比如33也被查找出来了，不过MYSQL还可以使用正则，挺有意思的

find_in_set()函数返回的所在的位置，如果不存在就返回0
mysql> SELECT find_in_set('e','h,e,l,l,o');
+------------------------------+
| find_in_set('e','h,e,l,l,o') |
+------------------------------+
| 2 |
+------------------------------+
1 row in set (0.00 sec)

还可以用来排序，如下;
mysql> SELECT * FROM TEST WHERE id in(4,2,3);
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 2 | 产品2 | 2,4,7 |
| 3 | 产品3 | 3,4 |
| 4 | 产品4 | 1,7,8,9 |
+----+-------+---------+
3 rows in set (0.03 sec)

如果想要按照ID为4，2，3这样排序呢？
mysql> SELECT * FROM TEST WHERE id in(4,2,3) ORDER BY find_in_set(id,'4,2,3');
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 4 | 产品4 | 1,7,8,9 |
| 2 | 产品2 | 2,4,7 |
| 3 | 产品3 | 3,4 |
+----+-------+---------+
3 rows in set (0.03 sec)

文章转载自 http://blog.csdn.net/myweishanli/article/details/11963137

Mysql  varchar 和 int 类型的字段 left join 关联查询查询不到 返回NULL的问题

可尝试

LEFT JOIN `tb1`
ON `tb1`.`order_id` = CAST( `tb2`.`order_id` AS UNSIGNED )

 

mysqldump 21cake_249 -u root -p1 --add-drop-table | mysql 21cake_249.bk -u root -p1

From China’s affordable, quality assurance of cheap shirts big run!
First we need to know is that our company has the greatest cheap jersey international-based sales network, if you buy cheap jerseys from us, we can help you save at least 60% of the price and we can guarantee that all freight borne by us, the consumer who is not dig and freight!
With the promotion of ball games, and its related products are also increasingly international, including shirts, ball caps, socks and shoes and other equipment, the network which acts as a very good medium, then we produce cheap shirts We have a lot of outstanding brands, including Adidas, JOMA, Nike, Puma, soldiers and so on.
We signed a sales agreement with the manufacturers, all export shirts are shipped directly from the production line to your home, which is in the middle will not have anyone charge you fees or touch your shirt. Cheap shipping is our guarantee, you only need to provide your body height we will be able to provide you with the quickest and easiest to report!
Come to talk about our cheap jerseys it, we all know that China is the world’s ancient silk products a big country, we cheap jerseys produce cheap shirts can help you keep cool on the court, but on the pitch you can keep cool is to win a major factor! We are using polyester cotton jersey can quickly draw your body sweat,
So you can beat the opponent on the shirt!
A smart consumer will choose the appearance and quality of the goods themselves, and make informed judgments. Our cheap jerseys these two are able to ensure that our shirts are very cheap jerseys unique in the details, no http://www.innathydepark.com/cheap-jerseys-free-shipping.html matter what your body type is what we can for you to make a suitable cheap jersey. Style and diverse, each team uniforms we have here the original and contains a lot of silk shirts, can be repeated washing,
so clothing is definitely a value for money! Favorite quickly click to buy it!

Related Articles:

• Wholesale NFL Jerseys-Wholesale jerseys online shop
• Cheap jerseys from China, all goods free shipping!
• Cheap Authentic MLB Jerseys With Paypal From China
• Cheap jerseys from China – Clearance sold!
• The Ultimate Guide to buy cheap jerseys
• Cheap jerseys from China – Clearance sold!
• Cheap Nfl Nba Nhl Mlb Jerseys Free Shipping From China
• Cheap Soccer Jerseys, Uniforms & Shirts On Sale

SELECT rec_id,GROUP_CONCAT(user_id)
FROM t1
GROUP BY rec_id

error: using group_concat in PHPMYADMIN will show the result as [BLOB – 3B]

can use:

SELECT rec_id,GROUP_CONCAT(CONVERT(user_id, CHAR(8)))
FROM t1
GROUP BY rec_id