MySQL有关问题集锦

mysql问题集锦

一、下载(免安装Archive版)

下载地址：http://dev.mysql.com/downloads/mysql/

MySQL有关问题集锦

二、安装

1.解压到目录：D:/mysql-5.1.59-winx64

2.注册mysql服务，命令行跳转到D:/mysql-5.1.59-winx64/bin，执行以下命令：

D:/mysql-5.1.59-winx64/bin&gt;mysqld install mysql Service successfully installed.

1	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&gt;mysqld remove mysql Service successfully removed.

1	D:/mysql-5.1.59-winx64/bin>mysqld remove mysql Service successfully removed.

4.启动停止mysql服务

启动：

C:/&gt;net start mysql MySQL 服务正在启动 .. MySQL 服务已经启动成功。

1	C:/>net start mysql MySQL 服务正在启动 .. MySQL 服务已经启动成功。

停止：

C:/&gt;net stop mysql MySQL 服务正在停止... MySQL 服务已成功停止。

1	C:/>net stop mysql MySQL 服务正在停止... MySQL 服务已成功停止。

5.设置服务自动启动

D:/&gt;sc config mysql start= auto [SC] ChangeServiceConfig 成功

1	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	[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&gt;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&gt;

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&gt; show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | test               | +--------------------+ 3 rows in set (0.00 sec)

1	mysql> show databases; +--------------------+ \| Database \| +--------------------+ \| information_schema \| \| mysql \| \| test \| +--------------------+ 3 rows in set (0.00 sec)

2.查看数据库表 use dbname; show tables;

mysql&gt; use mysql; Database changed mysql&gt; 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&gt; 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)

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

1	mysqladmin -u USER -p password PASSWORD

方法2：UPDATE user语句(注：必须现用root帐户登入mysql)

UPDATE user SET password=PASSWORD('123456') WHERE user='root';  FLUSH PRIVILEGES;

1	UPDATE user SET password=PASSWORD('123456') WHERE user='root'; FLUSH PRIVILEGES;

方法3：SET PASSWORD语句(注：必须现用root帐户登入mysql)

SET PASSWORD FOR root=PASSWORD('123456');

1	SET PASSWORD FOR root=PASSWORD('123456');

待续。。。

原创文章，转载请注明： 转载自搞代码

本文链接地址: MySQL有关问题集锦

分享到： QQ好友微信 QQ空间新浪微博印象笔记轻笔记有道云笔记

微信赏一包辣条吧~

支付宝赏一听可乐吧~

0条评论

搞代码