安装mysql数据库目录
/usr/local/mysql/bin/mysql_install_db --user=mysql --datadir=/home/mysql/var ##指定安装后的数据目录
mysql 启动/关闭
/usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysql/.my.cnf --user=mysql & #启动
/usr/local/mysql/bin/mysqladmin --defaults-file=/home/mysql/.my.cnf shutdown -uroot -p #关闭
update user set password=password(‘hello‘) where user=‘root‘;
FLUSH PRIVILEGES ;
忘记root密码
启动参数增加 --skip-grant-tables
备份数据库
/usr/local/mysql/bin/mysqldump -u -p napoli > dump.sql
恢复数据库
source dump.sql
创建用户
CREATE USER xxxx IDENTIFIED BY ‘hello‘;
grant all privileges on napoli.* to ‘napoli‘@‘%‘ identified by ‘napoli‘ ; #一定要设置两条记录,一条%,一条localhost
grant all privileges on napoli.* to ‘napoli‘@‘localhost‘ identified by ‘napoli‘ ;
FLUSH PRIVILEGES ;
指定运行目录
vi my.cnf
[client]
#password = your_password
port = 3306
socket = /home/mysql/var/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
lower_case_table_names = 1
port = 3306
socket = /home/mysql/var/mysql.sock
basedir = /usr/local/mysql #mysql软件安装目录
datadir = /home/mysql/data #mysql数据文件
tmpdir = /home/mysql/tmp<span style="white-space: normal;"> </span>
mysql开启innodb storage engine支持
相关文章:
http://dev.mysql.com/doc/refman/5.1/en/replacing-builtin-innodb.html innodb plugin install
http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html innodb config
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html storage engines
[mysqld]
......
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
;innodb_trx=ha_innodb_plugin.so
;innodb_locks=ha_innodb_plugin.so
;innodb_lock_waits=ha_innodb_plugin.so
;innodb_cmp=ha_innodb_plugin.so
;innodb_cmp_reset=ha_innodb_plugin.so
;innodb_cmpmem=ha_innodb_plugin.so
;innodb_cmpmem_reset=ha_innodb_plugin.so
innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
-bash-3.2# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 232
Server version: 5.1.39-log Source distribution
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> show plugins;
+------------+--------+----------------+---------------------+---------+
| Name | Status | Type | Library | License |
+------------+--------+----------------+---------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so | GPL |
+------------+--------+----------------+---------------------+---------+
6 rows in set (0.00 sec)
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
nnodb_buffer_pool_size:这是InnoDB最重要的设置,对InnoDB性能有决定性的影响。默认的设置只有8M,所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面,可以设置60-80%的内存。更精确一点,在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。
innodb_data_file_path:指定表数据和索引存储的空间,可以是一个或者多个文件。最后一个数据文件必须是自动扩充的,也只有最后一个文件允许自动扩充。这样,当空间用完后,自动扩充数据文件就会自动增长(以8MB为单位)以容纳额外的数据。例如: innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1中,当达到900M以后,数据就放在ibdata2中。一旦达到50MB,ibdata2将以8MB为单位自动增长。如果磁盘满了,需要在另外的磁盘上面增加一个数据文件。
innodb_autoextend_increment: 默认是8M, 如果一次insert数据量比较多的话, 可以适当增加.
innodb_data_home_dir:放置表空间数据的目录,默认在mysql的数据目录,设置到和MySQL安装文件不同的分区可以提高性能。
innodb_log_file_size:该参数决定了recovery speed。太大的话recovery就会比较慢,太小了影响查询性能,一般取256M可以兼顾性能和recovery的速度
innodb_log_buffer_size:磁盘速度是很慢的,直接将log写道磁盘会影响InnoDB的性能,该参数设定了log buffer的大小,一般4M。如果有大的blob操作,可以适当增大。
innodb_flush_logs_at_trx_commit=2: 该参数设定了事务提交时内存中log信息的处理。
1) =1时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。
2) =2时,在每个事务提交时,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务,不然不会丢失事务。
3) =0时, 日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务innodb_file_per_table:可以存储每个InnoDB表和它的索引在它自己的文件中。
transaction-isolation=READ-COMITTED: 如果应用程序可以运行在READ-COMMITED隔离级别,做此设定会有一定的性能提升。
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)。-
innodb_thread_concurrency: InnoDB kernel最大的线程数。
1) 最少设置为(num_disks+num_cpus)*2。
2) 可以通过设置成1000来禁止这个限制
mysql slow查询配置
long_query_time=1
log-slow-queries=/usr/local/mysql/var/slow.log
log-queries-not-using-indexes
说明:
long_query_time设置对应slow query的阀值,默认为10
log-show-queries指定对应的日志输出路径
log-queries-not-using-indexes指定答应没有走到索引的慢查询
文档:
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html 参数配置
http://dev.mysql.com/doc/refman/5.0/en/mysqldumpslow.html 分析工具
分析工具: http://www.iteye.com/topic/242516
mysql 索引机制
文章: http://www.cnblogs.com/leoo2sk/archive/2011/07/10/mysql-index.html
mysql 优化分析
查询索引:
show index from table;
分析执行计划:
explain select * from user where user_id = ‘10‘ and user_name = ‘Xiongchang.liuxch‘;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | user | const | PRIMARY,user_name | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
统计sql执行时间:
set profiling =1;
show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------+
| 1 | 0.00065700 | select * from user |
| 2 | 0.00035900 | show index from user |
| 3 | 0.00009200 | select * from user_id = ‘10‘
本文出自 “DavideyLee” 博客,请务必保留此出处http://davideylee.blog.51cto.com/8703117/1384300