mysql一些操作个人备忘(持续更新)-CSDN博客

安装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.  
3.[client]  
4.#password       = your_password  
5.port            = 3306  
6.socket          = /home/mysql/var/mysql.sock  
7.  
8.# Here follows entries for some specific programs  
9.  
10.# The MySQL server  
11.[mysqld]  
12.lower_case_table_names = 1  
13.port            = 3306  
14.socket          = /home/mysql/var/mysql.sock  
15.basedir         = /usr/local/mysql  #mysql软件安装目录  
16.datadir         = /home/mysql/data #mysql数据文件  
17.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

配置：

$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.  
7.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
8.  
9.mysql> show plugins;  
10.+------------+--------+----------------+---------------------+---------+  
11.| Name       | Status | Type           | Library             | License |  
12.+------------+--------+----------------+---------------------+---------+  
13.| binlog     | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  
14.| CSV        | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  
15.| MEMORY     | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  
16.| MyISAM     | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  
17.| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL                | GPL     |  
18.| InnoDB     | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so | GPL     |  
19.+------------+--------+----------------+---------------------+---------+  
20.6 rows in set (0.00 sec)  
21.  
22.mysql> show engines;  
23.+------------+---------+------------------------------------------------------------+--------------+------+------------+  
24.| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |  
25.+------------+---------+------------------------------------------------------------+--------------+------+------------+  
26.| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |  
27.| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |  
28.| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |  
29.| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |  
30.| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |  
31.+------------+---------+------------------------------------------------------------+--------------+------+------------+  
32.5 rows in set (0.00 sec)

Innodb的一些参数，备忘：

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查询配置

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://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