lanbos'blog

mysql初始安装的一些问题

网上的一些教程操作mysql的要不是windows环境,或者是linux环境,在mac上搭建虽然和linux差不多,但是还是略有不同,下面是我遇到的一些问题:

密码重置

mac环境利用安全模式重置mysql root的密码:
基本的思路是,以安全模式启动mysql,这样不需要密码可以直接以root身份登录,然后重设密码。
首先,我们停掉MySQL服务:

1
mysql.server stop

以安全模式启动MySQL:

1
mysqld_safe --skip-grant-tables --skip-networking &

注意我们加了–skip-networking,避免远程无密码登录 MySQL。
这样我们就可以直接用root登录,无需密码:

1
mysql -u root

接着重设密码:

1
2
3
4
mysql> use mysql;  
mysql> update user set password=PASSWORD("newPassword") where User='root';
mysql> flush privileges;
mysql> quit

注意,命令后需要加分号。
重设完毕后,我们退出,然后启动 MySQL 服务:

1
2
mysql.server restart
mysql -u root -p

输入密码之后就进入了数据库。

默认端口修改

mac下通过brew安装mysql时是没有my.cnf配置文件的,简单的可以通过ruby一行命令来自动生成:

1
sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

当然也可以自己创建一份/etc/my.cnf,附上linux的一份my.cnf:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with
# ticks/quotes escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing
# the socket location.
[client]
port = 3306
#socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions
# are currently parsed.
[mysqld_safe]
#socket = /var/run/mysqld/mysqld.sock
#nice = 0

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
# If you make changes to these settings and your system uses
# apparmor, you may also need to also adjust
# /etc/apparmor.d/usr.sbin.mysqld.
#

#user = mysql
#socket = /var/run/mysqld/mysqld.sock
port = 3306
#basedir = /usr
datadir = /usr/local/var/mysql
#tmpdir = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

log_error = /usr/local/var/mysql/MacBook15.local.err

# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or
# for replication.
# note: if you are setting up a replication slave, see
# README.Debian about other settings you may need
# to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

# Query Caching
query-cache-type = 1

# Default to InnoDB
default-storage-engine=innodb

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

mysql常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
#查看端口号
show variables like 'port';
#创建数据库
create database name;
#查看数据库
show databases;
show create database name;
#删除数据库
drop database name;
#进入数据库
use name;
#查看当前数据库名称
select database();
#创建数据表
create table name(username varchar(20),age tinyint unsigned,salary float(9,2) unsigned);
#查看数据表
show tables;
show tables from name;
#查看条目
show columns from name;
#插入记录
insert name values('tom',25,7681.10);
#插入所选记录的内容
insert tb1(username,salary) values('john',4500.88);
#用表达式插入单挑记录指定字段(set)
instert tb1 set username='tom',salary='8000';
#查看表详细内容
select * from name;
#添加单列
alter table name add cName tinyint unsigned not null default 10;
#删除单列
alter table name drop cName;
#修改列定义(modify和change)
alter table name modify int(2) first;
alter table name change name newName int(2);
#更新记录
update name set age=10,pwd='123' where id=1;
#查询语句
select name.id,name.username form name;
#查询分组
select name.id,name.sex from name group by sex;
#查询分组条件
select name.id,name.sex from name group by sex having id>2;
#查询结果分组,前面字段若有重复按照第二字段继续排序
select * from name order by age, id desc;
#限制查询结果条目数 从0 开始后的3条
select * from name limit 0,3;
#有条件的插入其他表中的记录到当前表
insert name(age) select age from othername where age>8 limit 1,2;

mysql 约束

非空约束not null
主键约束primary key
唯一约束unique key
默认约束default
外键约束foreign key