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 mysqlService successfully installed.
3.移除mysql服务,命令行跳转到D:\mysql-5.1.59-winx64\bin,执行以下命令:
D:\mysql-5.1.59-winx64\bin>mysqld remove mysqlService successfully removed.
?
4.启动停止mysql服务
??? 启动:
C:\>net start mysqlMySQL 服务正在启动 ..MySQL 服务已经启动成功。
?
??? 停止:
C:\>net stop mysqlMySQL 服务正在停止...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= 3306socket= MySQLdefault-character-set=utf8[mysqld]port= 3306socket= MySQLcharacter-set-server=utf8
?
?????????
?
?
四、常用命令1.命令行登陆:mysql -uroot -proot
D:\mysql-5.1.59-winx64\bin>mysql -uroot -prootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 changedmysql> 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');
?
待续。。。