mysql问题集锦
一、下载(免安装Archive版)
下载地址：http://dev.mysql.com/downloads/mysql/
二、安装
1.解压到目录：D:/mysql-5.1.59-winx64
2.注册mysql服务，命令行跳转到D:/mysql-5.1.59-winx64/bin，执行以下命令：
D:/mysql-5.1.59-winx64/bin>mysqld install mysql Service successfully installed.
欢迎大家阅读《MySQL有关问题集锦》,跪求各位点评，by 搞代码
3.移除mysql服务，命令行跳转到D:/mysql-5.1.59-winx64/bin，执行以下命令：
D:/mysql-5.1.59-winx64/bin>mysqld remove mysql Service successfully removed.
4.启动停止mysql服务
启动：
C:/>net start mysql MySQL 服务正在启动 .. MySQL 服务已经启动成功。
停止：
C:/>net stop mysql MySQL 服务正在停止... MySQL 服务已成功停止。
5.设置服务自动启动
D:/>sc config mysql start= auto [SC] ChangeServiceConfig 成功
注：start与=号之间不能有空格，=号与auto之间必须有一个空格
三.、配置
在D:/mysql-5.1.59-winx64目录下,有5个配置文件模板, 分别为：
my-small.ini、
my-medium.ini、
my-large.ini、
my-huge.ini、
my-innodb-heavy-4G.ini
1.我们选择my-innodb-heavy-4G.ini,作为配置模板
2.将其改名为：my.ini
3.修改默认引擎：找到default-storage-engine = MYISAM 这段,将其改为default-storage-engine = INNODB
4.修改默认引擎为INNODB后，可能导致服务器启动失败：删除在MySQL安装目录下的Data目录中的ib_logfile0，ib_logfile1，ibdata1三个文件，重新启动即可
5.修改默认编码：
打开my.ini文件
[client] 段添加： default-character-set=utf8
[mysqld]段添加：character-set-server=utf8
[client] port = 3306 socket = MySQL default-character-set=utf8 [mysqld] port = 3306 socket = MySQL character-set-server=utf8
四、常用命令
1.命令行登陆：mysql -uroot -proot
D:/mysql-5.1.59-winx64/bin>mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or /g. Your MySQL connection id is 3 Server version: 5.1.59-community-log MySQL Community Server (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '/h' for help. Type '/c' to clear the current input statement. mysql>
2.查看数据库 show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec)
2.查看数据库表 use dbname; show tables;
mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.03 sec)
3.查看数据配置变量信息 show variables;
mysql> show variables like '%innodb%'; +-----------------------------------------+------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------+ | have_innodb | YES | | ignore_builtin_innodb | OFF | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 16777216 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 2147483648 | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 120 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 268435456 | | innodb_log_files_in_group | 3 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_rollback_on_timeout | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 16 | | innodb_thread_sleep_delay | 10000 | | innodb_use_legacy_cardinality_algorithm | ON | +-----------------------------------------+------------------------+ 38 rows in set (0.00 sec)
参考：http://dev.mysql.com/doc/refman/5.0/en/show-variables.html
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
4.修改用户密码
方法1：mysqladmin命令(注：USER为用户名，PASSWORD为新密码)
mysqladmin -u USER -p password PASSWORD
方法2：UPDATE user语句(注：必须现用root帐户登入mysql)
UPDATE user SET password=PASSWORD('123456') WHERE user='root'; FLUSH PRIVILEGES;
方法3：SET PASSWORD语句(注：必须现用root帐户登入mysql)
SET PASSWORD FOR root=PASSWORD('123456');
待续。。。
转载请注明原文链接：MySQL有关问题集锦
