MySQL数据库操作个人备忘(持续更新)

安装mysql数据库目录

  1. /usr/local/mysql/bin/mysql_install_db --user=mysql --datadir=/home/mysql/var ##指定安装后的数据目录  


mysql 启动/关闭

  1. /usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/.my.cnf --user=mysql &  #启动  

  2. /usr/local/mysql/bin/mysqladmin --defaults-file=/home/mysql/.my.cnf shutdown   -uroot -p #关闭  

更改root密码
  1. update user set password=password(‘hello‘) where user=‘root‘;    

  2. FLUSH PRIVILEGES ;  

忘记root密码

  1. 启动参数增加 --skip-grant-tables  


备份数据库


  1. /usr/local/mysql/bin/mysqldump -u -p  napoli > dump.sql  


恢复数据库


  1. source dump.sql  


创建用户


  1. CREATE USER xxxx IDENTIFIED BY ‘hello‘;  

  2. grant all privileges on napoli.* to ‘napoli‘@‘%‘ identified by ‘napoli‘ ;  #一定要设置两条记录,一条%,一条localhost  

  3. grant all privileges on napoli.* to ‘napoli‘@‘localhost‘ identified by ‘napoli‘ ;  

  4. FLUSH PRIVILEGES ;  


指定运行目录


  1. vi my.cnf  

  2. [client]  

  3. #password       = your_password  

  4. port            = 3306

  5. socket          = /home/mysql/var/mysql.sock  

  6. # Here follows entries for some specific programs  

  7. # The MySQL server  

  8. [mysqld]  

  9. lower_case_table_names = 1

  10. port            = 3306

  11. socket          = /home/mysql/var/mysql.sock  

  12. basedir         = /usr/local/mysql  #mysql软件安装目录  

  13. datadir         = /home/mysql/data #mysql数据文件  

  14. tmpdir          = /home/mysql/tmp<span style="white-space: normal;"> </span>  

mysql开启innodb storage engine支持

相关文章:

配置:
$vi /etc/my.cnf
  1. [mysqld]  

  2. ......  

  3. ignore-builtin-innodb  

  4. plugin-load=innodb=ha_innodb_plugin.so  

  5.  ;innodb_trx=ha_innodb_plugin.so  

  6.  ;innodb_locks=ha_innodb_plugin.so  

  7.  ;innodb_lock_waits=ha_innodb_plugin.so  

  8.  ;innodb_cmp=ha_innodb_plugin.so  

  9.  ;innodb_cmp_reset=ha_innodb_plugin.so  

  10.  ;innodb_cmpmem=ha_innodb_plugin.so  

  11.  ;innodb_cmpmem_reset=ha_innodb_plugin.so  

  12. innodb_data_home_dir = /usr/local/mysql/var/  

  13. innodb_data_file_path = ibdata1:10M:autoextend  

  14. innodb_log_group_home_dir = /usr/local/mysql/var/  

  15. # You can set .._buffer_pool_size up to 50 - 80 %  

  16. # of RAM but beware of setting memory usage too high  

  17. innodb_buffer_pool_size = 16M  

  18. innodb_additional_mem_pool_size = 2M  

  19. # Set .._log_file_size to 25 % of buffer pool size  

  20. innodb_log_file_size = 5M  

  21. innodb_log_buffer_size = 8M  

  22. innodb_flush_log_at_trx_commit = 1

  23. innodb_lock_wait_timeout = 50

查看
  1. -bash-3.2# mysql -uroot -p  

  2. Enter password:  

  3. Welcome to the MySQL monitor.  Commands end with ; or \g.  

  4. Your MySQL connection id is 232

  5. Server version: 5.1.39-log Source distribution  

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

  7. mysql> show plugins;  

  8. +------------+--------+----------------+---------------------+---------+  

  9. | Name       | Status | Type           | Library             | License |  

  10. +------------+--------+----------------+---------------------+---------+  

  11. | binlog     | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  

  12. | CSV        | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  

  13. | MEMORY     | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  

  14. | MyISAM     | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  

  15. | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  

  16. | InnoDB     | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so | GPL     |  

  17. +------------+--------+----------------+---------------------+---------+  

  18. 6 rows in set (0.00 sec)  

  19. mysql> show engines;  

  20. +------------+---------+------------------------------------------------------------+--------------+------+------------+  

  21. | Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |  

  22. +------------+---------+------------------------------------------------------------+--------------+------+------------+  

  23. | CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |  

  24. | InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |  

  25. | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |  

  26. | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |  

  27. | MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |  

  28. +------------+---------+------------------------------------------------------------+--------------+------+------------+  

  29. 5 rows in set (0.00 sec)  

Innodb的一些参数,备忘:
  1. nnodb_buffer_pool_size:这是InnoDB最重要的设置,对InnoDB性能有决定性的影响。默认的设置只有8M,所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面,可以设置60-80%的内存。更精确一点,在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。

  2. innodb_data_file_path:指定表数据和索引存储的空间,可以是一个或者多个文件。最后一个数据文件必须是自动扩充的,也只有最后一个文件允许自动扩充。这样,当空间用完后,自动扩充数据文件就会自动增长(以8MB为单位)以容纳额外的数据。例如: innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1中,当达到900M以后,数据就放在ibdata2中。一旦达到50MB,ibdata2将以8MB为单位自动增长。如果磁盘满了,需要在另外的磁盘上面增加一个数据文件。

  3. innodb_autoextend_increment: 默认是8M, 如果一次insert数据量比较多的话, 可以适当增加.

  4. innodb_data_home_dir:放置表空间数据的目录,默认在mysql的数据目录,设置到和MySQL安装文件不同的分区可以提高性能。

  5. innodb_log_file_size:该参数决定了recovery speed。太大的话recovery就会比较慢,太小了影响查询性能,一般取256M可以兼顾性能和recovery的速度

  6. innodb_log_buffer_size:磁盘速度是很慢的,直接将log写道磁盘会影响InnoDB的性能,该参数设定了log buffer的大小,一般4M。如果有大的blob操作,可以适当增大。

  7. innodb_flush_logs_at_trx_commit=2: 该参数设定了事务提交时内存中log信息的处理。
    1) =1时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。
    2) =2时,在每个事务提交时,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务,不然不会丢失事务。
    3) =0时, 日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务

  8. innodb_file_per_table:可以存储每个InnoDB表和它的索引在它自己的文件中。

  9. transaction-isolation=READ-COMITTED: 如果应用程序可以运行在READ-COMMITED隔离级别,做此设定会有一定的性能提升。

  10. innodb_flush_method: 设置InnoDB同步IO的方式:
    1) Default – 使用fsync()。
    2) O_SYNC 以sync模式打开文件,通常比较慢。
    3) O_DIRECT,在Linux上使用Direct IO。可以显著提高速度,特别是在RAID系统上。避免额外的数据复制和double buffering(mysql buffering 和OS buffering)。

  11. innodb_thread_concurrency: InnoDB kernel最大的线程数。
    1) 最少设置为(num_disks+num_cpus)*2。
    2) 可以通过设置成1000来禁止这个限制


mysql slow查询配置

  1. long_query_time=1

  2. log-slow-queries=/usr/local/mysql/var/slow.log  

  3. log-queries-not-using-indexes  

说明:

  • long_query_time设置对应slow query的阀值,默认为10

  • log-show-queries指定对应的日志输出路径

  • log-queries-not-using-indexes指定答应没有走到索引的慢查询

文档:


分析工具: http://www.iteye.com/topic/242516


mysql 索引机制

文章: http://www.cnblogs.com/leoo2sk/archive/2011/07/10/mysql-index.html


mysql 优化分析


查询索引:


  1. show index from table;  


分析执行计划:


  1. explain select * from user where user_id = ‘10‘ and user_name = ‘Xiongchang.liuxch‘;  

  2. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+  

  3. | id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |  

  4. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+  

  5. |  1 | SIMPLE      | user  | const | PRIMARY,user_name | PRIMARY | 4       | const |    1 |       |  

  6. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+  

说明: key_len为数据库索引的最左前缀匹配的长度


统计sql执行时间:

设置开启profile:
  1. set profiling =1;  

查询统计结果:
  1. show profiles;  

  2. +----------+------------+-------------------------------------------+  

  3. | Query_ID | Duration   | Query                                                                               |  

  4. +----------+------------+------------------------------------------------+  

  5. |        1 | 0.00065700 | select * from user                                                                  |  

  6. |        2 | 0.00035900 | show index from user                                                                |  

  7. |        3 | 0.00009200 | select * from user_id = ‘10‘        


本文出自 “DavideyLee” 博客,请务必保留此出处http://davideylee.blog.51cto.com/8703117/1384300

MySQL数据库操作个人备忘(持续更新),布布扣,bubuko.com

MySQL数据库操作个人备忘(持续更新)

上一篇:ORA-20079: full resync from primary database is not done


下一篇:基于pacemaker和drbd提供mysql高可用