MySQL 安装配置(Mac系统)

1.安装最新的mysql

brew install mysql

2.配置文件

默认配置文件 /usr/local/etc/my.cnf
默认数据库目录 /usr/local/var/mysql

3.配置文件：~/shell/conf/mysql/3751.cnf

[client]
default-character-set=utf8mb4
port = 3306

[mysqld]
#user = dev 
port       = 3751
socket     = /Users/XXX/tmp/mysql3751.sock
pid-file   = /Users/XXX/tmp/mysql3751.pid
basedir    = /usr/local/Cellar/mysql/8.0.12
datadir    = /Users/XXX/Proj/mysql_data/ace
log_error  = /Users/XXX/tmp/error-mysql3751.log
#默认配置: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default-time-zone       = +7:00
max_connections         = 200 
wait_timeout            = 500 
key_buffer_size         = 16M 
max_allowed_packet      = 16M 
thread_stack            = 192K
thread_cache_size       = 8 
lower_case_table_names=2
default_authentication_plugin=mysql_native_password
default-storage-engine=InnoDB
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

4.初始化数据库

/usr/local/Cellar/mysql/8.0.12/bin/mysqld --initialize --datadir=/Users/XXX/Proj/mysql_data/ace --user=XXX
初始化完成后，会得到一个随机密码，记录下来，否则接下来无法登录
如：A temporary password is generated for root@localhost: -oh-JkfQQ0CG

5.修改数据库目录的拥有者和操作权限

cd ~/Proj/mysql_data/ace
sudo chown -R mysql:mysql .
chmod 750 .

6.启动后台进程

nohup mysqld --defaults-file=~/shell/conf/mysql/$1.cnf >>~/tmp/mysql_daemon.out &

7.关闭后台进程

mysqladmin -S ~/tmp/mysql3751.sock -u dev -p shutdown

8.登录实例

mysql -S ~/tmp/mysql3751.sock -uroot -p

输入上面得到的随机密码 ,登录后要求先更改密码
step 1: SET PASSWORD = PASSWORD('your new password');
 或者   alter user user() identified by ‘password';
step 2: ALTER USER 'root’@'localhost' PASSWORD EXPIRE NEVER;
step 3: flush privileges;

9.创建新用户

create user 'dev'@'%' identified by 'your_password’;

10.允许远程登录

GRANT ALL PRIVILEGES ON *.* TO 'dev'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
FLUSH  PRIVILEGES;

11.远程访问的设置 (#使用bind-address选项表示禁止远程访问)

[mysqld]
#Only allow connections from localhost
bind-address = 127.0.0.1

注意： conf文件不能用’host’ => localhost:3751的格式，要用’host’=>‘127.0.0.1:3751’
一些常用命令 mysql_secure_installation

打印配置 my_print_defaults mysqld
–defaults-file=/Users/XXX/shell/conf/mysql/3751.cnf

带 -s参数打印配置可以查看密码 my_print_defaults mysqld -s
–defaults-file=/Users/XXX/shell/conf/mysql/3751.cnf