ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
楼主: mchdba

MySQL知识点技能点总结贴--个人版!

[复制链接]
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
31#
发表于 2011-9-9 17:57 | 只看该作者
-- ==================================
-- Innodb 2011-02-14
-- ==================================
innodb_max_dirty_pages_pct

1, How to use transaction in innodb with different APIS.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A    | B      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
32#
发表于 2011-9-9 17:58 | 只看该作者
-- ============================================================================================================
-- 自增字段 autocommit
-- ============================================================================================================

MySQL自增字段,自增字段计数器在主存储里面,不在硬盘上(This counter is stored only in main memory, not on disk)。
1,添加表,设立自增主键字段
create table t(id int primary key auto_increment, name varchar(3000)) engine=innodb;

2,可以让系统自增,也可以自己手动设置输入自增。
insert into t select 4, 'a44';
insert into t(name) select 'a8';

3,查询当前自增值
SELECT MAX(id) FROM t FOR UPDATE;

4,如果你设置的自增值超过当前最大自增值,则以你设置的自增值为准,开始自增。例如:
SELECT MAX(id) FROM t FOR UPDATE;得出值为4,然后你insert into t select 9, 'a44';那么这个时候,MAX(id)就是9,
那么下一个自增值就是16而不是5,当然了,你执行语句insert into t select 5, 'a44';也是可以通过的。

5,如果有事务的话,insert之后了rollback,autocommit里面的counter不会回滚,依然+1.

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
33#
发表于 2011-9-9 17:59 | 只看该作者
-- ====================================================================
--  mysqladmin: connect to server at 'localhost' failed的解决方案!
-- ====================================================================

[root@mysql var]# mysqladmin -u root password '123456'
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

[root@mysql var]# /etc/init.d/mysqld stop
Shutting down MySQL.... SUCCESS!

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
34#
发表于 2011-9-9 18:00 | 只看该作者
--  安全模式启动
[root@mysql var]# mysqld_safe --skip-grant-tables &
[1] 10912
[root@mysql var]# 110407 17:39:28 mysqld_safe Logging to '/usr/local/mysql/var//mysql.chinascopefinanical.com.err'.
110407 17:39:29 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var/

[root@mysql var]# mysql -uroot -p 【注释,在下面的要求你输入密码的时候,你不用管,直接回车键一敲就过去了】
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.1.41-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed

mysql> update user set password=password("guNNhtqhjUnfky6ahyVh") where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@mysql var]# mysql -uroot -pguNNhtqhjUnfky6ahyVh
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 172
Server version: 5.1.41-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit
Bye

-- 正常重新启动
[root@mysql var]# service mysqld restart
Shutting down MySQL..110407 17:45:29 mysqld_safe mysqld from pid file

/usr/local/mysql/var//mysql.chinascopefinanical.com.pid ended
SUCCESS!
Starting MySQL.. SUCCESS!
[1]+  Done                    mysqld_safe --skip-grant-tables
[root@mysql var]#

使用道具 举报

回复
论坛徽章:
52
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:32:552012新春纪念徽章
日期:2012-02-07 09:59:35
35#
发表于 2011-9-9 19:00 | 只看该作者
mchdba ....继续在此贴 增加 学习笔记与感想...置顶帖中 我做了收藏链接

这个月新星  就推荐你哦.....

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
36#
发表于 2011-9-10 05:10 | 只看该作者

回复 #35 jinguanding 的帖子

谢谢斑竹鼓励,正在看书中,有啥想法都会贴出来的。

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
37#
发表于 2011-9-10 05:23 | 只看该作者
-- ========================
-- mysql资源参数限制优化
-- ========================


Msyql参数调优
针对my.cnf文件进行优化:
[mysqld]
skip-locking(取消文件系统的外部锁)
skip-name-resolve(不进行域名反解析,注意由此带来的权限/授权问题)
  key_buffer_size = 256M(分配给MyISAM索引缓存的内存总数)  对于内存在4GB左右的服务器该参数可设置为256M或384M。  注意

:该参数值设置的过大反而会是服务器整体效率降低!
  max_allowed_packet = 4M(允许最大的包大小)
  thread_stack = 256K(每个线程的大小)
  table_cache = 128K(缓存可重用的线程数)
  back_log = 384(临时停止响应新请求前在短时间内可以堆起多少请求,如果你需要在短时间内允许大量连接,可以增加该数值)
  sort_buffer_size = 2M(分配给每个线程中处理排序)
  read_buffer_size = 2M(读取的索引缓冲区大小)
  join_buffer_size = 2M(分配给每个线程中处理扫描表连接及索引的内存)
  myisam_sort_buffer_size = 64M(myisam引擎排序缓冲区的大小)
  table_cache = 512(缓存数据表的数量,避免重复打开表的开销)
  thread_cache_size = 64(缓存可重用线程数,见笑创建新线程的开销)
  query_cache_size = 64M(控制分配给查询缓存的内存总量)
  tmp_table_size = 256M(指定mysql缓存的内存大小)
  max_connections = 768(最大连接数)指mysql整个的最大连接数
        max_connect_errors = 10000(最大连接错误数据)
  wait_timeout = 10(超时时间,可以避免攻击)
  thread_concurrency = 8(根据cpu数量来设置)
  skip-bdb 禁用不必要的引擎
  skip-networking(关闭mysql tcp/ip连接方式)
  Log-slow-queries = /var/log/mysqlslowqueries.log
  long_query_time = 4(设定慢查询的时间)
  skip-host-cache(提高mysql速度的)
  open_files_limit = 4096(打开文件数)
interactive_timeout = 10(服务器在关闭它前在一个交互连接上等待行动的秒数)
max_user_connections = 500(最大用户连接数)  

Mysql内存使用公式:实际物理内存计算公式key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections  

MAX_QUERIES_PER_HOUR 用来限制用户每小时运行的查询数量:
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_connections_per_hour 5;
(db用户在dbname的数据库上限制用户每小时打开新连接的数量为5个)


MAX_USER_CONNECTIONS 限制有多少用户连接MYSQL服务器:
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_user_connections 2;
(db用户在dbname的数据库账户一次可以同时连接的最大连接数为2个)

MAX_UPDATES_PER_HOUR 用来限制用户每小时的修改数据库数据的数量:
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_updates_per_hour 5;
(db用户在dbname的数据库上限制用户每小时修改更新数据库的次数为5次)
MAX_USER_CONNECTIONS 用来限制用户每小时的修改数据库数据的数量:
mysql> grant all on dbname。* to db@localhost identified by “123456”
With  MAX_QUERIES_PER_HOUR 20 ;指mysql单个用户的最大连接数
(db用户在dbname的数据库上限制用户每小时的连接数为20个)


====================================================================================================
一般应用my-medium.cnf就足够了, 独立主机如果内存较大可以用my-large.cnf, 内存超过8G的可以用my-huge.cnf

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
38#
发表于 2011-9-10 05:35 | 只看该作者
-- =================================
-- 用户的一些管理
-- =================================

-- 创建用户
CREATE USER skyman@'192.168.0.90' IDENTIFIED BY 'skyman';
GRANT ALL PRIVILEGES ON *.* TO skyman@'192.168.0.90';
flush privileges;

-- 创建用户
CREATE USER man@localhost IDENTIFIED BY 'skyman';
GRANT ALL PRIVILEGES ON test.* TO man@localhost;
grant connect to man@localhost;
flush privileges;
grant select,insert,delete,update on accesslog.accesslog to man@localhost  identified by 'skyman';
grant select,insert,delete,update on test.* to man@localhost identified by 'skyman';
grant connection to man@localhost identified by 'skyman';

select * from accesslog.accesslog;


--------------------------
CREATE USER man1@localhost IDENTIFIED BY 'man1';
GRANT ALL ON test.* TO man1@localhost;
GRANT ALL ON accesslog.accesslog TO man1@localhost;
flush privileges;


----
grant all privileges on *.* to mysql@'192.168.0.90'  identified by 'mysql';

--忘记密码了,重新赋予权限
grant all privileges on *.* to root@'192.168.0.90'  identified by 'mysql';

grant select,insert,delete on test.* to skyman@'192.168.0.90'  identified by 'mysql';

--授予权限
1 如果授予所有库的权限,那么权限数据就存在于 select * from mysql.user;
  grant update(code) on  *.* to c0@'192.168.0.90'  identified by 'mysql';

2 如果授予单个库的权限,那么权限数据就存在于 select * from mysql.db;
  grant update(code) on  test.* to c1@'192.168.0.90'  identified by 'mysql';

3 如果授予单个库单个表的权限,那么权限数据就存在于 select * from mysql.tables_priv;
  grant update on  test.code to c2@'192.168.0.90'  identified by 'mysql';

4 如果授予单库单表单列的权限,那么权限数据就存在于  select * from mysql.columns_priv;
  grant update(code) on  test.code to c3@'192.168.0.90'  identified by 'mysql';


最后,查询下select * from mysql.host;【】每次查询都是空,无数据。

--删除用户
drop user c1@'192.168.0.90';【】不建议delete from tb的形式删除用户。

--用户权限级别
1 Global Level
  所有权限信息保存在mysql.user表中。
2 Database Level
  所有权限信息保存在mysql.db表中。
3 Table Level
  所有权限信息保存在mysql.tables_priv表中。
4 Column Level
  所有权限信息保存在mysql.columns_priv表中

使用道具 举报

回复
认证徽章
论坛徽章:
27
优秀写手
日期:2013-12-18 09:29:09ITPUB季度 技术新星
日期:2012-08-15 14:50:13嫦娥
日期:2013-03-04 09:47:05数据库板块每日发贴之星
日期:2011-09-08 01:01:01数据库板块每日发贴之星
日期:2011-09-11 01:01:01数据库板块每日发贴之星
日期:2011-09-10 01:01:02数据库板块每日发贴之星
日期:2011-09-09 01:01:01ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59ITPUB 11周年纪念徽章
日期:2012-09-28 17:34:422013年新春福章
日期:2013-03-04 09:50:49
39#
发表于 2011-9-13 09:08 | 只看该作者
原帖由 mchdba 于 2011-9-10 05:35 发表


今天来上班,第一次登录公司qa数据库,执行sql_no_cache 查询 innodb表总记录数 6913041 。发现,第一次需要33秒,第二次缓存不到一秒,再接下来用sql_no_cache
每次都只需要2.5秒左右,这是为什么,时间差距这么大,难道sql_no_cache没有起作用吗?记录下来,好好研究下。

select_count(1)_2.jpg (169.33 KB, 下载次数: 27)

select_count(1)_2.jpg

使用道具 举报

回复
论坛徽章:
52
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:33:22生肖徽章2007版:龙
日期:2012-02-07 10:32:552012新春纪念徽章
日期:2012-02-07 09:59:35
40#
发表于 2011-9-13 10:00 | 只看该作者
原帖由 mchdba 于 2011-9-13 09:08 发表


今天来上班,第一次登录公司qa数据库,执行sql_no_cache 查询 innodb表总记录数 6913041 。发现,第一次需要33秒,第二次缓存不到一秒,再接下来用sql_no_cache
每次都只需要2.5秒左右,这是为什么,时间差距这么大,难道sql_no_cache没有起作用吗?记录下来,好好研究下。





1.第一次执行的时候,数据已经缓存到innodb_buffer_pool_size
2.第二次,为1S不解 --- 可能数据依然全部缓存在内存中

3.第三次,你再查询 -- 可能部分数据被踢出了 innodb_buffer_pool_size,但是应该没有读磁盘...而可能是文件系统的缓存


你要清理的话,得借助FLUSH TABLES; 把数据刷回磁盘上.....要是担心查询缓存,可以先执行FLUSH QUERY CAHCE;

生产环境慎重,测试环境无所谓!!!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
http://www.vxiaotou.com