mysqldump参数介绍
mysql 重设root密码
一、linux系统
1、修改my.cnf中的参数
关闭数据库
修改my.cnf参数
在[mysqld]的段中加上一句:skip-grant-tables
启动数据库,使用mysqld/mysqld_safe/mysqld.service都可以
使用mysql直接登录修改root密码
2、mysqld_safe –skip-grant-tables 启动数据库
关闭数据库
mysqld_safe –skip-grant-tables & 启动数据库
使用mysql直接登录修改root密码
二、windows系统
关闭mysql
$mysqldir/bin/mysqld –skip-grant-tables启动数据库
使用mysql直接登录修改root密码
说明:修改密码请查看MYSQL修改密码
Oracle限制IP访问
通过修改sqlnet.ora文件实现,如果没有在$ORACLE_HOME/network/admin中没有该文件,自己创建一个或者从samples中复制一个出来。
#是否检测上述参数的设置
tcp.validnode_checking=yes
#允许访问的ip
tcp.invited_nodes =(ip1,ip2,……)
#不允许访问的ip
tcp.excluded_nodes=(ip1,ip2,……)
需要注意的问题:
1、 需要设置参数为YES,这样才能激活。
2、 建议设置允许访问的IP,因为IP地址有可能被随意修改,就不能起到自己的目的。
3、 TCP当参数TCP.INVITED_NODES和TCP.EXCLUDED_NODES设置的地址相同的时候将覆盖TCP.INVITED_NODES设置(10G)。
4、 需要重启监听器才能生效。
5、 这个方式只是适合TCP协议。
6、 这个配置适用于9i以上版本。在9i之前的版本使用文件protocol.ora。
7、 在服务器上直接连接数据库不受影响。
8、 这种限制方式事通过监听器来限制的。
9、 这个限制只是针对IP检测,对于用户名检测事不支持的。
10、不能设置ip段和通配符
11、如果配置TCP.INVITED_NODES就必须配置TCP.EXCLUDED_NODES,否则监听不能正常启动,报如下错误
TNS-12560: TNS:protocol adapter error
TNS-00584: Valid node checking configuration error
如(测试192.168.9.215地址不能登录):
tcp.validnode_checking = yes
tcp.excluded_nodes=(192.168.9.215)
tcp.included_nodes=(192.168.9.215,192.168.11.12,127.0.0.1,211.155.227.172)
触发器引起ORA-04091
原因:在行级触发器中,不能查询自身表
场景重现:通过触发器实现test_count表中统计test表中行数
--创建子表 create table TEST (id NUMBER, name varchar2(100), primary key (id)); --创建统计表 create table test_count (test_count int); --创建触发器 CREATE OR REPLACE TRIGGER T_TEST AFTER INSERT OR DELETE ON TEST FOR EACH ROW DECLARE A NUMBER; BEGIN SELECT COUNT(*) INTO A FROM TEST; UPDATE TEST_COUNT SET TEST_COUNT = A; END T_TEST;
模拟错误:
INSERT INTO TEST (ID,NAME)VALUES(2,'abc'); ORA-04091: table CHF.TEST is mutating, trigger/function may not see it ORA-06512: at "CHF.T_TEST", line 2 ORA-04088: error during execution of trigger 'CHF.T_TEST'
处理方法:
通过自治事务实现(修改触发器)
CREATE OR REPLACE TRIGGER T_TEST AFTER INSERT OR DELETE ON TEST FOR EACH ROW DECLARE A NUMBER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN SELECT COUNT(*) INTO A FROM TEST; UPDATE TEST_COUNT SET TEST_COUNT = A; COMMIT; END T_TEST;
PRAGMA AUTONOMOUS_TRANSACTION
当前的触发器作为已有事务的子事务运行,子事务自治管理,子事务的commit、rollback操作不影响父事务的状态
Read by other session等待事件
今天中午发现福建生产库报负载有点异常,处理思路记录下来:
1、使用top命令查看系统,发现系统负载是比以前要搞(平时都是1以下,今天已经稳定在4左右,总是有部分进城占用cpu比较高,系统cpu等待明显)
1.1)第一反应是有人执行sql导致,抓取占用cpu较高的spid,查询出对应sql,发现都是一些比较简单sql
1.2)查询这些spid的客户端是应用服务器,也就是说不是人为执行,那在一个稳定的系统中,不会出现sql突然改变的原因
2、查询系统是否因为有对象被阻塞导致,查询发现无对象被阻塞
3、查询系统等待事件,发现几十个read by other session等待,都是从一台web的服务器上连接过来
SELECT * FROM v$session WHERE wait_class#<>6;
4、read by other session等待事件比较陌生,幸好伴随有db file sequential read的等待事件,初步怀疑读取数据到内存中等待导致
5、查询资料发现
read by other session Definition: When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait.
In previous versions this wait was classified under the “buffer busy waits” event.
However, in Oracle 10.1 and higher this wait time is now broken out into the “read by other session” wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full-table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.
Confio concludes with a summary that “read by other session waits” are very similar to buffer busy waits
When a session waits on the “read by other session” event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache.
If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for “hot” blocks or objects so it is imperative to find out which data is being contended for. Once that is known this document lists several alternative methods for solving the issue.
总结:两个或者多个会话同时需要把硬盘中的对象装载到data buffer中,当其中一个会话把对象装入后,其他会话就处于read by other session等待状态;这个是oracle 10g 从oracle 9i的buffer busy waits中分离出来的,也是需要一种热块现象
6、根据FILE#,BLOCK#查询热块对象
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
FROM DBA_EXTENTS A
WHERE FILE_ID = &FILE_ID
AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS – 1;
7、通过这个对象查询出对应的操作语句
select * from v$sql where upper(sql_text) like ‘%object_name%’;
8、直接查找热点块对象语句
SELECT * FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME, FROM X$BH B, DBA_OBJECTS O WHERE B.OBJ = O.DATA_OBJECT_ID AND B.TS# > 0 GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE ORDER BY SUM(TCH) DESC) WHERE ROWNUM <= 10 --或者 SELECT E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE FROM DBA_EXTENTS E, (SELECT * FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH FROM X$BH ORDER BY TCH DESC) WHERE ROWNUM < 11) B WHERE E.RELATIVE_FNO = B.DBARFIL AND E.BLOCK_ID <= B.DBABLK AND E.BLOCK_ID + E.BLOCKS > B.DBABLK;
9、直接查找热点块操作语句
SELECT /*+rule*/ HASH_VALUE, SQL_TEXT FROM V$SQLTEXT WHERE (HASH_VALUE, ADDRESS) IN (SELECT A.HASH_VALUE, A.ADDRESS FROM V$SQLTEXT A, (SELECT DISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE FROM DBA_EXTENTS A, (SELECT DBARFIL, DBABLK FROM (SELECT DBARFIL, DBABLK FROM X$BH ORDER BY TCH DESC) WHERE ROWNUM < 11) B WHERE A.RELATIVE_FNO = B.DBARFIL AND A.BLOCK_ID <= B.DBABLK AND A.BLOCK_ID + A.BLOCKS > B.DBABLK) B WHERE A.SQL_TEXT LIKE '%' || B.SEGMENT_NAME || '%' AND B.SEGMENT_TYPE = 'TABLE') ORDER BY HASH_VALUE, ADDRESS, PIECE;
10、也可以通过awr查询出来相关对象
Segments by Buffer Busy Waits
语句需要通过这些等待对象进行判断
到了1点钟左右,数据库read by other session等待事件消失,数据库恢复正常负载(因为系统还能够承受,所以当时没有采用kill进程的方法)
事后对查询出来的热点块对象和操作语句,已经访问服务器和开发确认的结果为:这个是一个报表功能,但是平时没有人用,所以也没有关注,今天突然被人用了下,导致这个问题发生,他们承诺在升级下个版本中解决这个问题。
Mysql Merge表
MERGE引擎类型允许你把许多结构相同的表合并为一个表。然后,你可以执行查询,从多个表返回的结果就像从一个表返回的结果一样。每一个合并的表必须有同样的表定义。
MERGE存储引擎在下面这种使用场合会最为有用,如果需要把日志纪录不停的录入MySQL数据库,并且每天、每周或者每个月都创建一个单一的表,而且要制作来自多个表的合计查询,MERGE表这时会非常有效。然而,这项功能有局限性。你只能合并MyISAM表而且必须严格遵守相同的表定义的限制。
创建方法如下:
mysql> create table t1(id int not null primary key,name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2(id int not null primary key,name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create table mrg(id int not null primary key,name varchar(20)) engine=merge union(t1,t2) insert_method=first;
Query OK, 0 rows affected (0.00 sec)
测试:
1、在t1中插入数据
mysql> insert into t1 values(1,’tttttt’);
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values(2,’tttttt’);
Query OK, 1 row affected (0.00 sec)
2、查询t1表
mysql> select * from t1;
+—-+——–+
| id | name |
+—-+——–+
| 1 | tttttt |
| 2 | tttttt |
+—-+——–+
2 rows in set (0.00 sec)
3、查询mrg表
mysql> select * from mrg;
+—-+——–+
| id | name |
+—-+——–+
| 1 | tttttt |
| 2 | tttttt |
+—-+——–+
2 rows in set (0.00 sec)
4、在t2中插入数据
mysql> insert into t2 values(1,’ssssss’);
Query OK, 1 row affected (0.00 sec)
5、查询t2表
mysql> select * from t2;
+—-+——–+
| id | name |
+—-+——–+
| 1 | ssssss |
+—-+——–+
1 row in set (0.00 sec)
6、查询mrg表
mysql> select * from mrg;
+—-+——–+
| id | name |
+—-+——–+
| 1 | tttttt |
| 2 | tttttt |
| 1 | ssssss |
+—-+——–+
3 rows in set (0.00 sec)
7、mrg表中插入数据并测试
mysql> insert into mrg values(1,’ssssss’);
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’
mysql> insert into mrg values(2,’ssssss’);
ERROR 1062 (23000): Duplicate entry ‘2’ for key ‘PRIMARY’
mysql> insert into mrg values(3,’ssssss’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(4,’ssssss’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mrg values(4,’ssssss’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+—-+——–+
| id | name |
+—-+——–+
| 1 | tttttt |
| 2 | tttttt |
| 3 | ssssss |
| 4 | ssssss |
说明:因为我们设置的 INSERT_METHOD为FIRST,因此插入数据进入t1表,而t1表中有主键,所以部分数据插入失败
1. 此表类似于SQL中的union机制。
2. 此表结构必须与基本表完全一致,包括列名、顺序。UNION表必须同属一个DATABASE。
3. 基本表类型必须是MyISAM。
4. 可以通过修改.mrg文件来修改MERGE表,每个基本表的名字占一行。注意:修改后要通过FLUSH TABLES刷新表缓存。
5. 对基本表的更改可以直接反映在此表上。
6. INSERT_METHOD的取值可以是: 0 不允许插入 FIRST 插入到UNION中的第一个表 LAST 插入到UNION中的最后一个表。(4.0之后可用)
7. 定义在它上面的约束没有任何作用,约束是由基本表控制的,例如两个基本表中存在着同样的一个Key值,那么在MERGE表中会有两个一样的Key值。
mysql通过substring_index和substring截取字符串
今天朋友问我mysql中有个列如下数值,怎么提出出其中ip地址
BGP-BeiJing-59.151.105.130-AD_read-YeZongKun
BGP-BeiJing-59.151.105.138_140_141-AD_LVS-YeZongKun
我提供sql语句如下:
select SUBSTRING(substring_index(‘BGP-BeiJing-59.151.105.130-AD_read-YeZongKun’,’-‘,3),(length(substring_index(‘BGP-BeiJing-59.151.105.130-AD_read-YeZongKun’,’-‘,2))+2));
SUBSTRING
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
mysql> SELECT SUBSTRING(‘Quadratically’,5);
-> ‘ratically’
mysql> SELECT SUBSTRING(‘foobarbar’ FROM 4);
-> ‘barbar’
mysql> SELECT SUBSTRING(‘Quadratically’,5,6);
-> ‘ratica’
mysql> SELECT SUBSTRING(‘Sakila’, -3);
-> ‘ila’
mysql> SELECT SUBSTRING(‘Sakila’, -5, 3);
-> ‘aki’
mysql> SELECT SUBSTRING(‘Sakila’ FROM -4 FOR 2);
-> ‘ki’
substring_index
substring_index(str,delim,count)
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, 2);
-> ‘www.mysql’
mysql> SELECT SUBSTRING_INDEX(‘www.mysql.com’, ‘.’, -2);
-> ‘mysql.com’
mysql 二进制文件安装注意事项
执行mysql_secure_installation报错
错误现象:
Enter current password for root (enter for none):
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
错误原因:
mysql_secure_installation ignores –socket
解决方案:
1)ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock
2)修改mysql_secure_installation 脚本的do_query 函数(推荐处理方法)
do_query() {
echo “$1” >$command
#sed ‘s,^,> ,’ < $command # Debugging
# mysql --defaults-file=$config <$command ----此行修改如下:
mysql --defaults-extra-file=$config <$command
return $?
}
使用mysqld_safe启动msql错误
见mysqld_safe启动报错
注意:如果想把错误日志放到专门的日志目录中,需要在my.cnf中添加log-error=path选项
mysql二进制文件安装指导和my.cnf文件
mysql关于log_bin相关命令
mysql> show BINARY logs;
+—————–+———–+
| Log_name | File_size |
+—————–+———–+
| mysqlbin.000001 | 107 |
+—————–+———–+
1 row in set (0.00 sec)
–查看当前bin_log情况
mysql> show master logs;
+—————–+———–+
| Log_name | File_size |
+—————–+———–+
| mysqlbin.000001 | 107 |
+—————–+———–+
1 row in set (0.00 sec)
–查看当前bin_log情况
mysql> show variables like ‘%log_bin%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+———————————+——-+
3 rows in set (0.00 sec)
–查看log_bin相关配置
mysql> flush logs;
Query OK, 0 rows affected (0.06 sec)
–切换bin_log日志
mysql> PURGE BINARY LOGS TO ‘mysqlbin.000002’;
Query OK, 0 rows affected (0.03 sec)
–删除mysqlbin.000002之前的bin_log,并修改index中相关数据
mysql> PURGE BINARY LOGS BEFORE ‘2011-07-09 12:40:26’;
Query OK, 0 rows affected (0.04 sec)
–删除2011-07-09 12:40:26之前的bin_log,并修改index中相关数据
mysql> PURGE BINARY LOGS BEFORE ‘2011-07-09’;
Query OK, 0 rows affected (0.00 sec)
–删除2011-07-09之前的bin_log,并修改index中相关数据
mysql> set global expire_logs_days=5;
Query OK, 0 rows affected (0.00 sec)
–设置bin_log过期日期
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
–重设bin_log日志,以前的所有日志将被删除并且重设index中的数据