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

mchdba

-- ==================================
-- 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>

mchdba

-- ============================================================================================================
-- 自增字段 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.

mchdba

-- ====================================================================
--  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!

mchdba

--  安全模式启动
[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]#

jinguanding

mchdba ....继续在此贴 增加 学习笔记与感想...置顶帖中 我做了收藏链接

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

mchdba

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

mchdba

-- ========================
-- 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

mchdba

-- =================================
-- 用户的一些管理
-- =================================

-- 创建用户
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表中

mchdba

原帖由 mchdba 于 2011-9-10 05:35 发表

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

jinguanding

原帖由 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;

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