Category Archives: MySQL

mysql解锁

Posted on by

一、查看进程运行情况（会话1）
mysql> select id,user,host,db,command,time,state from processlist a;
+—-+——+—————–+——————–+———+——+———–
+
| id | user | host | db | command | time | state
|
+—-+——+—————–+——————–+———+——+———–
+
| 40 | root | localhost:14046 | information_schema | Query | 0 | executing
|
| 39 | root | localhost:13992 | chf | Sleep | 251 |
|
| 38 | root | localhost:13991 | chf | Sleep | 251 |
|
+—-+——+—————–+——————–+———+——+———–
+
3 rows in set (0.00 sec)

二、构造表被锁现象
1）锁住表（会话1）
mysql>LOCK TABLES chf.disc02 READ;或者–LOCK TABLES chf.disc02 WRITE;
2）执行dml操作（会话2）
mysql>delete from chf.disc02 limit 1;–会话处于卡死状态
3）查询进程运行情况（会话1）
mysql> select id,user,host,db,command,time,state from processlist a;
+—-+——+—————–+——————–+———+——+———–
+
| id | user | host | db | command | time | state
|
+—-+——+—————–+——————–+———+——+———–
+
| 41 | root | localhost:14358 | chf | Query | 5 | Locked
|
| 40 | root | localhost:14046 | information_schema | Query | 0 | executing
|
| 39 | root | localhost:13992 | chf | Sleep | 343 |
|
| 38 | root | localhost:13991 | chf | Sleep | 343 |
|
+—-+——+—————–+——————–+———+——+———–
+
4 rows in set (0.01 sec)

说明：发现进程id为41的进程状态为Locked

三、解锁操作
1）删掉被锁进程（会话1）
mysql> kill 41;
出现现象（会话2）
ERROR 2013 (HY000): Lost connection to MySQL server during query

2）查看进程（会话1）
mysql> select id,user,host,db,command,time,state from processlist a;
+—-+——+—————–+——————–+———+——+———–
+
| id | user | host | db | command | time | state
|
+—-+——+—————–+——————–+———+——+———–
+
| 40 | root | localhost:14046 | information_schema | Query | 0 | executing
|
| 39 | root | localhost:13992 | chf | Sleep | 298 |
|
| 38 | root | localhost:13991 | chf | Sleep | 298 |
|
+—-+——+—————–+——————–+———+——+———–
+
3 rows in set (0.01 sec)

四、批量解锁
mysql> select concat(‘kill ‘,id,’;’) kill_process from processlist a where a.state=’Locked’;
+————–+
| kill_process |
+————–+
| kill 43; |
| kill 42; |
+————–+
2 rows in set (0.01 sec)

Note：
1）可以使用show processlist查看当前用户连接
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。show processlist;只列出前100条，如果想全列出请使用show full processlist;
2）在构造锁的会话中，使用unlock tables;也可以解锁

mysql中group by操作

Posted on by

在我的思想中，group by函数应该的使用应该是SELECT 列表中指定的每一列也必须出现在 GROUP BY 子句中，除非这列是用于聚合函数，但是今天帮同事调试一个mysql中的group by函数，让我大跌眼镜，当时感觉不可思议，然后回来做了个简化版试验，试验过程如下：

mysql表结构
mysql> desc t;
+——-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————–+——+—–+———+——-+
| id | int(11) | YES | | 0 | |
| name | varchar(100) | YES | | NULL | |
| aa | varchar(45) | YES | | NULL | |
+——-+————–+——+—–+———+——-+
3 rows in set (0.01 sec)

插入数据
mysql> select * from t;
+——+——+——-+
| id | name | aa |
+——+——+——-+
| 1 | aaaa | bbbb |
| 1 | 1111 | 2222 |
| 1 | 2222 | 33333 |
| 1 | 2222 | 44444 |
| 2 | 2222 | 44444 |
| 2 | 2222 | 1111 |
| 3 | 2222 | 1111 |
| 1 | 2222 | 44444 |
| 1 | 2222 | 44444 |
| 1 | 2222 | 44444 |
| 3 | 2222 | aaaa |
+——+——+——-+
11 rows in set (0.00 sec)

group by 查询语句
mysql> select id,count(1) ,aa from t group by id;
+——+———-+——-+
| id | count(1) | aa |
+——+———-+——-+
| 1 | 7 | bbbb |
| 2 | 2 | 44444 |
| 3 | 2 | 1111 |
+——+———-+——-+
3 rows in set (0.00 sec)

在本试验中，一共select id,count(1),aa，结果group by按照规则，除了聚合函数（count(1)）外，其他两列（id,aa）都应该包含在group by中，可是试验只是包含了id。

对试验结果的说明
1、包含在group by后面的id列的count（1）统计数据为正确的
2、按照正常思维，aa的数据不能展示出来，可是mysql选择了展示表中aa数据的第一条
3、上述2也是个人猜测，暂时未查到官方相关说明

mysql 查询表相关语句

Posted on by

1、查询表结构
desc tablename;
show full columns from tablename;
select * from information_schema.columns where table_name=’tablename’;

2、查询表相关信息
select * from information_schema.tables where table_name=’tablename’;

3、查询列编码
select table_schema,table_name,column_name,character_set_name,collation_name from information_schema.columns where table_name=’tablename’;

4、查询表编码
select table_name,table_collation from information_schema.tables where table_name=’tablename’;

sql server中操作mysql

Posted on by

一、安装mysql官方的odbc驱动
mysql-connector-odbc-5.1.7-win32 myodbc驱动补丁（由于驱动自身有bug，发布了补丁）

二、配置odbc

三、创建mysql链接服务

EXEC sp_addlinkedserver 'MySQL50','','MSDASQL','mysql5_test';
EXEC sp_addlinkedsrvlogin 'MySQL50','false','sa','root','4020894';

四、执行相关查询

--查询操作
SELECT TOP 3 * FROM OPENQUERY (MySQL50 ,'select * from abc  ' ) ORDER BY Access_Time desc;
--插入操作
INSERT INTO OPENQUERY (MySQL50, ' SELECT * FROM abc')
values('aaa', 'bbb','ccc','dddd','eeee','2010-11-4','ggggggg')
--更新操作
UPDATE OPENQUERY (MySQL50, 'SELECT Cur_url FROM abc WHERE Pre_url = ''bbb''')
SET Cur_url = 'feifei';
--删除操作
DELETE OPENQUERY (MySQL50, 'SELECT Cur_url FROM abc WHERE Pre_url = ''bbb''')

mysql主从复制

Posted on by

1、分别在两台linux服务器上安装相同版本的mysql数据库
我使用的是最新的mysql 5.1.50 redhat版的
安装命令 rpm -ivh mysql.rpm
按照提示完成配置工作

2、建立复制账户
GRANT REPLICATION SLAVE ON *.* TO ‘mysql1’@’%’ IDENTIFIED BY ‘xifenfei’;

3、拷贝my-medium.cnf文件到/etc/my.cnf并修改相关参数
1）主服务器（确定这两个参数启动）
[mysqld]
log-bin
server-id=1

2）从服务器
server-id=2
方案一：
master-host =192.168.137.134
master-user =mysql1
master-password =xifenfei
master-port = 3306（默认可以不设置）
方案二：在mysql中执行
CHANGE MASTER TO
-> MASTER_HOST=’192.168.137.134′,
-> MASTER_USER=’mysql1′,
-> MASTER_PASSWORD=’xifenfei’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,[可选]
-> MASTER_LOG_POS=recorded_log_position;[可选]

4、建立主服务器快照
可以使用拷贝文件实现
FLUSH TABLES WITH READ LOCK;
使用tar打包，复制到从服务器上
SHOW MASTER STATUS;–记录下当前快照是的日志文件情况
UNLOCK TABLES;
在从服务器上解压tar包到数据文件目录下

5、启动主从服务器
在从服务器上执行
SHOW MASTER STATUS;
查看状态，如果都和主服务器刚刚记录下来的一致表明配置成功，也可以通过修改主数据库数据，然后从数据库查看，测试是否成功！
SHOW PROCESSLIST\G查看主从服务器相关状态

注：如果从用方案二配置从服务器，在启动的时候，可以通过START SLAVE；启动从服务器线程。

6、总结
从总体说来看，mysql的主从复制同步，比oracle的data guard配置起来简单多了，但是同时也表现出来了，没有oracle的强大，这个只是相当于oracle中的逻辑复制功能。